Python MySQL Database Connection using MySQL Connector

In this lesson, you will learn how to connect the MySQL database in Python using the ‘MySQL Connector Python‘ module. This Python MySQL tutorial demonstrates how to develop and integrate Python applications with a MySQL database server.

In Python, We can use the following modules to communicate with MySQL.

• MySQL Connector Python
• PyMySQL
• MySQLDB
• MySqlClient
• OurSQL

Note: Above all interfaces or modules are adhere to Python Database API Specification v2.0 (PEP 249) that means the syntax, method, and way of access the database is the same in all.

PEP 249 is designed to encourage and maintain similarity between the Python modules that are used to access databases. By doing this, above all modules are following rules defined in Python Database API Specification v2.0 (PEP 249).

You can choose any of the above modules as per your requirements. The way of accessing the MySQL database remains the same. I recommend you to use any of the following two modules:-

1. MySQL Connector Python
2. PyMySQL

Note: This tutorial focuses on the MySQL Connector Python module. All examples are created using MySQL Connector Python.

Advantages and benefits of MySQL Connector Python: –

• MySQL Connector Python is written in pure Python, and it is self-sufficient to execute database queries through Python.
• It is an official Oracle-supported driver to work with MySQL and Python.
• It is Python 3 compatible, actively maintained.

How to connect MySQL database in Python

Let’s see how to connect the MySQL database in Python using the ‘MySQL Connector Python’ module.

Arguments required to connect

You need to know the following detail of the MySQL server to perform the connection from Python.

Argument	Description
Username	The username that you use to work with MySQL Server. The default username for the MySQL database is a root.
Password	Password is given by the user at the time of installing the MySQL server. If you are using root then you won’t need the password.
Host name	The server name or Ip address on which MySQL is running. if you are running on localhost, then you can use localhost or its IP 127.0.0.0
Database name	The name of the database to which you want to connect and perform the operations.

Run the below query on the MySQL console if you have not created any database in MySQL. Otherwise, you can skip the below query.

Create Database in MySQL

Create database Electronics;

Example to connect to MySQL Database in Python

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output.

Connected to MySQL Server version  5.7.19
You're connected to database:  ('electronics',)
MySQL connection is closed

Points to remember

• Catch exceptions that may occur during this process by importing the Error class from the MySQL connector module using a from mysql.connector import Error statement.
  Error class is useful to debug when we failed to connect to MySQL. For example, ACCESS DENIED ERROR when the username or password is wrong.
• The connect() method can throw a Database error exception if one of the required parameters is wrong. For example, if you provide a database name that is not present in MySQL.
• The is_connected() is the method of the MySQLConnection class through which we can verify is our Python application connected to MySQL.
• At last, we are closing the MySQL database connection using a close() method of MySQLConnection class.

Create MySQL table from Python

Now you know how to connect to a MySQL server from Python, In this section, we will learn how to create a table in MySQL from Python. Let’s create table ‘Laptop’ under the ‘Electronics’ database.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_Create_Table_Query = """CREATE TABLE Laptop ( 
                             Id int(11) NOT NULL,
                             Name varchar(250) NOT NULL,
                             Price float NOT NULL,
                             Purchase_date Date NOT NULL,
                             PRIMARY KEY (Id)) """

    cursor = connection.cursor()
    result = cursor.execute(mySql_Create_Table_Query)
    print("Laptop Table created successfully ")

except mysql.connector.Error as error:
    print("Failed to create table in MySQL: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Laptop Table created successfully 
MySQL connection is closed

Python MySQL CRUD Operation

Also, learn how to execute various MySQL operations from Python by referring to the following articles.

Click on each tutorial to study operations in detail.

• Insert rows into MySQL table from Python: Insert a single and multiple rows into the MySQL table. Also, learn how to use Python variables in the parameterized query to insert dynamic data into a table.
• Select rows from MySQL table using Python: Execute a SQL SELECT query from a Python application to fetch rows from MySQL table. Also, learn how to process SELECT query results, Fetch all rows or single rows from the table, and count total rows of a table.
• Update rows of MySQL table from Python: Update a single row, multiple rows, a single column, and various columns. Additionally, learn how to use python variables in the parameterized query to update table data.
• Delete table rows from Python: Delete a single row, multiple rows, a single column, and various columns. Also, learn to Delete all Rows, Delete tables, and an entire database from MySQL using python.
• Execute MySQL stored procedures from Python and learn how to pass IN and OUT parameters to the MySQL stored procedure.
• Python MySQL Parameterized Query and Prepared Statement: Learn to use Parameterized Queries or Prepared Statement to use Python variables in the SQL query to pass dynamic data to MySQL table.
• Python MySQL Commit and Rollback to Manage Transactions: Manage MySQL database transactions from Python to maintain the ACID property of MySQL transactions using the commit() and rollback() methods.
• Python Database Connection Pooling With MySQL: Learn to create and use a connection pool to increase the performance of your Python MySQL applications.
• Python MySQL BLOB Insert and Retrieve digital data: Learn to insert or fetch any digital information such as a file, image, video, or song as blob data into MySQL table from Python.

Python MySQL Connection arguments list

We already discussed the four mandatory arguments required to connect the MySQL Server.

Let see what other connection arguments we can use to communicate with the MySQL server from Python. Below is the list of all other connection arguments and their meaning.

• port: The TCP/IP port of the MySQL server. This value must be an integer. We can specify the different port number if the MySQL server is listening to a different port. The default value for this port argument is 3306.
• use_unicode: Specify whether to use Unicode or not. The default value is True.
• charset: MySQL character set to use, character set variables relate to a client’s interaction with the server. There are almost 30 to 40 charset MySQL server supports. The default value of the charset argument is “utf8″.
• auto-commit: Set it to true if you want to auto-commit transactions. If you wish to manage transactions in MySQL from Python, you need to set this value true or false. The default value is False, i.e., the changes are not committed to the database. You need to explicitly call a commit method to persist your changes in the database.
• get_warnings: To fetch warning, this is helpful to know the connection is established but with warnings. The default value is False.
• raise_on_warnings: Set it when you want to raise an exception on warnings. The Default value is False.
• connection_timeout (connect_timeout*) : Timeout for the TCP and Unix socket connections. The connection terminates after this timeout expired.
• buffered: If true, the cursor objects fetch the results immediately after executing queries. The default value is False.
• raw: If true, MySQL results are returned as-is rather than converting into Python types. The default value is False. You can set it to true if you want a query result in MySQL type.
• force_ipv6: When setting to True, uses IPv6 when an address resolves to both IPv4 and IPv6. By default, IPv4 is used in such cases. The default value for this argument is false.
• pool_name: It is the Connection pool name that you are creating or using.
• pool_size: Connection pool size that you want to create. The default value is 5.
• pool_reset_session: Reset session variables when the connection is returned to the pool. The default is True.
• use_pure: Specify whether to use pure Python or C Extension. If use_pure=False, then a pure Python module is used; otherwise, it connects MySQL using C extension. Moreover, if C Extension is not available, MySQL Connector Python automatically falls back to the pure Python implementation.
• unix_socket: The location of the Unix socket file. These enable communication between two processes.
• auth_plugin: Authentication plugin to use, Added in 1.2.1.
• collation: MySQL collation to use. You can use the collation that you set while installing MySQL Server. The default value is utf8_generalW_chiich.
• sql_mode: Set the sql_mode session variable at connection time.

Use the Dictionary to keep MySQL Connection arguments

Furthermore, let see how to use a dictionary to store all of these connection arguments.

If you have lots of connection arguments, it’s best to keep them in a dictionary and use the ** operator.  for example, you know you require a minimum of four arguments  (i.e., username, password, hostname, database name) to connect MySQL.

If you have lots of connection arguments, it’s best to keep them in a dictionary and use the ** operator. In exceptional cases, we need more than four arguments in the connect method to connect the MySQL database. Let’s understand this. For example, below are three more connection arguments we can use in the connect() method.

1. connection_timeout – Timeout for the TCP and Unix socket connections
2. auto_commit – Whether to auto-commit transactions. The default is false
3. pool_size – Connection pool size if you want to use connection pooling.

You can use many other connection arguments as per your need, add them all in a dictionary, and pass a dictionary to connect() method. Let’s demonstrate it in the below example.

import mysql.connector
from mysql.connector import Error

try:
    connection_config_dict = {
        'user': 'pynative',
        'password': 'pynative@123',
        'host': '127.0.0.1',
        'database': 'Electronics',
        'raise_on_warnings': True,
        'use_pure': False,
        'autocommit': True,
        'pool_size': 5
    }
    connection = mysql.connector.connect(**connection_config_dict)

    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("Your connected to database: ", record)

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Change MySQL Connection Timeout from Python

Sometimes we need to change the connection timeout value if we read or insert extensive data to the MySQL server. Connection terminates if the request takes more time than this value.

Use a connection_timeout argument of MySQL connector Python to manage the timeout issues by increasing the timeout value.

The connection_timeout is the timeout value in second for the TCP and Unix socket connections. This time denotes the number of seconds the MySQL server waits to fulfill the current request.

You can also set the following Parameters of the MySQL server by executing SQL query from Python to handle the connection timeout issue. Change the following parameters’ value only when the connection_timeout argument alone can’t control the timeout issue.

• interactive_timeout: The number of seconds the server should wait for activity on an interactive connection before closing it.
• wait_timeout – Number of seconds the server should wait for activity on a connection before closing it.

Example

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29', connection_timeout=180)

    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL database... MySQL Server version on ", db_Info)

        cursor = connection.cursor()
        # global connection timeout arguments
        global_connect_timeout = 'SET GLOBAL connect_timeout=180'
        global_wait_timeout = 'SET GLOBAL connect_timeout=180'
        global_interactive_timeout = 'SET GLOBAL connect_timeout=180'

        cursor.execute(global_connect_timeout)
        cursor.execute(global_wait_timeout)
        cursor.execute(global_interactive_timeout)

        connection.commit()

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    # closing database connection.
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

As you can see, I have set all connection timeout values to 180 seconds, i.e., 3 min in the above program.

Connect to MySQL Using Connector Python C Extension

Python connector module has a C Extension interface to connect the MySQL database. The use_pure connection argument determines whether to connect to MySQL using a pure Python interface or a C Extension.

The default value of use_pure is False means it uses the pure Python implementation to connect that we already discussed. The below example demonstrates how to connect using a C extension.

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29', use_pure=True)

    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL database... MySQL Server version on ", db_Info)
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

I have created a Hospital Information System exercise using Python and MySQL. This Exercise has six questions. I have provided the required tables so you can proceed directly to solve the problems of this Exercise.

That’s it, Folks. Let me know your comments in the section below.