Python 数据库编程SQLite的使用介绍（超详细）

一、核心语法

• connect()  连接数据库，并返回数据库连接对象。
• cursor()  打开游标。用于执行各种SQL语句，包括DDL、DML、select查询语句等。
• commit()  提交事务。
• rollback()  回滚事务。
• close()  关闭数据库连接。
• isolation_level  返回或设置数据库连接中事务的隔离级别。
• in_transaction  判断当前是否处于事务中。

数据库游标对象拥有的属性和方法：

• execute(sql[,parameters])  执行SQL语句。parameters参数用于为SQL语句中的参数指定值。
• executemany(sql,seq_of_parameters)  重复执行SQL语句。可以通过 seq_of_parameters 序列为 SQL 语句中的参数指定值，该序列有多少个元素，SQL 语句被执行多少次。
• executescript(sql_script)  直接执行包含多条 SQL 语句的 SQL 脚本。
• fetchone()  获取查询结果集的下一行。如果没有下一行，则返回 None。
• fetchmany(size=cursor.arraysize)  返回查询结果集的下 N 行组成的列表。如果没有更多的数据行，则返回空列表。
• fetchall()  返回查询结果集的全部行组成的列表。
• close()  关闭游标。
• rowcount  该只读属性返回受SQL语句影响的行数。对于executemany() 方法，该方法所修改的记录条数可通过该属性获取。
• lastrowid  该只读属性可获取最后修改行的 rowid。
• arraysize  用于设置或获取 fetchmany() 默认获取的记录条数，该属性默认为 1。有些数据库模块没有该属性。
• description  该只读属性可获取最后一次查询返回的所有列的信息。
• connection  该只读属性返回创建游标的数据库连接对象。有些数据库模块没有该属性。

二、操作数据库的基本流程

基本流程：

• 调用 connect() 方法打开数据库连接，该方法返回数据库连接对象。
• 通过数据库连接对象打开游标。
• 使用游标执行SQL语句（包括 DDL、DML、select 查询语句等）。如果执行的是查询语句，则处理查询数据。
• 关闭游标。
• 关闭数据库连接。

代码示例 ：

import sqlite3
conn = sqlite3.connect('test.db') #打开或创建数据库
c = conn.cursor() #获取游标
#执行DDL语句创建数据表
c.execute('''create table user_tb(
    _id integer primary key autoincrement,
    name text,
    pass text,
    gender text)''')
#执行DDL语句创建数据表
c.execute('''create table order_tb(
    _id integer primary key autoincrement,
    item_name text,
    item_price real,
    item_number real,
    user_id inteter,
    foreign key(user_id) references user_tb(_id) )''')
c.close() #关闭游标
conn.close() #关闭连接

三、SQLite Expert安装和使用

1.登录 http://www.sqliteexpert.com/download.html 站点来下载 SQLite Expert，该工具提供了两个版本：免费的个人版和收费的商业版。此处选择免费的个人版。将页面滚动到下方，找到“SQLite Expert Personal 5.x”，然后单击下方的链接（64 位操作系统选择 64bit 版，32 位操作系统选择 32bit 版），如图所示：

2.直接点击安装，启动SQLite Expert工具，启动程序界面：

3.打开刚创建的数据库test.db ，如图：

我们可以看到test数据库包括了order_tb和user_tb两个数据表。随便点一个数据表，就可以看到该数据表的详细信息，包括数据列(Columns)、主键(Primary Key)、索引(Indexs)、外键(Foreign Keys)、唯一约束(Unique Constraints)等。在管理界面的右边，可以看到也提供创建数据表、删除数据表、添加数据、删除数据等操作，非常方便管理数据。

四、数据库常规操作

使用游标的execute()方法可以执行 DML（数据库操纵语言）的 insert、update、delete 语句，即可以对数据库执行插入、修改和删除数据操作。

插入单条数据：

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
#调用执行insert语句插入数据
c.execute('insert into user_tb values(null, ?, ?, ?)',
    ('刘德华', '123456', 'male'))
c.execute('insert into order_tb values(null, ?, ?, ?, ?)',
    ('iphone', '8888', '003', 1))
conn.commit()  #必须调用提交事务,否则不会对数据库所做的修改生效
c.close()      #关闭游标
conn.close()   #关闭连接

同时插入多条数据：

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
#调用executemany()方法把同一条SQL语句执行多次
c.executemany('insert into user_tb values(null, ?, ?, ?)',
    (('杨幂', '123456', 'female'),
    ('汪峰', '123456', 'male'),
    ('彭于晏', '123456', 'male'),
    ('迪丽巴热', '123456', 'female'),
    ('段奕宏', '123456', 'male')))
conn.commit()  #提交事务
c.close()      #关闭游标
conn.close()   #关闭连接

同时更新多条数据：

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
#调用executemany()方法把同一条SQL语句执行多次
c.executemany('update user_tb set name=? where _id=?',
    (('马云', 2),
    ('马化腾', 3),
    ('雷军', 4),
    ('董明珠', 5),
    ('任正非', 6)))
#通过rowcount获取被修改的记录条数
print('修改的记录条数：', c.rowcount)
conn.commit()
c.close()
conn.close()

查询数据库：

执行查询使用select语句，执行select语句完成后可以得到查询结果，因此程序可通过游标fetchone()、fetchmany(n)、fetchall()来获取查询结果。fetchone()获取一条数据、fetchmany(n)获取n条数据、fetchall()获取全部记录。

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
# ③、调用执行select语句查询数据
c.execute('select * from user_tb where _id > ?', (2,))
# 通过游标的description属性获取列信息
for col in (c.description):
    print(col[0], end='\t')
print('\n--------------------------------')
while True:
    # 获取一行记录，每行数据都是一个元组
    row = c.fetchone()
    # 如果抓取的row为None，退出循环
    if not row :
        break
    print(row)
    print(row[1] + '-->' + row[2])
c.close()    #关闭游标
conn.close() #关闭连接

后面还有create_function()(注册自定义函数)、create_aggregate()(自定义聚集函数)、create_collation()(创建自定义比较函数)等等。