SQLAlchemy: Table already exists

Question

when do we generally get the following error in SQLAlchemy?

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

The foobar table does already exists, but why SQLAlchemy is trying to create table when already present. I'm assuming it shouldn't create table if already exists.

I'm using following syntax to create table:

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

(I'm calling the same program in parallel 10 times)

Answer 1

Just use schema object's (Table,Index and Sequence) create and drop methods with checkfirst=True keyword and table will automatically add an "IF NOT EXISTS or IF EXISTS CLAUSE" whichever is appropriate to SQL.

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 existed, you could instead have done:

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

and SQLAlchemy would have automatically figured out the table's structure from the database.

Check first using autoload, table is there or not, if it is not there then create the table.

Answer 3

Here is my guess with some troubleshooting ideas. My guess is that the client thinks the table does not exist, because it can't see it, but when trying to create it, cannot because it in fact does exist.

Troubleshooting ideas:

• Check to see if maybe some other part of the code is writing to the same log file or whatever and is trying to create these tables.
• Manually login with the same ID and password as the client, and see if you can see the table.
• Pass echo=True to create_engine to learn the exact queries the client performs, then repeat all the queries in your own SQL shell to see what the client is seeing. Hopefully that will lead you to a conclusion.

Answer 4

Another option is to use metadata:

database_engine = create_engine(
    'mssql+pyodbc://username:password=@server:1433/databaseName?driver=ODBC+Driver+17+for+SQL+Server?trusted_connection=no')
with database_engine.connect() as database_connection:
    metaData = MetaData(bind=database_engine, reflect=True)
    metaData.reflect()

    try:
        Table("tbl_stuff",
              metaData,
              Column("id", Integer, primary_key=True, autoincrement=False),
              Column("Name", String(255)),
              extend_existing=True)
        metaData.create_all(database_connection, checkfirst=True)
        metaData.reflect()
    except Exception as an_exc:
        logging.exception("Exception creating tbl_stuff: " + str(an_exc))

For details please see https://docs.sqlalchemy.org/en/13/core/metadata.html