Python 39

Models

What is a model? (in ORM)

A model is a python class that represents a table in the database 

 One model = one database table 

 One object = one row in that table 

Instead of directly connecting to the database and writing inline sql queries 

What is Sqlalchemy?

Student table structure 

Task1: without ORM 

Step1: Create isolated environment

PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> python -m venv ccitenv

Step2:Activate

PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> ccitenv/Scripts/activate 

(ccitenv) PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> 

Step3: Install mysql

(ccitenv) PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> pip install pymysql

Collecting pymysql

  Using cached pymysql-1.1.2-py3-none-any.whl.metadata (4.3 kB)

Using cached pymysql-1.1.2-py3-none-any.whl (45 kB)

Installing collected packages: pymysql

Successfully installed pymysql-1.1.2

[notice] A new release of pip is available: 25.2 -> 25.3

[notice] To update, run: python.exe -m pip install --upgrade pip

Step4: 

app.py

import pymysql

Connection =pymysql.connect(

    host='localhost',

    user='root',

    password='root',

    port=3306,

    database='ccitdb'

)

cursor=Connection.cursor()

cursor.execute("Insert into students (name, email,mobile) values ('John Doe', 'john@example.com', '1234567890')")

Connection.commit()

print(cursor.rowcount, "Data inserted successfully")

cursor.close()

Output: Mysql 1 records insert successfully in students table

(ccitenv) PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> python app.py

1 Data inserted successfully

Task2: With ORM 

Step1:

ORM(Object Relation mapping)

ORM is a technique that allow us to interact with a database using objects and classes instead of writing SQL queries.

Why ORM is needed 

Database understand SQL, but applications are written in object-oriented programming language like python or java 

•  Object-oriented language
•  Relational database 

Without ORM :Above example given 

With ORM : Not required write SQL queries, need to write model

• Models

What is a Model?(in ORM)

A Model is  a python class that represents a table in the database

One model = One database table 

One Object =One row in that table 

Database   ORM

Table        Model(class)

ROW       Object

COLUMN Class attribute

SQL          Python code 

CREATE TABLE `students` (

  `id` int NOT NULL AUTO_INCREMENT,

  `name` varchar(45) DEFAULT NULL,

  `email` varchar(45) DEFAULT NULL,

  `mobile` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`)

) 

Student model (ORM/Python):

Class student:

 Id = integer

name =string 

Email =string

mobile =string

Instead of directly connecting to the database and writing inline queries ,we can use models to communicate with the database .This make code simpler, cleaner and easier to maintain.

For that we can use SQL Alchemy library

• SQL Alchemy

What is SQL Alchemy?

SQLAlchemy is a python library used to communicate with relational database(MYSQL,PostgreSQL,SQLlite,Oracle) using Python code  instead of raw SQL.

It acts as a bridge between python and databases.

In one line: SQLAlchemy lets you work with  database using python objects instead of writing sql queries.

Why Do we need SQLAlchemy?

Database Understand SQL

Applications are written in Python

SQLAlchemy connects both

(it will not support Complex queries),We can write stored procedure and calls 

It helps you: 

 Avoid writing raw Sql

write clean, readable python code

Safely interact with database

SQLAlchemy -->ORM Logic 

Mysql--->Mysql Driver 

MYSQL-->Database engine

Task3: With ORM 

Step1:Install SQLAlchemy 

(ccitenv) PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> pip install sqlalchemy

We have two approach 

1.Code first approach:

 Though using Code ,you need create the database design

2.Database first approach:

First need create/Design the database, after  u can write python code 

app.py

from sqlalchemy import create_engine,Column, Integer, String

from sqlalchemy.orm import declarative_base

from urllib.parse import quote_plus

DB_ENGINE = "mysql+pymysql"

DB_HOST = "localhost"

DB_PORT = 3306

DB_NAME = "ccitdb"

DB_USER = "root"

DB_PASSWORD = "root"

DATABASE_URL = f"{DB_ENGINE}://{DB_USER}:{quote_plus(DB_PASSWORD)}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

print(DATABASE_URL)

engine = create_engine(DATABASE_URL, echo=True)

Base = declarative_base()

# model creation start here

class Students(Base):

    __tablename__ = "students"

    id = Column(Integer, primary_key=True , autoincrement=True)

    name = Column(String(100) , nullable=False)

    email = Column(String(100), unique=True , nullable=False)

    mobile = Column(String(15), unique=True , nullable=False)

Base.metadata.create_all(engine)

    

Output: Table created success 

(ccitenv) PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> py app.py

mysql+pymysql://root:root@localhost:3306/ccitdb

2026-01-26 03:23:26,923 INFO sqlalchemy.engine.Engine SELECT DATABASE()

2026-01-26 03:23:26,923 INFO sqlalchemy.engine.Engine [raw sql] {}

2026-01-26 03:23:26,925 INFO sqlalchemy.engine.Engine SELECT @@sql_mode

2026-01-26 03:23:26,925 INFO sqlalchemy.engine.Engine [raw sql] {}

2026-01-26 03:23:26,928 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names

2026-01-26 03:23:26,928 INFO sqlalchemy.engine.Engine [raw sql] {}

2026-01-26 03:23:26,931 INFO sqlalchemy.engine.Engine BEGIN (implicit)

2026-01-26 03:23:26,932 INFO sqlalchemy.engine.Engine DESCRIBE `ccitdb`.`students`

2026-01-26 03:23:26,932 INFO sqlalchemy.engine.Engine [raw sql] {}

2026-01-26 03:23:26,941 INFO sqlalchemy.engine.Engine 

CREATE TABLE students (

        id INTEGER NOT NULL AUTO_INCREMENT,

        name VARCHAR(100) NOT NULL,

        email VARCHAR(100) NOT NULL,

        mobile VARCHAR(15) NOT NULL,

        PRIMARY KEY (id),

        UNIQUE (email),

        UNIQUE (mobile)

)

2026-01-26 03:23:26,943 INFO sqlalchemy.engine.Engine [no key 0.00188s] {}

2026-01-26 03:23:27,017 INFO sqlalchemy.engine.Engine COMMIT

(here is drawback your unable alert the exist table, for this overcome the issue ,

need to follow memory management it exist in Django ,it has state of the table is called memory management )

 

Task 4: Insert the record using ORM

app.py

from sqlalchemy import create_engine,Column, Integer, String

from sqlalchemy.orm import declarative_base,sessionmaker

from urllib.parse import quote_plus

DB_ENGINE = "mysql+pymysql"

DB_HOST = "localhost"

DB_PORT = 3306

DB_NAME = "ccitdb"

DB_USER = "root"

DB_PASSWORD = "root"

DATABASE_URL = f"{DB_ENGINE}://{DB_USER}:{quote_plus(DB_PASSWORD)}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

print(DATABASE_URL)

engine = create_engine(DATABASE_URL, echo=True)

Base = declarative_base()

# model creation start here

class Students(Base):

    __tablename__ = "students"

    id = Column(Integer, primary_key=True , autoincrement=True)

    name = Column(String(100) , nullable=False)

    email = Column(String(100), unique=True , nullable=False)

    mobile = Column(String(15), unique=True , nullable=False)

Base.metadata.create_all(engine)

SessionLocal = sessionmaker(bind=engine)

session = SessionLocal()

new_student = Students(name="John Doe", email="john.doe@example.com", mobile="1234567890")

session.add(new_student)

session.commit()

print("Student added successfully")

Output:

(ccitenv) PS C:\Users\Administrator\Desktop\CCIT\PythonCourse\Day39> py app.py

2026-01-26 03:30:38,420 INFO sqlalchemy.engine.Engine [generated in 0.00052s] {'name': 'John Doe', 'email': 'john.doe@example.com', 'mobile': '1234567890'}

2026-01-26 03:30:38,446 INFO sqlalchemy.engine.Engine COMMIT

Student added successfully

Task5:Select ORM

from sqlalchemy import create_engine,Column, Integer, String

from sqlalchemy.orm import declarative_base,sessionmaker

from urllib.parse import quote_plus

DB_ENGINE = "mysql+pymysql"

DB_HOST = "localhost"

DB_PORT = 3306

DB_NAME = "ccitdb"

DB_USER = "root"

DB_PASSWORD = "root"

DATABASE_URL = f"{DB_ENGINE}://{DB_USER}:{quote_plus(DB_PASSWORD)}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

print(DATABASE_URL)

engine = create_engine(DATABASE_URL, echo=True)

Base = declarative_base()

# model creation start here

class Students(Base):

    __tablename__ = "students"

    id = Column(Integer, primary_key=True , autoincrement=True)

    name = Column(String(100) , nullable=False)

    email = Column(String(100), unique=True , nullable=False)

    mobile = Column(String(15), unique=True , nullable=False)

Base.metadata.create_all(engine)

SessionLocal = sessionmaker(bind=engine)

session = SessionLocal()

new_student = Students(name="Subbu", email="vakati.subbu@gmail", mobile="9700217845")

new_student1 = Students(name="Karthik", email="karthik@gmail.com", mobile="9876543210")

session.add(new_student)

session.add(new_student1)

session.commit()

print("Student added successfully")

# read all students

students = session.query(Students).all()

print("All Students:")

for student in students:

    print(f"ID: {student.id}, Name: {student.name}, Email: {student.email}, Mobile: {student.mobile}")

session.close()

Output:

2026-01-26 03:36:30,566 INFO sqlalchemy.engine.Engine BEGIN (implicit)

2026-01-26 03:36:30,568 INFO sqlalchemy.engine.Engine SELECT students.id AS students_id, students.name AS students_name, students.email AS students_email, students.mobile AS students_mobile

FROM students

2026-01-26 03:36:30,568 INFO sqlalchemy.engine.Engine [generated in 0.00036s] {}

All Students:

ID: 1, Name: Subbu, Email: vakati.subbu@gmail, Mobile: 9700217845

ID: 2, Name: Karthik, Email: karthik@gmail.com, Mobile: 9876543210

2026-01-26 03:36:30,570 INFO sqlalchemy.engine.Engine ROLLBACK

 

Task6: For specific record, above code is same

app.py

# specific student by id

students = session.query(Students).filter_by(name="Subbu").all()

print("Specific Students:")

for student in students:

    print(f"ID: {student.id}, Name: {student.name}, Email: {student.email}, Mobile: {student.mobile}")

session.close()

Output:

2026-01-26 14:59:06,991 INFO sqlalchemy.engine.Engine SELECT students.id AS students_id, students.name AS students_name, students.email AS students_email, students.mobile AS students_mobile

FROM students

WHERE students.name = %(name_1)s

2026-01-26 14:59:06,992 INFO sqlalchemy.engine.Engine [generated in 0.00111s] {'name_1': 'Subbu'}

Specific Students:

ID: 1, Name: Subbu, Email: vakati.subbu@gmail, Mobile: 9700217845

2026-01-26 14:59:06,995 INFO sqlalchemy.engine.Engine ROLLBACK

Task7: Update and delete 

app.py

# update student

student = session.query(Students).filter_by(name="Subbu").first()

if student:

    student.email = "subbu.updated@gmail.com"

    session.commit()

    print("Student updated successfully")

else:

    print("Student not found")

# delete student

student = session.query(Students).filter_by(name="Karthik").first()

if student:

    session.delete(student)

    session.commit()

    print("Student deleted successfully")

else:

    print("Student not found")    

session.close()  

Specific Students:

ID: 1, Name: Subbu, Email: vakati.subbu@gmail, Mobile: 9700217845

2026-01-26 15:05:21,171 INFO sqlalchemy.engine.Engine SELECT students.id AS students_id, students.name AS students_name, students.email AS students_email, students.mobile AS students_mobile

FROM students

WHERE students.name = %(name_1)s

 LIMIT %(param_1)s

2026-01-26 15:05:21,172 INFO sqlalchemy.engine.Engine [generated in 0.00142s] {'name_1': 'Subbu', 'param_1': 1}

2026-01-26 15:05:21,177 INFO sqlalchemy.engine.Engine UPDATE students SET email=%(email)s WHERE students.id = %(students_id)s

2026-01-26 15:05:21,180 INFO sqlalchemy.engine.Engine [generated in 0.00293s] {'email': 'subbu.updated@gmail.com', 'students_id': 1}

2026-01-26 15:05:21,187 INFO sqlalchemy.engine.Engine COMMIT

Student updated successfully

2026-01-26 15:05:21,200 INFO sqlalchemy.engine.Engine BEGIN (implicit)

2026-01-26 15:05:21,201 INFO sqlalchemy.engine.Engine SELECT students.id AS students_id, students.name AS students_name, students.email AS students_email, students.mobile AS students_mobile

FROM students

WHERE students.name = %(name_1)s

 LIMIT %(param_1)s

2026-01-26 15:05:21,201 INFO sqlalchemy.engine.Engine [cached since 0.03067s ago] {'name_1': 'Karthik', 'param_1': 1}

2026-01-26 15:05:21,204 INFO sqlalchemy.engine.Engine DELETE FROM students WHERE students.id = %(id)s

2026-01-26 15:05:21,205 INFO sqlalchemy.engine.Engine [generated in 0.00106s] {'id': 2}

2026-01-26 15:05:21,209 INFO sqlalchemy.engine.Engine COMMIT

Student deleted successfully

So far we completed Code first approach, Database approach 

Database Approach:

You create the database first, then generate models from existing schema.

• Migration using Flask and Django

What is a migration ?

A migration is a version-controlled change to your database schema.

It records what changed, when it changed ,and how to apply or undo it.

In One line: A migration is a step-by-step history of changes made to a database structure

Why Do we need migrations?

Databases are not static 

Your app grows ,so your tables change

Examples:

Add a new column

Remove a column

Rename a table 

Add a foreign key

Without migrations ?

(you manually alter dB, changes are undocumented, production & local db )

--Thanks