MySQL 增删改查

数据库

import pymysql

db = pymysql.connect(host='localhost', user='root', password='password', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute('CREATE DATABASE test DEFAULT CHARACTER SET utf8')
db.close()

创建表

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

插入数据

id = '2012001'
user = 'Bob'
age = 20
db = pymysql.connect(host='localhost', user='root', password='password', port=3306, db='test')
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()  # 数据回滚，相当于什么都没有发生过
db.close()

如果突然增加了性别字段gender，此时SQL语句就需要改成

INSERT INTO students(id, name, age, gender) values(%s, %s, %s, %s)

相应的元组参数则需要改成：(id, name, age, gender)

如果我们需要动态添加字段，只需要传入一个动态变化的字典就好了。然后SQL语句会根据字典动态构造，元组也会动态构造，这样才能实现通用的插入方法。

data = {
	'id': '200120002',
	'name': 'Bob',
	'age': 20
}
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)
try:
	if cursor.execute(sql, tuple(data.values())):
		print('Successful')
		db.commit()
except:
	print('Failed')
	db.rollback()
db.close()

更新数据

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

在实际应用当中，大部分情况下插入数据，我们关心的是会不会出现重复数据，如果出现了，我们希望更细数据而不是重复保存一次。另外，就像前面所说的动态构造SQL的问题，所以这里可以再实现一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据。另外，这种做法支持灵活的字典传值

data = {
	'id': '200120002',
	'name': 'Bob',
	'age': 20
}
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])
sql += update
try:
	if cursor.execute(sql, tuple(data.values()) * 2):
		print('Successful')
		db.commit()
except:
	print('Failed')
	db.rollback()
db.close()

删除数据

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()

查询数据

sql = 'SELECT * FROM students WHERE age>20'
try:
	cursor.execute(sql)
	print('Count:', cursor.rowcount)
	one = cursor.fetchone()
	print('One:', one)
	results = cursor.fetchall()
	print('Results:', results)
	print('Results Type:', type(results))
	for row in results:
		print(row)
except:
	print('Error')

需要注意的是，由于这里第一次调用fetchone()，这样结果的偏移指针就指向下一条数据，fetchall()方法返回的是偏移指针指向的数据一致到结束的所有数据。此外fetchall()会将结果以元组形式全部返回，如果数据量过大，那么占用的开销会非常高。因此，推荐使用下面的方法来逐条获取数据：

sql = 'SELECT * FROM students WHERE age>20'
try:
	cursor.execute(sql)
	print('Count:', cursor.rowcount)
	row = cursor.fetchone()
	while row:
		print('Row:', row)
		row = cursor.fetchone()
except:
	print('Error')

这样每循环一次，指针就会偏移一条数据，随用随取，简单高效。