Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #%%
- 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()
Add Comment
Please, Sign In to add comment