Relational Tables

Create Tables

python

cursor.execute("""
CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100)
)
""")

cursor.execute("""
CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 product VARCHAR(100),
 FOREIGN KEY (user_id) REFERENCES users(id)
)
""")

connection.commit()

Insert Parent Record

python

cursor.execute(
 "INSERT INTO users (name) VALUES (%s)",
 ("Alice",)
)
connection.commit()

user_id = cursor.lastrowid

python

cursor.execute(
 "INSERT INTO orders (user_id,product) VALUES (%s,%s)",
 (user_id,"Laptop")
)
connection.commit()

Join Query

python

cursor.execute("""
SELECT users.name, orders.product
FROM orders
JOIN users ON orders.user_id = users.id
""")

for row in cursor.fetchall():
 print(row)