Get existing table using SQLAlchemy MetaData

Question

I have a table that already exists:

USERS_TABLE = Table("users", META_DATA,
                    Column("id", Integer, Sequence("user_id_seq"), primary_key=True),
                    Column("first_name", String(255)),
                    Column("last_name", String(255))
                   )

I created this table by running this:

CONN = create_engine(DB_URL, client_encoding="UTF-8")
META_DATA = MetaData(bind=CONN, reflect=True)
# ... table code
META_DATA.create_all(CONN, checkfirst=True)

the first time it worked and I was able to create the table. However, the 2nd time around I got this error:

sqlalchemy.exc.InvalidRequestError: Table 'users' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

which makes sense since the table users already exists. I'm able to see if the table exists like so:

TABLE_EXISTS = CONN.dialect.has_table(CONN, "users")

However, how do I actually get the existing table object? I can't find this anywhere in the documentation. Please help.

Answer 1

We have 3 different approaches here:

• assume that required tables have been created already, reflecting them and getting with MetaData.tables dictionary field like

  from sqlalchemy import MetaData, create_engine
  
  CONN = create_engine(DB_URL, client_encoding="UTF-8")
  
  META_DATA = MetaData(bind=CONN, reflect=True)
  
  USERS_TABLE = META_DATA.tables['users']
  

• removing reflect flag from MetaData object initialization, because we don't use it and moreover – trying to create tables that've been already reflected:

  from sqlalchemy import MetaData, Table, Column, Integer, String, Sequence, create_engine
  
  CONN = create_engine('sqlite:///db.sql')
  
  META_DATA = MetaData(bind=CONN)
  
  USERS_TABLE = Table("users", META_DATA,
                      Column("id", Integer, Sequence("user_id_seq"), primary_key=True),
                      Column("first_name", String(255)),
                      Column("last_name", String(255))
                      )
  
  META_DATA.create_all(CONN, checkfirst=True)
  

• assuming that we are keeping reflected table if it was previously created by setting in Table object initializer keep_existing flag to True:

  from sqlalchemy import MetaData, Table, Column, Integer, String, Sequence, create_engine
  
  CONN = create_engine('sqlite:///db.sql')
  
  META_DATA = MetaData(bind=CONN, reflect=True)
  
  USERS_TABLE = Table("users", META_DATA,
                      Column("id", Integer, Sequence("user_id_seq"), primary_key=True),
                      Column("first_name", String(255)),
                      Column("last_name", String(255)),
                      keep_existing=True
                      )
  
  META_DATA.create_all(CONN, checkfirst=True)
  

Which one to choose? Depends on your use case, but I prefer second one since it looks like you aren't using reflection, also it is simplest modification: just removing flag from MetaData initializer.

---

P. S.

we can always make reflection after initialization of MetaData object with MetaData.reflect method:

META_DATA.reflect()

also we can specify which tables to reflect with only parameter (may be any iterable of str objects):

META_DATA.reflect(only=['users'])

and many more.

Answer 2

This works for me pretty well -

import sqlalchemy as db

engine = db.create_engine("your_connection_string")

meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)

USERS = meta_data.tables['users']

# View the columns present in the users table
print(USERS.columns)

# You can run sqlalchemy queries
query = db.select([
    USERS.c.id,
    USERS.c.first_name,
    USERS.c.last_name,
])

result = engine.execute(query).fetchall()

Note that using reflect parameter in Metadata(bind=engine, reflect=True) is deprecated and will be removed in a future release. Above code takes care of it.