Python操作MySQL

from mysql.connector import connect

# 连接到服务器
print('连接到mysql服务器...')
db = connect(user="root", passwd="666666", database="talent_directory", use_unicode=True)
print('连接上了!')

# 创建表
cursor = db.cursor()  # 使用cursor()方法获取操作游标
cursor.execute("DROP TABLE IF EXISTS Student")  # 如果存在表Sutdent先删除
cursor.execute("CREATE TABLE Student(ID CHAR(10) NOT NULL, Name CHAR(8), Grade INT )")

# 往表里插入数据
try:
    cursor.execute("""INSERT INTO Student
         VALUES ('001', 'CZQ', 70),
                ('002', 'LHQ', 80),
                ('003', 'MQ', 90),
                ('004', 'WH', 80),
                ('005', 'HP', 70),
                ('006', 'YF', 66),
                ('007', 'TEST', 100)""")
    db.commit()
except Exception as e:
    print("插入数据失败{}".format(e))
    db.rollback()

# 查询数据
try:
    cursor.execute("SELECT * FROM Student")
    results = cursor.fetchall()
    for row in results:
        id_, name, grade = row
        print(id_, name, grade)
except Exception as e:
    print("无法查询到数据{}".format(e))

db.close()  # 最后一定要记得关闭连接
# def deletedb(db):
#     # 使用cursor()方法获取操作游标
#     cursor = db.cursor()
#
#     # SQL 删除语句
#     sql = "DELETE FROM Student WHERE Grade = '%d'" % (100)
#
#     try:
#         # 执行SQL语句
#         cursor.execute(sql)
#         # 提交修改
#         db.commit()
#     except:
#         print('删除数据失败!')
#         # 发生错误时回滚
#         db.rollback()
#
#
# def updatedb(db):
#     # 使用cursor()方法获取操作游标
#     cursor = db.cursor()
#
#     # SQL 更新语句
#     sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s'" % '003'
#
#     try:
#         # 执行SQL语句
#         cursor.execute(sql)
#         # 提交到数据库执行
#         db.commit()
#     except:
#         print('更新数据失败!')
#         # 发生错误时回滚
#         db.rollback()
#
#
# def closedb(db):
#     db.close()
#