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.

• Sean Pianka: Where is the sqlite:///db.sql file actually saved? I cannot locate this file anywhere after creation...
• Azat Ibrakov: @SeanPianka: it should be at the directory where your script is executed

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.

• mike01010: interesting..would love this to work. but i get MetaData.__init__() got an unexpected keyword argument 'bind' when trying it
• JimmyJames: @mike01010 It looks like something changed about this in version 2.0. I can't find what the equivalent call is to bind to an existing schema. I love SQLAlchemy but I don't understand what problem they have with giving full working examples in the docs.
• snakecharmerb: The bind parameter to MetaData__init__was removed in 2.0 - see docs.

Answer 3

__table_args__ = {'extend_existing': True}

right below __tablename__

• dopatraman: Can you please provide an example? What do you mean by "below"?

Answer 4

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))

Answer 5

I'm quite new to this, but what worked for me was this (variables are declared in the original question)

USERS_TABLE_NEW = Table("users", META_DATA, autoload_with=CONN)

• snakecharmerb: This solution is already provided by this existing answer. When answering old questions, please ensure that your answer provides a distinct and valuable contribution to the Q&A.

Answer 6

I used the connection with autoload=True and autoload_with=connection

with engine.connect() as connection:
 with connection:
 metadata=MetaData()
 my_table=Table("my_table", metadata,autoload=True, autoload_with=connection)

• Ghasem: This will throw TypeError: Additional arguments should be named <dialectname>_<argument>, got 'autoload'
• ListenSoftware Louise Ai Agent: It works with sql server

Answer 7

Adding to this thread because there seems to be so much confusion on this matter due to the current documentation. I'm running version 2.0 and can confirm this code can successfully reflect on all tables found in a MySQL database:

import sqlalchemy as db
connection_string = "mysql+mysqlconnector://root:@127.0.0.1:3306/mydb"
engine = db.create_engine(connection_string, echo=True)
connection = engine.connect()
meta_data = db.MetaData()
meta_data.reflect(bind=engine)
meta_data.reflect(engine)
redactions = meta_data.tables["redactions"]
pdfs = meta_data.tables["pdfs"]

Answer 8

I got this error when I had already defined a model for a table and I redefined it elsewhere in the code. Obviously the solution is to remove one of the definitions. In my case it was a child object of anther object and I defined the model in the parents model file as well as in its own file.