SQLite 簡介
SQLite
資料型別
- Datatypes In SQLite Version 3
- SQLite只有5種資料型別
- NULL: 空值
- INTEGER: 帶正負號整數
- REAL: 浮點數
- TEXT: 字串
- BLOB: 二進制物件
- SQLite也可以接受其它型別，但會自動轉換成上面5種型別之一
- Boolean:存成 INTEGER 0或1
- Date & Time: 依SQLite內建的函式存成下列3種型式
- TEXT: ISO8601字串 YYYY-MM-DD HH:MM:SS.SSS
- REAL: 以日為單位，西元前 4714 年 11 月 24 日中午12點起
- INTEGER: 以秒為單位，起始時間為 1970-01-01 00:00:00 UTC
Python sqlite3
- Python 2.5版後，內建 SQLite 模組
| sqlite3 方法 |
說明 |
| conn = sqlite3.connect(“資料庫檔名.db”) |
連接資料庫(檔案若不存在則建立新檔) |
| conn = sqlite3.connect("":memory:") |
在記憶體裡建立一個資料庫，關閉連線後消失 |
| conn.close() |
關閉資料庫連線 |
| cursor = conn.cursor() |
回傳 Cursor 物件 |
| cursor = conn.execute(SQL) |
執行 SQL 指令, 會回傳 Cursor 物件 |
| cursor.execute(SQL) |
執行 SQL 指令 |
| cursor.fetchone() |
讀取目前 Cursor 物件所指之下一筆紀錄, 若無傳回 None |
| cursor.fetchall() |
讀取全部剩餘之紀錄以串列傳回, 若無紀錄傳回空串列 |
| conn.commit() |
提交，將未提交的操作更新到資料庫 |
| conn.rollback() |
還原，取消未提交的操作 |
In-Memroy DB
- 在記憶體裡建立一個資料庫，關閉連線後消失
- 可以對記憶體裡的資料庫進行操作，最後再轉存到一般的資料庫檔案
In-Memroy DB 與實體DB互相轉存
SQLite 3.6.11 or higher
fileDB = sqlite3.connect('test.db')
memDB = sqlite3.connect(':memory:')
fileDB.backup(memDB)
SQLite 3.6.11 以前的版本
import sqlite3
memDB = sqlite3.connect(':memory:') # create a memory database
fileDB = sqlite3.connect('test.db')
query = "".join(line for line in fileDB.iterdump())
memDB.executescript(query) # Dump old database in the new one.
透過StringIO
memDB = sqlite3.connect(':memory:')
memDB.executescript('''省略''')
buffer = StringIO.StringIO()
for line in memDB.iterdump():
buffer.write(line)
memDB.close()
fileDB = sqlite3.connect("test.db")
cursor = fileDB.cursor()
cursor.executescript(buffer.getvalue())
cursor.executescript(buffer.getvalue())
cursor.close()
fileDB.close()
建立資料表
CREATE TABLE : 如果資料表已存在會發生錯誤CREATE TABLE IF NOT EXISTS : 如果資料表已存在不會發生錯誤
import sqlite3
conn = sqlite3.connect("test.db")
SQL = 'CREATE TABLE IF NOT EXISTS MyTable(id INTEGER, value TEXT)'
conn.execute(SQL)
插入單筆資料
import sqlite3
conn = sqlite3.connect("test.db")
SQL = 'INSERT INTO MyTable(ID, VALUE) VALUES(0, "value1")'
conn.execute(SQL)
conn.commit()
插入多筆資料
valuse = [(0, 'Value1'),
(1, 'Value2'),
(2, 'Value3')]
conn.executemany('INSERT INTO MyTable VALUES(?,?) ', values)
conn.executescript("""
'INSERT INTO MyTable VALUES(id, value) VALUES( 0, 'Value1');
'INSERT INTO MyTable VALUES(id, value) VALUES( 1, 'Value2');
'INSERT INTO MyTable VALUES(id, value) VALUES( 2, 'Value3');
""")
查詢資料
import sqlite3
conn = sqlite3.connect("test.db")
SQL = "SELECT * FROM MyTable"
cursor = conn.execute(SQL)
print(cursor.fetchone()) # tuple
print(cursor.fetchall()) # list
for row in c.execute('SELECT * FROM MyTable ORDER BY ID'):
print(row)
帶參數查詢資料
value = 'Value2'
conn.execute("SELECT * FROM MyTable WHERE VALUE = '%s'" % value) # '%s' 有單引號
value = ('Value2',)
conn.execute("SELECT * FROM MyTable WHERE VALUE = ?", value) # ? 無單引號
查詢表格是否存在
SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';
查詢日期
SELECT * FROM MyTable WHERE tm BETWEEN '2010-01-01' AND '2010-01-31'
查詢今天日期(不含時間)
修改資料
import sqlite3
conn = sqlite3.connect("test.db")
SQL = "UPDATE MyTable SET VALUE = 'Second Value' WHERE ID = 0"
conn.execute(SQL)
conn.commit()
刪除資料
import sqlite3
conn = sqlite3.connect("test.db")
SQL = "DELETE FROM MyTable WHERE ID = 0"
conn.execute(SQL)
conn.commit()
ALTER
ALTER TABLE MyTable RENAME COLUMN TEST TO NEW_TEST
ALTER TABLE MyTable ADD NEW_FIELD TEXT
ALTER TABLE MyTable RENAME TO NEW_TABLE_NAME
DROP Table
DROP TABLE IF EXISTS MyTable
Index
- 當建立索引時，SQLite會建立一個額外的平衡樹(B-tree)結構來儲存索引的資料
- 透過B-tree，SQLite可以快速的找到符合索引的資料
單欄位索引(Index)
CREATE INDEX idx_name ON MyTable (欄位);
CREATE UNIQUE INDEX idx_name ON MyTable (欄位);
- UNIQUE: 如果欄位的值不會重覆，加UNIQUE效能會比較好
多欄位索引(Index)
CREATE INDEX idx_name ON MyTable (欄位1, 欄位2);
有使用到索引
WHERE 欄位1 = 'Value1'
WHERE 欄位1 = 'Value1' AND 欄位2 = 'Value2'
沒有使用到索引
WHERE 欄位2 = 'Value1'
WHERE 欄位2 = 'Value2' OR 欄位1 = 'Value1'
索引其它語法
/* 查已建立的索引 */
PRAGMA index_list('table_name');
/* 查已索引的欄位 */
PRAGMA index_info('idx_contacts_name');
/* 刪除索引 */
DROP INDEX [IF EXISTS] index_name;
參考