Connect to a MySQL database in Python

Here, we will learn to connect to a MySQL database in python with the help of a library called “PyMySQL“. You would need some knowledge of writing SQL queries to proceed.

PyMySQL is basically to an obsolete library “MySQLdb” which was also used to connect to MySQL databases.

The python DB-API is a specification for a common interface to relational databases like MySQL and Oracle. The current version of the specification is version 2.0.

Many would know that Python has inbuilt support for SQLite but, it is not recomended to use it in production environment.

Why would we need a database connection?

Python is a strong language for data analytics and visualization (thanks to the various packages like numpy, pandas, matplotlib to name a few).

Some may require connection to a database for data analytics. We could easily pull specific data from a database by executing queries on the stored tables and then storing the values in a df for analytics and visualizaion.

Some may also use it to automate database related tasks like creating views, inserting data or even deleting data.

Installing PyMySQL

We can easily install PyMySQL using the following command.


pip install pymysql

Steps to connect to MySQL database in Python

  1. Import PyMySQL module
  2. Create the connection object (using the connect() method)
  3. Create the cursor object (using the cursor() method)
  4. Execute the SQL query
  5. Close the connection object (using the close() method)

Before we get started with the code, make sure you already have a MySQL database set up on your system.

Here is MySQL database configuration which I had used for this post.

  • Server: localhost
  • Username: user_test
  • Password: user123
  • Database Name: MostlyPy

Let’s create a table named TEST_TABLE first.

Note that the connect() method takes the following arguments in order.
1. Host / server of the database
2. Username
3. Password
4. Name of the Database

import pymysql

# Create a database connection object using the connect() method
db_conn = pymysql.connect("localhost","user_test","test123","MostlyPy")

# Initialize cursor object using the cursor() method
cursor = db_conn.cursor()

# Store the SQL query to be executed as a string
sql_query = """CREATE TABLE IF NOT EXISTS TEST_TABLE (\
FirstName CHAR(20), \
LastName CHAR(20), \
Age INT)"""

# Execute the SQL query using the execute() method
cursor.execute(sql_query)

# Fetch the data returned by the query using fetchall() method
data = cursor.fetchall()

# Close the connection
db_conn.close()

We have now created a table named “TEST_TABLE” as shown in the sql console below.

mysql database table

Let’s also try to see the newly created table using the “SHOW TABLES” query in python.

import pymysql

# Create a database connection object using the connect() method
db_conn = pymysql.connect("localhost","user_test","test123","MostlyPy")

# Initialize cursor object using the cursor() method
cursor = db_conn.cursor()

# Store the SQL query to be executed as a string
sql_query = """SELECT * FROM TEST_TABLE;"""

# Execute the SQL query using the execute() method
cursor.execute(sql_query)

# Fetch the queried data from the cursor
data = cursor.fetchall()
print(data)

# Close the connection
db_conn.close()
Output: (('TEST_TABLE',),)

Now, we will add data to the table we just created.

import pymysql

# Create a database connection object using the connect() method
db_conn = pymysql.connect("localhost","user_test","test123","MostlyPy")

# Initialize cursor object using the cursor() method
cursor = db_conn.cursor()

# Store the SQL query to be executed as a string
sql_query = """INSERT INTO TEST_TABLE VALUES ('Diana', 'Carter', 20);"""

# Execute the SQL query using the execute() method
cursor.execute(sql_query)

# Commit the changes to the database
db_conn.commit()

# Close the connection
db_conn.close()

Similarly, after adding a few more records, let’s try to see how to pull records from the database.

import pymysql

# Create a database connection object using the connect() method
db_conn = pymysql.connect("localhost","user_test","test123","MostlyPy")

# Initialize cursor object using the cursor() method
cursor = db_conn.cursor()

# Store the SQL query to be executed as a string
sql_query = """SELECT * FROM TEST_TABLE;"""

# Execute the SQL query using the execute() method
cursor.execute(sql_query)

# Fetch the queried data from the cursor
data = cursor.fetchall()
print(data)

# Close the connection
db_conn.close()

Finally, executing the code we get the output as shown below.

Output: (('Diana', 'Carter', 20), ('Mary', 'Scott', 18))

Similarly we can run any DDL (Data Definition Language), DML (Data Manipulation Language) or DQL (Data Query Language) query using python. Just remember to change the query inside the “sql_query variable to execute another query.

Links for reference:
PyMySQL official documentation

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: