We can hook up with relational databases for analysing data using the pandas library also as another additional library for implementing database connectivity. This package is known as as sqlalchemy which provides full SQL language functionality to be utilized in python.

Installing SQLAlchemy

The installation is extremely simple using Anaconda which we’ve discussed within the chapter Data Science Environment. Assuming you’ve got installed Anaconda as described during this chapter, run the subsequent command within the Anaconda Prompt Window to put in the SQLAlchemy package.

conda install sqlalchemy

Reading Relational Tables

We will use Sqlite3 as our electronic database because it is extremely light weight and straightforward to use. Though the SQLAlchemy library can hook up with a spread of relational sources including MySql, Oracle and Postgresql and Mssql. We first create a database engine then hook up with 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 subsequent 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. within the below code we previous csv file as input file set, store it during a relational table 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 info during 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 subsequent 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 supported 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 subsequent 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

So, this brings us to the end of blog. This Tecklearn ‘Relational Databases in Python’ blog helps you with commonly asked questions if you are looking out for a job in Python Programming. If you wish to learn Python and build a career in Data Science domain, then check out our interactive, Python with Data Science Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/python-with-data-science/

Python with Data Science Training

About the Course

Python with Data Science training lets you master the concepts of the widely used and powerful programming language, Python. This Python Course will also help you master important Python programming concepts such as data operations, file operations, object-oriented programming and various Python libraries such as Pandas, NumPy, Matplotlib which are essential for Data Science. You will work on real-world projects in the domain of Python and apply it for various domains of Big Data, Data Science and Machine Learning.

Why Should you take Python with Data Science Training?

Python is the preferred language for new technologies such as Data Science and Machine Learning.
Average salary of Python Certified Developer is $123,656 per annum – Indeed.com
Python is by far the most popular language for data science. Python held 65.6% of the data science market.

What you will Learn in this Course?

Introduction to Python

Define Python
Understand the need for Programming
Know why to choose Python over other languages
Setup Python environment
Understand Various Python concepts – Variables, Data Types Operators, Conditional Statements and Loops
Illustrate String formatting
Understand Command Line Parameters and Flow control

Python Environment Setup and Essentials

Python installation
Windows, Mac & Linux distribution for Anaconda Python
Deploying Python IDE
Basic Python commands, data types, variables, keywords and more

Python language Basic Constructs

Looping in Python
Data Structures: List, Tuple, Dictionary, Set
First Python program
Write a Python Function (with and without parameters)
Create a member function and a variable
Tuple
Dictionary
Set and Frozen Set
Lambda function

OOP (Object Oriented Programming) in Python

Object-Oriented Concepts

Working with Modules, Handling Exceptions and File Handling

Standard Libraries
Modules Used in Python (OS, Sys, Date and Time etc.)
The Import statements
Module search path
Package installation ways
Errors and Exception Handling
Handling multiple exceptions

Introduction to NumPy

Introduction to arrays and matrices
Indexing of array, datatypes, broadcasting of array math
Standard deviation, Conditional probability
Correlation and covariance
NumPy Exercise Solution

Introduction to Pandas

Pandas for data analysis and machine learning
Pandas for data analysis and machine learning Continued
Time series analysis
Linear regression
Logistic Regression
ROC Curve
Neural Network Implementation
K Means Clustering Method

Data Visualisation

Matplotlib library
Grids, axes, plots
Markers, colours, fonts and styling
Types of plots – bar graphs, pie charts, histograms
Contour plots

Data Manipulation

Perform function manipulations on Data objects
Perform Concatenation, Merging and Joining on DataFrames
Iterate through DataFrames
Explore Datasets and extract insights from it

Scikit-Learn for Natural Language Processing

What is natural language processing, working with NLP on text data
Scikit-Learn for Natural Language Processing
The Scikit-Learn machine learning algorithms
Sentimental Analysis – Twitter

Introduction to Python for Hadoop

Deploying Python coding for MapReduce jobs on Hadoop framework.
Python for Apache Spark coding
Deploying Spark code with Python
Machine learning library of Spark MLlib
Deploying Spark MLlib for Classification, Clustering and Regression

Got a question for us? Please mention it in the comments section and we will get back to you.

659