import mariadb
import sqlite3

sconn = sqlite3.connect("bj.db")
mconn = mariadb.connect(user="umwstud", password="davies4ever",
    port=3306, host="cpsc.umw.edu", database="deliveries")

scur = sconn.cursor()
scur.execute("select max(container_id) from container")
container_id = scur.fetchall()
next_container_id = container_id[0][0] + 1

mcur = mconn.cursor()
mcur.execute("select * from containers")
results = mcur.fetchall()
for result in results:
    scur.execute("select needsRef from mixin where name=?",
        (result[2].lower(),))
    does_it_need_ref = scur.fetchone() # could be None!
    # Evasive action for non-existent mixins: the Will way
    #print(f"We're about to look up {result[2].lower()}...")
    #ref = scur.fetchone()
    #if not ref:
    #    ref = 'no'
    #else:
    #    ref = ref[0]
    # Evasive action for non-existent mixins: the Keats way
    scur.execute("select count(*) from mixin where name=?",
        (result[2].lower(),))
    arethereany = scur.fetchone()
    if arethereany[0] > 0:
        ref = does_it_need_ref[0]
    else:
        ref = 'no'
    for i in range(result[3]):
        scur.execute("insert into container values (?, ?, ?, ?, ?)",
            (next_container_id,ref,result[1],result[1],result[2].lower()))
        next_container_id += 1
sconn.commit()