Python Program: Add new records to database table

Last update on April 02 2025 12:38:39 (UTC/GMT +8 hours)

2. Add New Student Record

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:

For more Practice: Solve these Related Problems:

• Write a Python program to connect to a SQLite database and insert a new student into the 'students' table using SQLAlchemy's session.add() and session.commit().
• Write a Python function that accepts student details as parameters, inserts a new record into the 'students' table, and then prints a success message with the inserted data.
• Write a Python script to prompt the user for a student's id, studentname, and email, insert the new record into the database, and then confirm the insertion by querying the table.
• Write a Python program that uses a transaction to insert multiple student records into the 'students' table, and then prints the total number of records added.

Python Code Editor :

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