Python MySql Database Connectivity

To connect Python application with mysql database, first we need to have installed following dependency in our computer system.

Prerequisite

• Python language 
• MySql Database
• Python-Mysql Connector (Driver)

In this topic, we are focused to connect python application to MySql. So, we assume that Python and MySql is already installed. 

Install MySql Driver for Python

Python-Mysql connector is a driver which acts as a bridge between Python and Mysql. To install Python-Mysql Connector, we can use following command. We can also download driver from mysql official site. Mysql Site.

$ pip install mysql-connector-python

It will download and install the driver in our computer system.

Python MySql Connectivity Steps

After successfully installing driver, follow the given steps which are required to create connection between Python and MySql.

1. Import python-mysql driver package
2. Create connection
3. Create cursor object
4. Create query
5. Execute query
6. Commit/rollback
7. Close connection

1. Import Driver

This is first and essential step in which we need to import the installed python-mysql driver package. This package contains classes and methods that are required to establish the connection.

import mysql.connector

2. Create Connection

To create a connection, connect() method is used which returns connection object. It takes various arguments like: username, password, host, dbname, port, charset, unix_socket etc.

con = mysql.connector.connect(username, password, host, database)

3. Create Cursor Object

Cursor object is used to execute Sql queries. This step is required only if we want to execute Sql queries. We can get cursor object from cursor() method of connection class.

cursor_obj = con.cursor()

4. Execute Query

In this step, execute the query using execute() method. We can pass sql query in this method as an argument. It can also be used for perameterized query. This step is required only if we want to execute Sql queries.

cursor_obj.execute(sql_query)

5. Commit/rollback

It is necessary to commit your changes after executing sql query. To perform commit use commit() method of connection class. We can also use roolback() method for roll back the changes in case of commit does not work.

con.commit()
# or 
con.rollback()

6. Close Connection

After performing database activities, now close the connection instance. To close the connection, use the close() method of connection class. This method releases all the occupied resources safely.

con.close()

Python Script to Connect MySql Database

Now, lets create Python script to connect with MySql. First, we need to import mysql.connector module then provide username, password, hostname, database name etc. 

mysqlconnect.py

import mysql.connector

from mysql.connector import errorcode

try:
  
con = mysql.connector.connect(user='root', password='mysqlpassword', host='127.0.0.1', database='dbname')
except mysql.connector.Error as err:
  
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    
print("Your user name or password is incorrect")
  
elif err.errno == errorcode.ER_BAD_DB_ERROR:
    
print("Database does not exist")
  
else:
    
print(err)
else:
  
con.close()
print(con)

Run the Python Script

To test the script and database connectivity, we can execute the script from terminal by using the below command. 

$ python mysqlconnect.py

After successful connectivity, It returns an object which further can be used to handle database.

If we print this object, it produce the following output to the console.

Output:

<mysql.connector.connection.MySQLConnection object at 0x7f715373b550>

Conclusion

In this tutorial, we learnt how to connect to MySql database using Python Script. We can use this script to connect our Python application or just to test the database connection.

In next chapter, we will discuss about the database handling like creating table and inserting data etc. Till then Happy Coding!