MySQL Python教程（2）

mysql官网关于python的API是最经典的学习材料，相信对于所有函数浏览一遍以后，Mysql数据库用起来一定得心应手。

首先看一下Connector/Python API包含哪些类和模块。

Module mysql.connector 
Class connection.MySQLConnection 
Class cursor.MySQLCursor 
Class cursor.MySQLCursorBuffered
Class cursor.MySQLCursorPrepared
Class constants.ClientFlag
Class constants.FieldType
Class constants.SQLMode
Class constants.CharacterSet
Class constants.RefreshOption
Errors and Exceptions

 

一、mysql.connector模块

 1 Constructor connection.MySQLConnection(**kwargs)
 2 Method MySQLConnection.close()
 3 Method MySQLConnection.config(**kwargs)
 4 Method MySQLConnection.connect(**kwargs)
 5 Method MySQLConnection.commit()
 6 Method MySQLConnection.cursor(buffered=None, raw=None, cursor_class=None)
 7 Method MySQLConnection.cmd_change_user(username='', password='', database='', charset=33)
 8 Method MySQLConnection.cmd_debug()
 9 Method MySQLConnection.cmd_init_db(database)
10 Method MySQLConnection.cmd_ping()
11 Method MySQLConnection.cmd_process_info()
12 Method MySQLConnection.cmd_process_kill(mysql_pid)
13 Method MySQLConnection.cmd_quit()
14 Method MySQLConnection.cmd_query(statement)
15 Method MySQLConnection.cmd_query_iter(statement)
16 Method MySQLConnection.cmd_refresh(options)
17 Method MySQLConnection.cmd_shutdown()
18 Method MySQLConnection.cmd_statistics()
19 Method MySQLConnection.disconnect()
20 Method MySQLConnection.get_rows(count=None)
21 Method MySQLConnection.get_row()
22 Method MySQLConnection.get_server_info()
23 Method MySQLConnection.get_server_version()
24 Method MySQLConnection.is_connected()
25 Method MySQLConnection.isset_client_flag(flag)
26 Method MySQLConnection.ping(attempts=1, delay=0)
27 Method MySQLConnection.reconnect(attempts=1, delay=0)
28 Method MySQLConnection.rollback()
29 Method MySQLConnection.set_charset_collation(charset=None, collation=None)
30 Method MySQLConnection.set_client_flags(flags)
31 Method MySQLConnection.start_transaction()
32 Property MySQLConnection.autocommit
33 Property MySQLConnection.charset_name
34 Property MySQLConnection.collation_name
35 Property MySQLConnection.connection_id
36 Property MySQLConnection.database
37 Property MySQLConnection.get_warnings
38 Property MySQLConnection.in_transaction
39 Property MySQLConnection.raise_on_warnings
40 Property MySQLConnection.server_host
41 Property MySQLConnection.server_port
42 Property MySQLConnection.sql_mode
43 Property MySQLConnection.time_zone
44 Property MySQLConnection.unix_socket
45 Property MySQLConnection.user

 

mysql.connector提供顶层的方法和属性。具体函数如下：

Method mysql.connector.connect()

该方法用来连接MySQL服务器。如果没有提供任何参数，将使用默认配置。关于函数的参数列表，参见：

建立连接的方法有两种：
　　1、使用mysql.connector.connect()方法：
　　　　cnx=mysql.connector.connect(user='joe',database='test')
　　2、使用mysql.connector.MySQLConnection()：
　　　　cnx = MySQLConnection(user='joe', database='test')

Property mysql.connector.apilevel
　　这个属性是用来标示所支持的数据库API的等级(level)。
　　>>> mysql.connector.apilevel
　　'2.0'

Property mysql.connector.paramstyle
标示参数默认的样式
　　>>> mysql.connector.paramstyle
　　'pyformat'

Property mysql.connector.threadsafety
标示支持的线程安全等级
>>> mysql.connector.threadsafety
1

Property mysql.connector.__version__
Connector/Python的版本号

Property mysql.connector.__version_info__
Connector/Python的版本信息

二、类 connection.MySQLConnection

包含以下方法和属性：
Constructor connection.MySQLConnection(**kwargs)

Method MySQLConnection.close()
相当于类中disconnect()方法

Method MySQLConnection.config(**kwargs)
对一个已经实例化的MySQLConnection对象进行配置。

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

Method MySQLConnection.connect(**kwargs)

Method MySQLConnection.commit()
在对数据库进行更改后调用此函数，使得改变立即生效。
>>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))
>>> cnx.commit()

Method MySQLConnection.cursor(buffered=None, raw=None, cursor_class=None)
如果buffered为ture，在cursor中会获得SQL语句执行后的所有结果，如果raw设置为true，则跳过由MYSQL数据向python数据类型的转换，自己执行转换。

返回的对象类型由buffered和raw参数决定：
If not buffered and not raw: cursor.MySQLCursor
If buffered and not raw: cursor.MySQLCursorBuffered
If buffered and raw: cursor.MySQLCursorBufferedRaw
If not buffered and raw: cursor.MySQLCursorRaw
Returns a CursorBase instance.

Method MySQLConnection.cmd_change_user(username='', password='', database='', charset=33)
改变用户

Method MySQLConnection.cmd_debug()
该方法需要root权限，可以将debug信息写入error log中。

Method MySQLConnection.cmd_init_db(database)
制定默认的数据库

Method MySQLConnection.cmd_ping()

Method MySQLConnection.cmd_process_info()
use the SHOW PROCESSLIST statement or query the tables found in the database INFORMATION_SCHEMA.

Method MySQLConnection.cmd_process_kill(mysql_pid)
关闭mysql进程。以下两种方法有同样的作用：
>>> cnx.cmd_process_kill(123)
>>> cnx.cmd_query('KILL 123')

Method MySQLConnection.cmd_quit()
关闭连接

Method MySQLConnection.cmd_query(statement)
发送statement语句到MYSQL服务器，并执行放回结果。如果想要执行多条statement，使用cmd_query_iter()

Method MySQLConnection.cmd_query_iter(statement)
同cmd_query()方法
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

Method MySQLConnection.cmd_refresh(options)
该方法清空缓存，并重设服务器信息。调用该方法的连接需要有RELOAD权限。

Example:

>>> from mysql.connector import RefreshOption
>>> refresh = RefreshOption.LOG | RefreshOption.THREADS
>>> cnx.cmd_refresh(refresh)

Method MySQLConnection.cmd_shutdown()
Asks the database server to shut down. The connected user must have the SHUTDOWN privilege.

Method MySQLConnection.cmd_statistics()
Returns a dictionary containing information about the MySQL server including uptime in seconds and the number of running threads, questions, reloads, and open tables.

Method MySQLConnection.disconnect()
This method tries to send a QUIT command and close the socket. It raises no exceptions.
MySQLConnection.close() is a synonymous method name and more commonly used.

Method MySQLConnection.get_rows(count=None)
该方法返回结果中的rows，如果count为None，则返回所有查询结果，否则返回指定数量的查询结果。
返回元组(tuple)的格式：
The row as a tuple containing byte objects, or None when no more rows are available.
EOF packet information as a dictionary containing status_flag and warning_count, or None when the row returned is not the last row.

Method MySQLConnection.get_row()
返回结果为一个元组。

Method MySQLConnection.get_server_info()

Method MySQLConnection.get_server_version()

Method MySQLConnection.is_connected()
测试连接是否可用。

Method MySQLConnection.isset_client_flag(flag)
如果客户端设置了flag，返回true，否则返回false。

Method MySQLConnection.ping(attempts=1, delay=0)
检测连接是否依旧可用。
当reconnect设置为true时，进行一次或者多次测试，使用delay设置重试的延迟时间。当连接不可用时，
抛出InterfaceError 错误，使用is_connected()方法可以测试连接并且不抛出异常错误。

Method MySQLConnection.reconnect(attempts=1, delay=0)
当你预测是由于网络暂时不可用，导致连接失败时，使用此函数。attempts尝试次数应该多一些，间隔delay应该稍微长一些。

Method MySQLConnection.rollback()
回滚当前transaction所进行的所有数据修改。

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

Method MySQLConnection.set_charset_collation(charset=None, collation=None)
还是人家英文文档写得比较明白，为了避免误导，贴在下面了。
This method sets the character set and collation to be used for the current connection. The charset argument can be either the name of a character set, or the numerical equivalent as defined in constants.CharacterSet.

When collation is None, the default collation for the character set is used.

In the following example, we set the character set to latin1 and the collation to latin1_swedish_ci (the default collation for: latin1):

>>> cnx = mysql.connector.connect(user='scott')
>>> cnx.set_charset('latin1')
Specify a given collation as follows:

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

Method MySQLConnection.set_client_flags(flags)
设置客户端的flag，如果添加相应flag，则使用正数，否则使用负数。

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

Method MySQLConnection.start_transaction()
该方法接受两个参数

cnx.start_transaction(consistent_snapshot=bool,
isolation_level=level)
consistent_snapshot默认为false，标示是否使用连续快照；
isolation_level 默认值为None，该参数接受 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', and 'SERIALIZABLE'这些值.
为了测试transaction是否active，使用in_transaction属性。

Property MySQLConnection.autocommit
该参数可以标示是否自动提交（This property can be assigned a value of True or False to enable or disable the autocommit feature of MySQL. ）

Property MySQLConnection.charset_name
字符集属性

Property MySQLConnection.collation_name

Property MySQLConnection.connection_id
连接的id，当没连接时为None

Property MySQLConnection.database
检索或者设置当前数据库
>>> cnx.database = 'test'
>>> cnx.database = 'mysql'
>>> cnx.database
u'mysql'

Property MySQLConnection.get_warnings
该属性标示SQL操作的结果中是否自动接受警告
>>> cnx.get_warnings = True
>>> cursor.execute('SELECT "a"+1')
>>> cursor.fetchall()
[(1.0,)]
>>> cursor.fetchwarnings()
[(u'Warning', 1292, u"Truncated incorrect DOUBLE value: 'a'")]

Property MySQLConnection.in_transaction
标示transaction是否是激活状态
>>> cnx.start_transaction()
>>> cnx.in_transaction
True
>>> cnx.commit()
>>> cnx.in_transaction
False

Property MySQLConnection.raise_on_warnings
标示警告情况下是否抛出异常
>>> cnx.raise_on_warnings = True
>>> cursor.execute('SELECT "a"+1')
>>> cursor.fetchall()
..
mysql.connector.errors.DataError: 1292: Truncated incorrect DOUBLE value: 'a'

Property MySQLConnection.server_host
返回一个string类型值，代表连接MYSQL的主机名称或者IP地址

Property MySQLConnection.server_port
返回连接TCP/IP的端口

Property MySQLConnection.sql_mode
设置连接的所有模式
>>> cnx.sql_mode = 'TRADITIONAL,NO_ENGINE_SUBSTITUTION'
>>> cnx.sql_mode.split(',')
[u'STRICT_TRANS_TABLES', u'STRICT_ALL_TABLES', u'NO_ZERO_IN_DATE',
u'NO_ZERO_DATE', u'ERROR_FOR_DIVISION_BY_ZERO', u'TRADITIONAL', 
u'NO_AUTO_CREATE_USER', u'NO_ENGINE_SUBSTITUTION']
>>> from mysql.connector.constants import SQLMode
>>> cnx.sql_mode = [ SQLMode.NO_ZERO_DATE, SQLMode.REAL_AS_FLOAT]
>>> cnx.sql_mode

u'REAL_AS_FLOAT,NO_ZERO_DATE'

Property MySQLConnection.time_zone
设置当前时区或者遍历当前连接所有可用的时区
>>> cnx.time_zone = '+00:00'
>>> cur.execute('SELECT NOW()') ; cur.fetchone()
(datetime.datetime(2012, 6, 15, 11, 24, 36),)
>>> cnx.time_zone = '-09:00'
>>> cur.execute('SELECT NOW()') ; cur.fetchone()
(datetime.datetime(2012, 6, 15, 2, 24, 44),)
>>> cnx.time_zone
u'-09:00'

Property MySQLConnection.unix_socket
只读属性，返回用来连接Mysql的Unix socket文件

Property MySQLConnection.user
返回连接服务器的用户姓名