Python Program: Add new records to database table

Python SQLAlchemy: Exercise-2 with Solution

Write a Python program that adds a new student to the 'students' table with a given id, studentname and email.

Sample Solution:

Code:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Create a SQLite database named students.db
engine = create_engine('sqlite:///testdatabase.db', echo=True)
# Create a base class for declarative models
Base = declarative_base()
# Define the Student model
class Student(Base):
    __tablename__ = 'students'    
    id = Column(Integer, primary_key=True)
    studentname = Column(String, nullable=False)
    email = Column(String, nullable=False)
# Create the table in the database if it doesn't exist
Base.metadata.create_all(engine)
# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()
# Add a new student to the database
def add_student(id, studentname, email):
    new_student = Student(id=id, studentname=studentname, email=email)
    session.add(new_student)
    session.commit()
    print("Student added successfully")
# Call the add_student function to add a new student
add_student(id=22, studentname='Hughie Glauco', email='[email protected]')
# Close the session
session.close()

Output:

2023-08-17 20:08:09,340 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2023-08-17 20:08:09,340 INFO sqlalchemy.engine.base.Engine ()
2023-08-17 20:08:09,341 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2023-08-17 20:08:09,341 INFO sqlalchemy.engine.base.Engine ()
2023-08-17 20:08:09,342 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("students")
2023-08-17 20:08:09,342 INFO sqlalchemy.engine.base.Engine ()
2023-08-17 20:08:09,345 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2023-08-17 20:08:09,345 INFO sqlalchemy.engine.base.Engine INSERT INTO students (id, studentname, email) VALUES (?, ?, ?)
2023-08-17 20:08:09,346 INFO sqlalchemy.engine.base.Engine (22, 'Hughie Glauco', '[email protected]')
2023-08-17 20:08:09,348 INFO sqlalchemy.engine.base.Engine COMMIT
Student added successfully

Note:

In the above exercise the messages we see are SQL statements and database operations executed by SQLAlchemy. These messages are part of SQLAlchemy's logging mechanism and are printed to the console due to the echo=True parameter we used when creating the database engine.

Flowchart:

Previous: Create SQLAlchemy model 'Student' with fields.
Next: Python program to retrieve table information from database.