python mysqldb pymysql_python连接mysql数据库之MySQLdb/pymysql

importMySQLdb#连接数据库 connect方法,参数有host,port,user,password,[db],charset

'''创建数据库'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

charset="utf8")

cursor= db.cursor() #新建游标

dbName = "newdb"sql= "create database %s"%dbName

cursor.execute(sql)

db.close()

'''创建数据表'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql= 'create table student_info(id int,name varchar(10),address varchar(255),tel int(10))'cursor.execute(sql)

db.close()'''插入数据'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql1= "insert into student_info(id,name,address,tel) values(1000002,'tttttshy','滨海大道1001',1388888888)"sql2= "insert into student_info(id,name,address,tel) values(1000005,'dddddnb','扬帆大道1010',1338888888)"

try:

cursor.execute(sql1)

cursor.execute(sql2)

db.commit()#提交操作事务 有内容的修改一定要commit

except:print("error")

db.rollback()#回滚事务

db.close()'''查询数据'''

#fetchone(): 该方法获取下一个查询结果集。结果集是一个对象#fetchall():接收全部的返回结果行.#rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql= "select * from student_info"cursor.execute(sql)

result=cursor.fetchall()for i inresult:print(i)

db.close()'''修改数据'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql= "update student_info set name='nnnnnning' where name like 'dddddnb'"

try:

cursor.execute(sql)

db.commit()except:print("error")

db.rollback()

db.close()'''删除数据'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql= 'delete from student_info where name like "nnnnnning"'

try:

cursor.execute(sql)

db.commit()except:print("error")

db.rollback()

db.close()'''删除数据表'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql= 'drop table student_info'

try:

cursor.execute(sql)

db.commit()except:print("error")

db.rollback()

db.close()'''删除数据库'''db= MySQLdb.connect(host="localhost",

port=3306,

user="root",

password="123456",

db='newdb',

charset="utf8")

cursor=db.cursor()

sql= 'drop database newdb'

try:

cursor.execute(sql)

db.commit()except:print("error")

db.rollback()

db.close()