本文依然是在 Jupyter notebook 中进行运行的。非常好用的 Python IDE有很多，例如PyCharm，Anaconda，Vim，PyDev，VisualStudio，VSCode，jupyter，Wing等待，各有优势，主要是根据自己的需求和爱好进行选择使用。之后会介绍一下我使用的 VSCode，选择它没别的原因，主要是安装包比较小，同时也还比较好用，学习成本不高。

Python 标准的数据库接口是 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。

Python 数据库 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 的支持。

本文将使用 PyMySQL 模块进行MySQL数据库的访问。

PyMySQL 介绍

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

安装 PyMySQL

PyPI 上提供了最新的稳定PyMySQL版本，可以使用 pip 安装：

pip install pymysql

连接数据库

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

• 已经创建了一个数据库 TESTDB
• 已经在 TESTDB 中创建了一个表 USER
• 表包含 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME 字段
• 设置用户ID“test”和密码“12345”访问TESTDB。
• PyMySQL 模块 已正确安装在机器上，了解其基本用法
• 已经了解 MySQL 基础知识

问题解决

注意：

一定要注意，my.ini 文件中mysql的安装路径是否正确。

出现错误：RuntimeError: ‘cryptography’ package is required for sha256_password or caching_sha2_password auth methods

解决：

1.安装 cryptography： pip install cryptography

2.重启 MySQL

3.重启 jupyter notebook

例如：

import pymysql

# 打开数据库连接
conn = pymysql.connect(  # 赋值给 conn连接对象
    host='localhost',  # 本地地址
    port=3306,  # 默认端口
    user='test',  # 用户名
    password='12345',  # 密码
    database='TESTDB',  # 连接数据库名称
    charset='utf8mb4'  # 编码 
)

# 产生游标对象
cursor = conn.cursor()

# 执行SQL语句
cursor.execute("SELECT VERSION()")

# 获取一行数据
data = cursor.fetchone()
print ("Database version : %s " % data)

# 关闭数据库连接
conn.close()
#运行结果
Database version : 8.0.32 

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

如果这段代码能够正常运行结果。那么表示 MySql 已经可以正常使用了。

创建数据库表

建立数据库连接后，就可以使用已创建游标的执行方法在数据库表中创建表或记录。

例如，创建USER表

数据插入操作

数据插入操作。将记录创建到数据库表中时，需要 INSERT 操作。例如执行 SQL INSERT 语句以在 EMPLOYEE 表中创建一条记录：

import pymysql

# 打开链接
conn = pymysql.connect(host="localhost",user="test",password="12345",database="TESTDB" )

cursor = conn.cursor()

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

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

cursor.execute(sql)

#sql 语句，插入一行数据
sql = """INSERT INTO USER(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('John', 'Mac', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 将变化提交到数据库
   conn.commit()
except:
   # 如果发生异常进行回滚
   conn.rollback()

# 关闭连接
conn.close()

也可以用直接传递参数的方式，拼写sql语句
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' %   (user_id, password))

数据表读操作

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

• fetchone() - 它获取查询结果集的下一行。结果集是使用游标对象查询表时返回的对象。
• fetchall() - 它获取结果集中的所有行。如果已经从结果集中提取了一些行，则它会从结果集中检索剩余的行。
• rowcount -- 这是一个只读属性，返回受 execute() 方法影响的行数。

import pymysql

conn = pymysql.connect(host="localhost",user="test",password="12345",database="TESTDB" )

cursor = conn.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)
try:
   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")

conn.close()
#运行结果
fname = John,lname = Mac,age = 20,sex = M,income = 2000

关于游标 cursor

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 生成一个游标对象，让数据自动组织成字典

cursor.scroll(1, 'relative') # 相对当前位置移动(相对移动)

cursor.scroll(0, 'absolute') # 相对数据开头位置移动(绝对移动)

execute() 返回值是执行SQL语句之后受影响的行数

fetchall() 获取所有的结果

fetchone() 获取结果集第一个结果

fetchmany(n) 括号内可以指定获取几个结果集

防SQL注入攻击

当 SQL 语句直接进行拼接时，容易造成SQL注入，不用验证用户名或则密码，例如

sql = "select * from userinfo where name='zhangsan' -- haha' and pwd='12345' "

sql = "select * from userinfo where name='zhangsan' or 1=1 -- heihei' and pwd='12345' "

这是利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑。

解决办法

针对用户输入的数据不要自己处理 交给专门的方法自动过滤。例如

sql = "select * from userinfo where name=%s and pwd=%s"

cursor.execute(sql, (username, password)) # 自动识别%s 并自动过滤各种符合 最后合并数据

execute方法，自动将 用户名和密码放在对应的%s内，并且放之前会自动对用户名和密码做特殊符号的校验，确保安全性。

更新数据操作

UPDATE 对任何数据库的操作意味着更新一条或多条记录。

import pymysql

conn = pymysql.connect(host="localhost",user="test",password="12345",database="TESTDB" )

cursor = conn.cursor()

sql = "UPDATE EMPLOYEE SET INCOME = INCOME + 500 WHERE SEX = '%c'" % ('M')
try:
   cursor.execute(sql)
   conn.commit()
except:
   conn.rollback()

conn.close()

删除数据操作

执行 DELETE 操作，可以从数据库中删除某些记录。要执行的 sql 语句：

sql = "DELETE FROM EMPLOYEE WHERE AGE >= '%d'" % (20)

事务操作

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

• 原子性 - 交易完成或什么都没有发生。
• 一致性 - 事务必须以一致的状态开始并使系统保持一致的状态。
• 隔离 - 交易的中间结果在当前交易之外不可见。
• 持久性 - 一旦提交事务，即使在系统出现故障后，效果也会持久。

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

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 将变化提交到数据库
   db.commit()
except:
   # 出错时进行回滚
   db.rollback()

提交 Commit 操作

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

回滚操作

如果对一项或多项更改不满意并且想要完全还原这些更改，或者某一部分操作出现错误，需要全部还原到原来状态，可以使用 rollback() 方法。

断开数据库连接

使用 close() 方法断开数据库连接，释放资源。

错误处理

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

DB API 定义了每个数据库模块中必须存在的一些错误。

• Warning 警告 用于非致命问题。必须继承 StandardError。
• Error 错误 错误的基类。必须继承 StandardError。
• InterfaceError 接口错误 用于数据库模块中的错误，而不是数据库本身。必须子类错误。
• DatabaseError 数据库错误 用于数据库中的错误。必须子类错误。
• DataError 数据错误 DatabaseError 的子类，指的是数据中的错误。
• OperationalError 操作错误 DatabaseError 的子类，指的是诸如与数据库的连接丢失之类的错误。这些错误通常不在 Python 脚本编写者的控制范围内。
• IntegrityError 完整性错误 DatabaseError 的子类，用于会破坏关系完整性的情况，例如唯一性约束或外键。
• InternalError 内部错误 DatabaseError 的子类，指的是数据库模块内部的错误，例如游标不再处于活动状态。
• ProgrammingError 编程错误 DatabaseError 的子类，指的是错误的表名和其他因为编程原因发生的错误。
• NotSupportedError 不支持错误 DatabaseError 的子类，指的是尝试调用不受支持的功能。

注意：在使用 Python 程序处理这些错误之前，要确保所安装的 MySQL 数据库 支持这些异常。

引入traceback模块，跟踪错误

可以引入python中的traceback模块，来跟踪错误。程序需要 import traceback。有三种方式跟踪错误。例如：

import pymysql
import traceback
import sys

 conn = self.getConnect()  
 # 使用cursor() 方法创建一个游标对象 cursor  
 cursor = conn.cursor()  
    
    
    try:  
        # 执行sql语句  
        cursor.execute(sql)  
        result = cursor.fetchone()  
    except: #方法一：采用traceback模块查看异常  
        #输出异常信息  
        traceback.print_exc()  
        # 如果发生异常，则回滚  
        db.rollback()  
    
    try:  
        # 执行sql语句  
        cursor.execute(sql)  
        results = cursor.fetchall()  
    except: #方法二：采用sys模块回溯最后的异常  
        #输出异常信息  
        info = sys.exc_info()  
        print( info[0], ":", info[1])  
        # 如果发生异常，则回滚  
        db.rollback()  
    
    try:  
        # 执行sql语句  
        cursor.execute(sql)  
        db.commit()  
    except: #如果想把这些异常保存到一个日志文件中，来分析这些异常  
        #将错误日志输入到目录文件中  
        f = open("c:log.txt", 'a')  
        traceback.print_exc(file=f)  
        f.flush()  
        f.close()  
        # 如果发生异常，则回滚  
        db.rollback()