Python - 数据库访问

上一页
下一页

Python 中的数据库访问

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

  • GadFly
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Informix
  • Oracle
  • Sybase
  • SQLite
  • 还有更多…

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

关系数据库使用 SQL（结构化查询语言）对数据库表执行 INSERT/DELETE/UPDATE 操作。但是，SQL 的实现方式因数据库类型而异。这会导致兼容性问题。一个数据库的 SQL 指令与另一个数据库不匹配。

DB-API（数据库 API）

为了解决这个问题，Python 增强提案 (PEP) 249 引入了一个标准化的接口，称为 DB-API。此接口为数据库驱动程序提供了一个一致的框架，确保不同数据库系统的行为一致。它通过建立一组通用的规则和方法，简化了在不同数据库之间切换的过程。

driver_interfaces

使用 Python 和 SQLite

Python 的标准库包含 **sqlite3** 模块，这是一个与 DB_API 兼容的 SQLite3 数据库驱动程序。它作为 DB-API 的参考实现。对于其他类型的数据库，您需要安装相关的 Python 包。

数据库 Python 包
Oracle cx_oracle, pyodbc
SQL Server pymssql, pyodbc
PostgreSQL PostgreSQL
MySQL MySQL Connector/Python, pymysql

使用 SQLite

由于内置的 **sqlite3** 模块，使用 Python 和 SQLite 非常容易。该过程包括：

  • **建立连接 -** 使用 sqlite3.connect() 创建连接对象，提供必要的连接凭据，例如服务器名称、端口、用户名和密码。

  • 事务管理 − 连接对象管理数据库操作，包括打开、关闭和事务控制（提交或回滚事务）。

  • 游标对象 − 从连接中获取游标对象以执行SQL查询。游标充当数据库上CRUD（创建、读取、更新、删除）操作的网关。

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

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

INSERT 操作

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

示例

下面的示例执行SQL INSERT语句，以在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()

READ 操作

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

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

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

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

  • rowcount − 这是一个只读属性，返回受execute()方法影响的行数。

示例

在下面的代码中，游标对象执行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

UPDATE 操作

任何数据库上的UPDATE操作都意味着更新数据库中已有的一个或多个记录。

以下过程更新所有income=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 操作

当您想要从数据库中删除一些记录时，需要DELETE操作。以下是删除EMPLOYEE中INCOME小于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

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

COMMIT 操作

Commit是一个操作，它向数据库发出绿灯信号，以完成更改，并且在此操作之后，无法撤消任何更改。

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

db.commit()

ROLLBACK 操作

如果您对一个或多个更改不满意，并且想要完全撤消这些更改，则使用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

注意 − 确保您具有安装上述模块的root权限。

MySQL 数据库连接

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

  • 您已创建数据库TESTDB。

  • 您已在TESTDB中创建了一个表EMPLOYEE。

  • 此表包含字段FIRST_NAME、LAST_NAME、AGE、SEX和INCOME。

  • 已设置用户ID“testuser”和密码“test123”以访问TESTDB。

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

  • 您已学习MySQL教程以了解MySQL基础知识。

示例

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

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

除了此更改之外，每个数据库操作都可以毫无困难地执行。

处理错误

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

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

序号 异常和描述
1

警告

用于非致命性问题。必须是StandardError的子类。
2

错误

错误的基类。必须是StandardError的子类。
3

InterfaceError

用于数据库模块中的错误，而不是数据库本身。必须是Error的子类。
4

DatabaseError

用于数据库中的错误。必须是Error的子类。
5

DataError

DatabaseError的子类，指的是数据中的错误。
6

OperationalError

DatabaseError的子类，指的是错误，例如与数据库的连接丢失。这些错误通常不受Python脚本编写者的控制。
7

IntegrityError

DatabaseError的子类，用于会破坏关系完整性的情况，例如唯一性约束或外键。
8

InternalError

DatabaseError的子类，指的是数据库模块内部的错误，例如游标不再活动。
9

ProgrammingError

DatabaseError的子类，指的是错误，例如错误的表名和其他可以安全地归咎于您的错误。
10

NotSupportedError

DatabaseError的子类，指的是尝试调用不受支持的功能。
打印页面
上一页
下一页
广告