操作mysql数据库

操作mysql数据库

新增，删除，修改，查询

实例：books表中添加数据

1：新增一条记录：

import pymysql
db = pymysql.connect(host = 'localhost',user = 'root', password = 'root',database= 'mrsoft',charset = 'utf8')
cursor = db.cursor()
data = ('python书名','python分类','128.36','2018-11-11')
sql = 'insert intobooks(name,category,price,publish_time) values(%s,%s,%s,%s)'
cursor.execute(sql,data)
cursor.close()
db.close()

2：新增多条记录：

import pymysql
db = pymysql.connect(host = 'localhost',user = 'root', password = 'root',database= 'mrsoft',charset = 'utf8')
cursor = db.cursor()
data = [('语文','教学','15.80','2015-1-01'),
        ('数学','教学','16.80','2016-1-01'),
        ('英语','教学','17.80','2017-1-01'),
        ('自然','教学','18.80','2018-1-01'),
       ]
sql = 'insert intobooks(name,category,price,publish_time) values(%s,%s,%s,%s)'
cursor.executemany(sql,data)
cursor.close()
db.close()

3：我们可以增加try..except语句，如果正确就提交，如果错误就回滚，代码如下：

import pymysql
db = pymysql.connect(host = 'localhost',user = 'root', password = 'root',database= 'mrsoft',charset = 'utf8')
cursor = db.cursor()
data = [('语文','教学','15.80','2015-1-01'),
        ('数学','教学','16.80','2016-1-01'),
        ('英语','教学','17.80','2017-1-01'),
        ('自然','教学','18.80','2018-1-01'),
       ]
try:
    sql = 'insert intobooks(name,category,price,publish_time) values(%s,%s,%s,%s)'
    cursor.executemany(sql,data)
    db.commit()
except:
    db.rollback()
cursor.close()
db.close()