1. Home
2. MySQL Connector/Python Tutorial
3. Connecting to MySQL using Connector/Python

Connecting to MySQL using Connector/Python

Last updated on July 27, 2020

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.

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

import mysql.connector

db = mysql.connector.connect(
    host='localhost',
    database='world',
    user='root',
    password='pass'
)

print("Connection ID:", db.connection_id)

print(db)

Expected Output:

1
2

Connection ID:  14
<mysql.connector.connection_cext.CMySQLConnection object at 0x7ff200815c88>

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:

1
2

Connection ID:  15
<mysql.connector.connection.MySQLConnection object at 0x7fef0f102c88>

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

import mysql.connector

connection_args = {
    'host': 'localhost',
    'database': 'world',
    'user': 'root',
    'password': 'pass'
}

db = mysql.connector.connect(**connection_args)

print("Connection ID:", db.connection_id)

print(db)

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import mysql.connector

db = mysql.connector.connect(
    host='localhost',
    database='world',
    user='root',
    password='pass',
    use_pure=True       # use pure Python implementation
)

print("Connection ID:", db.connection_id)

print(db)

Expected Output:

1
2

Connection ID: 18
<mysql.connector.connection.MySQLConnection object at 0x7fe4499f1c88>

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

import mysql.connector

db = mysql.connector.connect(
    host='localhost',
    database='world',
    user='root',
    password='pass',
    use_pure=True
)

print("Connection ID:", db.connection_id)

print(db)

db.close()   # close the connection

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:

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

my.conf

1
2
3
4
5
6

[connector_python]
host = 127.0.0.1
database = world
user = root
password = pass
port  = 3306

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

1
2
3
4
5
6
7
8
9

import mysql.connector

db = mysql.connector.connect(option_files='my.conf')

print("Connection ID:", db.connection_id)

print(db)

db.close()

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:

1
2
3
4
5
6

[connection_details]
host = 127.0.0.1
database = blog
user = root
password = pass
port  = 3306

1
2
3
4
5
6
7
8
9

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', option_groups=['connection_details'])

print("Connection ID:", db.connection_id)

print(db)

db.close()

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

1
2
3

[connector_python]
host = 127.0.0.1
port = 3306

my2.conf

1
2
3
4

[connector_python]
database = world
user = root
password = pass

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

1
2
3
4
5
6
7
8
9

import mysql.connector

db = mysql.connector.connect(option_files=['my1.conf', 'my1.conf'])

print("Connection ID:", db.connection_id)

print(db)

db.close()