IV Semester BCA Python programming

Unit 4 - 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.
SQLite Methods
Following are important sqlite3 module methods, which can suffice your requirement to work
with SQLite database from your Python program.

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 = [Link]('[Link]') name ‘[Link]’.

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 = [Link]('[Link]')
cursorObj = [Link]()

Now we can use the cursor object to call the execute() method to execute any SQL queries.

3. 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 [Link](...).
Eg:
import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
[Link](''' CREATE TABLE movie(title text, year int, score real) ''' )

1|Page
 IV Semester BCA Python programming

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. 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 = [Link]('[Link]')
#program statements
[Link]()

SQLite database operations

I. 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 = [Link]('[Link]') Output:
print ("Opened database successfully") Opened database successfully

II. 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 = [Link]('[Link]')
cursorObj = [Link]()
[Link]('''CREATE TABLE movie(title text, year int, score real)''')
[Link]()
[Link]()

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.

2|Page
 IV Semester BCA Python programming

To check if our table is created, you can use the DB browser for SQLite
([Link] to view your table. Open your [Link] file with the
program, and you should see your table:

III. Insert in Table

To insert data in a table, we use the INSERT INTO statement. Consider the following line of
code:

import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
[Link]('''CREATE TABLE movie(title text, year int, score real)''')
[Link]('''INSERT INTO movie VALUES ("Titanic",1997, 9.5)''')
[Link]()
[Link]()

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:

3|Page
 IV Semester BCA Python programming

import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
m=input("Movie Name :")
y=int(input("Year :"))
s=float(input("Score :"))
[Link]('''INSERT INTO MOVIE VALUES (?,?,?) ''',(m,y,s))
[Link]()
[Link]()

User Input:
Movie Name :Dil
Year :1990
Score :9.2

IV. 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 = [Link]('[Link]')
cursorObj = [Link]()
[Link]("UPDATE MOVIE SET SCORE=10 WHERE TITLE='Dil' ")
[Link]()
[Link]()

This will change the score for the movie Dil:

4|Page
 IV Semester BCA Python programming

V. Select statement

You can use the select statement to select data from a particular table. If you want to select all
the columns of the data from a table, you can use the asterisk (*). The syntax for this will be as
follows:

select * from table_name

In SQLite3, the SELECT statement is executed in the execute method of the cursor object. For
example, select all the columns of the movie table, run the following code:

[Link]("SELECT * FROM movie ")

If you want to select a few columns from a table, then specify the columns like the following:
select column1, column2 from tables_name

For example,
[Link]( "SELECT title, year FROM movie")

The select statement selects the required data from the database table, and if you want to fetch
the selected data, the fetchall() method of the cursor object is used. We will demonstrate this
in the next section.

Fetch all data

To fetch the data from a database, we will execute the SELECT statement and then will use
the fetchall() method of the cursor object to store the values into a variable. After that, we will
loop through the variable and print all values.

The code will be like this:

5|Page
 IV Semester BCA Python programming

import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
[Link]("SELECT * FROM movie ")
records=[Link]()
for row in records:
print(row)
[Link]()
[Link]()

The above code will print out the records in our database as follows:

Output:
('Titanic', 1997, 9.5)
('KGF', 2020, 9.5)
('Dil', 1990, 10.0)

You can also use the fetchall() in one line as follows:

[print(row) for row in [Link]()]

import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
[Link]("SELECT * FROM movie ")
[print(row) for row in [Link]()]
[Link]()
[Link]()

If you want to fetch specific data from the database, you can use the WHERE clause.

VI. Delete

In SQLite database we use the following syntax to delete data from a table:

DELETE FROM table_name [WHERE Clause]

• Import the required module.
• Create a connection object with the database using to connect().
• Create a Cursor object by calling the cursor().
• Finally, use execute() method by passing a DELETE statement as a parameter to it.

import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
#Deleting
[Link]("Delete from movie where title='Titanic' ")
[Link]()
[Link]()

6|Page
 IV Semester BCA Python programming

VII. Drop table

You can drop/delete a table using the DROP statement. The syntax of the DROP statement is
as follows:
drop table table_name

To drop a table, the table should exist in the database. Therefore, it is recommended to use “if
exists” with the drop statement as follows:

drop table if exists table_name

For example,

import sqlite3
con = [Link]('[Link]')
cursorObj = [Link]()
[Link](" DROP TABLE IF EXISTS MOVIE ")
[Link]()
[Link]()

#Program to demonstrate a database operations- create, insert, select, update, delete

and drop
#importing the module
import sqlite3

#create connection object

con = [Link]('[Link]')

#crete a cursor
cursorObj = [Link]()

#creating the table

[Link]('''CREATE TABLE movie(title text, year int, score real)''')

#inserting the data

[Link]('''INSERT INTO movie VALUES ("Titanic",1997, 9.5)''')
[Link]('''INSERT INTO movie VALUES ("KGF",2020, 9.1)''')
[Link]('''INSERT INTO movie VALUES ("Dil",1990, 8.5)''')

#Print the Initial Data

print("Initial Data...")
[Link]("SELECT * FROM movie ")
[print(row) for row in [Link]()]

#Updating
[Link]("UPDATE MOVIE SET SCORE=10 WHERE TITLE='Dil' ")

#Print the after updating

print("After updating...")
[Link]("SELECT * FROM movie ")
[print(row) for row in [Link]()]

7|Page
 IV Semester BCA Python programming

#Deleting
[Link]("Delete from movie where title='Titanic' ")

#Print the after Deleting

print("After deleting...")
[Link]("SELECT * FROM movie ")
[print(row) for row in [Link]()]

#Drop the table

[Link](" DROP TABLE IF EXISTS MOVIE ")

#commit changes in the database

[Link]()

#close the connection

[Link]()

Output
Initial Data...
('Titanic', 1997, 9.5)
('KGF', 2020, 9.1)
('Dil', 1990, 8.5)

After updating...
('Titanic', 1997, 9.5)
('KGF', 2020, 9.1)
('Dil', 1990, 10.0)

After deleting...
('KGF', 2020, 9.1)
('Dil', 1990, 10.0)

8|Page
 IV Semester BCA Python programming

Unit 3 - Object Oriented Programming

Multilevel Inheritance

In Python, we can also derive a class from the derived class. This form of inheritance is known
as multilevel inheritance.

Here's the syntax of the multilevel inheritance,

class SuperClass:
# Super class code here

class DerivedClass1(SuperClass):
# Derived class 1 code here

class DerivedClass2(DerivedClass1):
# Derived class 2 code here

Here, the DerivedClass1 class is derived from the SuperClass class, and the DerivedClass2
class is derived from the DerivedClass1 class.
#Program to demonstrate multilevel inheritance
class Manager:
def final_review(self):
print("Final Review")

class Reviewer(Manager):
def review(self):
print("Reviewing...")

class Writer(Reviewer):
def writes(self):
print("Writes the code")

obj = Writer()
obj.final_review()
[Link]()
[Link]()

Output:
Final Review
Reviewing...
Writes the code

#Program to demonstrate super() function in multilevel inheritance

class Parent:
def __init__(self):
print('Parent - Hii')
def age(self, a):
print('Printing the age (Parent): ', a)

class Child(Parent):
def __init__(self):
print('Child - Hii')

9|Page
 IV Semester BCA Python programming

super().__init__()
def age(self, a):
print('Printing the age(Child): ', a)
super().age(a +30)

class GrandChild(Child):
def __init__(self):
print('Grand Child - Hii')
super().__init__()
def age(self, a):
print('Printing the age(Grand Child): ', a)
super().age(a + 25)

# Main function
if __name__ == '__main__':
obj = GrandChild()
[Link](10)

Output
Grand Child - Hii
Child - Hii
Parent - Hii
Printing the age(Grand Child): 10
Printing the age(Child): 35
Printing the age (Parent): 65

Multipath Inheritance
When a class is derived from two or more classes which are derived from the same base
class then such type of inheritance is called multipath inheritance.

Here class 'D' derived from classes 'B' and 'C', which are
derived from same base class 'A'.

Here's the syntax of the multipath inheritance,

class ClassA:
# Super class code here

class ClassB(ClassA):
# Derived class B code here

class ClassC(ClassA):
# Derived class C code here

class ClassD(ClassB, ClassC):

# Derived class D code here

10 | P a g e
 IV Semester BCA Python programming

#Program to demonstrate Multipath Inheritance

class University:
def __init__(self):
print("Constructor of the Base class")
def display(self):
print(f"The University Class display method")

class Course(University):
def __init__(self):
print("Constructor of the Child Class 1 of Class University")
super().__init__()
def display(self):
print(f"The Course Class display method")
super().display()

class Branch(University):
def __init__(self):
print("Constructor of the Child Class 2 of Class University")
super().__init__()
def display(self):
print(f"The Branch Class display method ")
super().display()

class Student(Course, Branch):

def __init__(self):
print("Constructor of Child class of Course and Branch is called")
super().__init__()
def display(self):
print(f"The Student Class display method")
super().display()

# Object Instantiation:
ob = Student() # Object named ob of the class Student.
print()
[Link]() # Calling the display method of Student class.

Output:
Constructor of Child class of Course and Branch is called
Constructor of the Child Class 1 of Class University
Constructor of the Child Class 2 of Class University
Constructor of the Base class

The Student Class display method

The Course Class display method
The Branch Class display method
The University Class display method

11 | P a g e