sqlite3 可以与Python sqlite3 模块集成是由格哈德·哈林（Gerhard Haring）编写。 PEP249所描述的DB-API2.0规范，它提供了一个SQL接口兼容。不需要单独安装这个模块，因为它默认情况下随着Python版本在2.5.x 一起安装。
要使用sqlite3模块，必须首先创建一个连接对象，表示数据库中，然后可以选择创建游标对象，这将帮助在执行的所有SQL语句。
Python sqlite3 模块API
以下是重要的sqlite3模块程序，它可以足够Python程序SQLite数据库操作工作。如果要寻找一个更复杂的应用程序，那么你可以看看成的Python sqlite3 模块的官方文档。
| S.N. | API & 描述 |
|---|---|
| 1 |
sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file database. You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object. When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds). If given database name does not exist then this call will create the database. You can specify filename with required path as well if you want to create database anywhere else except in current directory. |
| 2 |
connection.cursor([cursorClass]) This routine creates a cursor which will be used throughout of your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor. |
| 3 |
cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style). For example:cursor.execute("insert into people values (?, ?)", (who, age)) |
| 4 |
connection.execute(sql [, optional parameters]) This routine is a shortcut of the above execute method provided by cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor's execute method with the parameters given. |
| 5 |
cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. |
| 6 |
connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given. |
| 7 |
cursor.executescript(sql_script) This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by semi colon (;). |
| 8 |
connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor's executescript method with the parameters given. |
| 9 |
connection.total_changes() This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. |
| 10 |
connection.commit() This method commits the current transaction. If you don.t call this method, anything you did since the last call to commit() is not visible from other database connections. |
| 11 |
connection.rollback() This method rolls back any changes to the database since the last call to commit(). |
| 12 |
connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! |
| 13 |
cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. |
| 14 |
cursor.fetchmany([size=cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. |
| 15 |
cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. |
连接到数据库
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()
上述程序执行时，它会创建表COMPANYtest.db的，它会显示以下消息：
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], " " 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], " " 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], " " 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
