Python Network Programming 简明教程

Python - Databases and SQL

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

The Python programming language has powerful features for database programming. Python supports various databases like SQLite, MySQL, Oracle, Sybase, PostgreSQL, etc. Python also supports Data Definition Language (DDL), Data Manipulation Language (DML) and Data Query Statements. The Python standard for database interfaces is the Python DB-API. Most Python database interfaces adhere to this standard.

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

Here is the list of available Python database interfaces: Python Database Interfaces and APIs. You must download a separate DB API module for each database you need to access.

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

In this chapter we will see the use of SQLite database in python programming language. It is done by using python’s inbuilt, sqlite3 module. You should first create a connection object that represents the database and then create some cursor objects to execute SQL statements.

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