I'm building a relational database (many to many) from json data scraped off the web. I have two main databases, one with 909 rows and the other with 13 rows. I need to match the ids of the large table to the ids of the small table in the intersection table, corresponding to how they are linked within the json file. The problem that I'm finding is that there is nothing relating these tables together, but I need to populate the intersection table. There is nothing that I try or can think of that will populate it, outside of doing it by hand which will take days. A small sample of the json file is below. The large table has course info, and the small table uses the fulfills key.
[
{"number": "CHIN 242", "subject": "Chinese", "title": "Chinese Cinema and Chinese Modernity", "description": "From the fall of the Clestial Empire to the rise of China's economy today, Chinese cinema has witnessed many social changes in the modern era. This course will focus on the interaction between Chinese cinema and the process of modernization.", "fulfills": ["Human Expression\u2014Primary Texts", "Intercultural"]},
{"number": "CLAS 240", "subject": "Classics", "title": "Classical Mythology", "description": "A survey of the major myths and legends of ancient Greece and Rome.", "fulfills": ["Human Expression\u2014Primary Texts", "Quantitative"]},
{"number": "CLAS 250", "subject": "Classics", "title": "The World of Ancient Greece", "description": "A historical survey of ancient Greek culture from the Trojan War to the rise of Rome.", "fulfills": ["Human Expression\u2014Primary Texts", "Religion"]},
{"number": "CLAS 255", "subject": "Classics", "title": "Ancient Roman Culture", "description": "This course explores various cultural institutions and practices of the ancient Romans.", "fulfills": ["Human Expression\u2014Primary Texts", "Human Behavior"]},
{"number": "CLAS 265", "subject": "Classics", "title": "Greece and Rome on Film", "description": "This course explores the ways in which various events and episodes from Greek and Roman myth and history have been adapted for modern film and television.", "fulfills": ["Human Expression\u2014Primary Texts"]},
{"number": "CLAS 270", "subject": "Classics", "title": "Archaeology of Ancient Greece", "description": "An in-depth study of the archaeology of ancient Greece, with a focus on the high points of Greek civilization and material culture.", "fulfills": ["Historical", "Human Expression\u2014Primary Texts"]},
{"number": "CLAS 275", "subject": "Classics", "title": "Archaeology of Ancient Rome", "description": "This course explores the archaeology of ancient Rome from its early beginnings to its rapid growth into one of the world's largest empires.", "fulfills": ["Historical", "Human Expression\u2014Primary Texts"]},
{"number": "CLAS 300", "subject": "Classics", "title": "Classics and Culture", "description": "Using texts in translation, this course explores select aspects or themes from the cultures of ancient Greece and Rome.", "fulfills": ["Human Expression\u2014Primary Texts"]},
]
I found the solution to this. To fill the intersection table, run a query on the id and fulfills on the small table, and id and something simple like number, then run myDict = dict(map(reversed,cur.fetchall())) for each query. from there, use a for loop to iterate the json data, and then a nested for loop to iterate the list items in fulfills and cur.execute(insert into ...). It looks like this:
cur.execute('select id, fulfills from reqs;')
dict1 = dict(map(reversed,cur.fetchall()))
cur.execute('select id, number from course;')
dict2 = dict(map(reversed,cur.fetchall()))
for x in geneds:
for y in x:
cur.execute('insert into table(course, req) values(%s, %s);', (dict2[x['number']], dict1[y]))