Load CSV

from pathlib import Path
Path("cities.csv").write_text("id,name\n1,Nairobi\n2,Mombasa\n", encoding="utf-8")
Path("sales.csv").write_text("city_id,units,price\n1,12,10.5\n2,9,22.0\n", encoding="utf-8")
print("wrote csv")

import csv
import sqlite3
from pathlib import Path

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE cities (id INTEGER, name TEXT)")
with Path("cities.csv").open(encoding="utf-8", newline="") as handle:
    for row in csv.DictReader(handle):
        con.execute("INSERT INTO cities VALUES (?, ?)", (int(row["id"]), row["name"]))
print(con.execute("SELECT * FROM cities").fetchall())
con.close()

import csv
import sqlite3
from pathlib import Path

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE cities (id INTEGER, name TEXT)")
con.execute("CREATE TABLE sales (city_id INTEGER, units INTEGER, price REAL)")
with Path("cities.csv").open(encoding="utf-8", newline="") as handle:
    for row in csv.DictReader(handle):
        con.execute("INSERT INTO cities VALUES (?, ?)", (int(row["id"]), row["name"]))
with Path("sales.csv").open(encoding="utf-8", newline="") as handle:
    for row in csv.DictReader(handle):
        con.execute("INSERT INTO sales VALUES (?, ?, ?)", (int(row["city_id"]), int(row["units"]), float(row["price"])))
print(con.execute("SELECT c.name, s.units FROM sales s JOIN cities c ON c.id = s.city_id").fetchall())
con.close()

import csv
from pathlib import Path
print(list(csv.DictReader(Path("sales.csv").open(encoding="utf-8", newline=""))))