SQLite – Python

安裝

SQLite3 可使用 sqlite3 模塊與 Python 進(jìn)行集成。sqlite3 模塊是由 Gerhard Haring 編寫的。它提供了一個與 PEP 249 描述的 DB-API 2.0 規(guī)范兼容的 SQL 接口。您不需要單獨(dú)安裝該模塊，因為 Python 2.5.x 以上版本默認(rèn)自帶了該模塊。

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

Python sqlite3 模塊 API

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

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

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

創(chuàng)建表

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

    #!/usr/bin/python

    import sqlite3

    conn = sqlite3.connect('test.db')
    print "Opened database successfully";

    conn.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.close()

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

    Opened database successfully
    Table created successfully

INSERT 操作

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

    #!/usr/bin/python

    import sqlite3

    conn = sqlite3.connect('test.db')
    print "Opened database successfully";

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
          VALUES (1, 'Paul', 32, 'California', 20000.00 )");

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
          VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
          VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

    conn.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

SELECT 操作

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

    #!/usr/bin/python

    import sqlite3

    conn = sqlite3.connect('test.db')
    print "Opened database successfully";

    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)生以下結(jié)果：

    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

UPDATE 操作

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

    #!/usr/bin/python

    import sqlite3

    conn = sqlite3.connect('test.db')
    print "Opened database successfully";

    conn.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)生以下結(jié)果：

    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

DELETE 操作

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

    #!/usr/bin/python

    import sqlite3

    conn = sqlite3.connect('test.db')
    print "Opened database successfully";

    conn.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)生以下結(jié)果：

    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