Python SQLAlchemy Programming Exercises and Solutions
This resource offers a total of 70 Python SQLAlchemy - A SQL toolkit problems for practice. It includes 14 main exercises, each accompanied by solutions, detailed explanations, and four related problems.
[An Editor is available at the bottom of the page to write and execute the scripts.]
1. Student Model Creation
Write a Python program to create a SQLAlchemy model 'Student' with fields: 'id', 'studentname', and 'email'.
Click me to see the sample solution
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.
Click me to see the sample solution
3. Retrieve Student by ID
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. Update Student Email
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. Delete Student Record
Write a Python program that deletes a student from the 'students' table by their id.
Click me to see the sample solution
6. Shop Database Models
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. Retrieve Items by Price
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. Update Item Quantity
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. 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.
Click me to see the sample solution
10. Retrieve Orders for Specific User
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. Total Cost of Orders
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. Top 2 Products by Sales
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. Delete Order Record
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. Aggregation Queries with SQLAlchemy
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