sqlserver to postgress

import pandas as pd

import os

import sqlalchemy
from 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'


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')

df = pd.read_sql("select * from table_name", source_database_connection)
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

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)

extract_and_load(source_database_connection, destination_database_connection)


Comments

Popular posts from this blog

Flutter for Single-Page Scrollable Websites with Navigator 2.0

A Data Science Portfolio is More Valuable than a Resume

Better File Storage in Oracle Cloud