mySQL储存（pymysql库）

创建数据库

import pymysql
# 打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306)
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute('SELECT VERSION()')
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print(data)
# 创建数据库
# str = "CREATE DATABASE spiders DEFAULT CHARACTER SET utf8"
# cursor.execute(str)
db.close()

创建表

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',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()

插入数据

import pymysql

id = '20120001'
user = 'Bob'
age = 20

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO students (id,name,age) values(%s,%s,%s)'

try:
    cursor.execute(sql,(id,user,age))
    #真正将语句提交到数据库执行方法
    db.commit()
except:
    #执行数据回滚
    db.rollback()
cursor.execute('SELECT * FROM STUDENTS')
data = cursor.fetchone()
print(data)
db.close()

插入、更新和删除都是对数据库进行更改的操作，而更改操作都必须为一个事务。
标准写法：

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

插入数据优化

import pymysql
import json

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
print('Database Version:',cursor.fetchone())
"""
当表中字段增加时由（id,name,age）增加到（id,name,age,gender）
原来写法：
id = '20120001'
user = 'Bob'
age = 20
gender = '男'

sql = 'INSERT INTO students (id,name,age) values(%s,%s,%s)'
改为：
sql = 'INSERT INTO students (id,name,age,gender) values(%s,%s,%s,%s)'
tyr:
    cursor.excutr(sql,(id,user,age,gender))
    db.commit()
except:
    db.rollback
当如果字段比较多时，此种写法比较麻烦。
"""
#优化 构造动态化的字典
data = {
    'id':'20120001',
    'name':'Bob',
    'age':20,
    'gender':'男'
}
table = 'students'
keys =','.join(data.keys())
#构造占位符
values =','.join(['%s'] * len(data))
# print(key)
# print(values)
# 旧写法：sql = 'INSERT INTO students (id,name,age,gender) values(%s,%s,%s,%s)'
sql = 'INSERT INTO {table}({keys}) values ({values})'.format(table=table,keys=keys,values=values)
try:
    print(sql)
    print(data.values())
    if cursor.execute(sql,tuple(data.values())):
        print('Successful')
        db.commit()
except:
    print('UnSuccessful')
    db.rollback()

#查询
cursor.execute('SELECT * FROM STUDENTS')
print("search result：",cursor.fetchone())
#关闭
db.close()

更新数据

import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
print('database version:',cursor.fetchone())

cursor.execute('SELECT * FROM students')
print(cursor.fetchone())

sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
    cursor.execute(sql,(25,'Bob'))
    db.commit()
except:
    db.rollback()

cursor.execute('SELECT * FROM students')
print(cursor.fetchone())

db.close()

更新数据（优化）

updat 优化 ON DUPLICATE KEY UPDATE 重复KEY 更新，非重复，插入。

import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
print('database version:',cursor.fetchone())

data = {
    'id':'20120003',
    'name':'May',
    'age':25,
    'gender':'女'
}

table = 'students'
keys =','.join(data.keys())
values =','.join(['%s'] * len(data))

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])
print(type(update),update)
sql +=  update
try:
    print('SQL:',sql)
    print(tuple(data.values())*2)
    if cursor.execute(sql,tuple(data.values())*2):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()

cursor.execute('SELECT * FROM STUDENTS')
print(cursor.fetchall())

db.close()

结果：
database version: ('8.0.15',)
<class 'str'>  id=%s, name=%s, age=%s, gender=%s
SQL: INSERT INTO students(id,name,age,gender) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE id=%s, name=%s, age=%s, gender=%s
('20120003', 'May', 25, '女', '20120003', 'May', 25, '女')
Successful
(('20120001', 'Bob', 21, '男'), ('20120002', 'Job', 21, '女'), ('20120003', 'May', 25, '女'))

删除

import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
cursor.execute('select version()')
print('database version:',cursor.fetchone())

table = 'students'
condition = 'age > 25'
sql = 'delete from {table} where {condition}'.format(table=table,condition=condition)
print(sql)

try:
    cursor.execute(sql)
    print('Successful Delete')
    db.commit()
except:
    db.rollback()

db.close()

查询

#slect 查询
import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
print('database version:',cursor.fetchone())

sql = 'SELECT * FROM students'
try:
    cursor.execute(sql)
    #fetchone() 返回一行
    # one = cursor.fetchone()
    # print('one:',one)
    # print(type(one))
    #fetchall()返回多行
    results = cursor.fetchall()
    # print('Results:',results)
    # print(type(results))
    for row in results:
        print(row)
except:
    print('Error')

db.close()

import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',db='spiders')
cursor = db.cursor()
cursor.execute('select version()')
print('database version:',cursor.fetchone())

sql = 'select * from students '

try:
    cursor.execute(sql)
    print('count:',cursor.rowcount)
    row = cursor.fetchone()
    while row:
        print('Row',row)
        row = cursor.fetchone()
except:
    print('Error')

db.close()