SQLite - Python

---

---

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

安装

SQLite3 可以使用 sqlite3 模块与 Python 集成，该模块由 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