SQL Alchemy Database Introspection through MetaData

inspect.py

#%%
print "Importing libraries"
import pandas as pd
import sqlalchemy as sa
from sqlalchemy.engine import reflection

#%%
# DB Connection Info 
print "Getting MySQL Connection on DB"
USER = 'someusername'
PASSWORD = 'somepassword'
HOST = 'somehost'
PORT = 3306
DB = 'somedb'
CONNECTION_STRING = "mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>"

#%%
# Connect to DB and get MetaData
print "Connecting to DB and do database reflection with Inspector"
engine = sa.create_engine('mysql+mysqldb://{0}:{1}@{2}:{3}/{4}'.format(USER, PASSWORD, HOST, PORT, DB))
print "Perform database schema inspection"
inspection = reflection.Inspector.from_engine(engine)

#%%
print "Looking at Tables"
table_names = inspection.get_table_names()  # e.g. 'hris_master_solds', 'i_aaar_property'
print table_names

print "Filtering on Specific Table(s)"
filtered_table_names = [_ for _ in table_names if 'r_mhmls_' in _] 
print filtered_table_names  # e.g. r_mhmls_agent_agent, r_mhmls_office_office, r_mhmls_openhouse_ci_6...

#%%
# read_sql_table loads the entire table into memory, no ability to chunk size
print "Looking at a specific Table"
df = pd.read_sql_table('r_mhmls_property_rr_3', engine)
print df.head()


print "Printing MetaData"
metadata = sa.MetaData(engine)
print metadata
print dir(metadata)
print metadata.tables
# Create a MetaData instance
#_table = sa.Table('stark_master', metadata, autoload=True, autoload_with=engine)

#%%
# print MetaData instance
print metadata.tables
stark_master = metadata.tables['stark_master']
#print stark_master
#print dir(my_table)
#print my_table.schema
#print my_table.metadata
#print my_table.columns
#print type(my_table)

#%%
print "Printing Columns"
print type(stark_master)
print stark_master.columns
print stark_master.columns.keys()
print stark_master.metadata
#print dir(stark_master.columns)
#dir(metadata.tables)
#metadata.tables.keys()

#%%
#dir(stark_master)
#print type(df)
#dir(df)
#dir(df.columns)
print df

#%%
print "Inspecting"
inspector = sa.inspect(engine)
print inspector.get_table_names()

#%%
print "Looking at Dataframe"
print df
df.info(verbose=True)
df.dtypes

#%%
df.head()