Python - insert nested dict to sqlite - performance issues

Question

I am trying to extract data from a dictionary where performance is very prioritized. I have ~1 million dicts to process (but this is going to scale up). However the dict can on occasion miss value.

The current solution is based upon the results below but I currently iterate the list to get the input, then pop course, insert it into an unique list if it does not exist (to remove redundancy) and then insert the id back to input with name of "course_id".

Problem: This is far from efficient but out of ideas or keywords for an better approach. Does Sqlite have any tricks to insert into different tables or can I separate the items in a better way?

My idea: id is a user_id and the course is an row that should be placed within another table. This data should be inserted to an sqlite database but not sure how to handle the conversion.

Input:

'id':user_id,
'course':{
   'id':course.get('id'),
   'name':course.get('name')
},
'interests':interests.get('id'),
'address':address.get('name'),

Expected results into user table:

'id':123,
'course_id':1, # ForeignKey to course id given above. 
'interests':'Likes to code',
'address':'Parents house',

Expected results into course table:

'id':1,
'name':'some course'

I am currently doing a bulk insert using SQLAlchemy core where I create a list of all items to be inserted and then call execute. Example query:

text('INSERT OR IGNORE INTO users (id, course_id, interests, address) VALUES (:id, :course_id, :interests, :address)'),

Answer 1

等待大神答复