The connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object. The following example shows how to connect to the MySQL server: import mysql.connector
cnx = mysql.connector.connect(user='scott', password='password',
host='127.0.0.1',
database='employees')
cnx.close()
Section 7.1, “Connector/Python Connection Arguments” describes the permitted connection arguments. It is also possible to create
connection objects using the connection.MySQLConnection() class: from mysql.connector import (connection)
cnx = connection.MySQLConnection(user='scott', password='password',
host='127.0.0.1',
database='employees')
cnx.close()
Both forms (either using the connect() constructor or the class directly) are valid and functionally equal, but using connect() is preferred and used by most examples in this manual. To handle connection errors, use the try statement and catch
all errors using the errors.Error exception: import mysql.connector
from mysql.connector import errorcode
try:
cnx = mysql.connector.connect(user='scott',
database='employ')
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)
else:
cnx.close()
Defining connection arguments in a dictionary and using the ** operator is another option: import mysql.connector
config = {
'user': 'scott',
'password': 'password',
'host': '127.0.0.1',
'database': 'employees',
'raise_on_warnings': True
}
cnx = mysql.connector.connect(**config)
cnx.close()
Using the Connector/Python Python or C Extension Connector/Python offers two implementations: a pure Python interface and a C
extension that uses the MySQL C client library (see Chapter 8, The Connector/Python C Extension). This can be configured at runtime using the use_pure connection argument. It defaults to False as of MySQL 8, meaning the C extension is used. If the C extension is not available on the system then use_pure defaults to True .
Setting use_pure=False causes the connection to use the C Extension if your Connector/Python installation includes it, while use_pure=True to False means the Python implementation is used if available. Note The use_pure option and C extension were added in Connector/Python 2.1.1. The following example shows how to set use_pure to False. import mysql.connector
cnx = mysql.connector.connect(user='scott', password='password',
host='127.0.0.1',
database='employees',
use_pure=False)
cnx.close()
It is also possible to use the C Extension directly by importing the _mysql_connector module rather than the mysql.connector module. For
more information, see Section 8.2, “The _mysql_connector C Extension Module”. A connection with the MySQL server can be established using either the mysql.connector.connect() function or the mysql.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
Argument Name | Default | Description |
---|
user (username *) |
| The user name used to authenticate with the MySQL server.
|
---|
password (passwd *) |
| The password to authenticate the user with the MySQL server.
|
---|
password1 , password2 , and password3 |
| For Multi-Factor Authentication (MFA); password1 is an alias for password . Added in 8.0.28.
|
---|
database (db *) |
| The database name to use when connecting with the MySQL server.
|
---|
host | 127.0.0.1
| The host name or IP address of the MySQL server.
|
---|
unix_socket |
| The location of the Unix socket file.
|
---|
port | 3306
| The TCP/IP port of the MySQL server. Must be an integer.
|
---|
conn_attrs |
| Standard performance_schema.session_connect_attrs values are sent; use conn_attrs to optionally set additional custom connection attributes as defined by a dictionary such as config['conn_attrs'] = {"foo": "bar"}. The c-ext and pure python implementations differ. The c-ext implementation depends on the mysqlclient library so its standard conn_attrs values originate from it. For example, '_client_name' is 'libmysql' with c-ext but 'mysql-connector-python' with pure python. C-ext adds
these additional attributes: '_connector_version', '_connector_license', '_connector_name', and '_source_host'. This option was added in 8.0.17, as was the default session_connect_attrs behavior.
|
---|
auth_plugin |
| Authentication plugin to use. Added in 1.2.1.
|
---|
fido_callback |
| An callable defined by the optional fido_callback option is executed when it's ready for user interaction with the hardware FIDO device. This option can be a callable object or a string path that the connector can import in runtime and execute. It does not block and is only used to notify the user of the need for interaction with the hardware FIDO device. This functionality is only available in the C extension. A NotSupportedError is raised when using the
pure Python implementation.
|
---|
use_unicode | True
| Whether to use Unicode.
|
---|
charset | utf8mb4
| Which MySQL character set to use.
|
---|
collation | utf8mb4_general_ai_ci (is utf8_general_ci in 2.x
| Which MySQL collation to use. The 8.x default values are generated from the latest MySQL Server 8.0 defaults.
|
---|
autocommit | False
| Whether to autocommit
transactions.
|
---|
time_zone |
| Set the time_zone session variable at connection
time.
|
---|
sql_mode |
| Set the sql_mode session variable at connection time.
|
---|
get_warnings | False
| Whether to fetch warnings.
|
---|
raise_on_warnings | False
| Whether to raise an exception on warnings.
|
---|
connection_timeout (connect_timeout *) |
| Timeout for the TCP and Unix socket connections.
|
---|
client_flags |
| MySQL client flags.
|
---|
buffered | False
| Whether cursor objects fetch the results immediately after executing
queries.
|
---|
raw | False
| Whether MySQL results are returned as is, rather than converted to Python types.
|
---|
consume_results | False
| Whether to automatically read result sets.
|
---|
tls_versions | ["TLSv1.2", "TLSv1.3"]
| TLS versions to support; allowed versions are TLSv1.2 and TLSv1.3. Versions TLSv1 and TLSv1.1 were removed in Connector/Python 8.0.28.
|
---|
ssl_ca |
| File containing the SSL certificate authority.
|
---|
ssl_cert |
| File containing the SSL certificate file.
|
---|
ssl_disabled | False
| True disables SSL/TLS usage. The TLSv1 and TLSv1.1 connection protocols are deprecated as of Connector/Python 8.0.26 and removed as of Connector/Python 8.0.28.
|
---|
ssl_key |
| File containing the SSL key.
|
---|
ssl_verify_cert | False
| When set to True , checks the server certificate against the certificate file specified by the ssl_ca option. Any mismatch causes a ValueError exception.
|
---|
ssl_verify_identity | False
| When set to True , additionally perform host name identity verification by checking the host name that the client uses for connecting to the server against the identity in the certificate that the server sends to the client. Option added in Connector/Python 8.0.14.
|
---|
force_ipv6 | False
| When set to True , uses IPv6 when an address resolves to both IPv4 and IPv6. By default, IPv4 is used in such
cases.
|
---|
oci_config_file | ""
| Optionally define a specific path to the authentication_oci server-side authentication configuration file.
|
---|
dsn |
| Not supported (raises NotSupportedError when used).
|
---|
pool_name |
| Connection pool name. The pool name is restricted to alphanumeric characters and the special characters . , _ , * , $ , and # . The pool name must be no more than pooling.CNX_POOL_MAXNAMESIZE characters long (default 64).
|
---|
pool_size | 5
| Connection pool size. The pool size must be greater than 0 and less than or equal to pooling.CNX_POOL_MAXSIZE (default 32).
|
---|
pool_reset_session | True
| Whether to reset session variables when connection is returned to pool.
|
---|
compress | False
| Whether to use compressed client/server protocol.
|
---|
converter_class |
| Converter class to use.
|
---|
converter_str_fallback | False
| Enable the conversion to str of value types not supported by the Connector/Python converter class or by a custom converter
class.
|
---|
failover |
| Server failover sequence.
|
---|
option_files |
| Which option files to read. Added in 2.0.0.
|
---|
option_groups | ['client', 'connector_python']
| Which groups to read from option files. Added in 2.0.0.
|
---|
allow_local_infile | True
| Whether to enable LOAD DATA
LOCAL INFILE . Added in 2.0.0.
|
---|
use_pure | False as of 8.0.11, and True in earlier versions. If only one implementation (C or Python) is available, then then the default value is set to enable the available implementation.
| Whether to use pure Python or C Extension. If use_pure=False and the C Extension is not available, then Connector/Python will automatically fall back to the pure Python implementation. Can be set with mysql.connector.connect() but not MySQLConnection.connect(). Added in
2.1.1.
|
---|
krb_service_principal | The "@realm" defaults to the default realm, as configured in the krb5.conf file.
| Must be a string in the form "primary/instance@realm" such as "ldap/" where "@realm" is optional. Added in 8.0.23.
|
---|
MySQL Authentication Options Authentication with MySQL typically uses a username 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 the MySQLConnection 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 setting unix_socket . Named pipes on the Windows platform are not supported. Connector/Python supports authentication plugins available as of MySQL 5.6. This includes mysql_clear_password and sha256_password , both of which require an SSL connection. The sha256_password plugin does not work over a non-SSL connection because Connector/Python does not support RSA encryption. The connect()
method supports an auth_plugin argument that can be used to force use of a particular plugin. For example, if the server is configured to use sha256_password by default and you want to connect to an account that authenticates using mysql_native_password , either connect using SSL or specify auth_plugin='mysql_native_password' . Note MySQL Connector/Python does not support the old, less-secure password protocols of MySQL versions prior to 4.1. Connector/Python supports the
Kerberos 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. Connector/Python supports Multi-Factor Authentication (MFA) as of v8.0.28 by utilizing the
password1 (alias of password ), password2 , and password3 connection options. Connector/Python supports FIDO Pluggable Authentication as of Connector/Python 8.0.29; an authentication mechanism added in MySQL Enterprise Edition 8.0.27. Restrictions: FIDO authentication functionality is only available in the C-extension implementation (installed by default); a
NotSupportedError is raised when using the pure Python implementation of this connector. Also only a 2-level structure is supported, in that the connector and the physical key are on the same machine. See FIDO Pluggable Authentication for installation details, and optionally use the Connector/Python
fido_callback connection option to notify users that they need to touch the hardware device. Character Encoding By default, strings coming from MySQL are returned as Python Unicode literals. To change this behavior, set use_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 to False , so transactions are not automatically committed. Call the commit() method
of the MySQLConnection 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 the autocommit configuration option turned off when using InnoDB or other transactional tables. Time Zones The time zone can be set per connection using the time_zone argument. This is useful, for example, if the MySQL server is set to UTC and TIMESTAMP
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 to TRADITIONAL . For more information, see Server SQL Modes. Troubleshooting and
Error Handling Warnings generated by queries are fetched automatically when get_warnings is set to True . You can also immediately raise an exception by setting raise_on_warnings to True . Consider using the MySQL sql_mode setting for turning warnings into errors. To set a timeout value for connections, use connection_timeout . Enabling and Disabling Features Using Client
Flags MySQL uses client flags to enable or disable features. Using the client_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 set FOUND_ROWS , but disable the default LONG_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 to True . It is also possible to set this per cursor (see Section 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 to True . 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 a datetime.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 your Python installation supports SSL, that is, when it is compiled against the OpenSSL libraries. When you provide the ssl_ca , ssl_key and ssl_cert
options, the connection switches to SSL, and the client_flags option includes the ClientFlag.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 or pool_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 the host , port , user , and
database connection arguments are given, in that order. If the pool_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, see
Section 9.1, “Connector/Python Connection Pooling”. Protocol Compression The boolean compress argument indicates whether to use the compressed client/server protocol (default False ). 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. An AttributeError is raised if the custom converter class is not a subclass of conversion.MySQLConverterBase . Server Failover The connect() method accepts a failover 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, see Section 7.2, “Connector/Python Option-File Support”. LOAD DATA LOCAL INFILE Prior to Connector/Python 2.0.0, to enable use of
LOAD DATA LOCAL
INFILE , clients had to explicitly set the ClientFlag.LOCAL_FILES flag. As of 2.0.0, this flag is enabled by default. To disable it, the allow_local_infile connection option can be set to False at connect time (the default is True ). Compatibitility with Other Connection Interfaces passwd , db and connect_timeout are valid for compatibility with other MySQL interfaces and are
respectively the same as password , database and connection_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. The use_pure mysql.connector.connect() connection argument determines which. The default changed
in Connector/Python 8 from True (use the pure Python implementation) to False . 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, see Chapter 8, The Connector/Python C Extension. |