Python Practice › SQLite Programs › Program 11
Join two tables
Write a Python program to list each order with the customer’s name.
Output
(10, 'Asha', 500.0) (11, 'Ravi', 250.0) (12, 'Asha', 900.0)
Approach
JOIN ... ON.
Solution
import sqlite3 conn = sqlite3.connect(":memory:")conn.executescript(""" CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'); INSERT INTO orders VALUES (10, 1, 500), (11, 2, 250), (12, 1, 900);""")query = "SELECT o.id, c.name, o.amount FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id"for row in conn.execute(query): print(row)Output
(10, 'Asha', 500.0) (11, 'Ravi', 250.0) (12, 'Asha', 900.0)
Line by line
Lines 4-9
executescript runs several statements at once, which is handy for setup.
Learn the concept
New to this topic? Read the lesson, then come back.