0

I successfully connected Python to my MySQL database with from sqlalchemy import create_engine.

With this engineI am able to get the data as wanted etc.

I am using table_names = engine.table_names() to get all the table names in the database.

What I ultimately need is the table information like #of entries, updated_time, etc.

I could do all of that with selects but I was wondering if there is also a handy function like table_names()to do that?

Here is the query that I would like to replace by something more handy:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_NAME = 'my_table'
mgruber
  • 751
  • 1
  • 9
  • 26

1 Answers1

1

You could use an Inspector object:

from sqlalchemy import create_engine
from sqlalchemy.engine import reflection

"""
initialize engine
"""

inspector = reflection.Inspector.from_engine(engine)
for table in inspector.get_table_names():
    print(inspector.get_columns(table))

Here is documentation to the reflection.

To extract values returned by Inspector you'll need to iterate over columns:

for column in inspector.get_columns(table):
    print(column['name'])
    print(column['type'])
    print(column['nullable'])
Alexandra Dudkina
  • 4,302
  • 3
  • 15
  • 27
  • Thanks for the answer. This might bring me one step closer to what I need. I know see the available information like `{'name': 'updated_at', 'type': TIMESTAMP(), 'default': None, 'comment': None, 'nullable': True}` but how do I get the value for that? – mgruber Aug 26 '20 at 08:53
  • Inspector get_column() returns list of dictionaries (dictionary per column), so you can iterate over the list and extract values by keys: name, type, default, nullable, – Alexandra Dudkina Aug 26 '20 at 08:59
  • Got the point about iterating over the list of dictionaries. But how do I extract the value then? – mgruber Aug 26 '20 at 09:02
  • 2
    I updated the answer with an example of value extraction. Please consider, that Inspector has many other convenient methods: get_foreign_keys(), get_indexes(), get_pk_constraint() etc. – Alexandra Dudkina Aug 26 '20 at 09:10
  • Okay I think I was miscommunicating here. I don´t need to get the "metadata" for the columns in each table. I need the metadata of the table itself. The select I provided above is giving me the time when the table itself got updated the last time – mgruber Aug 26 '20 at 09:42
  • For table metadata you could try get_table_options() method. But I'm afraid it would be difficult to get last update time of a particular query without executing queries to some system tables. For MySQL examples of such queries can be found here: https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated – Alexandra Dudkina Aug 26 '20 at 09:56