SQLite
SQLite 是一个基于磁盘的轻量级数据库。由于它不需要单独的数据库服务器，因此通常用于原型设计或小型应用程序，这些应用程序通常由单个用户或一个用户在给定时间使用。
import sqlite3
conn = sqlite3.connect("users.db")
c = conn.cursor()
c.execute("CREATE TABLE user (name text, age integer)")
c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")
conn.commit()
c.execute("SELECT * FROM user")
print(c.fetchall())
conn.close()
上面的代码连接到存储在名为 users.db 的文件中的数据库，如果文件尚不存在，则首先创建该文件。你可以通过 SQL 语句与数据库进行交互。
这个例子的结果应该是：
[(u'User A', 42), (u'User B', 43)]
SQLite 语法：深入分析
入门
-
使用导入 sqlite 模块
>>> import sqlite3 -
要使用该模块，必须首先创建一个表示数据库的 Connection 对象。这里的数据将存储在 example.db 文件中：
>>> conn = sqlite3.connect('users.db')或者，你也可以提供特殊名称
:memory:以在 RAM 中创建临时数据库，如下所示：>>> conn = sqlite3.connect(':memory:') -
一旦你有了
Connection，就可以创建一个Cursor对象并调用它的execute()方法来执行 SQL 命令：c = conn.cursor() # Create table c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''') # Insert a row of data c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)") # Save (commit) the changes conn.commit() # We can also close the connection if we are done with it. # Just be sure any changes have been committed or they will be lost. conn.close()
Connection 的重要属性和功能
-
isolation_level它是用于获取或设置当前隔离级别的属性。没有自动提交模式或
DEFERRED，IMMEDIATE或EXCLUSIVE之一。 -
cursor游标对象用于执行 SQL 命令和查询。
-
commit()提交当前交易。
-
rollback()回滚自上次调用
commit()以来所做的任何更改 -
close()关闭数据库连接。它不会自动调用
commit()。如果在没有先调用commit()的情况下调用close()（假设你未处于自动提交模式），则所有更改都将丢失。 -
total_changes一个属性，记录自打开数据库以来修改，删除或插入的行总数。
-
execute，executemany和executescript这些函数的执行方式与游标对象的执行方式相同。这是一个快捷方式，因为通过连接对象调用这些函数会导致创建中间游标对象并调用游标对象的相应方法
-
row_factory你可以将此属性更改为可接受游标和原始行作为元组的可调用对象，并返回实际结果行。
def dict_factory(cursor, row): d = {} for i, col in enumerate(cursor.description): d[col[0]] = row[i] return d conn = sqlite3.connect(":memory:") conn.row_factory = dict_factory
Cursor 的重要功能
-
execute(sql[, parameters])执行单个 SQL 语句。SQL 语句可以是参数化的（即占位符而不是 SQL 文本）。sqlite3 模块支持两种占位符：问号
?（“qmark 样式”）和命名占位符:name（命名样式）。import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("create table people (name, age)") who = "Sophia" age = 37 # This is the qmark style: cur.execute("insert into people values (?, ?)", (who, age)) # And this is the named style: cur.execute("select * from people where name=:who and age=:age", {"who": who, "age": age}) # the keys correspond to the placeholders in SQL print(cur.fetchone())
注意：不要使用
%s将字符串插入 SQL 命令，因为它可能使你的程序容易受到 SQL 注入攻击（请参阅 SQL 注入 ）。
-
executemany(sql, seq_of_parameters)对序列 sql 中找到的所有参数序列或映射执行 SQL 命令。sqlite3 模块还允许使用迭代器生成参数而不是序列。
L = [(1, 'abcd', 'dfj', 300), # A list of tuples to be inserted into the database (2, 'cfgd', 'dyfj', 400), (3, 'sdd', 'dfjh', 300.50)] conn = sqlite3.connect("test1.db") conn.execute("create table if not exists book (id int, name text, author text, price real)") conn.executemany("insert into book values (?, ?, ?, ?)", L) for row in conn.execute("select * from book"): print(row)你还可以将迭代器对象作为参数传递给 executemany，该函数将迭代迭代器返回的每个元组值。迭代器必须返回一个值元组。
import sqlite3 class IterChars: def __init__(self): self.count = ord('a') def __iter__(self): return self def __next__(self): # (use next(self) for Python 2) if self.count > ord('z'): raise StopIteration self.count += 1 return (chr(self.count - 1),) conn = sqlite3.connect("abc.db") cur = conn.cursor() cur.execute("create table characters(c)") theIter = IterChars() cur.executemany("insert into characters(c) values (?)", theIter) rows = cur.execute("select c from characters") for row in rows: print(row[0]), -
executescript(sql_script)这是一次执行多个 SQL 语句的非标准方便方法。它首先发出
COMMIT语句，然后执行它作为参数获取的 SQL 脚本。sql_script可以是str或bytes的一个实例。import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.executescript(""" create table person( firstname, lastname, age ); create table book( title, author, published ); insert into book(title, author, published) values ( 'Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', 1987 ); """)下一组函数与 SQL 中的
SELECT语句一起使用。要在执行SELECT语句后检索数据，可以将游标视为迭代器，调用游标的fetchone()方法以检索单个匹配行，或调用fetchall()以获取匹配行的列表。迭代器形式的示例：
import sqlite3 stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)] conn = sqlite3.connect(":memory:") conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)") conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks) cur = conn.cursor() for row in cur.execute('SELECT * FROM stocks ORDER BY price'): print(row) # Output: # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14) # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0) # ('2006-04-06', 'SELL', 'IBM', 500, 53.0) # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0) -
fetchone()获取查询结果集的下一行，返回单个序列，或者在没有更多数据可用时返回 None。
cur.execute('SELECT * FROM stocks ORDER BY price') i = cur.fetchone() while(i): print(i) i = cur.fetchone() # Output: # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14) # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0) # ('2006-04-06', 'SELL', 'IBM', 500, 53.0) # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0) -
fetchmany(size=cursor.arraysize)获取查询结果的下一组行（由 size 指定），返回一个列表。如果省略 size，则 fetchmany 返回单行。当没有更多行可用时，返回空列表。
cur.execute('SELECT * FROM stocks ORDER BY price') print(cur.fetchmany(2)) # Output: # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)] -
fetchall()获取查询结果的所有（剩余）行，返回列表。
cur.execute('SELECT * FROM stocks ORDER BY price') print(cur.fetchall()) # Output: # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
SQLite 和 Python 数据类型
SQLite 本身支持以下类型：NULL，INTEGER，REAL，TEXT，BLOB。
这是从 SQL 迁移到 Python 时反转数据类型的方式，反之亦然。
None <-> NULL
int <-> INTEGER/INT
float <-> REAL/FLOAT
str <-> TEXT/VARCHAR(n)
bytes <-> BLOB