import mariadb
import sqlite3

mconn = mariadb.connect(
    user="umwstud",
    password="supersecret",
    host="localhost",
    port=3306,
    database="deliveries"
)
sconn = sqlite3.connect("bj.db")

mcursor = mconn.cursor()
mcursor.execute("select * from containers")
results = mcursor.fetchall()
for i in range(len(results)):
    mixin_type = results[i][2].lower()
    num_containers = results[i][3]
    size = results[i][1]
    for container_number in range(num_containers):
        biggest_container_id = (
            sconn.execute("select max(container_id) from container").fetchone()[0]
        )
        new_container_id = biggest_container_id + 1
        refness = sconn.execute("select distinct ref from container where mixin_name=?", (mixin_type,)).fetchone()[0]
        sconn.execute("insert into container "
            "(container_id, ref, capacity, load, mixin_name) values "
            "(?, ?, ?, ?, ?)",
            (new_container_id, refness, size, size, mixin_type)
        )
    sconn.commit()