How To Connect MySQL Database From Python Application and Execute SQL Query with Examples?

MySQL is a very popular and open source database server. Python is an old but lately discovered language. In this tutorial, we will look at how to use this popular tech in our applications in order to run SQL queries.

Install Python MySQL Connector

In order to connect the MySQL database from Python application, we need some libraries. There are different libraries that support running SQL queries on MySQL but we will stick with mysql-connector-python3 .

Fedora, CentOS, RHEL

We can install the mysql-connector-python3 library with the following command. This requires root privileges.

$ yum install mysql-connector-python3

Debian, Ubuntu,Mint

We can install the mysql-connector-python3 library with the following command. This requires root privileges.

$ apt install mysql-connector-python3

Pip

Pip can be used to install mysql connector library.

$ pip3 install mysql-connector-python

Load MySQL Library

In order to use the MySQL library, we need to import it into our application. We will use import statement to import library which is named mysql.connector .

#!/bin/python3 
 
import mysql.connector

Connect MySQL Database with Username and Password

Now we can connect to the database. While connecting MySQL database we to provide following parameters to the mysql.connector.connect function

• user is the username to authenticate
• password is the password of the user
• host the database server hostname or IP address
• database is optional which provides the database name

#!/bin/python3 
 
import mysql.connector 
 
dbcon = mysql.connector.connect(user='root',password='',host='localhost', database='poftut') 
 
dbcon.close()

Run and Execute SQL Query

In order to run the SQL query, we need to create a cursor that is like a SQL query window in GUI SQL tools. We will use dbcon.cursor() and then use created cursor objects execute function by providing the SQL.

#!/bin/python3 
 
import mysql.connector 
 
dbcon = mysql.connector.connect(user='root',password='S3cR.t',host='localhost') 
 
cur = dbcon.cursor() 
 
cur.execute("SELET * FROM articles")

Print and List Returned Data

After executing the query the results will be saved to the cursor object named cur . We can get and list returned information from cur object. Provided data is stored in a list format. So we can use loops to iterate and print.

#!/bin/python3 
 
import mysql.connector 
 
dbcon = mysql.connector.connect(user='root',password='',host='localhost') 
 
cur = dbcon.cursor() 
 
cur.execute("SELET * FROM articles") 
 
for (id, name) in cur: 
      print("{}, {}".format(id, name))

Close MySQL Connection

One of the most important part of database programming is using sources very strictly. In order to prevent performance problems we need to close the connection to the MySQL database after finishing job. We will use close() function of connection object.

#!/bin/python3 
 
import mysql.connector 
 
dbcon = mysql.connector.connect(user='root',password='',host='localhost') 
 
cur = dbcon.cursor() 
 
cur.execute("SELET * FROM articles") 
 
for (id, name) in cur: 
      print("{}, {}".format(id, name)) 
 
dbcon.close()