import pandas as pd
import os
import sqlalchemyfrom sqlalchemy import create_engine
from sqlalchemy import inspect
SOURCE_SERVER = os.environ.get('MS SQL SERVER NAME')
SOURCE_DRIVER = os.environ.get('MS SQL SERVER DRIVER')
source_database_name = 'olympics_database'
SOURCE_DRIVER = os.environ.get('MS SQL SERVER DRIVER')
source_database_name = 'olympics_database'
destination_database = os.environ.get('postgresdbpass')
source_database_connection = sqlalchemy.create_engine(f'mssql://{SOURCE_SERVER}/{source_database_name}?driver={SOURCE_DRIVER}')
destination_database_connection = sqlalchemy.create_engine(f'postgresql://postgres:{postgresdbpass}@localhost:5432/olympics_db')
destination_database_connection = sqlalchemy.create_engine(f'postgresql://postgres:{postgresdbpass}@localhost:5432/olympics_db')
df = pd.read_sql("select * from table_name", source_database_connection)
df.to_sql("table_name", destination_database_connection, if_exists = append)
df.to_sql("table_name", destination_database_connection, if_exists = append)
inspector = inspect(source_database_connection)
table_names_list = inspector.get_table_names()
#Above code generates a list of all tables in the database
table_names_list = inspector.get_table_names()
#Above code generates a list of all tables in the database
def extract_and_load(source_conn, destination_conn):
for table in source_conn.table_names_list():
df = pd.read_sql(f'select * from [olympics_database].[dbo].{table}', source_conn)
df.to_sql(f'{table}', destination_conn, if_exists = 'append', index = False)
for table in source_conn.table_names_list():
df = pd.read_sql(f'select * from [olympics_database].[dbo].{table}', source_conn)
df.to_sql(f'{table}', destination_conn, if_exists = 'append', index = False)
extract_and_load(source_database_connection, destination_database_connection)
