python小技巧大应用--自己写sqlite3的CRUD供应用调用

既然SQLite能满足我的数据管理需求,就不劳烦Mysql大驾了,果断把用pymysql写的CRUD函数库改成sqlite3的CRUD库供应用调用(如有需要pymysql CRUD的可参见:python小技巧大应用--自己写pymysql CRUD函数库供调用).

1)先在项目test-SQLite中把数据库文件sqlite-data.db建好,不会的可参见:python小技巧大应用--测试SQLite是否好用

import sqlite3
conn = sqlite3.connect('sqlite-data.db')
cursor = conn.cursor()
# ---gtj 建立userinfo表,id字段为自增
cursor.execute('create table userinfo (id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(20), age int)')

cursor.close()
conn.close()

2)将pymysql的CRUD库文件稍作修改为uitl_SQLitecrud.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
' SQLite CRUD util '
__author__ = 'TianJiang Gui'

import sqlite3
# ---gtj sqlite数据库文件名
db = 'sqlite-data.db'

# ---gtj 单条插入
# insert_sql = "insert into userinfo (name,age) values (?,?)"
# val =('gtj',100)
def db_InsertOne(sql,val):
    connect = sqlite3.connect(db)
    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 (?,?)"
# val = [('gtj11', 101),('gtj12', 102),('gtj13', 103),('gtj14', 104)]
def db_InsertMany(sql,val):
    connect = sqlite3.connect(db)
    cursor = connect.cursor()
    try:
        cursor.executemany(sql, val)
        rows = cursor.rowcount
        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 = sqlite3.connect(db)
    cursor = connect.cursor()
    try:
        cursor.execute(sql)
        datas = cursor.fetchall()  # 获取查询结果
        rows = len(datas)
        print(u'查询结果行数:' + str(rows))
        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 = sqlite3.connect(db)
    cursor = connect.cursor()
    try:
        cursor.execute(sql)
        rows = cursor.rowcount
        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 = sqlite3.connect(db)
    cursor = connect.cursor()
    try:
        cursor.execute(sql)
        rows = cursor.rowcount
        print("待删除行数：" + str(rows))
        connect.commit()
        print(u'删除成功...')
    except Exception as e:
        print(u'删除错误...', e)
        connect.rollback()
    finally:
        cursor.close()
        connect.close()

       在此强调与pymysql的不同之处:

        1)sql语句中的'%s'变成了'?'

        2)不能再通过cursor.execute返回的数值获得执行的结果,只能通过cursor.rowcount获得

        3)最大的不同:查询语句也无法通过cursor.rowcount获得结果数值,只能通过变通方法获得

3)最后通过主程序test_SQLitecrud.py 调用 util_SQLitecrud.py 测试一下执行情况

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
' a test SQLite CRUD util module '
__author__ = 'TianJiang Gui'

from util_SQLitecrud import *

if __name__ == '__main__':
    # ---gtj 插入一条
    insert_sql = "insert into userinfo (name,age) values (?,?)"
    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 where name like 'gtj%'"
    db_Select(select_sql)

    # ---gtj 修改数据
    update_sql = "update userinfo set age=36 where name='gtj14'"
    db_Update(update_sql)

    # ---gtj 删除
    delete_sql = "delete from userinfo where name='gtj14'"
    db_Delete(delete_sql)

    # ---gtj 修改不存在的数据
    update_sql = "update userinfo set age=36 where name='gtj141'"
    db_Update(update_sql)

    # ---gtj 错误的删除数据
    delete_sql = "delete from userinfo where name1='gtj14'"
    db_Delete(delete_sql)

    # # ---gtj 删除全部数据
    # delete_sql = "delete from userinfo"
    # db_Delete(delete_sql)

4)看看执行测试的结果

再用Navicate for SQLite 看看 

 

 

总结:结果正确,令人满意.今后在小数据应用开发上又多了一个好的选择,希望我的这个小库能帮到大家,恭喜发财! :)