Sqlite 简明教程

SQLite - Python

在本章中，您将学习如何在 Python 程序中使用 SQLite。

In this chapter, you will learn how to use SQLite in Python programs.

Installation

SQLite3 可以使用 sqlite3 模块与 Python 集成，该模块由 Gerhard Haring 编写。它提供与 PEP 249 描述的 DB-API 2.0 规范兼容的 SQL 接口。您无需单独安装此模块，因为它默认随 Python 2.5.x 及更高版本一起提供。

SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards.

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

To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements.

Python sqlite3 module APIs

以下是重要的 sqlite3 模块例程，它可以满足您使用 Python 程序处理 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，则可以查看 Python sqlite3 模块的官方文档。

Following are important sqlite3 module routines, which can suffice your requirement to work with SQLite database from your Python program. If you are looking for a more sophisticated application, then you can look into Python sqlite3 module’s official documentation.

Sr.No.	API & Description
1	sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file. 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 the given database name does not exist then this call will create the database. You can specify filename with the required path as well if you want to create a database anywhere else except in the 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 the 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 a 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.

Connect To Database

以下 Python 代码展示了如何连接到现有数据库。如果数据库不存在，则将创建数据库，最后将返回一个数据库对象。

Following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

#!/usr/bin/python

import sqlite3

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

print "Opened database successfully";

在此，您还可以将数据库名称作为 :memory: 的特殊名称提供，以创建 RAM 中的数据库。现在，让我们运行上述程序以在当前目录中创建我们的数据库 test.db 。您可以根据需要更改路径。将上述代码保存在 sqlite.py 文件中，并按照下方所示执行。如果数据库成功创建，则它将显示以下消息。

Here, you can also supply database name as the special name :memory: to create a database in RAM. Now, let’s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. Keep the above code in sqlite.py file and execute it as shown below. If the database is successfully created, then it will display the following message.

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

Create a Table

将使用以下 Python 程序在先前创建的数据库中创建一个表。

Following Python program will be used to create a table in the previously created database.

#!/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 表，并且它将显示以下消息：

When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages −

Opened database successfully
Table created successfully

INSERT Operation

以下 Python 程序展示了如何在上面示例中创建的 COMPANY 表中创建记录。

Following Python program shows how to create records in the COMPANY table created in the above example.

#!/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 表中创建给定的记录，并且它将显示以下两行：

When the above program is executed, it will create the given records in the COMPANY table and it will display the following two lines −

Opened database successfully
Records created successfully

SELECT Operation

以下 Python 程序演示如何从上述示例中创建的 COMPANY 表中获取和显示记录。

Following Python program shows how to fetch and display records from the COMPANY table created in the above example.

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

执行上述程序后，它将产生以下结果。

When the above program is executed, it will produce the following result.

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 Operation

以下 Python 代码展示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取和显示更新过的记录。

Following Python code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table.

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

执行上述程序后，它将产生以下结果。

When the above program is executed, it will produce the following result.

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 Operation

以下 Python 代码展示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取和显示剩余的记录。

Following Python code shows how to use DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table.

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

执行上述程序后，它将产生以下结果。

When the above program is executed, it will produce the following result.

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