数据库
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')
这样每循环一次,指针就会偏移一条数据,随用随取,简单高效。