Get existing table using SQLAlchemy MetaData

user_858962 0
Viewed 34284

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.

sqlalchemy python
4 Answers

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.

user_116089620

If you're using async Sqlalchemy, you can use

metadata = MetaData()

async with engine.connect() as conn:
    await conn.run_sync(metadata.reflect, only=["harshit_table"])
    harshit_table = Table("harshit_table", metadata, autoload_with=engine)
    print("tables: ", harshit_table, type(harshit_table))
user_100350010 
__table_args__ = {'extend_existing': True}

right below __tablename__

user_23496380
Related