Python SQLAlchemy Programming Exercises and Solutions

Python SQLAlchemy - A SQL toolkit [ 14 exercises with solution ]

[An editor is available at the bottom of the page to write and execute the scripts.   Go to the editor]

1. Write a Python program to create a SQLAlchemy model 'Student' with fields: 'id', 'studentname', and 'email'.
Click me to see the sample solution

2. Write a Python program that adds a new student to the 'students' table with a given id, studentname and email.
Click me to see the sample solution

3. Write a Python program that retrieves a student's information from the 'students' table using their id..
Click me to see the sample solution

4. Write a Python program that updates a student's email in the 'students' table based on their id.
Click me to see the sample solution

5. Write a Python program that deletes a student from the 'students' table by their id.
Click me to see the sample solution

6. 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 'shop2.db' . Consider i) 'item_id' as a primary key of 'items' table. ii) 'user_id' as a primary key of 'users' table. iii) 'order_id' as a primary key of 'orders' table and 'item_id' and 'user_id' as two foreigh keys. Insert some records in the 'items', 'users' and 'orders' tables.
Click me to see the sample solution

7. Write a Python program to retrieve items with a price greater than a certain value from the 'items' table using the SQLAlchemy model.
Click me to see the sample solution

8. Write a Python program to update the quantity of an item in the 'Items' table based on its name using the SQLAlchemy model.
Click me to see the sample solution

9. 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.
Click me to see the sample solution

10. Write a Python program to retrieve all orders for a specific user from the 'Order' table user using the SQLAlchemy model.
Click me to see the sample solution

11. Write a Python program to calculate and display the total cost of all orders for a given user using the SQLAlchemy model.
Click me to see the sample solution

12. Write a Python program to list the top 2 products by sales quantity from the 'orders' table using the SQLAlchemy model.
Click me to see the sample solution

13. Write a Python program to delete an order from the 'Order' table by its id. Use the SQLAlchemy model.
Click me to see the sample solution

14. Write a Python program that performs aggregation queries to get the total number of orders, total sales amount, minimum item price, maximum item price and average order quantity using SQLAlchemy.
Click me to see the sample solution

Python Code Editor:

More to Come !

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.

Test your Python skills with w3resource's quiz