CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
Programmer120
Programmer120

Reputation: 2592

How to truncate and insert on the same transaction using sqlalchemy?

I have a production_table and stage_table. I have a python script that runs for few hours and generate data in the stage_table. I want at the end of the script to COPY data from the stage_table to the production_table.

Basically this is what I want:

1. TRUNCATE production_table
2. COPY production_table from stage_table

This is my code:

from sqlalchemy import create_engine
from sqlalchemy.sql import text as sa_text
engine = create_engine("mysql+pymysql://  AMAZON AWS")
engine.execute(sa_text('''TRUNCATE TABLE {1}; COPY TABLE {1} from {0}'''.format(stage_table, production_table)).execution_options(autocommit=True))

This should generate :

TRUNCATE TABLE production_table; COPY TABLE production_table from stage_table

However this doesn't work.

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax;

How can I make it work? and how can I make sure that the TRUNCATE and COPY are together. I don't want TRUNCATE to happen if COPY aborts.

Upvotes: 0

Views: 5532

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52937

The usual way to handle multiple statements in a single transaction in SQLAlchemy would be to begin an explicit transaction and execute each statement in it:

with engine.begin() as conn:
    conn.execute(statement_1)
    conn.execute(statement_2)
    ...

As to your original attempt, there is no COPY statement in MySQL. Some other DBMS do have something of the kind. Also not all DB-API drivers support multiple statements in a single query or command, at least out of the box, which would seem to be the case here as well. See this issue and the related note in the PyMySQL ChangeLog.

The biggest issue is that not all statements in MySQL can be rolled back, of which the most common are DDL statements. In other words you simply cannot execute TRUNCATE [TABLE] ... in the same transaction as the following INSERT INTO ... and must design your application around that limitation. As suggested in the comments by Christian W. you could perhaps create an entirely new table from your staging table and rename, or just swap the production and staging tables. RENAME TABLE ... cannot be rolled back either, but at least you'd reduce the window for error, and could undo the changes since the original production table would still be there, just under a new name. You could then remove the original production table when all else is done. Here's something that demonstrates the idea, but requires manual intervention, if something goes awry:

# No point in faking transactions here, since MySQL in use.
engine.execute("CREATE TABLE new_production AS SELECT * FROM stage_table")
engine.execute("RENAME TABLE production_table TO old_production")
engine.execute("RENAME TABLE new_production TO production_table")
# Point of no return:
engine.execute("DROP TABLE old_production")

Upvotes: 3

Related Questions