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.

Geopolitics

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.

Eam

Where is the sqlite:///db.sql file actually saved? I cannot locate this file anywhere after creation... – Xylophagous

@SeanPianka: it should be at the directory where your script is executed – Eam

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.

Illusive

interesting..would love this to work. but i get MetaData.__init__() got an unexpected keyword argument 'bind' when trying it – Haff

@Haff 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. – Kish

The bind parameter to MetaData__init__was removed in 2.0 - see docs. – Elviraelvis

Answer 3

__table_args__ = {'extend_existing': True}

right below __tablename__

Pamela

Can you please provide an example? What do you mean by "below"? – Geopolitics

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

Dearly

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)

Countermark

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. – Elviraelvis

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)

Bartholomeo

This will throw TypeError: Additional arguments should be named <dialectname>_<argument>, got 'autoload' – Shala

It works with sql server – Bartholomeo

Answer 7

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.

Steeple

Answer 8

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"]

Schatz