Python Network Programming 简明教程

Python - Databases and SQL

Python 编程语言具有功能强大的数据库编程功能。Python 支持各种数据库，如 SQLite、MySQL、Oracle、Sybase、PostgreSQL 等。Python 还支持数据定义语言 (DDL)、数据操作语言 (DML) 和数据查询语句。Python 数据库接口的标准是 Python DB-API。大多数 Python 数据库接口都遵循此标准。

以下是可用的 Python 数据库接口列表： Python Database Interfaces and APIs 。您必须为需要访问的每个数据库下载一个单独的 DB API 模块。

在本章中，我们将看到在 Python 编程语言中使用 SQLite 数据库。使用 Python 的内置 sqlite3 模块即可实现。您应首先创建一个代表数据库的连接对象，然后创建一些游标对象来执行 SQL 语句。

Connect To Database

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

Create a Table

将使用以下 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 Operation

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

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

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

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