1

A dumb question but I couldn't get why it does not work. I have a DB with several tables. I can map it manually

Earnings = Table ('Earnings', metadata, autoload=True, autoload_with=engine)

or automatically using a loop.

tablenames = inspect(engine).get_table_names()
for tabname in tablenames:
    tabname = Table (tabname, metadata, autoload=True, autoload_with=engine)

I can use this code to see the mapped tables :

for tab in metadata.tables:
    print (tab)
...
>>> Earnings
...

So far, no problem.

The question is that if try use the automatically mapped tables, it does not locate it.

Lista_colunas = Earnings.columns.items()
for col in Lista_colunas:
    print (col)

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
c:\Users\fabio\Banco do Brasil S.A\DINED Parcerias Digitais - General\Python\Amazon\SQLA_Access_Test.ipynb Cell 13' in <cell line: 1>()
----> 1 Lista_colunas = Earnings.columns.items()
      2 for col in Lista_colunas:
      3     print (col)

NameError: name 'Earnings' is not defined

I realized that the auto mode is not creating the variables with the 'tabnames', but why not?

Somehow, VSCODE identifies that the 'Earnings' is a table objetc (see the picture), but does not let me call for it.

VScode mouseover

FábioRB
  • 335
  • 1
  • 12

1 Answers1

2

Once the loop has completed, tabname will always be the table that corresponds to the final entry tin tablenames. You could collect the tables in a dict keyed on tabname, but SQLAlchemy already provides a way to do this:

metadata = MetaData()
metadata.reflect(bind=engine)  # Reflect all tables in the database.
Earnings = metadata.tables['Earnings']

The docs for reflection in general are here, the docs for MetaData.reflect are here.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
  • Ok. The variable 'tabname' will have the last table nome. No issue here. I am listing the existinting tables by doing this and it shows me a list of tables. 'for tab in metadata.tables: print(tab)' Even when I type the name 'Earnings", VSCODE shows it a Table object "(Variable) Earnings : Table" – FábioRB Jul 13 '22 at 03:30
  • By doing your suggestion, how do I do a loop to create variables for all tables? – FábioRB Jul 13 '22 at 03:34
  • I'm not sure why you need to create variables, since you can access them via the metadata object's `tables` attribute. But some of the answers [here](https://stackoverflow.com/q/1373164/5320906) might help you. I don't use VSCode so I can't explain its behaviour. – snakecharmerb Jul 13 '22 at 09:38
  • Variables just to be quick and easier to access such tables. Like it would work on a manual mapping. It's easier to say earnings.c.revebue than Metadata.tables[earnjngs].c.revenue – FábioRB Jul 14 '22 at 12:50