How to Convert SQL to DataFrame in Pandas

To convert a result of the SQL query into a Pandas DataFrame, use the pd.read_sql_query() method. 

PyMySQL is a pure-Python MySQL client library based on PEP 249. Most public APIs are compatible with mysqlclient and MySQLdb. In addition, PyMySQL works with MySQL 5.5+ and MariaDB 5.5+.

Here is the step-by-step guide:

Step 1: Showing MySQL Table

Here is the example table:

Screenshot of MySQL Database and Table

Step 2: Installing and loading Pandas and pymysql

Check out this link to install the pymysql package if you have not installed it.

You also need to install the Pandas package. So, if you have not installed it, type the following command.

python3 -m pip install pandas

My default version is Python 3.8. So, in the above command, I am using python3.

python --version
Python 3.8.2

Now, create a programming file called app.py and import the packages.

import pymysql

import pandas as pd

Step 3: Connect Python to MySQL with connect()

The pymysql.connect() function takes the following four parameters.

  1. Hostname
  2. Username
  3. Password
  4. Database
import pymysql
import pandas as pd

dbcon = pymysql.connect("localhost", "root", "root", "laravel7crud")

Step 4: Reading the SQL query

The pd.read_sql_query() function takes the parameter’s SQL Query and connection object. In our case, it is a SQL SELECT Statement and dbcon object.

Optionally provide an index_col parameter to use one of the columns as the index; otherwise, the default integer index will be used.

import pymysql
import pandas as pd

dbcon = pymysql.connect("localhost", "root", "root", "laravel7crud")

try:
  SQL_Query = pd.read_sql_query(
     '''select
     symptoms,
     country_name,
     cases
 from coronas''', dbcon)

Step 5: Convert the variable into DataFrame using pd.DataFrame()

df = pd.DataFrame(SQL_Query, columns=['symptoms', 'country_name', 'cases'])

print(df)
print(type(df))

Here is the complete code:

import pymysql
import pandas as pd

dbcon = pymysql.connect("localhost", "root", "root", "laravel7crud")

try:
  SQL_Query = pd.read_sql_query(
  '''select
   symptoms,
   country_name,
   cases
 from coronas''', dbcon)

 df = pd.DataFrame(SQL_Query, columns=['symptoms', 'country_name', 'cases'])
 print(df)
 print('The data type of df is: ', type(df))
except:
  print("Error: unable to convert the data")

dbcon.close()

Output

   symptoms  country_name   cases
0  Fever       India        29000
1  Cough       USA         3000000

The data type of df is: <class 'pandas.core.frame.DataFrame'>

That’s it!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.