SQLAlchemy: table already exists

Question

when do we usually get the following error in SQLAlchemy?

sqlalchemy.exc.OperationalError: (OperationalError) (1050, "Table 'foobar' already exists") 

The foobar table already exists, but why does SQLAlchemy try to create the table when it is already present. I assume that he should not create the table if it already exists.

I use the following syntax to create a table:

 t = Table('foobar', metadata, Column('col1', String(50), primary_key=True), Column('col2', String(100)), mysql_engine='InnoDB', mysql_charset='utf8') 

(I call the same program in parallel 10 times)

Answer 1

Just use the schema objects (table, index and sequence) create and drop with the checkfirst = True keyword , and the table will automatically add “IF NOT EXISTS or IF EXISTS CLAUSE” depending on what SQL suits.

FOR EXAMPLE:

 t = Table('foobar', metadata, Column('col1', String(50), primary_key=True), Column('col2', String(100)), mysql_engine='InnoDB', mysql_charset='utf8') t.create(checkfirst=True) 

Answer 2

If the foobar table already exists, you could do:

 users = Table('foobar', metadata, autoload=True) 

and SQLAlchemy automatically calculated the table structure from the database.

First check the use of autoload, is there a table or not, if it is not created there, create a table.

Answer 3

Here is my hunch with some troubleshooting ideas. I assume that the client thinks the table does not exist, because it cannot see it, but when trying to create it, it cannot, because it actually exists.

Troubleshooting recommendations:

• Check if any other part of the code can write to the same log file or something else and is trying to create these tables.
• Manually log in with the same ID and password as the client, and see if you can see the table.
• Pass echo=True to create_engine to find out the exact queries that the client executes, and then repeat all the queries in its own SQL shell to see what the client sees. Hope this leads you to a conclusion.