python操作数据库

import pymysql

#打开连接数据库
db = pymysql.connect("localhost","root","12345","graduationdesigndb",charset='utf8')

#使用cursor()方法获取操作游标
cursor = db.cursor()
#使用execute（）方法执行SQL，如果表真的存在则删除
cursor.execute("DROP TABLE IF EXISTS books")
#使用sql预处理语句创建表
sql = """
CREATE TABLE books(
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY (id)
)ENGINE=MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
"""
#数据列表
# data = [("零基础学Python",'Python','79.80','2018-5-20'),
#         ("Python从入门到精通",'Python','69.80','2018-6-18'),
#         ("零基础学PHP",'PHP','69.8','2017-5-21'),
#         ("PHP项目开发项目实战入门",'PHP','79.80','2016-5-21'),
#         ("零基础学JAVA",'IAVA','69.80','2017-5-21'),]
# try:
#     #执行sql语句，插入多条数据
#     result=cursor.executemany("insert into books (name, category, price, publish_time) values (%s,%s,%s,%s)",data)
#     print(result)
#     #提交数据
#     db.commit()
# except:
#     #发生错误回滚
#     db.rollback()
#关闭数据库连接
cursor.execute(sql)
db.close()