CODEWITHSUNDEEP
pythonsqlalchemy
Capitán Planeta
Capitán Planeta

Reputation: 57

How do I query the table in sqlalchemy when I use the reflect parameter "only=[table_name]"?

I'm trying to build a flask web app that interfaces with a remote, pre-existing sql server database.

I have a successful connection, according to the output of echo='debug.'

I'm using the .reflect() "only" parameter because the database I'm connecting to has hundreds of tables. Without it, the entire database gets reflected and it runs way too slow.

engine = create_engine('mssql+pymssql://user:pass@server/db', echo='debug')

conn = engine.connect()
meta = MetaData(engine).reflect(schema='dbo', only=['Bookings'])
table = meta.tables['Bookings']

select_st = select([table]).where(
   table.c.ID == 'id-1234')
res = conn.execute(select_st)
for _row in res:
    print(_row)

The problem is that I'm getting the error:

    table = meta.tables['Bookings']
AttributeError: 'NoneType' object has no attribute 'tables'

My guess is that .tables doesn't work with the subset 'Bookings' that I've passed it because .tables calls the meta object, which it believes should be a database, not a a table.

How can I get sqlalchemy features to work with that 'only' parameter? Again, I'm building a flask web app so the solution needs to be able to interface with Base.automap.

Upvotes: 1

Views: 390

Answers (1)

bitflip
bitflip

Reputation: 3684

.reflect() does not return a value / returns None. So meta is None. Try it like this please:

meta = MetaData(engine)
meta.reflect(schema='dbo', only=['Bookings'])
table = meta.tables['Bookings']

Upvotes: 1

Related Questions