【第三十五篇】SQLite – Python

SQLite – Python 安装

SQLite3 可使用 sqlite3 模块与 Python 进行集成。sqlite3 模块是由 Gerhard Haring 编写的。它提供了一个与 PEP 249 描述的 DB-API 2.0 规范兼容的 SQL 接口。您不需要单独安装该模块，因为 Python 2.5.x 以上版本默认自带了该模块。

为了使用 sqlite3 模块，您首先必须创建一个表示数据库的连接对象，然后您可以有选择地创建光标对象，这将帮助您执行所有的 SQL 语句。

Python sqlite3 模块 API

以下是重要的 sqlite3 模块程序，可以满足您在 Python 程序中使用 SQLite 数据库的需求。如果您需要了解更多细节，请查看 Python sqlite3 模块的官方文档。

连接数据库

下面的 Python 代码显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。

    #!/usr/bin/python

    import sqlite3

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

    print "Opened database successfully";

在这里，您也可以把数据库名称复制为特定的名称 :memory:，这样就会在 RAM 中创建一个数据库。现在，让我们来运行上面的程序，在当前目录中创建我们的数据库 test.db。您可以根据需要改变路径。保存上面代码到 sqlite.py 文件中，并按如下所示执行。如果数据库成功创建，那么会显示下面所示的消息：

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

创建表

下面的 Python 代码段将用于在先前创建的数据库中创建一个表：

    #!/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()

上述程序执行时，它会在 test.db 中创建 COMPANY 表，并显示下面所示的消息：

    Opened database successfully
    Table created successfully

INSERT 操作

下面的 Python 程序显示了如何在上面创建的 COMPANY 表中创建记录：

    #!/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()

上述程序执行时，它会在 COMPANY 表中创建给定记录，并会显示以下两行：

    Opened database successfully
    Records created successfully

SELECT 操作

下面的 Python 程序显示了如何从前面创建的 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()

上述程序执行时，它会产生以下结果：

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

上述程序执行时，它会产生以下结果：

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

上述程序执行时，它会产生以下结果：

    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