Connecting to MySQL using Connector/Python

To connect to the database we use the connect() function of the mysql.connector module. It accepts connection credentials and returns an object of type MySQLConnection or CMySQLConnection (if C extension is installed).

The following table lists some common arguments of the connect() function.

Argument Default Description
database Database name
user Username to authenticate to authenticate with MySQL
password Password to authenticate the user
host 127.0.0.1 Hostname where the MySQL server is installed
port 3306 The TCP/IP port of the MySQL server

Note: For complete list of arguments consult the official documentation.

The code in the following listing connects to the world database and prints the connection id.

Expected Output:

Since I am on a Linux distribution where C extension is installed, the connect() function returns an object of type CMySQLConnection instead of MySQLConnection.

On a system where the C extension is not installed the output will look like this:

Instead of passing connection credentials as keyword arguments, you also pass them in a dictionary.

Using pure Python or C Extension

The use_pure connection argument determines whether to use C extension or not. If use_pure set to True, Connector/Python will use the pure Python implementation. As of 8.0.11, the
use_pure argument defaults to False. This is the reason why preceding snippet uses the C extension. If however, use_pure is set to False and the C extension is not available, then Connector/Python will use the pure Python implementation.

Expected Output:

Closing Connection

The connection to the database is automatically closed when the program ends. However, it is always good idea to close the connection explicitly when you are finished working with it.

To close the connection, we use the close() method of the MySQLConnection object.

We now know how to connect to the database. Let’s see how we can handle errors.

Using Configuration Files

Hardcoding connection credentials into the application is fine for testing purposes but it is not very feasible for the production environment for two good reasons.

  1. Anyone who has access to the source can peek into the connection details.
  2. On migrating to a new server, you would have to update the source code again.

A much better approach is to store the connection details in an external file. Since version 2.0 Connector/Python can read connection details from a Windows-INI-style file.

The following two arguments control settings about the configuration files:

Argument Default Description
option_files It species the configuration files to read. Its value can be a string or list of strings.
option_groups ['client', 'connector_python'] It specifies the name of the section to read options from. By default, options are only read from client and connector_python section.

Create a new file named my.conf with the connection credentials as follows:

my.conf

The code in the following listing reads connection details from my.conf file.

Notice that in my.conf file we have specified the connection details under the section connector_python', which is one of the two sections from where MySQL Connector/Python will read options by default. If you want to change section name use the option_groups argument, as follows:

We can also split the connection details into multiple files. This can come in handy when you want to share some configuration across connections.

my1.conf

my2.conf

To read options from multiple configuration files change the value of option_files to a list.

Leave a Comment