World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? How to leave/exit/deactivate a Python virtualenvironment What is the process of executing a Python script within Power Automate? Change the hyperparameter means ? Why is my f1_scores different when i calculate them manually vs output by sklearn.metrics How can a sentence be matched to a cluster of keywords? How Python interpreter Works? Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Why You Should Learn Data Science in 2023?
Jun 8th (7:00 PM) 289 Registered
Python - Relational Databases
Neha Kumawat
a year ago
Table of Contents
- Introduction
- Installing SQLAlchemy
- Reading Relational Tables
- Inserting Data to Relational Tables
- Deleting Data from Relational Tables
Introduction
We can connect to relational databases for analysing data using the pandas library as well as another additional library for implementing database connectivity.
This package is named as sqlalchemy which provides full SQL language functionality to be used in python.
Installing SQLAlchemy
The installation is very straight forward using Anaconda which we have discussed in the chapter Data Science Environment. Assuming you have installed Anaconda as described in this chapter,
run the following command in the Anaconda Prompt Window to install the SQLAlchemy package.
conda install sqlalchemy
Reading Relational Tables
We will use Sqlite3 as our relational database as it is very light weight and easy to use. Though the SQLAlchemy library can connect to a variety of relational sources including MySql, Oracle and Postgresql and Mssql.
We first create a database engine and then connect to the database engine using the to_sql function of the SQLAlchemy library.
In the below example we create the relational table by using the to_sql function from a dataframe already created by reading a csv file.
Then we use the read_sql_query function from pandas to execute and capture the results from various SQL queries.
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('/path/input.csv')
# Create the db engine
engine = create_engine('sqlite:///:memory:')
# Store the dataframe as a table
data.to_sql('data_table', engine)
# Query 1 on the relational table
res1 = pd.read_sql_query('SELECT * FROM data_table', engine)
print('Result 1')
print(res1)
print('')
# Query 2 on the relational table
res2 = pd.read_sql_query('SELECT dept,sum(salary) FROM data_table group by dept', engine)
print('Result 2')
print(res2)
When we execute the above code, it produces the following result.
Result 1
index id name salary start_date dept
0 0 1 Rick 623.30 2012-01-01 IT
1 1 2 Dan 515.20 2013-09-23 Operations
2 2 3 Tusar 611.00 2014-11-15 IT
3 3 4 Ryan 729.00 2014-05-11 HR
4 4 5 Gary 843.25 2015-03-27 Finance
5 5 6 Rasmi 578.00 2013-05-21 IT
6 6 7 Pranab 632.80 2013-07-30 Operations
7 7 8 Guru 722.50 2014-06-17 Finance
Result 2
dept sum(salary)
0 Finance 1565.75
1 HR 729.00
2 IT 1812.30
3 Operations 1148.00
Inserting Data to Relational Tables
We can also insert data into relational tables using sql.execute function available in pandas. In the below code we previous csv file as input data set, store it in a relational table and then
insert another record using sql.execute.
from sqlalchemy import create_engine
from pandas.io import sql
import pandas as pd
data = pd.read_csv('C:/Users/Rasmi/Documents/pydatasci/input.csv')
engine = create_engine('sqlite:///:memory:')
# Store the Data in a relational table
data.to_sql('data_table', engine)
# Insert another row
sql.execute('INSERT INTO data_table VALUES(?,?,?,?,?,?)', engine, params=[('id',9,'Ruby',711.20,'2015-03-27','IT')])
# Read from the relational table
res = pd.read_sql_query('SELECT ID,Dept,Name,Salary,start_date FROM data_table', engine)
print(res)
When we execute the above code, it produces the following result.
id dept name salary start_date
0 1 IT Rick 623.30 2012-01-01
1 2 Operations Dan 515.20 2013-09-23
2 3 IT Tusar 611.00 2014-11-15
3 4 HR Ryan 729.00 2014-05-11
4 5 Finance Gary 843.25 2015-03-27
5 6 IT Rasmi 578.00 2013-05-21
6 7 Operations Pranab 632.80 2013-07-30
7 8 Finance Guru 722.50 2014-06-17
8 9 IT Ruby 711.20 2015-03-27
Deleting Data from Relational Tables
We can also delete data into relational tables using sql.execute function available in pandas. The below code deletes a row based on the input condition given.
from sqlalchemy import create_engine
from pandas.io import sql
import pandas as pd
data = pd.read_csv('C:/Users/Rasmi/Documents/pydatasci/input.csv')
engine = create_engine('sqlite:///:memory:')
data.to_sql('data_table', engine)
sql.execute('Delete from data_table where name = (?) ', engine, params=[('Gary')])
res = pd.read_sql_query('SELECT ID,Dept,Name,Salary,start_date FROM data_table', engine)
print(res)
When we execute the above code, it produces the following result.
id dept name salary start_date
0 1 IT Rick 623.3 2012-01-01
1 2 Operations Dan 515.2 2013-09-23
2 3 IT Tusar 611.0 2014-11-15
3 4 HR Ryan 729.0 2014-05-11
4 6 IT Rasmi 578.0 2013-05-21
5 7 Operations Pranab 632.8 2013-07-30
6 8 Finance Guru 722.5 2014-06-17
Liked what you read? Then don’t break the spree. Visit our insideAIML blog page to read more awesome articles.
Or if you are into videos, then we have an amazing Youtube channel as well. Visit our InsideAIML Youtube Page to learn all about Artificial Intelligence, Deep Learning, Data Science and Machine Learning.
Keep Learning. Keep Growing.