Created
April 10, 2018 18:29
-
-
Save WilliamQLiu/325f71a91658068491b95e4aa5baf9e3 to your computer and use it in GitHub Desktop.
SQL Alchemy Database Introspection through MetaData
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#%% | |
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment