Python 3 - MySQL数据库访问

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

您可以为您的应用程序选择正确的数据库. Python Database API支持各种数据库服务器,例如 :

  • GadFly

  • mSQL

  • MySQL

  • PostgreSQL

  • Microsoft SQL Server 2000

  • Informix

  • Interbase

  • Oracle

  • Sybase

  • SQLite

以下是可用的Python数据库接口列表 :   Python数据库接口和API .您必须为需要访问的每个数据库下载单独的DB API模块.例如,如果您需要访问Oracle数据库以及MySQL数据库,则必须同时下载Oracle和MySQL数据库模块.

DB API提供最低的工作标准尽可能使用Python结构和语法的数据库.此API包括以下 :

  • 导入API模块.

  • 获取与发布SQL语句和存储过程.

  • 关闭连接

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

什么是PyMySQL?

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

如何安装PyMySQL?

在继续之前,请确保你的机器上安装了PyMySQL.只需在Python脚本中键入以下内容并执行它 :

#!/usr/bin/python3

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

或者(例如,如果pip不可用) ),可以从 GitHub 下载tarball,并使用Setuptools安装如下 :

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

注意 : 确保您具有root权限以安装上述模块.

数据库连接

在连接到MySQL数据库之前,请确保以下几点和减号;

  • 您已创建数据库TESTDB.

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

  • 此表包含字段FIRST_NAME,LAST_NAME,AGE,SEX和INCOME.

  • 用户ID"testuser"和密码"test123"设置为访问TESTDB.

  • 安装Python模块PyMySQL正确地在你的机器上.

  • 你已经通过MySQL教程来理解 MySQL基础知识.

示例

以下是连接MySQL数据库的示例"TESTDB" :

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

运行此脚本时,会产生以下结果.

Database version : 5.5.20-log

如果与数据源建立连接,则返回一个连接对象并保存到 db 供进一步使用,否则 db 设置为无.接下来, db 对象用于创建游标对象,该对象又用于执行SQL查询.最后,在发布之前,它确保关闭数据库连接并释放资源.

创建数据库表

建立数据库连接后,我们准备使用创建的游标的执行方法在数据库表中创建表或记录.

示例

让我们创建一个数据库表EMPLOYEE :

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

INSERT操作

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

示例

以下例如,执行SQL INSERT 语句在EMPLOYEE表中创建记录 :

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
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()

# disconnect from server
db.close()

上面的例子可以写成如下动态创建SQL查询 :

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
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()

# disconnect from server
db.close()

示例

以下代码段是另一种执行形式,您可以直接传递参数 :

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

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

阅读操作

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

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

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

  • fetchall() : 它获取结果集中的所有行.如果已经从结果集中提取了某些行,那么它将从结果集中检索剩余行的
    .

  • rowcount : 这是一个只读属性,它返回受execute()方法影响的行数.

示例

以下程序查询EMPLOYEE表中薪水超过1000&minus的所有记录;

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

输出

这将产生以下结果 :

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

更新操作

UPDATE对任何数据库的操作意味着更新一个或多个记录,这些记录已在数据库.

以下过程将所有具有SEX的记录更新为'M'.在这里,我们将所有男性的年龄增加一年.

示例

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
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()

# disconnect from server
db.close()

DELETE操作

如果要从数据库中删除某些记录,则需要执行DELETE操作.以下是从EMPLOYEE中删除所有记录的过程,其中AGE超过20 :

示例

#!/usr/bin/python3

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

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

# disconnect from server
db.close()

执行交易

事务是一种确保数据一致性的机制.交易具有以下四个属性 :

  • Atomicity : 交易完成或根本没有任何事情发生.

  • 一致性 : 交易必须以一致的状态开始,并使系统保持一致状态.

  • 隔离 : 在当前交易之外,交易的中间结果不可见.

  • 耐久性 : 提交事务后,即使系统出现故障,效果也会持续存在.

Python DB API 2.0提供了两种方法提交回滚交易.

示例

您已经知道如何实现交易.下面是一个类似的示例 :

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

COMMIT操作

提交是一个操作,它向数据库发出绿色信号以完成更改,并且在此操作之后,不能恢复任何更改.

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

db.commit()

ROLLBACK操作

如果您对一项或多项更改不满意,并且想要完全恢复这些更改,然后使用 rollback()方法.

这是一个调用t的简单示例他 rollback()方法.

db.rollback()

断开数据库

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

db.close()

如果用户使用close()方法关闭了与数据库的连接,则会滚动任何未完成的事务由DB回来.但是,您的应用程序最好不要依赖于任何DB较低级别的实现细节,而是明确地调用commit或rollback.

处理错误

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

DB API定义了许多错误必须存在于每个数据库模块中.下表列出了这些例外情况.

Sr.No.Exception&说明
1

Warning

用于非致命问题.必须是StandardError的子类.

2

Error

错误的基类.必须是StandardError的子类.

3

InterfaceError

用于数据库模块中的错误,而不是数据库本身.必须是子类错误.

4

DatabaseError

用于数据库中的错误.必须是子类错误.

5

DataError

引用数据错误的DatabaseError子类.

6

OperationalError

DatabaseError的子类,指的是丢失与数据库的连接等错误.这些错误通常不受Python脚本编程器的控制.

7

IntegrityError

对于会破坏关系完整性的情况,例如唯一性约束或外来情况,DatabaseError的子类键.

8

InternalError

DatabaseError的子类,它引用数据库模块内部的错误,例如游标不再处于活动状态.

9

ProgrammingError

DatabaseError的子类,它引用了诸如错误的表名等错误以及可以安全地归咎于你的其他事情.

10

NotSupportedError

DatabaseError的子类,指的是尝试调用不支持的功能.

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