Python program: Create new order using SQLAlchemy

Python SQLAlchemy: Exercise-9 with Solution

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:

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