CODEWITHSUNDEEP
pythonsqlalchemy
abhiomkar
abhiomkar

Reputation: 5068

SQLAlchemy: Table already exists

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)

Upvotes: 12

Views: 35537

Answers (4)

user8128167
user8128167

Reputation: 7696

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

Upvotes: 1

Robins Gupta
Robins Gupta

Reputation: 3153

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)

Upvotes: 22

wberry
wberry

Reputation: 19377

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.

Upvotes: 2

NIlesh Sharma
NIlesh Sharma

Reputation: 5665

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.

Upvotes: 3

Related Questions