Data Management With Python, SQLite, and SQLAlchemy

In this article, We will be creating a table named Movies and do CRUD operations on it using Python.

What is Data Management?

Data management is the practice of collecting, keeping, and using data securely, efficiently, and cost-effectively.

• We can manage the data through OOPS(classes) also. But, why database? the only difference is the data in the database world is stored in the hard disk persistently(permanently) whereas in classes the data is stored but in the instance(variables) which are stored in RAM(memory) temporarily.

What is SQLite?

SQLite is a relational database management system contained in a C library. In contrast to many other database management systems, SQLite is not a client-server database engine. Rather, it is embedded into the end program. SQLite generally follows PostgreSQL syntax.

What is SQLAlchemy?

SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the time, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.

Let’s start, we require Python software, Flask_SQLAlchemy library, and DB Browser for SQLite(GUI tool for seeing the table(database)).

• Since python is already installed we will be installing DB browser and Flask-SQLAlchemy using pip:

pip install Flask-SQLAlchemy

In Class, we create a template we never give values there…similarly while creating a table we give the column names. The class name is the Table name.

Creating an Object(instantiate) of the class is similar to creating a record of the table.

class name =table name, class attributes = table column names, objects=records this relation between the class and the table is known as Object Relational Mapper(ORM).

We just need to use classes and functions all the database things will be taken care of and will be abstracted by SQLAlchemy. We as programmer don’t have to know SQL, all the things will be taken care by SQLAlchemy(ORM Library).

We will also be using Flask(It is an API of Python that allows us to build up web applications)

from flask import Flask
from flask_sqlalchemy import SQLAlchemy 
app=Flask(“dbapp”) 
app.config[‘SQLALCHEMY_DATABASE_URI’]=’sqlite:///mydb/data.sqlite’ 

db = SQLAlchemy(app)
print(db)

SQLite is embedded in SQLAlchemy hence we don’t have to install SQLite separately.

All the mapping part is done. Now we will create a class(table)

class Movies():

But to tell python that this is not the python class this is the database table we need to inherit db.Model method to map the class with the database. Model is nothing but the table(table is also known as Model).

class Movie(db.Model):

Now, this class has the capability to connect with the database and create a model(table) there.

• Creating column names

class Movies(db.Model):
    mid=db.Column(db.Integer, primary_key=True)
    moviename = db.Column( db.Text )
    actor = db.Column( db.Text )
    actress = db.Column( db.Text )
    yearofrelease = db.Column( db.Integer )
    director  = db.Column( db.Text )
    def __init__(self, moviename, actor, actress, yearofrelease, director):
        self.moviename=moviename
        self.actor=actor
        self.actress=actress
        self.yearofrelease=yearofrelease
        self.director=director
db.create_all() # will create a sqlite file

Now the template(schema) is created.

• A primary key is the column or columns that contain values that uniquely identify each row in a table.

CRUD Operations:

1. Create:

#******  1.Create  ******
def addrecord(mname,actor,actress,yor,director):
    obj=Movies(mname,actor,actress,yor,director)
    db.session.add(obj)
    db.session.commit()
addrecord("Shershaah","Sidhart Malhotra","Kiara Advani",2021,"Vishnu Vardhan")
addrecord("Gunjan Saxsena","Pankaj Tripati","Janhvi Kapoor",2020,"Sharan Sharma")
addrecord("Bhuj","Ajay Devgan","Pranita Subhash",2021,"Abhishek Dundhaiya")
addrecord("Lage Raho Munna Bhai","Sanjay Dutt","Vidya balan",2006,"Rajkumar Hirani")
addrecord("Muna Bhai MBBS","Sanjay Dutt","Gracy Singh",2003,"Rajkumar Hirani")
addrecord("Bajrangi Bhaijan","Salman Khan","Kareena Kapooor",2015,"Kabir Khan")
addrecord("Kesari","Akshay Kumar","Parneeti Chopra",2019,"Anurag Singh")
addrecord("Parmanu","John Abraham","Diana Penty",2018,"Abhishek Sharma")
addrecord("Angrezi Medium","Irfan khan","Radhika Madan",2020,"Homi Adjania")

Now the Table with (9 records is created. we can view this table from “DB Browser for SQLite” GUI Tool

2. Read:

#******  2.Read  ******
r2c=Movies.query.filter_by(director="Rajkumar Hirani").count()
print(f"There are {r2} records with director as Rajkumar Hirani")

Output:

There are 2 records with director as Rajkumar Hirani

#query all
rall=Movies.query.all()
for i in range(len(rall)):
    print(rall[i].moviename)

Output:

Shershaah

Gunjan Saxsena

Bhuj

Lage Raho Munna Bhai

Muna Bhai MBBS

Bajrangi Bhaijan

Kesari

Parmanu

Angrezi Medium

#query on parameter
ryor=Movies.query.filter_by(yearofrelease=2021)
for i in range(len(ryor.all())):
    print(ryor.all()[i].moviename, ryor.all()[i].actor, ryor.all()[i].yearofrelease )

Output:

Shershaah Sidhart Malhotra 2021

Bhuj Ajay Devgan 2021

3. Update:

#******  3. Update  ******
rsher=Movies.query.filter_by(moviename="Shershaah")
print(rsher.all())
for i in rsher.all():
    i.director="directorchanged"
    print(f"Director of {i} changed")
    db.session.commit()

output:

# adding another record
addrecord("Dangal","Amir Khan","Fatima Sana Shaikh",2016,"Nitesh Tiwari")
db.session.commit()
print("New record added!!")

output:

4. Delete:

#******  4. Delete  ******
#deleting director "Rajkumar Hirani" films
rdf=Movies.query.filter_by(director="Rajkumar Hirani")
for i in rdf.all():
    print("Deleting Movie : ", rdf.all()[0])
    print(i)
    db.session.delete(rdf.all()[0])
    db.session.commit()

output:

fig-d1: pic of the table where Rajkumar Hirani director’s movies are deleted…. and Kesari movie record is also deleted

#deleting kesari record
rkes=Movies.query.filter_by(moviename="Kesari")
print(rkes.all())
db.session.delete(rkes.all()[0])
db.session.commit()
print(f"Movie {rkes.all()} deleted") #ouput in above fig-d1

• deleting all records

#deleting all records
rall=Movies.query.all()
print(rall)
for i in range((len(rall))):
    print("Deleting Movie")
    db.session.delete(rall[i])
    db.session.commit()

output:

all records deleted

Thank you! keep learning! keep growing! keep sharing!

If you enjoyed this give it a clap, follow me on Medium for more
Let’s connect on LinkedIn