Python program: Create SQLAlchemy models and populating tables

Python SQLAlchemy: Exercise-6 with Solution

Write a Python program that creates a i) SQLAlchemy model named 'Item' and table name 'items' with fields: 'item_id', 'item_name', 'item_price', and 'item_quantity'. ii) Create a SQLAlchemy model 'Order' and table name 'orders' with fields: 'order_id', 'user_id', 'item_id', and 'order_quantity'. iii) SQLAlchemy model named 'User' and table name 'users' with fields: 'user_id', 'user_name', 'user_email'.

Tables will be created under the database 'shop.db' .

Consider

1. 'item_id' as a primary key of 'items' table.
2. 'user_id' as a primary key of 'users' table.
3. 'order_id' as a primary key of ‘orders’ table and 'item_id' and 'user_id' as two foreign keys.

Insert some records in the 'items', 'users' and 'orders' tables.

Sample Solution:

Code:

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

# 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 Item model
class Item(Base):
    __tablename__ = 'items'
    
    item_id = Column(Integer, primary_key=True)
    item_name = Column(String, nullable=False)
    item_price = Column(Float, nullable=False)
    item_quantity = Column(Integer, nullable=False)


# Define the User model
class User(Base):
    __tablename__ = 'users'
    
    user_id = Column(Integer, primary_key=True)
    user_name = Column(String, nullable=False)
    user_email = Column(String, nullable=False)


# Define the Order model
class Order(Base):
    __tablename__ = 'orders'
    
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id'), nullable=False)
    item_id = Column(Integer, ForeignKey('items.item_id'), nullable=False)
    order_quantity = Column(Integer, nullable=False)
    
    user = relationship("User")
    item = relationship("Item")


# Create the tables in the database if they don't exist
Base.metadata.create_all(engine)

# For demonstration purposes, we can create a session and add some example data
Session = sessionmaker(bind=engine)
session = Session()

# Add three items
new_item1 = Item(item_id=1, item_name='Pizza', item_price=15.82, item_quantity=100)
new_item2 = Item(item_id=2, item_name='Burger', item_price=2.82, item_quantity=100)
new_item3 = Item(item_id=3, item_name='Ramen', item_price=.45, item_quantity=300)
session.add(new_item1)
session.add(new_item2)
session.add(new_item3)

# Add three users
new_user1 = User(user_id=1, user_name='Dznpc Ucmrr', user_email='dznpc@example.com')
new_user2 = User(user_id=2, user_name='Anas Richa', user_email='anas@example.com')
new_user3 = User(user_id=3, user_name='Martin Elma', user_email='martin@example.com')
session.add(new_user1)
session.add(new_user2)
session.add(new_user3)
# Add some orders
new_order1 = Order(order_id=100, user_id=new_user1.user_id, item_id=new_item1.item_id, order_quantity=2)
new_order2 = Order(order_id=101, user_id=new_user1.user_id, item_id=new_item3.item_id, order_quantity=10)
new_order3 = Order(order_id=102, user_id=new_user3.user_id, item_id=new_item2.item_id, order_quantity=5)
session.add(new_order1)
session.add(new_order2)
session.add(new_order3)
session.commit()
# Close the session
session.close()

Output:

2023-08-18 11:32:01,529 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("items")
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("items")
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("users")
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("users")
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("orders")
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("orders")
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE items (
	item_id INTEGER NOT NULL, 
	item_name VARCHAR NOT NULL, 
	item_price FLOAT NOT NULL, 
	item_quantity INTEGER NOT NULL, 
	PRIMARY KEY (item_id)
)


2023-08-18 11:32:01,532 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,549 INFO sqlalchemy.engine.base.Engine COMMIT
2023-08-18 11:32:01,551 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	user_id INTEGER NOT NULL, 
	user_name VARCHAR NOT NULL, 
	user_email VARCHAR NOT NULL, 
	PRIMARY KEY (user_id)
)


2023-08-18 11:32:01,551 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,559 INFO sqlalchemy.engine.base.Engine COMMIT
2023-08-18 11:32:01,561 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE orders (
	order_id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	item_id INTEGER NOT NULL, 
	order_quantity INTEGER NOT NULL, 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id), 
	FOREIGN KEY(item_id) REFERENCES items (item_id)
)


2023-08-18 11:32:01,561 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 11:32:01,566 INFO sqlalchemy.engine.base.Engine COMMIT
2023-08-18 11:32:01,570 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2023-08-18 11:32:01,572 INFO sqlalchemy.engine.base.Engine INSERT INTO items (item_id, item_name, item_price, item_quantity) VALUES (?, ?, ?, ?)
2023-08-18 11:32:01,572 INFO sqlalchemy.engine.base.Engine ((1, 'Pizza', 15.82, 100), (2, 'Burger', 2.82, 100), (3, 'Ramen', 0.45, 300))
2023-08-18 11:32:01,574 INFO sqlalchemy.engine.base.Engine INSERT INTO users (user_id, user_name, user_email) VALUES (?, ?, ?)
2023-08-18 11:32:01,575 INFO sqlalchemy.engine.base.Engine ((1, 'Dznpc Ucmrr', 'dznpc@example.com'), (2, 'Anas Richa', 'anas@example.com'), (3, 'Martin Elma', 'martin@example.com'))
2023-08-18 11:32:01,575 INFO sqlalchemy.engine.base.Engine INSERT INTO orders (order_id, user_id, item_id, order_quantity) VALUES (?, ?, ?, ?)
2023-08-18 11:32:01,575 INFO sqlalchemy.engine.base.Engine ((100, 1, 1, 2), (101, 1, 3, 10), (102, 3, 2, 5))
2023-08-18 11:32:01,579 INFO sqlalchemy.engine.base.Engine COMMIT

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: Python program to delete a record from a table.
Next: Python program: Retrieve items with price greater than.