MySQL :: MySQL Connector/Python Developer Guide :: 7.1 Connector/Python Connection Arguments (original) (raw)

A connection with the MySQL server can be established using either the mysql.connector.connect() function or themysql.connector.MySQLConnection() class:

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

The following table describes the arguments that can be used to initiate a connection. An asterisk (*) following an argument indicates a synonymous argument name, available only for compatibility with other Python MySQL drivers. Oracle recommends not to use these alternative names.

Table 7.1 Connection Arguments for Connector/Python

MySQL Authentication Options

Authentication with MySQL typically uses ausername and password.

When the database argument is given, the current database is set to the given value. To change the current database later, execute a USE SQL statement or set the database property of theMySQLConnection instance.

By default, Connector/Python tries to connect to a MySQL server running on the local host using TCP/IP. The host argument defaults to IP address 127.0.0.1 and port to 3306. Unix sockets are supported by settingunix_socket. Named pipes on the Windows platform are not supported.

Connector/Python supports authentication plugins available as of MySQL 8.0, including the preferredcaching_sha2_password authentication plugin.

The deprecatedmysql_native_password plugin is supported, but it is disabled by default as of MySQL Server 8.4.0 and removed as of MySQL Server 9.0.0.

The connect() method supports anauth_plugin argument that can be used to force use of a particular authentication plugin.

Note

MySQL Connector/Python does not support the old, less-secure password protocols of MySQL versions prior to 4.1.

Connector/Python supports theKerberos authentication protocol for passwordless authentication. Linux clients are supported as of Connector/Python 8.0.26, and Windows support was added in Connector/Python 8.0.27 with the C extension implementation, and in Connector/Python 8.0.29 with the pure Python implementation. For Windows, the relatedkerberos_auth_mode connection option was added in 8.0.32 to configure the mode as either SSPI (default) or GSSAPI (via the pure Python implementation, or the C extension implementation as of 8.4.0). While Windows supports both modes, Linux only supports GSSAPI.

Optionally use the [gssapi] shortcut when installing the mysql-connector-python pip package to pull in specific GSSAPI versions as defined by the connector, which is v1.8.3 as of Connector/Python 9.1.0:

$ pip install mysql-connector-python[gssapi]

The following example assumesLDAP Pluggable Authentication is set up to utilize GSSAPI/Kerberos SASL authentication:

import mysql.connector as cpy
import logging

logging.basicConfig(level=logging.DEBUG)

SERVICE_NAME = "ldap"
LDAP_SERVER_IP = "server_ip or hostname"  # e.g., winexample01

config = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "myuser@example.com",
    "password": "s3cret",
    "use_pure": True,
    "krb_service_principal": f"{SERVICE_NAME}/{LDAP_SERVER_IP}"
}

with cpy.connect(**config) as cnx:
    with cnx.cursor() as cur:
        cur.execute("SELECT @@version")
        res = cur.fetchone()
        print(res[0])

Connector/Python supports Multi-Factor Authentication (MFA) as of v8.0.28 by utilizing the password1 (alias ofpassword), password2, andpassword3 connection options.

Connector/Python supportsWebAuthn Pluggable Authentication as of Connector/Python 8.2.0, which is supported in MySQL Enterprise Edition. Optionally use the Connector/Pythonwebauthn_callback connection option to notify users that they need to touch the hardware device. This functionality is present in the C implementation (which uses libmysqlclient) but the pure Python implementation requires the FIDO2 dependency that is not provided with the MySQL connector and is assumed to already be present in your environment. It can be independently installed using:

$> pip install fido2

Previously, the now removed (as of version 8.4.0)authentication_fido MySQL Server plugin was supported using thefido_callback option that was available in the C extension implementation.

Connector/Python supports OpenID Connect as of Connector/Python 9.1.0. Functionality is enabled with theauthentication_openid_connect_client client-side authentication plugin connecting to MySQL Enterprise Edition with theauthentication_openid_connect authentication plugin. These examples enable the plugin withauth_plugin and defines the JWT Identity Token file location with openid_token_file:

# Standard connection
import mysql.connector as cpy
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "openid_token_file": "{path-to-id-token-file}",
    "auth_plugin": "authentication_openid_connect_client",
    "use_pure": True, # Use False for C-Extension
}
with cpy.connect(**config) as cnx:
    with cnx.cursor() as cur:
        cur.execute("SELECT @@version")
        print(cur.fetchall())

# Or, using an async connection
import mysql.connector.aio as cpy_async
import asyncio
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "auth_plugin": "authentication_openid_connect_client",
    "openid_token_file": "{path-to-id-token-file}",
}
async def test():
    async with await cpy_async.connect(**config) as cnx:
        async with await cnx.cursor() as cur:
            await cur.execute("SELECT @@version")
            print(await cur.fetchall())
asyncio.run(test())

Character Encoding

By default, strings coming from MySQL are returned as Python Unicode literals. To change this behavior, setuse_unicode to False. You can change the character setting for the client connection through the charset argument. To change the character set after connecting to MySQL, set the charset property of the MySQLConnection instance. This technique is preferred over using the SET NAMES SQL statement directly. Similar to the charset property, you can set the collation for the current MySQL session.

Transactions

The autocommit value defaults toFalse, so transactions are not automatically committed. Call the commit() method of theMySQLConnection instance within your application after doing a set of related insert, update, and delete operations. For data consistency and high throughput for write operations, it is best to leave theautocommit configuration option turned off when using InnoDB or other transactional tables.

Time Zones

The time zone can be set per connection using thetime_zone argument. This is useful, for example, if the MySQL server is set to UTC andTIMESTAMP values should be returned by MySQL converted to the PST time zone.

SQL Modes

MySQL supports so-called SQL Modes. which change the behavior of the server globally or per connection. For example, to have warnings raised as errors, set sql_mode toTRADITIONAL. For more information, seeServer SQL Modes.

Troubleshooting and Error Handling

Warnings generated by queries are fetched automatically whenget_warnings is set to True. You can also immediately raise an exception by settingraise_on_warnings to True. Consider using the MySQL sql_mode setting for turning warnings into errors.

To set a timeout value for connections, useconnection_timeout.

Enabling and Disabling Features Using Client Flags

MySQL uses client flags to enable or disable features. Using theclient_flags argument, you have control of what is set. To find out what flags are available, use the following:

from mysql.connector.constants import ClientFlag
print '\n'.join(ClientFlag.get_full_info())

If client_flags is not specified (that is, it is zero), defaults are used for MySQL 4.1 and higher. If you specify an integer greater than 0, make sure all flags are set properly. A better way to set and unset flags individually is to use a list. For example, to setFOUND_ROWS, but disable the defaultLONG_FLAG:

flags = [ClientFlag.FOUND_ROWS, -ClientFlag.LONG_FLAG]
mysql.connector.connect(client_flags=flags)

Result Set Handling

By default, MySQL Connector/Python does not buffer or prefetch results. This means that after a query is executed, your program is responsible for fetching the data. This avoids excessive memory use when queries return large result sets. If you know that the result set is small enough to handle all at once, you can fetch the results immediately by setting buffered toTrue. It is also possible to set this per cursor (seeSection 10.2.6, “MySQLConnection.cursor() Method”).

Results generated by queries normally are not read until the client program fetches them. To automatically consume and discard result sets, set the consume_results option toTrue. The result is that all results are read, which for large result sets can be slow. (In this case, it might be preferable to close and reopen the connection.)

Type Conversions

By default, MySQL types in result sets are converted automatically to Python types. For example, a DATETIME column value becomes adatetime.datetime object. To disable conversion, set the raw option to True. You might do this to get better performance or perform different types of conversion yourself.

Connecting through SSL

Using SSL connections is possible when yourPython installation supports SSL, that is, when it is compiled against the OpenSSL libraries. When you provide thessl_ca, ssl_key andssl_cert options, the connection switches to SSL, and the client_flags option includes theClientFlag.SSL value automatically. You can use this in combination with the compressed option set to True.

As of Connector/Python 2.2.2, if the MySQL server supports SSL connections, Connector/Python attempts to establish a secure (encrypted) connection by default, falling back to an unencrypted connection otherwise.

From Connector/Python 1.2.1 through Connector/Python 2.2.1, it is possible to establish an SSL connection using only the ssl_ca opion. The ssl_key and ssl_cert arguments are optional. However, when either is given, both must be given or an AttributeError is raised.

# Note (Example is valid for Python v2 and v3)
from __future__ import print_function

import sys

#sys.path.insert(0, 'python{0}/'.format(sys.version_info[0]))

import mysql.connector
from mysql.connector.constants import ClientFlag

config = {
    'user': 'ssluser',
    'password': 'password',
    'host': '127.0.0.1',
    'client_flags': [ClientFlag.SSL],
    'ssl_ca': '/opt/mysql/ssl/ca.pem',
    'ssl_cert': '/opt/mysql/ssl/client-cert.pem',
    'ssl_key': '/opt/mysql/ssl/client-key.pem',
}

cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=True)
cur.execute("SHOW STATUS LIKE 'Ssl_cipher'")
print(cur.fetchone())
cur.close()
cnx.close()

Connection Pooling

With either the pool_name orpool_size argument present, Connector/Python creates the new pool. If the pool_name argument is not given, the connect() call automatically generates the name, composed from whichever of thehost, port,user, and database connection arguments are given, in that order. If thepool_size argument is not given, the default size is 5 connections.

The pool_reset_session permits control over whether session variables are reset when the connection is returned to the pool. The default is to reset them.

For additional information about connection pooling, seeSection 9.5, “Connector/Python Connection Pooling”.

Protocol Compression

The boolean compress argument indicates whether to use the compressed client/server protocol (defaultFalse). This provides an easier alternative to setting the ClientFlag.COMPRESS flag. This argument is available as of Connector/Python 1.1.2.

Converter Class

The converter_class argument takes a class and sets it when configuring the connection. AnAttributeError is raised if the custom converter class is not a subclass ofconversion.MySQLConverterBase.

Server Failover

The connect() method accepts afailover argument that provides information to use for server failover in the event of connection failures. The argument value is a tuple or list of dictionaries (tuple is preferred because it is nonmutable). Each dictionary contains connection arguments for a given server in the failover sequence. Permitted dictionary values are: user,password, host,port, unix_socket,database, pool_name,pool_size. This failover option was added in Connector/Python 1.2.1.

Option File Support

As of Connector/Python 2.0.0, option files are supported using two options for connect():

• option_files: Which option files to read. The value can be a file path name (a string) or a sequence of path name strings. By default, Connector/Python reads no option files, so this argument must be given explicitly to cause option files to be read. Files are read in the order specified.
• option_groups: Which groups to read from option files, if option files are read. The value can be an option group name (a string) or a sequence of group name strings. If this argument is not given, the default value is['client', 'connector_python'] to read the[client] and[connector_python] groups.

For more information, seeSection 7.2, “Connector/Python Option-File Support”.

LOAD DATA LOCAL INFILE

Prior to Connector/Python 2.0.0, to enable use ofLOAD DATA LOCAL INFILE, clients had to explicitly set theClientFlag.LOCAL_FILES flag. As of 2.0.0, this flag is enabled by default. To disable it, theallow_local_infile connection option can be set to False at connect time (the default isTrue).

Compatibility with Other Connection Interfaces

passwd, db andconnect_timeout are valid for compatibility with other MySQL interfaces and are respectively the same aspassword, database andconnection_timeout. The latter take precedence. Data source name syntax or dsn is not used; if specified, it raises a NotSupportedError exception.

Client/Server Protocol Implementation

Connector/Python can use a pure Python interface to MySQL, or a C Extension that uses the MySQL C client library. Theuse_pure mysql.connector.connect() connection argument determines which. The default changed in Connector/Python 8 fromTrue (use the pure Python implementation) toFalse. Setting use_pure changes the implementation used.

The use_pure argument is available as of Connector/Python 2.1.1. For more information about the C extension, seeChapter 8, The Connector/Python C Extension.