Python 简明教程

Python - Database Access

Database Access in Python

在 Python 中使用数据库访问用于与数据库进行交互，允许应用程序以一致的方式存储、检索、更新和管理数据。各种关系数据库管理系统 (RDBMS) 都支持这些任务，每个系统都需要用于连接的特定 Python 包 -

Database access in Python is used to interact with databases, allowing applications to store, retrieve, update, and manage data consistently. Various relational database management systems (RDBMS) are supported for these tasks, each requiring specific Python packages for connectivity −

GadFly
MySQL
PostgreSQL
Microsoft SQL Server
Informix
Oracle
Sybase
SQLite
and many more…

在程序执行期间输入和生成的数据存储在 RAM 中。如果要永久存储它，则需要将它存储在数据库表中。

Data input and generated during execution of a program is stored in RAM. If it is to be stored persistently, it needs to be stored in database tables.

关系数据库使用 SQL（结构化查询语言）对数据库表执行插入/删除/更新操作。但是，SQL 的实现因一种数据库类型而异。这会产生不兼容问题。一个数据库的 SQL 指令与另一个不匹配。

Relational databases use SQL (Structured Query Language) for performing INSERT/DELETE/UPDATE operations on the database tables. However, implementation of SQL varies from one type of database to other. This raises incompatibility issues. SQL instructions for one database do not match with other.

DB-API (Database API)

为了解决此兼容性问题，Python Enhancement Proposal (PEP) 249 引入了称为 DB-API 的标准化界面。此接口为数据库驱动程序提供了一个一致的框架，确保在不同的数据库系统中保持统一的行为。它通过建立一组通用的规则和方法简化了在各种数据库之间转换的过程。

To address this issue of compatibility, Python Enhancement Proposal (PEP) 249 introduced a standardized interface known as DB-API. This interface provides a consistent framework for database drivers, ensuring uniform behavior across different database systems. It simplifies the process of transitioning between various databases by establishing a common set of rules and methods.

Using SQLite with Python

Python 的标准库包括 sqlite3 模块，这是 SQLite3 数据库的与 DB_API 兼容的驱动程序。它用作 DB-API 的参考实现。对于其他类型的数据库，你必须安装相关的 Python 包 -

Python’s standard library includes sqlite3 module, a DB_API compatible driver for SQLite3 database. It serves as a reference implementation for DB-API. For other types of databases, you will have to install the relevant Python package −

Working with SQLite

借助内置的 sqlite3 模块，使用 SQLite 搭配 Python 非常容易。流程包括以下步骤：

Using SQLite with Python is very easy due to the built-in sqlite3 module. The process involves −

Connection Establishment − Create a connection object using sqlite3.connect(), providing necessary connection credentials such as server name, port, username, and password.
Transaction Management − The connection object manages database operations, including opening, closing, and transaction control (committing or rolling back transactions).
Cursor Object − Obtain a cursor object from the connection to execute SQL queries. The cursor serves as the gateway for CRUD (Create, Read, Update, Delete) operations on the database.

在本文档中，我们将学习如何使用 Python 访问数据库、如何在 SQLite 数据库中存储 Python 对象数据，以及如何从 SQLite 数据库检索数据并使用 Python 程序对其进行处理。

In this tutorial, we shall learn how to access database using Python, how to store data of Python objects in a SQLite database, and how to retrieve data from SQLite database and process it using Python program.

The sqlite3 Module

SQLite 是一个无服务器的基于文件的轻量级事务关系数据库。它不需要任何安装，而且无需任何用户名或密码等凭据即可访问数据库。

SQLite is a server-less, file-based lightweight transactional relational database. It doesn’t require any installation and no credentials such as username and password are needed to access the database.

Python 的 sqlite3 模块包含了适用于 SQLite 数据库的 DB-API 实现。它由 Gerhard Häring 所撰写。我们将学习如何使用 sqlite3 模块来实现 Python 数据库访问。

Python’s sqlite3 module contains DB-API implementation for SQLite database. It is written by Gerhard Häring. Let us learn how to use sqlite3 module for database access with Python.

我们先从导入 sqlite3 并检查其版本开始。

Let us start by importing sqlite3 and check its version.

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.39.4'

The Connection Object

连接对象由 sqlite3 模块中的 connect() 函数设置。此函数的第一个参数是一个字符串，用于表示 SQLite 数据库文件的路径（相对或绝对）。该函数返回一个引用数据库的连接对象。

A connection object is set up by connect() function in sqlite3 module. First positional argument to this function is a string representing path (relative or absolute) to a SQLite database file. The function returns a connection object referring to the database.

>>> conn=sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>

连接类中定义了不同的方法。其中一个方法是 cursor() 方法，该方法返回一个游标对象，我们将在下一部分中了解它。事务控制是由连接对象的 commit() 和 rollback() 方法实现的。连接类具有重要的方法，可定义要在 SQL 查询中使用的自定义功能和聚合。

Various methods are defined in connection class. One of them is cursor() method that returns a cursor object, about which we shall know in next section. Transaction control is achieved by commit() and rollback() methods of connection object. Connection class has important methods to define custom functions and aggregates to be used in SQL queries.

The Cursor Object

接下来，我们需要从连接对象获取游标对象。它是您在数据库上执行任何 CRUD 操作时对数据库的控制。连接对象的 cursor() 方法返回游标对象。

Next, we need to get the cursor object from the connection object. It is your handle to the database when performing any CRUD operation on the database. The cursor() method on connection object returns the cursor object.

>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>

现在，我们可以借助于 cursor 对象中可用的 execute() 方法执行所有 SQL 查询操作。该方法需要一个字符串参数，该字符串必须是有效的 SQL 语句。

We can now perform all SQL query operations, with the help of its execute() method available to cursor object. This method needs a string argument which must be a valid SQL statement.

Creating a Database Table

现在，我们将 Employee 表添加到新创建的“testdb.sqlite3”数据库中。在以下脚本中，我们调用 cursor 对象的 execute() 方法，并向其提供一个包含 CREATE TABLE 语句的字符串。

We shall now add Employee table in our newly created 'testdb.sqlite3' database. In following script, we call execute() method of cursor object, giving it a string with CREATE TABLE statement inside.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry='''
CREATE TABLE Employee (
EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRST_NAME TEXT (20),
LAST_NAME TEXT(20),
AGE INTEGER,
SEX TEXT(1),
INCOME FLOAT
);
'''
try:
   cur.execute(qry)
   print ('Table created successfully')
except:
   print ('error in creating table')
conn.close()

当以上程序运行时，在当前工作目录中会创建一个包含 Employee 表的数据库。

When the above program is run, the database with Employee table is created in the current working directory.

我们可以通过在 SQLite 控制台列出该数据库中的表来进行验证。

We can verify by listing out tables in this database in SQLite console.

sqlite> .open mydb.sqlite
sqlite> .tables
Employee

INSERT Operation

当您想将记录创建到数据库表中时，需要使用 INSERT 操作。

The INSERT Operation is required when you want to create your records into a database table.

Example

以下示例执行 SQL INSERT 语句以在 EMPLOYEE 表中创建记录：

The following example, executes SQL INSERT statement to create a record in the EMPLOYEE table −

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   cur.execute(qry)
   conn.commit()
   print ('Record inserted successfully')
except:
   conn.rollback()
print ('error in INSERT operation')
conn.close()

您还可以使用参数替换技术执行 INSERT 查询，如下所示：

You can also use the parameter substitution technique to execute the INSERT query as follows −

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES (?, ?, ?, ?, ?)"""
try:
   cur.execute(qry, ('Makrand', 'Mohan', 21, 'M', 5000))
   conn.commit()
   print ('Record inserted successfully')
except Exception as e:
   conn.rollback()
   print ('error in INSERT operation')
conn.close()

READ Operation

在任何数据库上进行 READ 操作意味着从数据库中获取一些有用的信息。

READ Operation on any database means to fetch some useful information from the database.

建立数据库连接后，便可针对此数据库进行查询。你可以使用 fetchone() 方法来获取单一记录，或使用 fetchall() 方法来获取数据库表格中的多个值。

Once the database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch a single record or fetchall() method to fetch multiple values from a database table.

fetchone() − It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.
fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.
rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.

Example

在以下代码中，光标对象执行 SELECT * FROM EMPLOYEE 查询。通过 fetchall() 方法获取结果集。我们以 for 循环打印出结果集中的所有记录。

In the following code, the cursor object executes SELECT * FROM EMPLOYEE query. The resultset is obtained with fetchall() method. We print all the records in the resultset with a for loop.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="SELECT * FROM EMPLOYEE"

try:
   # Execute the SQL command
   cur.execute(qry)
   # Fetch all the rows in a list of lists.
   results = cur.fetchall()
   for row in results:
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      # Now print fetched result
      print ("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income ))
except Exception as e:
   print (e)
   print ("Error: unable to fecth data")

conn.close()

它将生成以下 output −

It will produce the following output −

fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0
fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0

Update Operation

对任何数据库进行 UPDATE 操作表示更新数据库中已有的一个或多个记录。

UPDATE Operation on any database means to update one or more records, which are already available in the database.

以下过程更新了 income=2000 的所有记录。此处，我们将 income 增加 1000。

The following procedure updates all the records having income=2000. Here, we increase the income by 1000.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"

try:
   # Execute the SQL command
   cur.execute(qry, (1000,))
   # Fetch all the rows in a list of lists.
   conn.commit()
   print ("Records updated")
except Exception as e:
   print ("Error: unable to update data")
conn.close()

DELETE Operation

当你想从数据库中删除一些记录时，需要 DELETE 操作。以下过程用于删除 EMPLOYEE 中 INCOME 小于 2000 的所有记录。

DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from EMPLOYEE where INCOME is less than 2000.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="DELETE FROM EMPLOYEE WHERE INCOME<?"

try:
   # Execute the SQL command
   cur.execute(qry, (2000,))
   # Fetch all the rows in a list of lists.
   conn.commit()
   print ("Records deleted")
except Exception as e:
   print ("Error: unable to delete data")

conn.close()

Performing Transactions

事务是一种确保数据一致性的机制。事务具有以下四个属性−

Transactions are a mechanism that ensure data consistency. Transactions have the following four properties −

Atomicity − Either a transaction completes or nothing happens at all.
Consistency − A transaction must start in a consistent state and leave the system in a consistent state.
Isolation − Intermediate results of a transaction are not visible outside the current transaction.
Durability − Once a transaction was committed, the effects are persistent, even after a system failure.

Python DB API 2.0 提供了两种方法来提交或回滚事务。

The Python DB API 2.0 provides two methods to either commit or rollback a transaction.

Example

你已经知道如何实现事务了。此处是一个类似的示例−

You already know how to implement transactions. Here is a similar example −

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > ?"
try:
   # Execute the SQL command
   cursor.execute(sql, (20,))
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

COMMIT Operation

提交是一种操作，它向数据库发出执行更改的绿灯信号，在此操作后，任何更改都不能再撤销。

Commit is an operation, which gives a green signal to the database to finalize the changes, and after this operation, no change can be reverted back.

此处是调用 commit 方法的一个简单示例。

Here is a simple example to call the commit method.

db.commit()

ROLLBACK Operation

如果你不满意其中的一个或多个更改，并且想要完全撤消那些更改，请使用 rollback() 方法。

If you are not satisfied with one or more of the changes and you want to revert back those changes completely, then use the rollback() method.

此处是调用 rollback() 方法的一个简单示例。

Here is a simple example to call the rollback() method.

db.rollback()

The PyMySQL Module

PyMySQL 是一个接口，用于从 Python 连接到 MySQL 数据库服务器。它实现了 Python 数据库 API v2.0 并包含一个纯 Python MySQL 客户端库。PyMySQL 的目标是成为 MySQLdb 的直接替代品。

PyMySQL is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and contains a pure-Python MySQL client library. The goal of PyMySQL is to be a drop-in replacement for MySQLdb.

Installing PyMySQL

在继续进行之前，请确保已在您的机器上安装了 PyMySQL。只需在您的 Python 脚本中输入以下内容并执行它 −

Before proceeding further, you make sure you have PyMySQL installed on your machine. Just type the following in your Python script and execute it −

import PyMySQL

如果它产生了以下结果，则表示 MySQLdb 模块未安装 −

If it produces the following result, then it means MySQLdb module is not installed −

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import PyMySQL
ImportError: No module named PyMySQL

最近的稳定版本在 PyPI 上可用，并可以使用 pip 安装 −

The last stable release is available on PyPI and can be installed with pip −

pip install PyMySQL

Note − 确保您具有安装上述模块的根权限。

Note − Make sure you have root privilege to install the above module.

MySQL Database Connection

在连接到 MySQL 数据库之前，请确保以下几点 −

Before connecting to a MySQL database, make sure of the following points −

You have created a database TESTDB.
You have created a table EMPLOYEE in TESTDB.
This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
User ID "testuser" and password "test123" are set to access TESTDB.
Python module PyMySQL is installed properly on your machine.
You have gone through MySQL tutorial to understand MySQL Basics.

Example

要在早期示例中使用 MySQL 数据库而不是 SQLite 数据库，我们需要将 connect() 函数更改为以下内容 −

To use MySQL database instead of SQLite database in earlier examples, we need to change the connect() function as follows −

import PyMySQL
# Open database connection
db = PyMySQL.connect("localhost","testuser","test123","TESTDB" )

除了此更改之外，每个数据库操作都可以轻松执行。

Apart from this change, every database operation can be performed without difficulty.

Handling Errors

有许多错误源。一些示例是执行的 SQL 语句中的语法错误、连接失败或为已取消或已完成的语句句柄调用 fetch 方法。

There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a connection failure, or calling the fetch method for an already cancelled or finished statement handle.

DB API 定义了一些错误，这些错误必须存在于每个数据库模块中。下表列出了这些异常。

The DB API defines a number of errors that must exist in each database module. The following table lists these exceptions.