84 lines
2.2 KiB
Python
84 lines
2.2 KiB
Python
|
import sqlite3
|
||
|
|
|
||
|
|
# Connect to the database (creates it if it doesn't already exist)
|
||
|
|
conn = sqlite3.connect('mydatabase.db')
|
||
|
|
|
||
|
|
# Create a cursor object to execute SQL commands
|
||
|
|
c = conn.cursor()
|
||
|
|
|
||
|
|
# Drop all tables
|
||
|
|
c.execute('''DROP TABLE customers''')
|
||
|
|
c.execute('''DROP TABLE orders''')
|
||
|
|
|
||
|
|
# Create a table for customers
|
||
|
|
c.execute('''CREATE TABLE customers
|
||
|
|
(id INTEGER PRIMARY KEY,
|
||
|
|
name text,
|
||
|
|
email text)''')
|
||
|
|
|
||
|
|
# Create a table for orders
|
||
|
|
c.execute('''CREATE TABLE orders
|
||
|
|
(id INTEGER PRIMARY KEY,
|
||
|
|
customer_id INTEGER,
|
||
|
|
product text,
|
||
|
|
quantity INTEGER,
|
||
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id))''')
|
||
|
|
|
||
|
|
# Insert some data into customers
|
||
|
|
c.execute("INSERT INTO customers VALUES (1, 'Alice', 'alice@example.com')")
|
||
|
|
c.execute("INSERT INTO customers VALUES (2, 'Bob', 'bob@example.com')")
|
||
|
|
c.execute("INSERT INTO customers VALUES (3, 'Charlie', 'charlie@example.com')")
|
||
|
|
|
||
|
|
# Insert some data into orders
|
||
|
|
c.execute("INSERT INTO orders VALUES (1, 1, 'Product A', 2)")
|
||
|
|
c.execute("INSERT INTO orders VALUES (2, 2, 'Product B', 1)")
|
||
|
|
c.execute("INSERT INTO orders VALUES (3, 1, 'Product C', 3)")
|
||
|
|
|
||
|
|
# Save (commit) the changes
|
||
|
|
conn.commit()
|
||
|
|
|
||
|
|
# Retrieve data
|
||
|
|
print("SELECT * FROM customers")
|
||
|
|
c.execute("SELECT * FROM customers")
|
||
|
|
rows = c.fetchall()
|
||
|
|
for row in rows:
|
||
|
|
print(row)
|
||
|
|
print("")
|
||
|
|
|
||
|
|
print("SELECT * FROM orders")
|
||
|
|
c.execute("SELECT * FROM orders")
|
||
|
|
rows = c.fetchall()
|
||
|
|
for row in rows:
|
||
|
|
print(row)
|
||
|
|
print("")
|
||
|
|
|
||
|
|
print("SELECT orders.product, customers.name FROM orders JOIN customers ON orders.customer_id == customers.id")
|
||
|
|
c.execute("SELECT orders.product, customers.name FROM orders JOIN customers ON orders.customer_id == customers.id")
|
||
|
|
rows = c.fetchall()
|
||
|
|
for row in rows:
|
||
|
|
print(row)
|
||
|
|
print("")
|
||
|
|
|
||
|
|
|
||
|
|
# Close the cursor and the connection
|
||
|
|
c.close()
|
||
|
|
conn.close()
|
||
|
|
|
||
|
|
|
||
|
|
# OUTPUT
|
||
|
|
'''
|
||
|
|
SELECT * FROM customers
|
||
|
|
(1, 'Alice', 'alice@example.com')
|
||
|
|
(2, 'Bob', 'bob@example.com')
|
||
|
|
(3, 'Charlie', 'charlie@example.com')
|
||
|
|
|
||
|
|
SELECT * FROM orders
|
||
|
|
(1, 1, 'Product A', 2)
|
||
|
|
(2, 2, 'Product B', 1)
|
||
|
|
(3, 1, 'Product C', 3)
|
||
|
|
|
||
|
|
SELECT orders.product, customers.name FROM orders JOIN customers ON orders.customer_id == customers.id
|
||
|
|
('Product A', 'Alice')
|
||
|
|
('Product B', 'Bob')
|
||
|
|
('Product C', 'Alice')
|
||
|
|
'''
|