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