sqlite python 編程接口

1. 安裝

sqlite3 可使用 sqlite3 模塊與 python 進行集成。sqlite3 模塊是由 gerhard haring 編寫的。它提供了一個與 pep 249 描述的 db-api 2.0 規(guī)范兼容的 sql 接口。您不需要單獨安裝該模塊，因為 python 2.5.x 以上版本默認自帶了該模塊。

為了使用 sqlite3 模塊，您首先必須創(chuàng)建一個表示數(shù)據(jù)庫的連接對象，然后您可以有選擇地創(chuàng)建光標對象，這將幫助您執(zhí)行所有的 sql 語句。

2. python sqlite3 模塊 api

以下是重要的 sqlite3 模塊程序，可以滿足您在 python 程序中使用 sqlite 數(shù)據(jù)庫的需求。如果您需要了解更多細節(jié)，請查看 python sqlite3 模塊的官方文檔。

3. 連接數(shù)據(jù)庫

下面的 python 代碼顯示了如何連接到一個現(xiàn)有的數(shù)據(jù)庫。如果數(shù)據(jù)庫不存在，那么它就會被創(chuàng)建，最后將返回一個數(shù)據(jù)庫對象。

#!/usr/bin/python

import sqlite3

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

print "opened database successfully"

在這里，您也可以把數(shù)據(jù)庫名稱復制為特定的名稱 :memory:，這樣就會在 ram 中創(chuàng)建一個數(shù)據(jù)庫。現(xiàn)在，讓我們來運行上面的程序，在當前目錄中創(chuàng)建我們的數(shù)據(jù)庫 test.db。您可以根據(jù)需要改變路徑。保存上面代碼到 sqlite.py 文件中，并按如下所示執(zhí)行。如果數(shù)據(jù)庫成功創(chuàng)建，那么會顯示下面所示的消息：

$chmod +x sqlite.py
$./sqlite.py
open database successfully

4. 創(chuàng)建表

下面的 python 代碼段將用于在先前創(chuàng)建的數(shù)據(jù)庫中創(chuàng)建一個表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "opened database successfully"
c = conn.cursor()
c.execute('''create table company
       (id int primary key     not null,
       name           text    not null,
       age            int     not null,
       address        char(50),
       salary         real);''')
print "table created successfully"
conn.commit()
conn.close()

上述程序執(zhí)行時，它會在 test.db 中創(chuàng)建 company 表，并顯示下面所示的消息：

opened database successfully
table created successfully

5. insert 操作

下面的 python 程序顯示了如何在上面創(chuàng)建的 company 表中創(chuàng)建記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "opened database successfully"

c.execute("insert into company (id,name,age,address,salary) \
      values (1, 'paul', 32, 'california', 20000.00 )")

c.execute("insert into company (id,name,age,address,salary) \
      values (2, 'allen', 25, 'texas', 15000.00 )")

c.execute("insert into company (id,name,age,address,salary) \
      values (3, 'teddy', 23, 'norway', 20000.00 )")

c.execute("insert into company (id,name,age,address,salary) \
      values (4, 'mark', 25, 'rich-mond ', 65000.00 )")

conn.commit()
print "records created successfully"
conn.close()

上述程序執(zhí)行時，它會在 company 表中創(chuàng)建給定記錄，并會顯示以下兩行：

opened database successfully
records created successfully

6. select 操作

下面的 python 程序顯示了如何從前面創(chuàng)建的 company 表中獲取并顯示記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "opened database successfully"

cursor = c.execute("select id, name, address, salary  from company")
for row in cursor:
   print "id = ", row[0]
   print "name = ", row[1]
   print "address = ", row[2]
   print "salary = ", row[3], "\n"

print "operation done successfully"
conn.close()

上述程序執(zhí)行時，它會產(chǎn)生以下結果：

opened database successfully
id =  1
name =  paul
address =  california
salary =  20000.0

id =  2
name =  allen
address =  texas
salary =  15000.0

id =  3
name =  teddy
address =  norway
salary =  20000.0

id =  4
name =  mark
address =  rich-mond
salary =  65000.0

operation done successfully

7. update 操作

下面的 python 代碼顯示了如何使用 update 語句來更新任何記錄，然后從 company 表中獲取并顯示更新的記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "opened database successfully"

c.execute("update company set salary = 25000.00 where id=1")
conn.commit()
print "total number of rows updated :", conn.total_changes

cursor = conn.execute("select id, name, address, salary  from company")
for row in cursor:
   print "id = ", row[0]
   print "name = ", row[1]
   print "address = ", row[2]
   print "salary = ", row[3], "\n"

print "operation done successfully"
conn.close()

上述程序執(zhí)行時，它會產(chǎn)生以下結果：

opened database successfully
total number of rows updated : 1
id =  1
name =  paul
address =  california
salary =  25000.0

id =  2
name =  allen
address =  texas
salary =  15000.0

id =  3
name =  teddy
address =  norway
salary =  20000.0

id =  4
name =  mark
address =  rich-mond
salary =  65000.0

operation done successfully

8. delete 操作

下面的 python 代碼顯示了如何使用 delete 語句刪除任何記錄，然后從 company 表中獲取并顯示剩余的記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "opened database successfully"

c.execute("delete from company where id=2;")
conn.commit()
print "total number of rows deleted :", conn.total_changes

cursor = conn.execute("select id, name, address, salary  from company")
for row in cursor:
   print "id = ", row[0]
   print "name = ", row[1]
   print "address = ", row[2]
   print "salary = ", row[3], "\n"

print "operation done successfully"
conn.close()

上述程序執(zhí)行時，它會產(chǎn)生以下結果：

opened database successfully
total number of rows deleted : 1
id =  1
name =  paul
address =  california
salary =  20000.0

id =  3
name =  teddy
address =  norway
salary =  20000.0

id =  4
name =  mark
address =  rich-mond
salary =  65000.0

operation done successfully