【转】使用pymysql库来操作mysql

import pymysql

#1、查询mysql版本和创建数据库spiders
db = pymysql.connect(host='localhost',user='root',password = '',port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER  SET utf8")
db.close()

#2、创建表
db = pymysql.connect(host='localhost',user='root',password = '',port=3306,db='spiders')
cursor = db.cursor()
sql = 'create table if not exists students (id varchar(255) not null, name varchar(255) not null, age int not null, primary key (id))'
cursor.execute(sql)
db.close()

#3、插入数据
id = '20120001'
user = 'Bob'
age = 20
db = pymysql.connect(host='localhost',user='root',password = '',port=3306,db='spiders')
cursor = db.cursor()
sql = 'insert into students(id, name, age) value(%s, %s, %s)'
try:
    cursor.execute(sql, (id, user, age))
    db.commit() #这里需要关注，增删改必须commit()后才能生效
except:
    db.rollback()
db.close()

#4、插入数据（better）
db = pymysql.connect(host='localhost',user='root',password = '',port=3306,db='spiders')
cursor = db.cursor()
mydatas = [{'id': '20120001','name': 'Bob','age': 20},{'id': '20120002','name': 'Cyber','age': 31},{'id': '20120003','name': 'sallice','age': 31}, {'id': '20120004','name': 'david','age': 5}]
for i in range(len(mydatas)):
    data = mydatas[i]
    table = 'students'
    keys = ', '.join(data.keys())
    values = ', '.join(['%s'] * len(data))
    sql = 'insert into {table}({keys}) values ({values})'.format(table=table, keys=keys, values=values)
    try:
        if cursor.execute(sql, tuple(data.values())):
            print('Successful')
            db.commit()
    except:
        print('Failed')
        db.rollback()
db.close()

#5、更新数据（支持去重）
db = pymysql.connect(host='localhost',user='root',password = '',port=3306,db='spiders')
cursor = db.cursor()
data = {
    'id': '20120001',
    'name': 'Cyber',
    'age': 31
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
#下面语句中的'on duplicate key update'的作用是：如果主键已经存在，就执行更新操作
sql = 'insert into {table}({keys}) values ({values}) on duplicate key update '.format(table=table, keys=keys, values=values)
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql +=update
#mysql中标准的语句应该是：insert into students(id, name, age) values (%s,%s,%s) on duplicate key update id = %s,name = %s, age= %s
try:
    if cursor.execute(sql, tuple(data.values())*2):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

#6、删除数据
db = pymysql.connect(host='localhost',user='root',password = '',port=3306,db='spiders')
cursor = db.cursor()
table = 'students'
condition = 'age > 20'
sql = 'delete from {table} where {condition}'.format(table = table, condition = condition)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

#7、查询数据
db = pymysql.connect(host='localhost',user='root',password = '',port=3306,db='spiders')
cursor = db.cursor()
sql = 'select * from students where age >=25'
try:
    cursor.execute(sql)
    print('Count:', cursor.rowcount)
    # fetchone()的作用是获得结果的第一条数据，这一条语句执行后，偏移指针到了第二行，会导致下面fetchall()执行的语句其实从第二行开始的，获得的结果也不全
    '''
    one = cursor.fetchone()
    print('One:', one)
    '''
    #fetchall()的作用是获得所有数据，但是返回的结果是元组形式，如果数据量大占用的开销会非常高，所以并不建议
    '''
    results = cursor.fetchall()
    print('Results:', results)
    print('Results Type:', type(results))
    for row in results:
        print(row)
    '''
    #可以使用fetchone()来逐条偏移数据同时获取数据
    row = cursor.fetchone()
    while row:
        print(row)
        row = cursor.fetchone()
except:
    print('Error')
db.close()