既然多次打开关闭数据库对我这样的小数据量应用影响不是太大(开关一次也就0.007秒,参见我的上篇文章python小技巧大应用--实测pymysql打开关闭数据库的效率问题),那我就直接写些操作数据库的CRUD函数库供应用调用好了.这些函数的特点是每执行一次都会开关一次数据库.
(不想长链接,毕竟mysql连接资源有限,只有16384;也不想使用连接池和异步读写,开发复杂度高且目前没有必要.)
1)新建一个工具函数包文件util_DBcrud.py
(所用数据库的建立等内容请参见:python小技巧大应用--测试通过pymysql实现数据库CRUD)
import pymysql
config = {
'host':'localhost',
'port':3306,
'user':'pubuser',
'password':'123321',
'db':'test-pythondata',
'charset':'utf8'
}
# ---gtj 单条插入
# insert_sql = "insert into userinfo (name,age) values (%s,%s)"
# val =('gtj',100)
def db_InsertOne(sql,val):
connect = pymysql.connect(**config)
cursor = connect.cursor()
try:
cursor.execute(sql,val)
connect.commit()
print(u'插入成功影响行数1')
except Exception as e:
print(u'插入错误...', e)
connect.rollback()
finally:
cursor.close()
connect.close()
# ---gtj 多条插入
# insert_sql = "insert into userinfo (name,age) values (%s,%s)"
# val = [('gtj11', 101),('gtj12', 102),('gtj13', 103),('gtj14', 104)]
def db_InsertMany(sql,val):
connect = pymysql.connect(**config)
cursor = connect.cursor()
try:
rows = cursor.executemany(sql, val)
connect.commit()
print(u'插入成功影响行数:' + str(rows))
except Exception as e:
print(u'插入多条错误...', e)
connect.rollback()
finally:
cursor.close()
connect.close()
# ---gtj 查询
def db_Select(sql):
connect = pymysql.connect(**config)
cursor = connect.cursor()
try:
rows = cursor.execute(sql)
print(u'查询结果行数:' + str(rows))
datas = cursor.fetchall() # 获取查询结果
for data in datas:
print(data)
except Exception as e:
print(u'查询错误...', e)
connect.rollback()
finally:
cursor.close()
connect.close()
# ---gtj 修改
# update_sql = "UPDATE userinfo SET age = 50 WHERE `name` = 'gtj'"
def db_Update(sql):
connect = pymysql.connect(**config)
cursor = connect.cursor()
try:
rows = cursor.execute(sql)
print("待更新行数:" + str(rows))
connect.commit()
print(u'修改成功...')
except Exception as e:
print(u'修改错误...', e)
connect.rollback()
finally:
cursor.close()
connect.close()
# ---gtj 删除
# delete_sql = "DELETE from userinfo WHERE `name` = 'gtj2'"
def db_Delete(sql):
connect = pymysql.connect(**config)
cursor = connect.cursor()
try:
rows = cursor.execute(sql)
print("待删除行数:" + str(rows))
connect.commit()
print(u'删除成功...')
except Exception as e:
print(u'删除错误...', e)
connect.rollback()
finally:
cursor.close()
connect.close()
2)再写个主应用程序test_DBcrud.py通过调用前述函数对MySQL数据库进行CRUD操作
from util_DBcrud import *
if __name__ == '__main__':
# ---gtj 插入一条
insert_sql = "insert into userinfo (name,age) values (%s,%s)"
val = ('gtj', 100)
db_InsertOne(insert_sql,val)
# ---gtj 插入多条
val = [('gtj11', 101),('gtj12', 102),('gtj13', 103),('gtj14', 104)]
db_InsertMany(insert_sql,val)
# ---gtj 查询
select_sql = "SELECT * from userinfo"
db_Select(select_sql)
# ---gtj 修改数据
update_sql = "update userinfo set age=36 where name='gtj'"
db_Update(update_sql)
# ---gtj 查询
delete_sql = "delete from userinfo where name='gtj14'"
db_Delete(delete_sql)
运行结果如图:
3)现在进行错误操作,看看执行结果如何:
3.1)插入语句写错
3.2)插入数据写错
3.3)插入多条数据有一条数据错误
3.4)查询语句错
3.5)更新或删除一条不存在的记录
3.6)更新或删除语句错误
总结:通过调用自己写的CRUD函数,轻松实现了在开发过程中对数据库的操作,希望这些小函数对大家也有所帮助.