import pymysql
# 连接数据库
db = pymysql.connect(
host='localhost',
user='test',
password="liang4",
port=3306,
database='test',
autocommit=False
)
# 创建游标
cursor = db.cursor()
# # 创建表格student-(name, sex, age, score)
# cursor.execute('drop table if EXISTS student') # 如果存在则删除student
# sql = '''
# create table `student` (
# num int(8) primary key auto_increment,
# name varchar(30) not null,
# sex varchar(5),
# age int(2),
# score float(3,1)
# )
# '''
# cursor.execute(sql)
# # 插入单条数据
# sql = '''
# insert into student (name, sex, age, score) values ('liang', 'girl', 25, 60)
# '''
# cursor.execute(sql)
# db.commit() # 提交事务，必须加这个
# # 插入多条数据
# sql = '''
# insert into student (name, sex, age, score) values (%s, %s, %s, %s)
# '''
# add_data_list = [
# ('wang', 'boy', 24, 59),
# ('yuan', 'girl', 26, 100) # 100输出99.9，因为前面设置的score float(3,1)
# ]
# cursor.executemany(sql, add_data_list)
# db.commit()
# # 数据表更新
# sql = '''
# update student set score=%s where num=%s
# '''
# cursor.execute(sql, (90, 1))
# db.commit()
# # 数据表删除
# sql = '''
# delete from student where num=2
# '''
# try:
# cursor.execute(sql)
# db.commit()
# print('succeed!')
# except:
# # 如果出现异常，回滚（要求autocommit=False，autocommit在pymysql.connect中设置）
# db.rollback()
# print('failed!')
# finally:
# cursor.close()
# db.close()
# # 查询数据表
# sql = '''
# select * from student where age=25
# '''
# try:
# cursor.execute(sql)
# result = cursor.fetchall() # 把所有行都取出来
# print(type(result))
# print(result)
# for row in result:
# num = row[0]
# name = row[1]
# sex = row[2]
# age = row[3]
# score = row[4]
# print('num:', num, 'name:', name, 'sex:', sex, 'age:', age, 'score:', score)
# except Exception as e:
# print(e)
# print('failed')
# finally:
# cursor.close()
# db.close()
# 关闭游标,关闭数据库连接
cursor.close()
db.close()
感谢这位博主提供很多有效的帮助
https://blog.csdn.net/u011027547/article/details/122520529
