An Introduction to relational databases: SQL
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.
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.
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/[Link]')
# 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
Statements for data manipulation
SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate
server process and allows accessing the database using a nonstandard variant of the SQL query
language. Some applications can use SQLite for internal data storage. It’s also possible to prototype
an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.
Connecting to SQLite Database
To use SQLite, we must import sqlite3.
import sqlite3
Then create a connection using connect() method and pass the name of the database you want
to access if there is a file with that name, it will open that file. Otherwise, Python will create a
file with the given name.
sqliteConnection = [Link]('[Link]')
After this, a cursor object is called to be capable to send commands to the SQL.
cursor = [Link]()
Example: Connecting to SQLite3 database using Python
import sqlite3
# connecting to the database
connection = [Link]("[Link]")
# cursor
crsr = [Link]()
# print statement will execute if there
# are no errors
print("Connected to the database")
# close the connection
[Link]()
Example: Creating SQLite3 tables using Python
In this example, we will create the SQLite3 tables using Python. The standard SQL command will be
used for creating the tables.
import sqlite3
# connecting to the database
connection = [Link]("[Link]")
# cursor
crsr = [Link]()
# SQL command to create a table in the database
sql_command = """CREATE TABLE emp (
staff_number INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(30),
gender CHAR(1),
joining DATE);"""
# execute the statement
[Link](sql_command)
# close the connection
[Link]()
Example 1: Inserting Data into SQLite3 table using Python
import sqlite3
# connecting to the database
connection = [Link]("[Link]")
# cursor
crsr = [Link]()
# SQL command to insert the data in the table
sql_command = """INSERT INTO emp VALUES (23, "Rishabh",\
"Bansal", "M", "2014-03-28");"""
[Link](sql_command)
[Link]()
# close the connection
[Link]()
Example: Reading Data from sqlite3 table using Python
# importing the module
import sqlite3
# connect with the myTable database
connection = [Link]("[Link]")
# cursor object
crsr = [Link]()
# execute the command to fetch all the data from the table emp
[Link]("SELECT * FROM emp")
# store all the fetched data in the ans variable
ans = [Link]()
for i in ans:
print(i)
Example: Updating SQLite3 table using Python
# Import module
import sqlite3
# Connecting to sqlite
conn = [Link]('[Link]')
# Creating a cursor object using
# the cursor() method
cursor = [Link]()
# Updating
[Link]('''UPDATE emp SET lname = "Jyoti" WHERE fname="Rishabh";''')
# Commit your changes in the database
[Link]()
# Closing the connection
[Link]()
Example: Deleting from SQLite3 table using Python
# Import module
import sqlite3
# Connecting to sqlite
conn = [Link]('[Link]')
# Creating a cursor object using
# the cursor() method
cursor = [Link]()
# Updating
[Link]('''DELETE FROM emp WHERE fname="Rishabh";''')
# Commit your changes in the database
[Link]()
# Closing the connection
[Link]()
Using SQLite Manager to work with a database
The sqlite3 module provides a straightforward interface for interacting with SQLite
databases. A connection object is created using [Link](); the connection must be
closed at the end of the session with the .close() command. While the connection is open,
any interactions with the database require you to make a cursor object with the .cursor()
command. The cursor is then ready to perform all kinds of operations with .execute().
import sqlite3
# Create a SQL connection to our SQLite database
con = [Link]("data/portal_mammals.sqlite")
cur = [Link]()
# The result of a "[Link]" can be iterated over by row
for row in [Link]('SELECT * FROM species;'):
print(row)
# Be sure to close the connection
[Link]()
Queries
One of the most common ways to interact with a database is by querying: retrieving data
based on some search parameters. Use a SELECT statement string. The query is returned as a
single tuple or a tuple of tuples. Add a WHERE statement to filter your results based on some
parameter.
import sqlite3
# Create a SQL connection to our SQLite database
con = [Link]("data/portal_mammals.sqlite")
cur = [Link]()
# Return all results of query
[Link]('SELECT plot_id FROM plots WHERE plot_type="Control"')
[Link]()
# Return first result of query
[Link]('SELECT species FROM species WHERE taxa="Bird"')
[Link]()
# Be sure to close the connection
[Link]()
Using Python to work with a database
Python MySQL Connector is a Python driver that helps to integrate Python and MySQL. This Python
MySQL library allows the conversion between Python and MySQL data types. MySQL Connector
API is implemented using pure Python and does not require any third-party library.
While working with Python we need to work with databases, they may be of different types like
MySQL, SQLite, NoSQL, etc. In this article, we will be looking forward to how to connect MySQL
databases using MySQL Connector/Python.
MySQL Connector module of Python is used to connect MySQL databases with the Python programs,
it does that using the Python Database API Specification v2.0 (PEP 249). It uses the Python standard
library and has no dependencies.
Connecting to the Database
In the following example we will be connecting to MySQL database using connect()
Example:
# Python program to connect
# to mysql database
import [Link]
# Connecting from the server
conn = [Link](user = 'username',
host = 'localhost',
database = 'database_name')
print(conn)
# Disconnecting from the server
[Link]()
Example:
Return a list of your system's databases:
import [Link]
mydb = [Link](
host="localhost",
user="myusername",
password="mypassword"
)
mycursor = [Link]()
[Link]("SHOW DATABASES")
for x in mycursor:
print(x)
Creating a GUI that handles an event
Python offers multiple options for developing GUI (Graphical User Interface). Out of all the GUI
methods, tkinter is the most commonly used method. It is a standard Python interface to the Tk GUI
toolkit shipped with Python. Python with tkinter is the fastest and easiest way to create the GUI
applications. Creating a GUI using tkinter is an easy task.
An empty Tkinter top-level window can be created by using the following steps.
1. import the Tkinter module.
2. Create the main application window.
3. Add the widgets like labels, buttons, frames, etc. to the window.
4. Call the main event loop so that the actions can take place on the user's computer
screen.
There are two main methods used which the user needs to remember while creating the
Python application with GUI.
1. Tk(screenName=None, baseName=None, className=’Tk’, useTk=1): To create
a main window, tkinter offers a method ‘Tk(screenName=None, baseName=None,
className=’Tk’, useTk=1)’. To change the name of the window, you can change the
className to the desired one. The basic code used to create the main window of the
application is:
m=[Link]() where m is the name of the main window object
2. mainloop(): There is a method known by the name mainloop() is used when your
application is ready to run. mainloop() is an infinite loop used to run the application,
wait for an event to occur and process the event as long as the window is not closed.
[Link]()
import tkinter
m = [Link]()
'''
widgets are added here
'''
[Link]()
EXAMPLE
1 . Button:To add a button in your application, this widget is used.
The general syntax is:
w=Button(master, option=value)
master is the parameter used to represent the parent window.
There are number of options which are used to change the format of the Buttons. Number of
options can be passed as parameters separated by commas. Some of them are listed below.
activebackground: to set the background color when button is under the cursor.
activeforeground: to set the foreground color when button is under the cursor.
bg: to set the normal background color.
command: to call a function.
font: to set the font on the button label.
image: to set the image on the button.
width: to set the width of the button.
height: to set the height of the button.
import tkinter as tk
r = [Link]()
[Link]('Counting Seconds')
button = [Link](r, text='Stop', width=25,
command=[Link])
[Link]()
[Link]()
[Link]: It is used to draw pictures and other complex layout like graphics, text and
widgets.
The general syntax is:
w = Canvas(master, option=value)
master is the parameter used to represent the parent window.
There are number of options which are used to change the format of the widget. Number of
options can be passed as parameters separated by commas. Some of them are listed below.
bd: to set the border width in pixels.
bg: to set the normal background color.
cursor: to set the cursor used in the canvas.
highlightcolor: to set the color shown in the focus highlight.
width: to set the width of the widget.
height: to set the height of the widget.
from tkinter import *
master = Tk()
w = Canvas(master, width=40, height=60)
[Link]()
canvas_height=20
canvas_width=200
y = int(canvas_height / 2)
w.create_line(0, y, canvas_width, y )
mainloop()