Python - Panda - SQL Home : www.sharetechnote.com
Panda - SQL
Panda DataFrame itself provide many powerful tools for data manipulation and simple to use, but depending on the skillset which you are more familiar with, you might have thought 'it would be good if I can use sql to the panda table'. This page is to show you how to convert the panda table (DataFrame) to sql database and manipulate the data using SQL.
Installation of SQL engine
First, you need to install a sql engine and that can be installed as followed. (At the time of writing this note (Jul 2020), I was using Python 3.7.5 and the installed sqlalchemy version is shown below).
C:\>pip install sqlalchemy
Downloading SQLAlchemy-1.3.18-cp37-cp37m-win_amd64.whl (1.2 MB)
|¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 1.2 MB 1.6 MB/s
Installing collected packages: sqlalchemy
Successfully installed sqlalchemy-1.3.18
Connection to SQL engine
Following is a short example that connect to an sql engine and convert a panda table to the sql table, retrieve data from the table.
import pandas as pd
from sqlalchemy import create_engine
df = pd.read_csv('Sacramentorealestatetransactions.csv')
sqlEngine = create_engine('sqlite://', echo=False)
df.to_sql('TransactionDB', con=sqlEngine, if_exists='replace')
sqlList = sqlEngine.execute("SELECT * FROM TransactionDB").fetchall();
In this example, I am creating a panda table by reading an existing csv file by pd.read_csv('Sacramentorealestatetransactions.csv'). You can get this csv file as described in this page. For the simplicity, you can create your own / simple table by yourself if you like.
sqlEngine = create_engine('sqlite://', echo=False) : this create an SQL engine and name it as 'sqlEngine'. From follow on, you will get access to the SQL engine using this name.
df.to_sql('TransactionDB', con=sqlEngine, if_exists='replace') : this line mean 'convert the panda table named 'df' into an SQL table named 'TransactionDB'. if_exists='replace' mean 'if there is already a table named 'TransactionDB', replace the table with this new data.
sqlList = sqlEngine.execute("SELECT * FROM TransactionDB").fetchall() : this line mean 'execute the specified SQL query and save it to the variable 'sqlList'. As you may guess here, the queried SQL result is returned as a list.
Once the above code runs without any error, I would suggest a few basic checkups to see everything is done as intended.
First check if the csv file is successfully and the data is stored to the variable 'df'.
street city zip ... price latitude longitude
0 3526 HIGH ST SACRAMENTO 95838 ... 59222 38.631913 -121.434879
1 51 OMAHA CT SACRAMENTO 95823 ... 68212 38.478902 -121.431028
2 2796 BRANCH ST SACRAMENTO 95815 ... 68880 38.618305 -121.443839
3 2805 JANETTE WAY SACRAMENTO 95815 ... 69307 38.616835 -121.439146
4 6001 MCMAHON DR SACRAMENTO 95824 ... 81900 38.519470 -121.435768
[5 rows x 12 columns]
Now check if the sql command is properly executed by sqlList = sqlEngine.execute("SELECT * FROM TransactionDB").fetchall();
Squeezed Text(1832 lines)
As shown here, the data would not be printed in case the size of the data is too much. You can expand the data or just print out one element just to make it sure it worked as shown below.
(0, '3526 HIGH ST', 'SACRAMENTO', 95838, 'CA', 2, 1, 836, 'Residential', 'Wed May 21 00:00:00 EDT 2008', 59222, 38.631913, -121.43487900000001)
If you like, you can convert the SQL result (a List) back to panda DataFrame and process it further using Panda DataFrame functionality explained in this page.
>>> sqldf = pd.DataFrame(sqlList)
0 1 2 ... 10 11 12
0 0 3526 HIGH ST SACRAMENTO ... 59222 38.631913 -121.434879
1 1 51 OMAHA CT SACRAMENTO ... 68212 38.478902 -121.431028
2 2 2796 BRANCH ST SACRAMENTO ... 68880 38.618305 -121.443839
3 3 2805 JANETTE WAY SACRAMENTO ... 69307 38.616835 -121.439146
4 4 6001 MCMAHON DR SACRAMENTO ... 81900 38.519470 -121.435768
[5 rows x 13 columns]