python与Mysql的交互

1.链接数据库import pymysql# 连接数据库# 参数1：mysql服务所在主机的IP# 参数2：用户名# 参数3：密码# 参数4：要连接的数据库名#db = pymysql.connect("localhost", "root", "sunck", "kaige")db = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")# 创建一个cursor对象cursor = db.cursor()sql = "select version()"#执行sql语句cursor.execute(sql)#获取返回的信息data = cursor.fetchone()print(data)#断开cursor.close()db.close()2.创建数据库表import pymysqldb = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")cursor = db.cursor()# 检查表是否存在，如果存则删除cursor.execute("drop table if exists bandcard")#建表sql = 'create table bandcard(id int auto_increment primary key, money int not null)'cursor.execute(sql)cursor.close()db.close()3.数据库插入数据import pymysqldb = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")cursor = db.cursor()sql = 'insert into bandcard values(0, 300),(0, 400),(0, 500),(0, 600),(0, 700)'try:    cursor.execute(sql)    db.commit()except:    # 如果提交失败，回滚到上一次数据    db.rollback()cursor.close()db.close()4.数据库更新操作import pymysqldb = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")cursor = db.cursor()sql = 'update bandcard set money=1000 where id=1'try:    cursor.execute(sql)    db.commit()except:    # 如果提交失败，回滚到上一次数据    db.rollback()cursor.close()db.close()5数据库删除操作import pymysqldb = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")cursor = db.cursor()# 检查表是否存在，如果存则删除cursor.execute("drop table if exists bandcard")#建表sql = 'create table bandcard(id int auto_increment primary key, money int not null)'cursor.execute(sql)cursor.close()db.close()6.数据库查询操作'''fetchone()功能：获取下一个查询结果集，结果集是一个对象fetchall()功能：接收全部的返回的行rowcount:是一个只读属性，返回execute()方法影响的行数'''import pymysqldb = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")cursor = db.cursor()sql = 'select * from bandcard where money>400'try:    cursor.execute(sql)    reslist = cursor.fetchall()    for row in reslist:        print("%d--%d" % (row[0], row[1]))except:    # 如果提交失败，回滚到上一次数据    db.rollback()cursor.close()db.close()