Setting client flags with MySQL Connector/Python

Setting client flags with MySQL Connector/Python works a bit differently than the other MySQL Python drivers. This blog post describes how to set and unset flags, like the CLIENT_FOUND_ROWS.

The default client flags for the MySQL Client/Server protocol can be retrieved using the constants.ClientFlag class:

>>> from mysql.connector.constants import ClientFlag
>>> defaults = ClientFlag.get_default()
>>> print ClientFlag.get_bit_info(defaults)
['SECURE_CONNECTION', 'TRANSACTIONS', 'CONNECT_WITH_DB',
 'PROTOCOL_41', 'LONG_FLAG', 'MULTI_RESULTS',
 'MULTI_STATEMENTS', 'LONG_PASSWD']

To set an extra flag when connecting to MySQL you use the client_flags argument of connect()-method. For example, you’d like to have the CLIENT_FOUND_ROWS set:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS]
cnx = mysql.connector.connect(client_flags=extra_flags)

Similar, you can unset a flag passing a list of negative values, or all at the same time. For example, you’d like the CLIENT_FOUND_ROWS set, but you don’t want CLIENT_MULTI_STATEMENTS:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS, -ClientFlag.MULTI_STATEMENTS]
cnx = mysql.connector.connect(client_flags=extra_flags)

It is also possible to pass the client_flags an integer, but you need to get first the defaults, and do bitwise operations to set/unset flags yourself. At the moment of writing, there is a bug about this, see lp:695514, but I recommend using the list-method.

Comments

Mark Huang
Where would I do this in a pylons project? Development.ini? Middleware.py?
Geert Vanderkelen
@Mark Huang I’m afraid you can’t, since MySQL Connector/Python dialect is part of SQLAlchemy 0.6 and AFAIK Pylons can’t use that. Also, the dialect in SQLAlchemy might need some updating with the recent changes done in MySQL Connector/Python.