MySQL Python connector API详解

无聊顺便把Python的API也看了。
Python有个数据库接口规范，目前是2.0版本，规范的作者显然希望数据库模块的作者都遵守这个规范。
Mysql的Python connector也遵循这个规范，并对原规范做了一些扩展，有一些Mysql特有的方法和属性。

MYSQL python connector

MySQL Connector可以理解为一个包，就像C的头文件和库一样。Python版的connector与python的数据库api规范第二版（Python Database API Specification v2.0 (PEP 249)）完全兼容。

安装

对于我安装的MYSQL版本来说，官方推荐安装connector 8版本，可以下载安装，但那样就需要手动挑选linux发行版，比较麻烦，懒人当然是pip安装了。

root@linux:~# apt install python3-pip
root@linux:~# pip3 search mysql-connector
mysql-connector (2.1.6)                                   - MySQL driver written in Python
bottle-mysql-connector (0.0.4)                            - MySQL integration for Bottle.
mysql-connector-python (8.0.11)                           - MySQL driver written in Python
mysql-connector-repackaged (0.3.1)                        - MySQL driver written in Python
mysql-connector-python-dd (2.0.2)                         - MySQL driver written in Python
mysql-connector-python-rf (2.2.2)                         - MySQL driver written in Python
....
# 看到官方推荐的8.0.11了，就安装这个。
root@linux:~# pip3 install mysql-connector-python

一些编写规范

在手册里提到了一些给开发者的忠告，想必都是金玉良言，先翻译留存

• 为安全起见，不要在主脚本中硬编码登陆数据库所需要的一些值，python提供了config.py模块，可以将这些值和你的主脚本隔离开来。

• python脚本经常在内存里创建和销毁大型的数据结构，直到达到内存限制。因为MYSQL经常处理大型的数据集合，这些集合的大小可能数倍于可用内存，所以从技术上优化内存使用和I/O是非常重要的。比如，在MYSQL表中，建议使用数字ID作为主键，而不是字符串；而且为每个表都设置主键也很重要，因为这些主键的值将会被用于创建二级目录，加快查询速度等。

• 在任何接受输入的程序中，都必须采取技术手段防止错误数据录入。

  • 有些错误输入是无意的，比如说超范围的数值或者不符合规范的字符串，客户端程序可以使用server端的check机制，比如说完整性约束来防止这些错误数据进入数据库，但在客户端一侧，应该捕获一场来报告错误并且采取恰当的处置措施。
  • 有些错误输入就是故意的了，比如说SQL注入攻击。这些攻击手段经常试图插入一些包含特定含义的字符，比如引号、分号、百分号、下划线、通配符等其他在SQL语句中有特殊含义的字符。在将用户数据提交给数据库server之前，必须对用户输入进行验证和筛查，排除任何可能带来数据库行为异常的数据。
• 考虑到python已经有pickle、cPickle，shelve模块来向硬盘读取和写入数据结构，因此建议只讲符合下列特点的数据放到Mysql数据库中：
  • 数据结构太大了，以致一次性载入内存十分吃力。

  此时你可以将这样的数据结构拆分后放入MYSQL数据库，利用SELECT只选出你需要的数据列，或者说直接用聚集函数来对多个结果进行一定的运算。可以通过调整server端的innodb_buffer_pool_size选项为查询结果预留一定的内存空间。

  • 数据结构太复杂，用单个数据结构很难描述。

  这时候可以将这种复杂的数据结构切分为不同的表，在查询时可以使用join来连接。应当确保你正确设置了外键约束，以保证各个表之间的数据是同步的。

  • 频繁需要更新，并且有并发操作需求。

  对数据的更新可能只是其中一小部分，犯不着把整个数据结构都载入内存重写一遍然后再写回去。这种情况下可以利用SQL提供的功能只更新其中一小部分，并且保证并发操作不冲突。

• 参照Mysql：数据库优化一章的内容，对数据库的设计、服务端的配置进行优化，确保在程序升级时不需要重写整个程序。

• 通过学习MYSQL的语法（相对于标准SQL来说有不少的扩展），可以避免重复造轮子。特别是在操作大量数据时，扩展过的MYSQL语法将帮助你快速实现目标……说好的编程指南，怎么忽然就变广告了= =

• 一句SQL语句可能很长，跨越多行不说了，还有可能包含单引号、双引号，为方便起见，可使用python提供的引号三连来写SQL语句，这样一切就OK了。

• 写一个快速的、可升级的Mysql程序的密码在于，在最开始创建数据库的阶段就要足够的骚。比如Oracle推荐在CREATE DATABASE时，就使用 ENGINE=INNODB语句，这个引擎好啊……多好懒得翻译了。定义数字类型的主键，而不是其他类型的主键，也有利于提升数据库的查询速度，并且更方便做外键。另外，在设置列数据类型时，尽量选择满足程序需要的最小数据类型，这样可以避免数据库在硬盘和内存之间频繁搬运数据。

Python数据库API规范

既然说了符合mysql的数据库API规范，那我们就直接看Python手册：数据库API规范v2.0

constructors（相关结构体）

与数据库建立连接必须通过connection类，这个模块必须提供connect方法供调用，该方法创建一个与数据库的连接，返回一个connection类。

模块内全局变量

• apilevel

  字符串。

  表示这个模块采取的是1.0规范还是2.0规范，目前只允许1.0和2.0这两个值。如果这个值没有给出，那么假设这个模块采取的是1.0规范。

• threadsafety

  数字。

  表示线程安全级别。0表示线程不应当共享该模块；1表示线程可以共享模块，但不能共享连接；2表示可以共享模块和连接，但不能共享游标；3表示可以共享模块、连接和游标。

  可以共享意味着可以不使用锁机制来进行线程同步和资源保护。需要注意的是，有时候即使用了锁机制仍然不能保证线程安全，因为有时候模块使用了全局变量。

• paramstyle

  字符串。

  规范参数的格式。可用值如下：

异常

• Warning

  必须定义为Python StandardError的子类，当遇到类似插入时遇到数据截断这样的重要情况时抛出该异常。

• Error

  必须定义为Python StandardError的子类，是其他所有数据库错误的基类，可以使用该异常在一条except语句中捕捉所有的数据库错误。Warning不应当被视为错误，不能使用Error类作为基类。

• InterfaceError

  Error的子类。表示那些与数据库接口有关，而非与数据库本身有关的错误。（没明白）

• DatabaseError

  Error的子类。表示那些与数据库有关的错误。

• DataError

  DatabaseError的子类。表示数字被0除、或者数字超过范围这样的一些错误。

• OperationalError

  DatabaseError的子类。表示数据库操作错误，并不一定是程序逻辑出错。比如说意外断开连接，或者数据名没找到，某次要求无法满足，或者产生了内存分配异常等。

• IntergrityError

  DatabaseError的子类。表示数据操作违反了数据库的完整性约束。

• InternalError

  DatabaseError的子类。表示一个内部错误，比如游标不再可用，或者此次请求没有同步等。

• ProgrammingError

  DatabaseError的子类。表示程序逻辑出错，比如说表没找到或者已存在，SQL语言出错，参数数量错误等。

• NotSupportedError

  DatabaseError的子类。当使用了数据库不支持的API或方法时抛出。比如在一个不支持的回滚的连接上调用了rollback()方法。

• 异常的继承树。

  StandardError
  |__Warning
  |__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError
  

Connection类（连接类）

该类必须支持以下方法：

• .close()

  立即关闭数据库连接。

  该操作将使数据库连接不可用。之后所有针对该连接的操作（包括对所有针对该连接的游标的操作）都会引发一个Error异常。需要注意的是，在没有committing the changes first（英文原文，指将此次修改保存到数据库。数据库操作：commit）的情况下关闭连接将会引起一次隐式的回滚。

• .commit()

  将所有未决的修改都保存到数据库。

  当数据库提供了自动保存特性时，.commit()方法应当默认关闭，另外提供一个方法来打开该方法。

• .rollback()

  该方法可选，因为并非所有数据库都支持回滚操作。

  如果数据库支持回滚操作，那么该方法将会使数据库回滚到所有未决的请求之前的状态。在保存修改之前关闭连接，将引起一次隐式的回滚。

• .cursor()

  返回一个cursor类（游标类）以操作该连接。

  如果数据库不提供直接的游标概念，那么模块就必须通过其他方式模仿一个游标出来，以满足该规范的要求。

Cursor类（游标类）

该类代表一个数据库游标，用于维护与数据库的操作环境。从同一个连接中创建的游标是紧密关联的，也就是说其中一个游标对数据库所做的任何操作都立即对其他游标可见；不同连接上创建的游标是否关联取决于具体实现。

该类必须支持以下方法和属性。

• .description

  这是一个只读数组，数组成员是一个7元素组，每个7元素组都用来描述一个结果列。

  也就是说这个属性应该是这样的：[[name, type_code, display_size…], [name, type_code, display_size…], [name, type_code, display_size…],…]，是一个数组的数组，每一个数组是一个七元组，描述了一列的属性。

  • name
  • type_code
  • display_size
  • internal_size
  • precision
  • scale
  • null_ok

  除了前两个是强制性的，其他5个值可以留None，如果操作没有返回任何值，或者还没有通过该游标做任何操作，那么.description值可以是None。

  type_code可以和Type类进行对比来确定到底是什么数据类型。

• .row_count

  只读属性，指示的是最近一个.execute*()返回的结果集的行数（类SELECT语句），或者被影响的行的数量（INSERT和UPDATE、DELETE等语句）

  当没有.execute*()操作时，或者最后一个操作无法该接口识别时，该值应当置为-1

• .callproc( procname [, parameters ])

  该方法可选，因为并非所有数据库都支持预设函数（不知道官方该怎么翻译，姑且这么翻译吧）。

  通过procname调用一个数据库预定义好的函数，之后的parameter必须与数据库函数所需要的参数一一对应。

  返回值的描述没看懂，粘贴原文以备以后看：

  The result of the call is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.

  该操作也有可能返回结果集，如果返回结果集，就需要通过.fetch*()方法来获取。

• .close()

  立即关闭游标。任何在一个关闭游标上进行的操作都会引发一个Error。

• .execute(operation [, parameters])

  执行数据库操作。

  参数可以以数组或字典的形式提供。参数的具体解读方式将依照paramstyle变量而定。

  游标将会保存operation的一个引用，如果相同的operation类再次被传入，游标就可以对相关操作进行优化，这对于那种不断用不同参数调用同一operation的程序来说能大幅度提高效率。当然，对于追求极致效率的程序来说，最好使用.setinputsizes()方法来预先生命参数的类型和大小，这之后调用同样的operation速度就更快，即使后面的operation传入的parameters不同，程序也能够正确运行，只是牺牲了一些速度（相对于优化过的速度而言）。

  返回值未定义。

• .executemany(operation, seq_of_parameters)

  seq_of_parameters是一个数组或者tuple，其成员就是.execute()方法的parameters。该方法对每一个成员都调用一遍operation。

  当多个命令可能产生多个结果集时，该方法的行为是未定义的。该API规范允许（而不是要求）具体的实现在遇到这种情况时抛出一个异常。（相比之下，要是C-API遇到这种情况，还能用mysql_next_result()一个结果集一个结果集地查询呢，哈哈）

  返回值未定义。

• .fetchone()

  获取结果集中的下一个元组，返回值为一个序列。当没有更多元组可以返回时，返回None。

  如果上一个.execute()没有产生结果集，或者还没有进行.execute()操作，该方法应当抛出一个Error（或其子类）的异常。

• .fetchmany([size=cursor.arraysize])

  返回结果集接下来的一些元组，类型是一个序列的序列（比如一个成员为元组的列表）。如果没有更多元组，那就返回一个空序列。

  一次抓取多少元组由参数定义，如果参数留空，那么游标的.arraysize参数将定义一次抓取多少行元组。该方法应当在size参数的限制下，尽量抓取尽可能多的元组，除非剩下的元组不够。

  如果上一个.execute()没有产生结果集，或者还没有进行.execute()操作，该方法应当抛出一个Error（或其子类）的异常。

  建议用户一般使用默认的.arraysize就可以，如果一定要重设这个值，那么尽量在程序中将这个值保持稳定，而不要变来变去。

• .fetchall()

  返回结果集中所有剩下的结果，类型为一个序列的序列。

  如果上一个.execute()没有产生结果集，或者还没有进行.execute()操作，该方法应当抛出一个Error（或其子类）的异常。

• .nextset()

  该方法是可选的，因为并不是所有的数据库都支持多结果集操作。

  该方法使游标直接调跳到下一个可用的结果集，丢弃到当前结果集中所有剩下的列。如果没有更多的结果集，该方法返回None，否则该函数返回一个真值并且下一个对.fetch*()的调用将从下一个结果集中抓取元组。

• .arraysize

  一个可读写的属性，用来规范.fetchmany()函数默认情况下一次性抓取多少行数据。

• .setinputsize(sizes)

  该方法一般在.execute*()系列方法之前调用，为operations的参数提前定义好内存需求。

  sizes是一个序列，每个成员对应execute*()方法的一个输入参数。成员应当是与即将使用的输入参数相符的Type类，或者是一个整数，定义字符串类型的参数的最大长度。如果某一个参数对应的item为None，那么模块将不会提前为该列预留空间，这在那些列是一些较大输入时比较有用。

  该方法可以啥也不干，当然用户也可以不调用它。（佛系佛系）

• .setoutputsize(size[,cloumn])

  当抓取一个较大的结果（LONGS，BLOBS）时，该方法可以为这些较大的结果列设置缓存空间。

  该方法可以啥也不干，当然用户也可以不调用它。

Type类以及构造体（Type Objects and Constructors）

一些数据库要求operation的输入参数有特定的格式。比如向一个Date类型的列传入数据，那传入的字符串必须是经过特别格式化的，同样的问题也存在于大型二进制数据或者RowID这样的列（自动编号blabla的）。

为了解决这个问题，一个模块必须提供一定的构造体，来创建符合格式规范的对象，当将这样的对象传输给游标的方法时，模块就可以检测适当的类型，并将数据正确绑定。

游标对象的description对象返回结果集中每一个列的属性，列属性中type_code必须与下列某一个Type类相等，一个Type类可能与多个Type code相等（比如DATETIME必须同时与Date，Time和Timestamp列相当）

• Date(year,month,day)

• Time(hour,minute,second)

• Timestamp(year,month,day,hour,minute,second)

• DateFromTicks(ticks)

  构造一个以距离Epoch（1970年1月1日00:00:00 UTC）的秒数为参数的日期戳。

• TimeFromTicks(ticks)

• TimestampFromTicks(ticks)

• Binary(string)

  构造一个二进制字符串流。

• STRING 类型

  用来匹配结果集中的字符串相关类型，如CHAR

• BINARY 类型

  匹配结果集中的二进制类型，如LONG, RAW, BLOBS

• NUMBER 类型

• DATETIME 类型

• ROWID 类型

  匹配结果集中的ROWID列。

SQL的NULL值对应Python中的None值。

可选的数据库API扩展

在数据库API v2.0发展过程中，模块作者经常在对API规范进行扩展，为了提高兼容性，并且为将来可能到来的新版API指明道路，这里将一些常用的扩展直接列出来。

在没有实现这些API的模块中，调用这些方法可能出现一个AttributeError（该类不存在这个方法）或者抛出一个NotSupportedError。

我们推荐模块作者让程序员知道他们用了扩展功能，通知方法是采用Python的警告框架，我们在鞋面列出了标准化的警告信息。

• Cursor.rownumber

  只读属性。提供当前游标所在结果集中的位置，从0开始编号，返回None表示当前位置无法判定。

  当前位置，是指下一个.fetch()方法将要返回的那一行的序列号。

  Warning Message: “DB-API extension cursor.rownumber used”

• Connection.Error, Connection.ProgrammingError等等

  所有API规范要求实现的异常类，都以属性的形式暴露在Connection类中，可以在模块范围内访问到。这些属性的出现简化了多模块环境下的错误处理。

  Warning Message: “DB-API extension connection.\ used”

• Cursor.connection

  只读属性，返回创建当前游标的链接对象的引用。该属性的存在简化了多连接环境下多态代码的编写。

  Warning Message: “DB-API extension cursor.connection used”

• Cursor.scroll(value[,mode=’relative’])

  将游标移动到结果集中的一个新位置，移动方式由mode指定。

  如果mode是relative（默认值），游标的位置将会被设置为相对当前位置偏移value位的位置，如果mode是absolute，游标的位置将会被设置为value指示的位置。

  如果一不小心移出结果集了，应当抛出一个IndexError异常，在这种情况下，游标的位置未定义，建议游标根本不要动。

  Warning Message: “DB-API extension cursor.scroll() used”

• Cursor.messages

  这是一个Python列表，模块可以将所有从游标指向的数据库中获取的消息（异常类、异常码）等消息以tuple的形式追加在该值后面。

  该值在除.fetch*()以外的所有方法调用时，都会被清空，以避免过高的内存占用，也可以手动执行del cursor.messages[:]来清空。

  所有由数据库产生的错误或警告信息都将追加在这个列表后面，因此通过检查这个列表，用户就能发现具体的错误类型和修正方式。

  该属性的存在是为了避免使用一些经常引发问题的warning异常。

  Warning Message: “DB-API extension cursor.messages used”

• Connection.messages

  和Cursor.messages的功能一致，只是这个错误列表是附着在Connection类上的。

  一样可以调用del connection.messages[:]来清空，或者在任何一个标准方法调用前清空。

  Warning Message: “DB-API extension connection.messages used”

• Cursor.next()

  和.fetchone()函数一样，返回结果集中的下一个元组，如果结果集已经用光了，在Python2.2之后将会抛出一个StopIteration异常，在2.2之前，因为没有StopIteration异常，会抛出一个IndexError异常。

  Warning Message: “DB-API extension cursor.next() used”

• Cursor.__iter__()

  返回一个标准的Python可迭代对象（意味着可以使用python的迭代语义，如for i in XX）

  Warning Message: “DB-API extension cursor.iter() used”

• Cursor.lastrowid

  只读属性，提供了一个最后一个修改过的RowID（大部分数据库只有在单个INSERT语句后才会返回一个RowID），如果最后一个operation没有设置RowID或者数据库不支持RowID，这个属性应当被设置为None

  如果一次操作产生了多个RowID，该值是未定义的。（比如使用.executemany()一次性执行了多个INSERT语句）

  Warning Message: “DB-API extension cursor.lastrowid used”

可选的错误处理扩展

核心的数据库API仅仅引入了一个较小的错误集，以向用户报告错误，在某些情况下，这些错误集可能很容易引起混乱，甚至使得一些操作变得不可能实现。为了处理这些情况、简化错误处理，数据库模块的作者可以选择实现一些用户可定义的错误处理器。

• Connection.errorhandler, Cursor.errorhandler

这是一个可读写的属性，指向一个在特定错误条件满足时将会调用的错误处理器。处理器必须是一个Python可调用对象，并接受以下参数：

errorhandler(connection, cursor, errorclass, errorvalue)

其中connection是生成当前游标的链接，cursor是当前游标，如果错误与游标无关，可以设为None，errorclass是一个异常类，该异常类使用errorvalue作为构造参数。

标准的errorhandler应当将错误信息附加到Connection.messages或者Cursor.message上，并抛出一个由errorclass和errorvalue参数定义的异常。

Warning Message: “DB-API extension .errorhandler used”

再说回Mysql的Python API

Mysql的Python API依靠mysql.connector类来实现。其模块结构如下：

mysql.connector
  errorcode
  errors
  connection
  constants
  conversion
  cursor
  dbapi
  locales
    eng
      client_error
  protocol
  utils

手册本身也只讲了程序员经常用到的一些功能而没有全部讲，所以我也只能翻译那么多了。

mysql.connertor模块

• mysql.connector.connect()方法

  该方法的功能与mysql.connector.MySQLConnection()完全一致，用于和服务端建立连接，如果没有给出任何参数，将会使用默认值。

  cnx = mysql.connector.connect(user='joe', database='test')
  cnx = MySQLConnection(user='joe', database='test')
  

  所有的参数值如下，有星号标的参数名是相同语义的参数名，仅为了与其他的Mysql Python API兼容，官方不建议使用：

  Connection Arguments for Connector/Python

• 鉴权

  连接中的鉴权依靠用户名和密码。

  如果给出了database参数，那么database将直接设置为该值。要改变一个连接的数据库，可以execute一个USE命令，或者直接修改MySQLConnection实例的.database属性。

• 字符编码

  字符集默认使用Puyhon的Unicode。如果不用，需要将use_unicode置为False。字符编码用的是UTF-8，要改变该设置，可以修改charset参数。改变一个已经存在的连接的字符编码，可以直接修改MySQLConnection实例的charset属性，但推荐的做法是使用SET NAMES这个SQL语句。

• 自动提交（autocommit）

  不建议打开

• 错误处理

  如果get_warnings参数为True，query生成的warning会自动被抓取到客户端，也可以将raise_on_warnings设置为True，这时候warning也会抛出异常。

• 缓存选项与结果集处理

  一般来说，生成结果集后，需要程序员手动将结果fetch到本地，如果buffered参数设置为True，这个过程将会自动完成，代价是内存占有量增加。

• 类型转化

  默认情况下，MySQL类型会直接转化为Python类型，比如DATETIME列会直接转化为datatime.datatime对象。可以将raw参数置为True以关闭自动类型转化，程序员必须手动处理这些类型转换。、

• 使用配置文件（Option-File）

  在mysql-python-api 2.0以上版本，该模块可以读取配置文件。默认情况下不读取，因此需要通过option_files参数来显式读取，该参数可以是一个文件路径的字符串，也可以是文件路径的字符串的数组，当存在option_files参数但未提供option_groups参数时，默认读取配置文件中的[client] 和 [connector_python]字段。如：

  cnx = mysql.connector.connect(option_files='/etc/mysql/connectors.cnf')
  ####或：
  mysql_option_files = [
      '/etc/mysql/connectors.cnf',
      './development.cnf',
  ]
  cnx = mysql.connector.connect(option_files=mysql_option_files)
  ####或
  cnx = mysql.connector.connect(option_files='/etc/mysql/connectors.cnf',
                                option_groups='connector_python')
  

  配置文件里可包含所有的参数，包括账号、密码、数据库等。如：

  [client]
  port=3306
  database=cpyapp
  user=nbt
  password=nbt123
  #需要注意的是，等号后面的值并没有引号，如果使用引号会出错。
  

• C扩展

  C扩展在需要处理大型结果集的场合效率比纯Python要高。在8.0以上的mysql-python-api中C扩展默认开启，在其他版本默认关闭。use_pure参数控制C扩展的开启和关闭。

  • mysql.connector.apilevel属性

  意义如Python的数据库规范所言，值为2.0。

  • mysql.connector.paramstyle属性

  意义如Python数据库规范所言，值为pyformat。

  意思是使用Python扩展格式化代码，比如WHERE name=%(name)s，实际例子如下：

  add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")
  data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))
  cursor.execute(add_employee, data_employee)
  

  或者

  add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")
  data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
  }
  cursor.execute(add_salary, data_salary)
  

  又或者

  query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
  
  hire_start = datetime.date(1999, 1, 1)
  hire_end = datetime.date(1999, 12, 31)
  
  cursor.execute(query, (hire_start, hire_end))
  

  • mysql.connector.threadsafety属性

  值为1，代表可以在不同线程之间共享模块，但不能共享连接和游标。

connection.MySQLConnection类

这看起来是核心类，用来管理与服务端之间的链接，以及发送SQL命令并获得结果。

• connection.MySQLConnection()结构体

  功能和mysql.connector.connect()一样，也返回一个连接

• MySQLConnection.close()

• MySQLConnection.disconnect()

  两个语义一致，都是向服务端发送一个QUIT，之后关闭连接，不会抛出异常。

  如果连接是从一个连接池中获取，close不会真正关闭它，而是将其归还到连接池中，从而该连接可以被随后的请求复用。

• MySQLConnection.commit()

  提交更改。

  因为自动提交autocommit是默认关闭的，因此应当在每次事务transaction之后都调用该方法。如：

  >>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))
  >>> cnx.commit()
  

• MySQLConnection.config(**kwargs)

  配置一个已经实例化的MySQLConnection。比如可以修改用户名，之后使用reconnect()重新连接。如：

  cnx = mysql.connector.connect(user='joe', database='test')
  # Connected as 'joe'
  cnx.config(user='jane')
  cnx.reconnect()
  # Now connected as 'jane'
  

  对一个从连接池中获得的连接，调用该方法将会引发一个异常。

• MySQLConnection.connect(**kwargs)

  不缀

• MySQLConnection.cursor([arg=value[, arg=value]…])

  根据参数的不同，该方法返回一个MySQLCursor()对象或其子类对象。返回的对象是一个cursor.CursorBase实例。

  可选参数如下：

  • buffered 

  当该值为True时，游标自动将结果集抓取到本地，在结果集较小的情况下比较常用。

  如果在建立连接时就将buffered参数置为True，那么从该连接中产生的所有游标都默认设置为True

  • raw

  值为True时游标会跳过自动转换。如果建立连接时就设置了raw参数，从该连接中产生的所有游标都采取该默认值。

  • dictionary

  值为True时，游标将返回的元组组织成字典形式。

  • named_tuple

  值为True时，游标将返回的元组组织成命名元组。（相当于给某个元组命个名，并非是像字典那样对每个值命名，而是为整个元组命名。大致接近C语言中的类型别名）

  • prepared

  值为True时，游标被用来执行准备好的SQL命令。（不懂啥意思）

  返回值取决于参数，有以下四种：

  • If not buffered and not raw: MySQLCursor
  • If buffered and not raw: MySQLCursorBuffered
  • If not buffered and raw: MySQLCursorRaw
  • If buffered and raw: MySQLCursorBufferedRaw
• MySQLConnection.cmd_change_user()

  修改连接用户和密码，修改数据库，甚至字符集。

  cnx.cmd_change_user(username='', password='', database='', charset=33)

  返回一个字典，包含OK信息。

• MySQLConnection.cmd_debug()

  指示服务端将debug()信息写入错误日志。连接用户必须是超级用户。

  返回一个字典，包含OK信息。

• MySQLConnection.cmd_init_db(db_name)

  将db_name数据库设置为默认数据库，之后的所有查询都将查询该数据库中的表。

  返回一个字典，包含OK信息。

• MySQLConnection.cmd_ping()

  检查连接状况。但是该命令不应当被直接使用，应当使用ping()或者is_connected()代替

  返回一个字典，包含OK信息。

• MySQLConnection.cmd_query(statement)

  向服务端发送给定的SQL命令并获得一个结果。一次发送多个指令时，应采用.cmd_query_iter()

  当SQL命令是一个SELECT时，返回一个包含列信息的字典，其他SQL命令返回一个包含OK或者EOF包的信息。

  当服务端产生错误时，抛出一个异常。当产生多个结果集时，抛出一个InterfaceError异常。

• MySQLConnection.cmd_query_iter(statement)

  一次性发送多个由引号隔开的指令。采用迭代的方式，一次处理一个结果，如：

  statement = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
  for result in cnx.cmd_query_iter(statement):
  if 'columns' in result:
    columns = result['columns']
    rows = cnx.get_rows()
  else:
    # do something useful with INSERT result
  

• MySQLConnection.cmd_quit()

  向服务端发送一个QUIT，随后关闭当前连接。

• MySQLConnection.cmd_reset_connection()

  在不进行重新认证的情况下，通过向服务器发送 COM_RESET_CONNECTION命令清除连接状态来重置一个连接。

• MySQLConnection.cmd_statistics()

  返回一个字典，包含MySQL服务端的信息，包括启动时间、当前运行线程数、打开的表等等。

• MySQLConnection.get_row()

  该方法从结果集中取回下一个行，返回一个tuple，该tuple由以下两部分组成：

  • 一个以tuple形式组织的行，如果没有更多行，会是None
  • 一个EOF包信息，包含status_flag、warning_count，如果返回的行不是最后一行，就是None

  MySQLCursor类调用了该方法来抓取行。

• MySQLConnection.get_rows(count=None)

  一次性获取所有未抓取的结果集，返回一个tuple，包含了结果集所有行的信息以及EOF包信息。当count参数给出时，一次性最多抓取count行。返回的tuple由以下两部分组成：

  • 一个tuples的列表，每个tuple表示一行的信息，如果没有更多行可用，该列表为空
  • EOF包信息。

  当所有行都被取回时，会引发一个InterfaceError异常。

  MySQLCursor用到了该方法抓取行。

• MySQLConnection.get_server_version()

  以元组的形式返回服务端信息，如未连接返回None

• MySQLConnection.is_connected()

• MySQLConnection.isset_client_flag()

• MySQLConnection.reconnect(attempts=1, delay=0)

  试图重连服务器。attempts参数定义了尝试次数，delay定义了尝试间隔。

• MySQLConnection.rollback()

  回滚上一次事务请求。

  >>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))
  >>> cnx.rollback()
  

• MySQLConnection.set_charset_collation(charset=None, collation=None)

  设置字符集和字符序（collation，用来排序等），参数可以是字符集名，也可以是字符集的编号（定义在“constants.CharacterSet`中），如果collation留空，那么将根据字符集设置默认字符序。如：

  >>> cnx = mysql.connector.connect(user='scott')
  >>> cnx.set_charset_collation('latin1', 'latin1_general_ci')
  

• MySQLConnection.set_client_flags(flags)

  设置客户端在连接服务端时使用的flags，以int形式返回新的值。flags参数可以以int形式提供，或以已知flag的序列数组形式提供，值得关注的是，如果用了-号，意味着取消这个flag。如下：

  >>> from mysql.connector.constants import ClientFlag
  >>> cnx.set_client_flags([ClientFlag.FOUND_ROWS, -ClientFlag.LONG_FLAG])
  >>> cnx.reconnect()
  

  每次设置完了之后都必须重新连接。

• MySQLConnection.shutdown()

  在不向服务器发送QUIT命令的前提下关闭连接。用于连接因为某些原因受到干扰的情况下（这种情况下disconnect()和close()会阻塞）

• MySQLConnection.start_transaction()

  意义不明。参见start_tarnsaction

• MySQLConnection.autocommit

  一个布尔值

• MySQLConnection.unread_results

  一个布尔值。指示是否还有未阅读的结果。永远不要试图修改这个值。

• MySQLConnection.can_consume_results

  一个布尔值。指示是否一个query产生的结果集已经被自动读取并丢弃了。

• MySQLConnection.charset

• MySQLConnection.collation

• MySQLConnection.connection_id

  一个整型的连接ID（线程ID或者是会话ID）,如果未连接返回None

• MySQLConnection.database

  一个可读写的字符串，修改该值将修改当前使用的数据库。

  MySQLConnection.cmd_init_db(db_name)可以实现类似功能。

• MySQLConnection.get_warnings

  一个布尔值，指示是否自动取回警告信息，默认值为False。

• MySQLConnection.in_transaction

  一个布尔值。指示是否当前处于一个事务之中。如：

  >>> cnx.start_transaction()
  >>> cnx.in_transaction
  True
  >>> cnx.commit()
  >>> cnx.in_transaction
  False
  

• MySQLConnection.raise_on_warnings

  一个布尔值，指示warnings消息是否作为一个异常抛出。默认值为False

• MySQLConnection.server_host

  一个只读字符串，返回主机名或者IP地址

• MySQLConnection.unix_socket

  一个只读字符串，返回使用的unix套接字名

• MySQLConnection.server_port

  一个只读整型数。

• MySQLConnection.sql_mode

  用来获取或设置sql_mode。

• MySQLConnection.user

cursor.MySQLCursor类

游标类的对象能够使用SQL语句来执行SQL操作。可以使用以下语句创建一个游标：

import mysql.connector

cnx = mysql.connector.connect(database='world')
cursor = cnx.cursor()

一些类似游标的类继承于游标类，在创建游标时传入不同的参数来创建不同的类。

MySQLCursorBuffered，cursor = cnx.cursor(buffered=True)

MySQLCursorRaw，cursor = cnx.cursor(raw=True)

MySQLCursorBufferedRaw，cursor = cnx.cursor(raw=True, buffered=True)

MySQLCursorDict，cursor = cnx.cursor(dictionary=True)

MySQLCursorBufferedDict，cursor = cnx.cursor(dictionary=True, buffered=True)

MySQLCursorNamedTuple，cursor = cnx.cursor(named_tuple=True)

MySQLCursorBufferedNamedTuple，cursor = cnx.cursor(named_tuple=True, buffered=True)

MySQLCursorPrepared，cursor = cnx.cursor(prepared=True)

这些类在之后会一一介绍。

• MySQLCursor.callproc(proc_name, args=())

  调用一个预先保存在MySQL服务端的代码片段。

• MySQLCursor.close()

• MySQLCursor.execute(operation, params=None, multi=False)

  执行一个SQL语句。params是一个字典或者元组，指定了operation中的格式化字符串（mysql.connector.paramstyle一节讲过也举过例子）

  insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
  )
  data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
  cursor.execute(insert_stmt, data)
  
  select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
  cursor.execute(select_stmt, { 'emp_no': 2 })
  

  注意里面的datetime.data方法。

  当multi为True时，该方法可以执行一系列分号隔开的语句，同时返回一个符合python迭代器协议的迭代器（意味着可以用for循环使用之），但更建议不要使用multi参数，而改使用.executemany()

• MySQLCursor.executemany(operation, seq_of_params)

  data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
  ]
  stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
  cursor.executemany(stmt, data)
  

  该函数的限制是，operation操作只有一个，也就是只能执行一种SQL操作。如果要执行多种混合操作，还是使用上面的multi。不过我们为什么要这么干呢……

• MySQLCursor.fetchall()

  标准用法rows = cursor.fetchall()。一次性获取整个结果集，返回一个tuple的列表，如果没有更多结果集，返回一个空列表。

  在同一个连接上进行下一个操作之前，必须将结果集中的所有列全部fetch掉。

• MySQLCursor.fetchmany(size = 1)

• MySQLCursor.fetchone()

  返回一行，其中每一行是一个数组

• MySQLCursor.fetchwarnings()

  >>> cnx.get_warnings = True
  >>> cursor.execute("SELECT 'a'+1")
  >>> cursor.fetchall()
  [(1.0,)]
  >>> cursor.fetchwarnings()
  [(u'Warning', 1292, u"Truncated incorrect DOUBLE value: 'a'")]
  

• MySQLCursor.stored_results()

  返回一个callproc()方法生成的可迭代对象，每个可迭代对象指向一个结果集。

  >>> cursor.callproc('myproc')
  ()
  >>> for result in cursor.stored_results():
  ...     print result.fetchall()
  ...
  [(1,)]
  [(2,)]
  

• MySQLCursor.column_names

  只读属性，返回列名的数组。

  cursor.execute("SELECT last_name, first_name, hire_date "
               "FROM employees WHERE emp_no = %s", (123,))
  row = dict(zip(cursor.column_names, cursor.fetchone()))
  print("{last_name}, {first_name}: {hire_date}".format(row))
  

• MySQLCursor.description

  如API规范要求，返回一个七元tuple的tuple，每个七元组包括：

  (column_name, type, None, None, None, None, null_ok, column_flags)

  import mysql.connector
  from mysql.connector import FieldType
  
  ...
  
  cursor.execute("SELECT emp_no, last_name, hire_date "
               "FROM employees WHERE emp_no = %s", (123,))
  for i in range(len(cursor.description)):
  print("Column {}:".format(i+1))
  desc = cursor.description[i]
  print("  column_name = {}".format(desc[0]))
  print("  type = {} ({})".format(desc[1], FieldType.get_info(desc[1])))
  print("  null_ok = {}".format(desc[6]))
  print("  column_flags = {}".format(desc[7]))
  

• MySQLCursor.lastrowid

  返回最近一个INSERT或UPDATE修改的自增列序号。如果没有符合条件的值就返回None

• MySQLCursor.rowcount

  类似C API的mysql_affected_rows()

  要么返回select得到的结果集的行数，要么返回insert或update修改的行数。

  但在非直接缓存的游标中，rowcount直到全部获取结果集后才可用。

• MySQLCursor.statement

  以字符串形式返回最后一个成功执行的SQL语句

• MySQLCursor.with_rows

  只读布尔值。以指示最近的执行的一个语句是否产生了结果集。为真的话表示可以用fetch*()抓取数据。也可以用来鉴定到底是SELECT还是其他语句。

  即使SELECT语句生成空结果集，with_rows属性一样为True

• cursor.MySQLCursor的子类

  • cursor.MySQLCursorBuffered

  执行一个query语句后，结果集将自动缓存到本地。

  • cursor.MySQLCursorRaw

  不会执行自动转化，必须由程序员手动执行转换。

  • cursor.MySQLCursorBufferedRaw

  自动缓存且不执行类型转换

  • cursor.MySQLCursorDict

  这类游标返回的结果每一行是一个字典，字典的键是列名。

  • cursor.MySQLCursorBufferedDict

  • cursor.MySQLCursorNamedTuple

  • cursor.MySQLCursorBufferedNamedTuple

  • cursor.MySQLCursorPrepared

  prepare主要是为了提高重复执行语句的运行效率，比如说要不断执行某一种语句，仅仅是参数有所区别，使用这种功能将利用MySQL的二进制协议，减少解析语句的时间、提高客户端与服务器端的通信效率。

  cursor = cnx.cursor(prepared=True)
  stmt = "SELECT fullname FROM employees WHERE id = %s" # (1)
  cursor.execute(stmt, (5,))                            # (2)
  # ... fetch data ...
  cursor.execute(stmt, (10,))                           # (3)
  # ... fetch data ...
  

常量类

• constants.ClientFlag类

  提供了一些flag常量，先懒得翻译。

  flag常量

• constants.FieldType类

  提供了一些数据类型常量。在处理RAW数据时比较有用。

• constants.SQLMode

• constants.CharacterSet

• constants.RefreshOption

  该类提供一系列flush操作：

  • RefreshOption.GRANT

  Refresh the grant tables, like FLUSH PRIVILEGES

  • RefreshOption.LOG

  Flush the logs, like FLUSH LOGS

  • RefreshOption.TABLES

  Flush the table cache, like FLUSH TABLES

  • RefreshOption.HOSTS

  Flush the host cache, like FLUSH HOSTS

  • RefreshOption.STATUS

  Reset status variables, like FLUSH STATUS

  • RefreshOption.THREADS

  Flush the thread cache.

  • RefreshOption.SLAVE

  On a slave replication server, reset the master server information and restart the slave, like RESET SLAVE

错误和异常

mysql.connector.errors定义了MySQL Python API可能抛出的异常和警告。大多数错误类在import mysql.connector模块时就会导入。

• errorcode类

  该类包含了客户端和服务端可能产生的错误码。

• errors.Error 异常

  所有Error异常的基类。可以在单一语句中捕获所有的异常。

  import mysql.connector
  
  try:
  cnx = mysql.connector.connect(user='scott', database='employees')
  cursor = cnx.cursor()
  cursor.execute("SELECT * FORM employees")   # Syntax error in query
  cnx.close()
  except mysql.connector.Error as err:
  print("Something went wrong: {}".format(err))
  

  异常类和Python 数据库API规范v2.0的规定相同。

连接池及相关类

• 为什么需要连接池

  参见CSDN：MySQL连接池专题

  总结起来说，创建数据库连接是一个耗时且容易产生安全隐患的操作，开销很大，因此在那些数据库访问频繁的应用中，可以在程序初始化的时候一次性创建多个数据库连接，并将他们集中管理，形成一个连接池。当应用需要使用连接的时候，向连接池申请连接，使用完毕后将连接返回连接池，当程序退出时，断开所有连接，并释放资源。

  通过使用连接池，避免了高频使用数据库时不断创建和释放连接的开销，提高了数据库读写效率和应用程序稳定性。

• MySQL的连接池概况

  • MySQL通过mysql.connector.pooling模块支持连接池。
  • 一个连接池创建一系列线程安全的连接，按请求分配和管理连接。
  • 连接池的大小在连接池创建时已经确定，之后不允许修改。
  • 在连接池创建时，可以为连接池命名，如果没有命名，连接池模块会自动产生一个名字。
  • 在运行过程中，连接池的名字可以从连接池本身或从连接池中申请的连接中获取。
  • 可以使用多个连接池，通过多个连接池，应用程序就能与不同的MySQL服务器进行通信。
  • 对于每个连接请求，连接池提供下一个可用的连接。如果一个池已经枯竭，将抛出一个PoolError
  • 可以对连接池的配置进行重新设置，这些新设置将应用于接下来从连接池中申请的连接。但是对一个从连接池中申请的连接使用.config()方法将会引发一个PoolError异常。
• 连接池的应用场景
  • 维护了与多个MySQL服务器之间的连接，并且与每个服务器都有多个连接的那些中间件；
  • 可能需要与MySQL服务器建立多个“永久”连接的网站。
• 连接池的创建

  连接池可以显式创建也可以隐式创建。

  • 隐式创建

  在打开一个连接时使用连接池相关参数。比如：

  dbconfig = {
    "database": "test",
    "user":     "joe"
  }
  
  cnx = mysql.connector.connect(pool_name = "mypool",
                                pool_size = 3,
                                **dbconfig)
  

  连接池的名字必须是字母、数字以及. _ * $ #这五个特殊字符的组合，名字长度不能超过pooling.CNX_POOL_MAXNAMESIZE（默认64），连接池大小必须大于0小于等于pooling.CNX_POOL_MAXSIZE (默认32)。

  这两个参数只要有其一，模块就会创建一个连接池，如果没有给名字，就自动生成一个，如果没有给数量，就自动设置成5。

  隐式创建的连接池，可以在之后调用connect()时，用相同的连接池名字做为参数，以从已经创建的连接池中申请连接。在之后所有对连接池本身做修改的参数都将被忽略，比如以下三个例子，效果是完全相同的，前两个例子中除pool_name以外的参数都被忽略了。

  cnx = mysql.connector.connect(pool_name = "mypool", pool_size = 3)
  cnx = mysql.connector.connect(pool_name = "mypool", **dbconfig)
  cnx = mysql.connector.connect(pool_name = "mypool")
  

  • 显式创建连接池

  直接创建一个连接池对象，即MySQLConnectionPool实例。

  dbconfig = {
    "database": "test",
    "user":     "joe"
  }
  
  cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",
                                                        pool_size = 3,
                                                        **dbconfig)
  

  要从显式创建的连接池中获取一个连接，可以使用连接池实例的.get_connection()方法。

  cnx1 = cnxpool.get_connection()
  cnx2 = cnxpool.get_connection()
  

  显式创建的连接池可以用.set_config()对象进行重新配置。

  dbconfig = {
    "database": "performance_schema",
    "user":     "admin",
    "password": "password"
  }
  
  cnxpool.set_config(**dbconfig)
  

• 连接池相关模块和方法

  从连接池获得的连接实际上是一个PooledMySQLConnection类，除以下几个方面以外，他的功能与MySQLConnection 相同：

  • 当使用.close()释放一个连接时，连接池中的连接不会关闭，而是返回连接池备用；
  • .config()方法不能使用，否则将会引发一个异常。所有对连接池中的连接进行修改的操作都必须对连接池本身做出。
  • 连接池中的连接有一个pool_name属性，返回连接池名。

  相关模块是mysql.connector.pooling模块。

  • 构造函数

  PooledMySQLConnection(cnxpool, cnx)

  其中cnxpool是MySQLConnetionPool实例，通过mysql.connector.pooling.MySQLConnectionPool创建。cnx是一个MySQLConnection实例。

  也可以通过cnxpool.get_connection()来获取一个PooledMySQLConnection实例。

  • .close()

  将连接返回连接池

  • .config()

  引发一个PoolError。

  • .pool_name

  获得连接池名。

  其他方法的行为与MySQLConnection一致。

一个综合示例

import mysql.connector
from mysql.connector import errorcode

"""
config = {
    'user':'nbt',
    'password':'nbt123',
    'database':'test',
    'buffered':True,
}
"""

def main():

    try:
        #cnx = mysql.connector.connect(**config);
        cnx = mysql.connector.connect(option_files='/root/python-db-config.cnf')
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
        exit(1)
    else:
        print_cnx_info(cnx)

    cursor = cnx.cursor() #获取游标

    while(True):
        try:
            stmt = input("请输入SQL命令:")
        except EOFError:
            print("Bye")
            cursor.close()
            cnx.close()
            exit(1)
        except:
            print("What Happened")
            exit(1)

        try:
            cursor.execute(stmt)
        except mysql.connector.Error as err:
            print("执行>>>%s<<<出错,错误是:"%(stmt,),err)
            continue

        if cursor.with_rows:  #有结果集返回
            print("得到了%d个结果"%(cursor.rowcount,))
            print_description(cursor)
            print_rows(cursor)
        else: #没有返回结果集
            print("插入/更新了%d个元组"%(cursor.rowcount))
            cnx.commit()    #应当提交保存一下，不然close的时候回滚

def print_cnx_info(cnx):
    if cnx.is_connected():
        version = cnx.get_server_version()
        if version:
            print("数据库已连接，服务端版本为:%s"%(version,))
            print("字符集:%s，字符序:%s，连接用户:%s，连接数据库:%s"%(cnx.charset,
                cnx.collation,cnx.user,cnx.database))
        else:
            print("连接成功，但获取服务端版本失败")
    else:
        print("服务器连接失败")
        exit(1)

def print_description(cursor):

    from mysql.connector import FieldType

    for column in cursor.description:
        print("%-15s"%(column[0],),end="") #宽度15，左对齐
    print()

    for column in cursor.description:
        print("%-15s"%(FieldType.get_info(column[1]),),end="")
    print()

def print_rows(cursor):

    for row in cursor.fetchall():
        for column in row:
            if column == None:      #值是NuLL时会被转化为Python的null对象
                column = "None"
            print ("{:<15}".format(column),end="") #宽度15，左对齐
        print()

if __name__=="__main__":
    main()