壹 简介
Python的SQLITE数据库是一款非常小巧的嵌入式开源数据库软件，也就是说没有独立的维护进程，所有的维护都来自于程序本身。它使用一个文件存储整个数据库，操作十分方便。它的最大优点是使用方便，功能相比于其它大型数据库来说，确实有些差距。但是性能表现上，SQLITE并不逊色。麻雀虽小，五脏俱全， sqlite 实现了多数 sql-92 的标准，比如说 transaction 、 trigger 和复杂的查询等。
贰 安装
内置库。
叁 查看所有类和方法
import sqlite3
print(dir(sqlite3))
# ['Binary', 'Connection', 'Cursor', 'DataError', 'DatabaseError', 'Date', 'DateFromTicks', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'OptimizedUnicode', 'PARSE_COLNAMES', 'PARSE_DECLTYPES', 'PrepareProtocol', 'ProgrammingError', 'Row', 'SQLITE_ALTER_TABLE', 'SQLITE_ANALYZE', 'SQLITE_ATTACH', 'SQLITE_CREATE_INDEX', 'SQLITE_CREATE_TABLE', 'SQLITE_CREATE_TEMP_INDEX', 'SQLITE_CREATE_TEMP_TABLE', 'SQLITE_CREATE_TEMP_TRIGGER', 'SQLITE_CREATE_TEMP_VIEW', 'SQLITE_CREATE_TRIGGER', 'SQLITE_CREATE_VIEW', 'SQLITE_CREATE_VTABLE', 'SQLITE_DELETE', 'SQLITE_DENY', 'SQLITE_DETACH', 'SQLITE_DONE', 'SQLITE_DROP_INDEX', 'SQLITE_DROP_TABLE', 'SQLITE_DROP_TEMP_INDEX', 'SQLITE_DROP_TEMP_TABLE', 'SQLITE_DROP_TEMP_TRIGGER', 'SQLITE_DROP_TEMP_VIEW', 'SQLITE_DROP_TRIGGER', 'SQLITE_DROP_VIEW', 'SQLITE_DROP_VTABLE', 'SQLITE_FUNCTION', 'SQLITE_IGNORE', 'SQLITE_INSERT', 'SQLITE_OK', 'SQLITE_PRAGMA', 'SQLITE_READ', 'SQLITE_RECURSIVE', 'SQLITE_REINDEX', 'SQLITE_SAVEPOINT', 'SQLITE_SELECT', 'SQLITE_TRANSACTION', 'SQLITE_UPDATE', 'Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'Warning', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__spec__', 'adapt', 'adapters', 'apilevel', 'collections', 'complete_statement', 'connect', 'converters', 'datetime', 'dbapi2', 'enable_callback_tracebacks', 'enable_shared_cache', 'paramstyle', 'register_adapter', 'register_converter', 'sqlite_version', 'sqlite_version_info', 'threadsafety', 'time', 'version', 'version_info']
肆 方法说明
| 方法 | 说明 |
|---|---|
| sqlite3.connect(database [,timeout ,other optional arguments]) | 该 API 打开一个到 SQLite 数据库文件 database 的链接。您可以使用 “:memory:” 来在 RAM 中打开一个到 database 的数据库连接，而不是在磁盘上打开。如果数据库成功打开，则返回一个连接对象。当一个数据库被多个连接访问，且其中一个修改了数据库，此时 SQLite 数据库被锁定，直到事务提交。timeout 参数表示连接等待锁定的持续时间，直到发生异常断开连接。timeout 参数默认是 5.0（5 秒）。如果给定的数据库名称 filename 不存在，则该调用将创建一个数据库。如果您不想在当前目录中创建数据库，那么您可以指定带有路径的文件名，这样您就能在任意地方创建数据库。 |
| connection.cursor([cursorClass]) | 该例程创建一个 cursor，将在 Python 数据库编程中用到。该方法接受一个单一的可选的参数 cursorClass。如果提供了该参数，则它必须是一个扩展自 sqlite3.Cursor 的自定义的 cursor 类。 |
| cursor.execute(sql [, optional parameters]) | 该例程执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。例如：cursor.execute(“insert into people values (?, ?)”, (who, age)) |
| connection.execute(sql[, optional parameters]) | 该例程是上面执行的由光标（cursor）对象提供的方法的快捷方式，它通过调用光标（cursor）方法创建了一个中间的光标对象，然后通过给定的参数调用光标的 execute 方法。 |
| cursor.executemany(sql, seq_of_parameters) | 该例程对 seq_of_parameters 中的所有参数或映射执行一个 SQL 命令。 |
| connection.executemany(sql[, parameters]) | 该例程是一个由调用光标（cursor）方法创建的中间的光标对象的快捷方式，然后通过给定的参数调用光标的 executemany 方法。 |
| cursor.executescript(sql_script) | 该例程一旦接收到脚本，会执行多个 SQL 语句。它首先执行 COMMIT 语句，然后执行作为参数传入的 SQL 脚本。所有的 SQL 语句应该用分号 ; 分隔。 |
| connection.executescript(sql_script) | 该例程是一个由调用光标（cursor）方法创建的中间的光标对象的快捷方式，然后通过给定的参数调用光标的 executescript 方法。 |
| connection.total_changes() | 该例程返回自数据库连接打开以来被修改、插入或删除的数据库总行数。 |
| connection.commit() | 该方法提交当前的事务。如果您未调用该方法，那么自您上一次调用 commit() 以来所做的任何动作对其他数据库连接来说是不可见的。 |
| connection.rollback() | 该方法回滚自上一次调用 commit() 以来对数据库所做的更改。 |
| connection.close() | 该方法关闭数据库连接。请注意，这不会自动调用 commit()。如果您之前未调用 commit() 方法，就直接关闭数据库连接，您所做的所有更改将全部丢失！ |
| cursor.fetchone() | 该方法获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回 None。 |
| cursor.fetchmany([size=cursor.arraysize]) | 该方法获取查询结果集中的下一行组，返回一个列表。当没有更多的可用的行时，则返回一个空的列表。该方法尝试获取由 size 参数指定的尽可能多的行。 |
| cursor.fetchall() | 该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表。 |
伍 简单使用
python的数据库模块有统一的接口标准，所以数据库操作都有统一的模式，基本上都是下面几步（假设数据库模块名为db）：
- a.用db.connect创建数据库连接，假设连接对象为conn
- b.如果该数据库操作不需要返回结果，就直接用conn.execute查询，根据数据库事务隔离级别的不同，可能修改数据库需要conn.commit
- c.如果需要返回查询结果则用conn.cursor创建游标对象cur, 通过cur.execute查询数据库，用cur.fetchall/cur.fetchone/cur.fetchmany返回查询结果。根据数据库事 务隔离级别的不同，可能修改数据库需要conn.commit
- d.关闭cur, conn
5.1 导入模块
import sqlite3
5.2 连接数据库
# 创建并连接test.db数据库
conn = sqlite3.connect('test.db')
print("成功创建数据库！")
其实我们不需要特地创建一个sqlite数据库，在调用connect函数的时候，指定库名称，如果指定的数据库存在就直接打开这个数据库，如果不存在就新创建一个再打开。
有以下操作：
| 方法 | 说明 |
|---|---|
| conn.commit() | 事务提交 |
| conn.rollback() | 事务回滚 |
| conn.close() | 关闭一个数据库连接 |
| conn.cursor() | 创建一个游标 |
5.3 创建游标
游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联。因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功， 该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。
我们知道关系数据库管理系统实质是面向集合的，在Sqlite中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。
个人理解：就是在索引数据库时，数据会放到一个集合中（除非使用where子句限制），这时候要想读取修改集合中的某一个数据，那么就需要标记他，游标就起到这个重要。
# 创建游标
cu = conn.cursor()
游标对象有以下的操作：
| 方法 | 说明 |
|---|---|
| cu.execute() | 执行sql语句 |
| cu.executemany() | 执行多条sql语句 |
| cu.close() | 关闭游标 |
| cu.fetchone() | 从结果中取一条记录，并将游标指向下一条记录 |
| cu.fetchmany() | 从结果中取多条记录 |
| cu.scroll() | 游标滚动 |
5.4 创建表
# 创建一个名叫COMPANY的表，键有ID、NAME、AGADDRESS、SALARY
cu.execute('''
CREATE TABLE IF NOT EXISTS TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL, --只能这样备注
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
''')
print("Table created successfully")
# 注意这里执行完要加下面这句提交事务
conn.commit()
在执行完语句后记得提交事务，原因在这篇文章：数据库事务概念理解：已完成
5.5 INSERT 操作
# 插入操作，值为(1, 'Paul', 32, 'California', 20000.00 )
cu.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )")
# 注意这里执行完要加下面这句提交事务
conn.commit()
5.6 SELECT 操作
# 查询操作，查询COMPANY表的id、name、address、salary
cursor = cu.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print("ID = {}".format(row[0]))
print("NAME = {}".format(row[1]))
print("ADDRESS = {}".format(row[2]))
print("SALARY = {}".format(row[3]))
# 或者使用cu.fetchone一个一个输出
print(cu.fetchone())
# 或者使用cu.fetchmany全部输出
print(cu.fetchmany())
可以看到查询不需要加conn.commit()语句。
5.7 UPDATE 操作
# 修改操作，查询COMPANY表中id=1的SALARY值
cu.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
5.8 DELETE 操作
# 修改操作，查询COMPANY表中id=2的值
cu.execute("DELETE from COMPANY where ID=2;")
conn.commit()
5.9 关闭连接
# 关闭游标
cu.close()
# 关闭连接
conn.close()
陆 例子
#导入sqlite3模块
import sqlite3
# 创建与数据库的连接
conn = sqlite3.connect(r'C:\Users\11231\Desktop\数据库测试\A7.db')
# 创建一个游标 cursor
cur = conn.cursor()
# 建表的sql语句
sql_text_1 = '''CREATE TABLE IF NOT EXISTS TABLE scores
(姓名 TEXT,
班级 TEXT,
性别 TEXT,
语文 NUMBER,
数学 NUMBER,
英语 NUMBER);'''
# 执行sql语句
cur.execute(sql_text_1)
# 插入单条数据
sql_text_2 = "INSERT INTO scores VALUES('A', '一班', '男', 96, 94, 98)"
cur.execute(sql_text_2)
data = [
('B', '一班', '女', 78, 87, 85),
('C', '一班', '男', 98, 84, 90),
]
cur.executemany('INSERT INTO scores VALUES (?,?,?,?,?,?)', data)
# 连接完数据库并不会自动提交，所以需要手动 commit 你的改动conn.commit()
# 查询数学成绩大于90分的学生
sql_text_3 = "SELECT * FROM scores WHERE 数学>90"
cur.execute(sql_text_3)
# 获取查询结果
print(cur.fetchall())
# 提交改动的方法
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()
