import pymysql
#打开数据库连接
db = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="aaa",
db="test",
charset = "utf-8")
#游标
cursor = db.cursor()
cursor.execute("select version()")
data= cursor.fetchone()
print(data)
db.close()
#使用execute()方法执行sql, 如果表存在则删除
cursor.execute("drop table if exists user")
#使用预处理语句创建表
sql = """create table user(
first_name char(20) not null,
last_name char(20),
age int,
sex char(1),
income float)"""
cursor.execute(sql)
#sql 插入语句
sql = "insert into user (first_name, last_name, age, sex, income)\
values('%s','%s','%s','%s','%s')"%\
('mac', 'mohan', 20, 'm', 2000)
try:
#执行sql语句
cursor.execute(sql)
#执行sql语句
db.commit()
except:
#发生错误时回滚
db.rollback()
#关闭数据库连接
db.close()
-------
sql = "select * from user where income > %s"%(1000)
try:
cursor.execute(sql)
results = cursor.fetchall()
print(results)
except:
print("error: unable to fetch data")
db.rollback()
-----
sql = "delete from user where age > %s"%(20)
try:
#执行sql语句
cursor.execute(sql)
#向数据提交
db.commit()
except:
#发生错误时回滚
db.rollback()
commit()方法游标的所有更新操作,rollback()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事物。