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:
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.
- Hostname
- Username
- Password
- 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!
Krunal Lathiya is a seasoned Computer Science expert with over eight years in the tech industry. He boasts deep knowledge in Data Science and Machine Learning. Versed in Python, JavaScript, PHP, R, and Golang. Skilled in frameworks like Angular and React and platforms such as Node.js. His expertise spans both front-end and back-end development. His proficiency in the Python language stands as a testament to his versatility and commitment to the craft.