Python 数据库访问

程序执行期间产生的数据输入和生成的数据存储在RAM中。如果需要对其进行持久化存储，就需要将其存储在数据库表中。目前有各种关系型数据库管理系统（RDBMS）可供选择。

• GadFly
• MySQL
• PostgreSQL
• Microsoft SQL Server
• Informix
• Oracle
• Sybase
• SQLite
• 还有许多其他的…

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

关系型数据库使用结构化查询语言（SQL）来对数据库表执行插入、删除、更新等操作。然而，不同数据库类型之间的SQL实现是不相同的。这就引起了不兼容性的问题。一个数据库的SQL指令与其他数据库不匹配。

为了解决这种不兼容性，提出了一个名为DB-API的公共接口，该接口在PEP（Python Enhancement Proposal）249中提出。该提案要求与Python互动的数据库驱动程序应该符合DB-API规范。

Python的标准库包括sqlite3模块，它是SQLite3数据库的DB_API兼容驱动程序，也是DB-API的参考实现。

由于所需的DB-API接口已经内置，我们可以轻松地在Python应用程序中使用SQLite数据库。对于其他类型的数据库，您将需要安装相应的Python包。

一个DB-API模块，例如sqlite3，包含连接和游标类。通过提供所需的连接凭证（如服务器名称和端口号，以及适用的用户名和密码），可以使用connect()方法获得连接对象。连接对象处理数据库的打开和关闭，以及事务控制机制的提交或回滚事务。

通过从连接对象获取的游标对象，在执行所有的CRUD操作时充当数据库的句柄。

sqlite3模块

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

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

让我们首先导入sqlite3并检查它的版本。

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

连接对象

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

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

连接类中定义了各种方法。其中之一是cursor()方法，它返回一个光标对象，关于光标对象我们将在下一节中了解。事务控制通过连接对象的commit()和rollback()方法实现。连接类有重要的方法来定义在SQL查询中使用的自定义函数和聚合函数。

光标对象

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

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

我们现在可以使用游标对象提供的execute()方法执行所有的SQL查询操作。这个方法需要一个字符串参数，该参数必须是一个有效的SQL语句。

创建数据库表

现在我们将在我们新创建的’testdb.sqlite3’数据库中添加Employee表。在下面的脚本中，我们调用游标对象的execute()方法，给它一个包含CREATE TABLE语句的字符串。

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表的数据库。 我们可以通过在SQLite控制台中列出这个数据库中的表来进行验证。

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

插入操作

插入操作是在你想要将记录创建到数据库表中时所需的操作。

示例

以下示例执行SQL插入语句，在EMPLOYEE表中创建一条记录 –

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查询−

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()

读操作

对任何数据库的读操作意味着从数据库中获取一些有用的信息。

一旦建立了数据库连接，就可以对该数据库进行查询。您可以使用fetchone()方法获取单条记录，或者使用fetchall()方法从数据库表中获取多个值。

• fetchone() - 它获取查询结果集的下一行。当使用游标对象查询表时，结果集是返回的对象。

• fetchall() - 它获取结果集中的所有行。如果已经从结果集中提取了一些行，则会从结果集中检索剩余的行。

• rowcount - 这是一个只读属性，返回通过执行方法影响到的行数。

示例

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

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()

它将产生以下 输出 −

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

更新操作

在任何数据库上的更新操作意味着更新已经存在于数据库中的一个或多个记录。

下面的过程更新所有收入为2000的记录。在这里，我们将收入增加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操作。以下是删除所有收入小于2000的EMPLOYEE记录的步骤。

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()

执行事务

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

• 原子性 - 事务要么完成，要么完全不执行。

• 一致性 - 事务必须在一个一致的状态下开始，并在离开系统时保持一致的状态。

• 隔离性 - 事务的中间结果对当前事务以外的部分是不可见的。

• 持久性 - 一旦事务提交，其效果就具有持久性，即使在系统故障之后。

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

示例

您已经了解如何实现事务。这是一个类似的示例-

# 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()

提交操作

提交是一种操作，向数据库发出信号以完成更改，在此操作之后，不可撤销任何更改。

以下是调用提交方法的简单示例。

db.commit()

回滚操作

如果您对一个或多个更改不满意，并且希望完全恢复这些更改，则使用rollback()方法。

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

db.rollback()

PyMySQL模块

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

安装PyMySQL

在进一步操作之前，确保您的机器上已经安装了PyMySQL。只需在Python脚本中输入以下内容并执行即可：

import PyMySQL

如果出现以下结果，则意味着MySQLdb模块未安装−

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

最新的稳定版本可在 PyPI 上获取，并可以使用 pip 安装：

pip install PyMySQL

注意 - 确保您具有根权限以安装上述模块。

MySQL数据库连接

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

• 您已经创建了一个名为TESTDB的数据库。

• 您已经在TESTDB中创建了一个名为EMPLOYEE的表。

• 该表具有字段：FIRST_NAME，LAST_NAME，AGE，SEX和INCOME。

• 用户ID为”testuser”，密码为”test123″，用于访问TESTDB。

• Python模块PyMySQL已正确安装在您的计算机上。

• 您已经通过MySQL教程了解了MySQL基础知识。

示例

要在之前的示例中使用MySQL数据库而不是SQLite数据库，我们需要将connect()函数更改如下 −

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

除了这个变化，每一个数据库操作都可以毫无困难地完成。

错误处理

错误有许多来源。一些例子包括在执行的SQL语句中的语法错误、连接失败，或者对已经取消或完成的语句句柄调用fetch方法。

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