How to increase the speed of the loop containing sqlalchemy query request as conditional

Question

This loop checks to see if the record is in the sqlite database and builds a list of dictionaries for those records that are missing, and then executes the multiple insert statement with this list. This works, but it is very slow (at least I think it is slow), since it takes 5 minutes to process 3500 requests. I'm a complete newbie to python, sqlite and sqlalchemy, so I wonder if there is a faster way to do this.

list_dict = [] session = Session() for data in data_list: if session.query(Class_object).filter(Class_object.column_name_01 == data[2]).filter(Class_object.column_name_00 == an_id).count() == 0: list_dict.append({'column_name_00':a_id, 'column_name_01':data[2]}) conn = engine.connect() conn.execute(prices.insert(),list_dict) conn.close() session.close() 

edit: I moved session = Session() outside of the loop. Irrelevant.

Decision

thanks to mcabral answer. I changed the code as:

 existing_record_list = [] list_dict = [] conn = engine.connect() s = select([prices.c.column_name_01], prices.c.column_name_00==a_id) result = conn.execute(s) for row in result: existing_record_list.append(row[0]) for data in raw_data['data']: if data[2] not in existing_record_list: list_dict.append({'column_name_00':a_id, 'column_name_01':data[2]} conn = engine.connect() conn.execute(prices.insert(),list_dict) conn.close() 

Now it takes 6 seconds. This is some improvement!

Answer 1

3,500 queries seem like a lot

Do you find a selection of all objects in a single query? Then you will iterate over the list in memory and not query the database for each item.

Answer 2

Glad you found something that works as an extra 2 cents:

I agree with mcabral. Typically, if you ask a query inside a loop, you ask for problems. Popular SQL databases are usually optimized for data collection. Quoting by request usually indicates that you are procedurally performing what should / should be performed with either a single request or a string - requests that put data into each other.

There are exceptions to this, but from my experience they are usually few in number and far from each other ... Each time I ran a query through a loop, I regretted it later.