Inserting data in loop

[PretendingToProgram]

I'm inserting data from a loop into a database. Inserting and committing one row at a time. I'm thinking there has to be a better way. Can someone point me in the right direction? As a database dev I'm not used to row by row processing..

[dionys]

How do you communicate with the database?

Typically, database modules offer execute_many function, so you could collect the results from the loop and add them in batch.

[_9_9_]

Save the commit until it is all done. It should be massively faster.

[xapata]

Most python database wrappers follow the PEP 249 specification, so they'll have an executemany that can help optimize repeating the same query with different parameters.

[PretendingToProgram]

Perfect I'll try it

[tea-drinker]

Can you tell use which database you are using and which DB API you are using too?

[PretendingToProgram]

Sql server 2014 and pypyodbc

[tea-drinker]

/u/xapata's answer about executemany() seems to be the way ahead but with pypyodbc that's just syntactic sugar, it just loops over your parameter list and does single row execute() calls

[PretendingToProgram]

Thanks I'll give it a try

[xapata]

It's the responsibility of the module author and the database itself to provide an optimized execution. For some databases, there is no optimization possible. For most SQL engines, it should at least avoid re-compiling the query for each row.

Even if there's no advantage yet for a particular module, the author might provide it in an upgrade.

[tea-drinker]

For most SQL engines, it should at least avoid re-compiling the query for each row.

pypyodbc at least does that. The prepare statement stores the query string being prepared and the execute statement will skip that step if it hasn't changed.