UNIT - IV
Python SQLite & Data Analysis
4.1. Python SQLite
4.1.1. The SQLite3 module
4.1.2. SQLite Methods
4.1.2.1. connect,
4.1.2.2. cursor,
4.1.2.3. execute,
4.1.2.4. close
4.2. Connect to Database:
4.2.1. Create Table;
4.2.2. Operations on Tables:
4.2.3. Insert
4.2.4. Select
4.2.5. Update
4.2.6. Delete
4.2.7. Drop Records
4.3. Data Analysis : Numpy
4.3.1. Introduction to NumPy
4.3.2. Array Creation using NumPy
4.3.3. Operations on Arrays
4.4. Data Analysis : Pandas
4.4.1. Introduction to Pandas,
4.4.2. Data Structure of Pandas
4.4.2.1. Series
4.4.2.2. DataFrames.
4.4.3. Operations on DataFrames.
4.4.4. .csv file
4.4.5. Creating DataFrames from Excel Sheet
4.5. Data Visualization:
4.5.1. Introduction to Data Visualisation
4.5.2. Matplotlib Library;
4.5.3. Different Types of Charts
4.5.4. using Pyplot
4.5.4.1. Line chart,
4.5.4.2. Bar chart
4.5.4.3. Histogram and Pie chart
4.1 Python SQLite
Python SQLite3 module is used to integrate the SQLite database with Python. It provides a
straightforward and simple-to-use interface for interacting with SQLite databases.
Installation
SQLite3 can be integrated with Python using sqlite3 module and it provides an SQL
interface. You do not need to install this module separately because it is shipped by default
along with Python version 2.5.x onwards.
To use sqlite3 module, you must first create a connection object that represents the
database and then create a cursor object, which will help you in executing all the SQL
statements.
4.1.1 SQLite Methods
Following are important sqlite3 module methods, which can suffice your requirement to work
with SQLite database from your Python program.
4.1.2.1 connect()
To use SQLite3 in Python, first we have to import the sqlite3 module and then create a
connection object. Connection object allows to connect to the database and will let us execute
the SQL statements.
Creating Connection object using the connect() function:
import sqlite3 This will create a new file with the
con = sqlite3.connect('mydatabase.db') name ‘mydatabase.db’.
4.1.2.2 cursor()
To execute SQLite statements in Python, we need a cursor object. We can create it using
the cursor() method.
The SQLite3 cursor is a method of the connection object. To execute the SQLite3 statements,
you should establish a connection at first and then create an object of the cursor using the
connection object as follows:
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
Now we can use the cursor object to call the execute() method to execute any SQL queries.
4.1.2.2 execute()
Once the database and connection object is created, we can create a table using CREATE
TABLE statement. Then we execute the CREATE TABLE statement by calling cur.execute(...).
Eg:
import sqlite3
con = sqlite3.connect('mydatabase.db') cursorObj =
con.cursor()
cursorObj.execute(''' CREATE TABLE movie(title text, year int, score real) ''' )
Here, ‘movie’ is the table name with columns title, year and score. Since SQLite is flexible, we
can just use column names in the table declaration, specifying the data types is optional.
4.1.2.3 Close()
Once we are done with our database, it is a good practice to close the connection. We can close the
connection by using the close() method.
To close a connection, use the connection object and call the close() method as follows:
con = sqlite3.connect('mydatabase.db') #program
statements
con.close()
4.2 SQLite database operations
4.2.1 Creating and connecting to Database
When you create a connection with SQLite, that will create a database file automatically if it
doesn’t already exist. This database file is created on disk with the connect function.
Following Python code shows how to connect to an existing database. If the database does
not exist, then it will be created and finally a database object will be returned.
import sqlite3
conn = sqlite3.connect('test.db') Output:
print ("Opened database successfully") Opened database successfully
4.2.2 Create Table
To create a table in SQLite3, you can use the Create Table query in the execute() method.
Consider the following steps:
1. Create a connection object.
2. From the connection object, create a cursor object.
3. Using the cursor object, call the execute method with create table query as the
parameter.
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('''CREATE TABLE movie(title text, year int, score real)''')
con.commit()
con.close()
In the above code, it establishes a connection and creates a cursor object to execute the create
table statement.
The commit() method saves all the changes we make.
To check if our table is created, you can use the DB browser for SQLite
(https://sqlitebrowser.org/dl/) to view your table. Open your mydatabase.db file with the
program, and you should see your table:
4.2.3 Insert in Table
To insert data in a table, we use the INSERT INTO statement. Consider the following line of
code:
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('''CREATE TABLE movie(title text, year int, score real)''')
cursorObj.execute('''INSERT INTO movie VALUES ("Titanic",1997, 9.5)''')
con.commit()
con.close()
We can also pass values to an INSERT statement in the execute() method. You can use the
question mark (?) as a placeholder for each value. The syntax of the INSERT will be like the
following:
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
m=input("Movie Name :")
y=int(input("Year :"))
s=float(input("Score :"))
cursorObj.execute('''INSERT INTO MOVIE VALUES (?,?,?) ''',(m,y,s))
con.commit()
con.close()
User Input:
Movie Name :Dil
Year :1990
Score :9.2
4.2.4 Update Table
To update the table, simply create a connection, then create a cursor object using the
connection and finally use the UPDATE statement in the execute() method.
Suppose that we want to update the score with the movie title Dil. For updating, we will
use the UPDATE statement and for the movie whose title equals Dil. We will use the WHERE
clause as a condition to select this employee.
Consider the following code:
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute("UPDATE MOVIE SET SCORE=10 WHERE TITLE='Dil' ")
con.commit()
con.close()
This will change the score for the movie Dil:
