Python数据库操作SQLite3实例

Python数据库操作SQLite实例

此文章主要记录了Python基于SQLite3如何初始化数据库、初始化数据、事务描述、查询数据的一些基本操作函数示例与说明。

import sqlite3

print('DB API 2.0 sqlite3的全域变量')
print('apilevel:',sqlite3.apilevel)
print('threadsafety:',sqlite3.threadsafety)
print('paramstyle:',sqlite3.paramstyle)

print('说明：SQLite支持数据类型NULL、INTEGER、TEXT、REAL、BLOB') 

print('DB API 2.0数据库操作步骤')
print('开始->打开connection->获取cursor->获取或者处理数据->关闭cursor->关闭connection->结束')

#初始化数据库函数
def initdb():
    print('打开数据库连接')
    my_conn = sqlite3.connect(r'db\test.db')

    print('打开游标')
    my_cursor = my_conn.cursor()

    print('动态创建数据表')
    my_cursor.execute('''
        create table if not exists user_tb (
            _id integer primary key autoincrement,
            name text,
            code text,
            age integer
        )    
    ''')
    my_cursor.execute('''
        create table if not exists order_tb (
            _id integer primary key autoincrement,
            item_name text,
            item_price real,
            item_number integer,
            user_id integer,
            foreign key (user_id) references user_tb(_id) on delete cascade on update cascade
        )    
    ''')

    print('关闭游标')
    my_cursor.close()

    print('关闭数据库连接')  
    my_conn.close()

#初始化数据函数
def initdata():
    #打开数据库连接 
    my_conn = sqlite3.connect(r'db\test.db')
    #打开游标
    my_cursor = my_conn.cursor()
    #执行SQL
    my_cursor.execute('insert into user_tb values (null,?,?,?)', ('zhangsan','101',22))
    my_cursor.execute('insert into user_tb values (null,?,?,?)', ('lisi','102',25))
    my_cursor.execute('insert into order_tb values (null,?,?,?,?)', ('鼠标',30,10,1))
    #executemany的用法
    my_cursor.executemany('insert into user_tb values (null,?,?,?)', (('wangwu','103',22),('zhaoliu','104',22)))  
    print('影响记录数：', my_cursor.rowcount)
    #提交事务
    my_conn.commit()
    #关闭游标
    my_cursor.close()
    #关闭数据库连接  
    my_conn.close()

#查询输出
def queryprint():
    #打开数据库连接 
    my_conn = sqlite3.connect(r'db\test.db')
    #打开游标
    my_cursor = my_conn.cursor()
    #执行SQL
    my_cursor.execute('select * from user_tb where _id>?', (1,))
    #通过my_cursor.description获取列信息
    print('通过my_cursor.description获取列信息') 
    for col in my_cursor.description:
        print(col[0], end='\t')
    print('')    
    #通过my_cursor的fetchone、fetchmany(n)、fetchall获取数据，或者直接迭代游标获取
    #使用fetchone获取数据
    '''
    print('使用fetchone获取数据')     
    while True:
        #获取一行
        row =  my_cursor.fetchone()
        if not row:
            break;
        else:
            for data in row:
                print(data,end='\t')
            print('')     
    '''        
    #迭代游标获取数据
    print('迭代游标获取数据') 
    for row in my_cursor:
        for data in row:
                print(data,end='\t')
        print('')
    print('') 
    #关闭游标
    my_cursor.close()
    #关闭数据库连接  
    my_conn.close()
  
#游标对象的executescript执行SQL脚本
def executesql():
    #打开数据库连接 
    my_conn = sqlite3.connect(r'db\test.db')
    #打开游标
    my_cursor = my_conn.cursor()
    #每条SQL语句后面需加分号
    sql = "";
    with open('a.sql','r',True,'UTF-8') as f:
        sql = f.read()
    #执行SQL
    my_cursor.executescript(sql);
    #提交事务
    my_conn.commit()
    #关闭游标
    my_cursor.close()
    #关闭数据库连接  
    my_conn.close()
    
#初始化数据库
print('初始化数据库开始')  
#initdb();
print('初始化数据库完毕') 

#初始化数据
print('初始化数据开始')
#initdata();
print('初始化数据完毕')
    
#查询输出
print('查询输出开始')  
queryprint();
print('查询输出完毕')

#事务特性
#事务的ACID（原子性、一致性、隔离性、持续性）
#事务提交commit
#如果程序只执行DDL语句，不需要显式提交事务
#如果程序先执行DML语句，再执行DDL语句，那么需要显式提交事务（执行DML语句，事务自动开启）
#事务回滚rollback
#程序可以显式调用回滚
#程序发生异常退出会自动回滚

#直接使用连接对象也可以执行SQL语句，如：
#数据库连接对象也提供了execute、executemany、executescript