最後更新: 2015-04-21

Version

# 匯入 sqlite3 的 class

>>> import sqlite3

# Class 的 version

>>> sqlite3.version_info
(2, 4, 1)

# engine version:

>>> sqlite3.sqlite_version_info
(3, 7, 3)

 

---

應用流程

 

建立 Connection Objects

建立 Cursor Objects            <--- Default: tuples(sqlite3.Row)  (如果想用 dict, 那就要 con.row_factory = dict_factory)

建立 Row Object

 

 

---

一般使用 (connect(), cursor(), execute(), commit(), close())

 

# 建立 connection:

conn = sqlite3.connect('example.db')

# 當 DB 不存在時, 那會自己建立它:
# special name :memory: to create a database in RAM.

# 一切操作都是透過 cursor 來實現 !

cur = conn.cursor()

# 執行 SQL:

>>> cur.execute("create table accesslog( username text, atime text, success integer)")
<sqlite3.Cursor object at 0xf728dd40>

>>> cur.execute("INSERT INTO accesslog VALUES ('datahunter','2012-12-5',1)")
<sqlite3.Cursor object at 0xf728dd40>

# 所有 insert 的動作都要 commit 才會寫入

conn.commit()                # result 永遠都是 null 的

# close() 是沒有包含 commit 的!!

conn.close()

 

---

拿出 Data

 

* 一共有兩個方法拿 Data (fetchone(), fetchall())

fetchone()

Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.

result: <type 'tuple'>

fetchall()

Fetches all (remaining) rows of a query result, returning a list.

result: <type 'list'>

    rows = cur.fetchall()

    for row in rows:
        print row

支援 list 及 dict 的架取

list:

result = cursor.execute('''SELECT name, email, phone FROM users''')
for row in result:
    print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))

dict:

result = cursor.execute('''SELECT name, email, phone FROM users''')
for row in result:
    # row['name'] returns the name column in the query, row['email'] returns email column.
    print('{0} : {1}, {2}'.format(row['name'], row['email'], row['phone']))

完整的 Code:

try:
    con = lite.connect('test.db')
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()

    print "SQLite version: %s" % data                
    
except lite.Error, e:
    
    print "Error %s:" % e.args[0]
    sys.exit(1)
    
finally:
    if con:
        con.close()

 

---

進階 execute

 

多行:

cur.executescript("""
    .......................
""")

Example

con.executescript("""
    insert into users (name) values ('tester1');
    insert into users (name) values ('tester2');
    insert into users (name) values ('tester3');
    """)

 

---

Placeholders:

tuple:

cur.execute("UPDATE Cars SET Price=? WHERE Id=?", (uPrice, uId))

當只有一個 placehoder 時

res = cur.execute("select * from users where uid=?",(1,))

dict:

cur.execute("SELECT Name, Price FROM Cars WHERE Id=:Id", {"Id": 123})

# ?     = placeholders(tuple)
# :s    = placeholders(dict)

 

---

insert 與 lastrowid

 

c = conn.cursor()
result  = c.execute("INSERT INTO username VALUES ('datahunter")
conn.commit()

rowid = c.lastrowid

conn.close()

# result 永遠都是 null 的

---

Delete

conn = sqlite3.connect('./db/cms.sqlite3')
cur = conn.cursor()
result = cur.execute("delete from address where aid = ?", (self.rowid, ))
conn.commit()
conn.close()

# result 永遠都是 null 的

---

插入大量資料: list

 

userlist = (
    (1, 'test1', 1234),
    (2, 'test2', 2345),
)

con = sqlite3.connect('test.db')
cur = con.cursor()
result = cur.executemany("INSERT INTO Cars VALUES(?, ?, ?)", userlist)

# result 永遠都是 null 的

 

---

DOC