python小技巧大应用--自己写pymysql CRUD函数库供调用

既然多次打开关闭数据库对我这样的小数据量应用影响不是太大(开关一次也就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函数,轻松实现了在开发过程中对数据库的操作,希望这些小函数对大家也有所帮助.