I'm building a data migration script in Python and I need to run several SQL commands—like creating a table, inserting data, and updating a record—all at once. Currently, I'm calling cursor.execute() for every single line, which feels inefficient and slow. Is there a way to execute a batch of statements in a single string, or perhaps a method like executescript() or executemany() that handles multiple queries more effectively while maintaining ACID properties?
3 answers
The method for executing multiple statements depends heavily on the database driver you are using. In sqlite3, you can use the cursor.executescript() method to run a large block of SQL separated by semicolons. However, for libraries like psycopg2 (PostgreSQL) or mysql-connector, you usually cannot run multiple distinct commands in one execute() call for security reasons, specifically to prevent SQL injection. Instead, you should use executemany() for batching the same command with different data, or wrap your multiple execute() calls within a single transaction using connection.commit(). This ensures that either all commands succeed or none do. For pure performance in PostgreSQL, execute_values from the extras module is often the fastest way to handle large multi-row inserts.
Does using executescript() automatically handle the commit() for me, or do I still need to manually call it to ensure the changes are written to the disk? I've had issues in the past where my script finished without errors, but the database remained empty because I forgot the final step.
For batching many rows of the same INSERT statement, executemany() is the industry standard. It’s significantly faster than a loop because it reduces the overhead of network round-trips to the server.
I agree with Sarah. I recently switched a data-loading task from a basic loop to executemany() and saw the execution time drop from minutes to seconds. It's the first thing I look for when optimizing any Python-based ETL process.
Marcus, that depends on the library's "autocommit" setting. In sqlite3, executescript() issues a COMMIT statement internally before it starts, but it's always safer to explicitly call connection.commit() at the end of your logic. If you are using a context manager with the with connection: syntax, it should handle the commit or rollback automatically if an exception occurs. Always check your specific driver’s documentation, as mysql-connector and psycopg2 behave quite differently regarding default transaction states.