当前位置：极客教程 > Python > Python3 入门教程 > Python 3 – MySQL数据库访问

Python 3 – MySQL数据库访问

Python 3 – MySQL数据库访问

Python数据库接口的标准是Python DB-API。大多数Python数据库接口都遵循此标准。

您可以选择适合您的应用程序的正确数据库。Python Database API支持各种数据库服务器，例如 −

这是可用的Python数据库接口列表 − Python Database Interfaces and APIs 。您必须为每个需要访问的数据库下载单独的DB API模块。例如，如果您需要访问Oracle数据库以及MySQL数据库，则必须下载Oracle和MySQL数据库模块。

DB API提供了一个最小的标准，使用Python结构和语法尽可能地与数据库一起使用。该API包括以下内容 −

  • 导入API模块。
  • 获取与数据库的连接。
  • 发出SQL语句和存储过程。
  • 关闭连接

Python具有对SQLite的内置支持。在本节中，我们将学习使用MySQL的所有概念。MySQLdb模块是与Python不兼容的流行的与MySQL的接口。相反，我们将使用 PyMySQL 模块。

PyMySQL是什么？

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

如何安装PyMySQL？

在进一步进行之前，请确保在计算机上安装了PyMySQL。只需在Python脚本中键入以下内容并执行它即可 −

#!/usr/bin/python3

import pymysql
Python

如果它产生以下结果，则意味着未安装MySQLdb模块−

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

最后的稳定版本可在PyPI上获得并且可以使用pip安装−

pip install pymysql
Python

或者（例如，如果没有pip）可以从 GitHub 下载tarball，并在Setuptools中安装如下−

$ # X.X是所需的pymysql版本（例如0.5或0.6）。
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # 现在可以安全地删除文件夹PyMySQL*。
Python

注意 −确保具有根特权以安装上述模块。

数据库连接

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

  • 您已创建数据库TESTDB。
  • 您已在TESTDB中创建表EMPLOYEE。
  • 该表具有字段FIRST_NAME，LAST_NAME，AGE，SEX和INCOME。
  • 用户ID“testuser”和密码“test123”设置为访问TESTDB。
  • 计算机上正确安装的Python模块PyMySQL。

实例

下面是连接到MySQL数据库“TESTDB”的示例−

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print ("数据库版本 : %s " % data)

# 关闭数据库连接
db.close()
Python

运行此脚本，会产生以下结果。

数据库版本 : 5.5.20-log
Python

如果与数据源建立了连接，则返回 Connection 对象并将其保存到 db 中以供进一步使用，否则将 db 设置为 None。接下来，使用 db 对象创建一个 cursor 对象，然后使用该对象来执行 SQL 查询。最后，在退出之前，确保关闭数据库连接并释放资源。

创建数据库表

一旦建立数据库连接，我们就可以使用创建的游标的 execute 方法创建表或将记录插入数据库表中。

示例

让我们创建一个名为 EMPLOYEE 的数据库表 –

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 如果 EMPLOYEE 表已经存在，删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据库表，例如
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()
Python

插入操作

插入操作用于将记录插入数据库表中。

示例

以下示例使用 SQL INSERT 语句向 EMPLOYEE 表中创建一条记录 –

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行 SQL 语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()

# 关闭数据库连接
db.close()
Python

上面的示例可以使用以下方法动态创建 SQL 查询：

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 动态准备 SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行 SQL 语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()

# 关闭数据库连接
db.close()
Python

示例

下面的代码段是另一种执行方式，您可以直接传递参数 –

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................
Python

读操作

在任何数据库上的读操作都表示从数据库中提取一些有用的信息。

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

  • fetchone() - 它取出查询结果集的下一行。当光标对象用于查询表时，返回一个结果集对象。

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

  • rowcount - 这是一个只读属性，返回由execute（）方法影响的行数。

示例

以下过程查询薪资超过1000美元的EMPLOYEE表中的所有记录-

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor（）方法创建一个游标对象cursor
cursor = db.cursor()

# 准备SQL查询以将记录插入数据库。
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL命令
   cursor.execute(sql)
   # 从列表中获取所有行。
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 现在打印获取的结果
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# 从服务器断开连接
db.close()
Python

输出

这将产生以下结果 –

fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000
Python

更新操作

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

以下过程更新具有 ‘M’ 性别的所有记录。这里，我们将所有男性的年龄增加一岁。

示例

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

# 准备SQL查询以更新所需的记录
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL命令
   cursor.execute(sql)
   # 在数据库中提交更改
   db.commit()
except:
   # 如果有任何错误，则回滚
   db.rollback()

# 从服务器断开连接
db.close()
Python

删除操作

当您想从数据库中删除一些记录时，需要使用DELETE操作。以下是从EMPLOYEE删除年龄大于20的所有记录的过程 –

示例

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor（）方法创建一个游标对象cursor
cursor = db.cursor()

# 准备SQL查询以删除所需的记录
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # 执行SQL命令
   cursor.execute(sql)
   # 在数据库中提交更改
   db.commit()
except:
   # 如果有任何错误，则回滚
   db.rollback()

# 从服务器断开连接
db.close()
Python

执行事务

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

  • 原子性 − 事务要么全部完成，要么就什么也不做。

  • 一致性 − 事务必须在一致的状态下开始，并将系统留在一致的状态下。

  • 隔离性 − 事务的中间结果在当前事务之外是不可见的。

  • 持久性 − 一旦事务提交，其效果是持久的，即使系统发生故障。

Python DB API 2.0 提供了两个方法，即 commit 和 rollback 用于执行事务。

示例

您已经知道如何实现事务。以下是类似的示例 −

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

COMMIT 操作

Commit 是一种操作，它向数据库发送信号以完成更改，在此操作之后，无法撤销任何更改。

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

db.commit()
Python

ROLLBACK 操作

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

以下是调用 rollback() 方法的简单示例。

db.rollback()
Python

断开数据库连接

要断开数据库连接，请使用 close() 方法。

db.close()
Python

如果用户使用 close() 方法关闭与数据库的连接，则由 DB 回滚所有未完成的事务。但是，与其依赖于任何数据库低级别实现的详细信息，您的应用程序最好明确调用 commit 或 rollback。

处理错误

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

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

序号 异常及其说明
1 警告 用于非致命问题。必须是StandardError的子类。
2 错误 错误的基类。必须是StandardError的子类。
3 接口错误 用于数据库模块中的错误，而不是数据库本身的错误。必须是Error的子类。
4 数据库错误 用于数据库中的错误。必须是Error的子类。
5 数据错误 指数据中的错误，是DatabaseError的子类。
6 操作错误 指诸如与数据库的连接丢失之类的错误。这些错误通常不受Python脚本程序员的控制。
7 完整性错误 对于可能损坏关系完整性的情况，如唯一性约束或外键，属于DatabaseError的子类。
8 内部错误 指数据库模块内部的错误，如光标不再有效。属于DatabaseError的子类。
9 编程错误 指如错误的表名等原因导致的错误和其它人为造成的错误。属于DatabaseError的子类。
10 不支持的错误 指调用不支持的功能。属于DatabaseError的子类。

您的Python脚本应该处理这些错误。但在使用上述任何异常之前，请确保您的MySQLdb支持该异常。您可以通过阅读DB API 2.0规范来获取更多关于它们的信息。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

Python3 入门教程

登录

找回密码？

注册