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:-
- MySQL Connector Python
- 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.
Table of contents
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. |
How to Connect to MySQL Database in Python
- Install MySQL connector module
Use the pip command to install MySQL connector Python.
pip install mysql-connector-python
- Import MySQL connector module
Import using a
import mysql.connector
statement so you can use this module’s methods to communicate with the MySQL database. - Use the connect() method
Use the
connect()
method of the MySQL Connector class with the required arguments to connect MySQL. It would return aMySQLConnection
object if the connection established successfully - Use the cursor() method
Use the cursor() method of a
MySQLConnection
object to create a cursor object to perform various SQL operations. - Use the execute() method
The execute() methods run the SQL query and return the result.
- Extract result using fetchall()
Use
cursor.fetchall()
orfetchone()
orfetchmany()
to read query result. - Close cursor and connection objects
use
cursor.clsoe()
andconnection.clsoe()
method to close open connections after your work completes

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 aDatabase 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 theMySQLConnection
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.
connection_timeout
– Timeout for the TCP and Unix socket connectionsauto_commit
– Whether to auto-commit transactions. The default is falsepool_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.