Python program: Create new order using SQLAlchemy

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

9. Create New Order Record

Write a Python program to create a new order in the 'Order' table for a user and a item with a specified quantity using the SQLAlchemy model.

Sample Solution:

Code:

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create a SQLite database named shop.db
engine = create_engine('sqlite:///shop2.db', echo=True)
# Create a base class for declarative models
Base = declarative_base()
# Define the Order model
class Order(Base):
    __tablename__ = 'orders'    
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, nullable=False)
    item_id = Column(Integer, nullable=False)
    order_quantity = Column(Integer, nullable=False)
# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()
def create_order(user_id, item_id, order_quantity):
    new_order = Order(order_id=103,user_id=user_id, item_id=item_id, order_quantity=order_quantity)
    session.add(new_order)
    session.commit()
    print("New order created successfully")
# Create a new order for a user and a product with a specified quantity
user_id = 1  # Replace with the user's ID
item_id = 1  # Replace with the item's ID
order_quantity = 3  # Replace with the order quantity
create_order(user_id, item_id, order_quantity)
# Close the session
session.close()

Output:

2023-08-18 15:13:47,054 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2023-08-18 15:13:47,056 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 15:13:47,056 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2023-08-18 15:13:47,057 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 15:13:47,057 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2023-08-18 15:13:47,058 INFO sqlalchemy.engine.base.Engine INSERT INTO orders (order_id, user_id, item_id, order_quantity) VALUES (?, ?, ?, ?)
2023-08-18 15:13:47,059 INFO sqlalchemy.engine.base.Engine (103, 1, 1, 3)
2023-08-18 15:13:47,061 INFO sqlalchemy.engine.base.Engine COMMIT
New order created 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 create a new order in the 'orders' table by specifying user_id, item_id, and order_quantity, then print the newly created order.
• Write a Python function that accepts order details, inserts a new record into the 'orders' table using SQLAlchemy, and returns the generated order_id.
• Write a Python script to prompt for order information, create a new order record, and verify the insertion by querying the 'orders' table.
• Write a Python program to add a new order record and then join with the 'users' and 'items' tables to display full order details.

Python Code Editor :

Previous: Update item quantity using SQLAlchemy.
Next: Retrieve user-specific orders with SQLAlchemy.