def execute_sql_statement(sql):
'''
execute the sql queries to insert new records, update existing information,
and mark expired records
'''
warehouse = PostgresHook(postgres_conn_id='warehouse').get_conn()
cursor = warehouse.cursor()
cursor.execute(sql)
warehouse.commit()
warehouse.close()
def drop_api_table(schema, table):
'''
drop the api table that contains the updated wmata information
'''
warehouse = PostgresHook(postgres_conn_id='warehouse').get_conn()
cursor = warehouse.cursor()
cursor.execute('''DROP TABLE IF EXISTS {0}.{1}_api;'''.format(
schema, table))
warehouse.commit()
warehouse.close()
def write_api_table(data, schema, table):
'''
write a temporary table to the database,using the temporary table to execute
SQL statements (@ cursor.execute(sql)), and then dropping the temporary table.
'''
sql_hook = PostgresHook(postgres_conn_id='warehouse')
warehouse = PostgresHook(postgres_conn_id='warehouse').get_conn()
cursor = warehouse.cursor()
cursor.execute('''DROP TABLE IF EXISTS {0}.{1}_api;'''.format(
schema, table))
warehouse.commit()
data.to_sql(schema=schema,
name=table + '_api',
index=False,
con=sql_hook.get_sqlalchemy_engine())
warehouse.close()