SQLite – Python

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

安装

SQLite3 可以使用由 Gerhard Haring 编写的 sqlite3 模块与 Python 集成。它提供了一个符合 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

插入操作

下面的 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

选择操作

以下 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

更新操作

以下 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

删除操作

以下 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