最後更新: 2021-10-15

目錄

• Version
• 應用流程
• 一般使用 (connect(), cursor(), execute(), commit(), close())
• 拿出 Data
• 同時執行多行 scripts
• Placeholders
• insert 與 lastrowid
• Delete
• 插入大量資料(executemany())

 

---

Version

 

匯入 sqlite3 的 class

>>> import sqlite3

Class 的 version

>>> sqlite3.version_info

(2, 4, 1)

engine version:

>>> sqlite3.sqlite_version_info

(3, 7, 3)

 

---

應用流程

 

1. 建立 Connection Objects

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

3. 建立 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

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]))

完整的 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()

 

---

同時執行多行 scripts

 

多行:

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

Example

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

 

---

Placeholders

 

• ?           placeholders(tuple)
• :s          placeholders(dict)

 

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})

 

---

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 的

 

---

插入大量資料(executemany())

 

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 的