Skip to content

Instantly share code, notes, and snippets.

@WilliamQLiu

WilliamQLiu/inspect.py

Created April 10, 2018 18:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save WilliamQLiu/325f71a91658068491b95e4aa5baf9e3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save WilliamQLiu/325f71a91658068491b95e4aa5baf9e3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. WilliamQLiu created this gist Apr 10, 2018.
    88 changes: 88 additions & 0 deletions inspect.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,88 @@
    #%%
    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()