Use Python to transport data

An alternative to a previous post is the programme below. This uses Pandas dataframe to transport data.

import pandas as pd
from sqlalchemy.engine import create_engine
from sqlalchemy.engine import URL
from sqlalchemy.types import String, Date, DateTime
from sqlalchemy import text
import sqlalchemy as sa
import pyodbc
import oracledb


table_name = 'migrated_pension_accrual__c'

conntarget = oracledb.connect(
    user="HR",
    password="AAaa11!!",
    dsn="192.168.178.6/orcl")

cursortarget = conntarget.cursor()
zoek_query = "select count(table_name) from user_tables where lower(table_name)=lower('" + table_name + "')"
print(zoek_query)
cursortarget.execute(zoek_query)
if cursortarget.fetchone()[0] >  0:
    cursortarget.execute("drop table " + table_name )

connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-8J58OIP;DATABASE=Speel;UID=sa;PWD=AAaa11!!"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
engine = create_engine(connection_url)
with engine.begin() as conn:
    df = pd.read_sql_query(sa.text("SELECT * FROM Rapportage." + table_name), conn)


sql_script = 'CREATE TABLE ' + table_name + ' (' \
+ '"index" NUMBER(19,0), ' \
+ '"Id" CLOB NULL, '  \
+ '"Accrued_OP65__c" NUMBER NULL, ' \
+ '"Accrued_OP67_VPL__c" NUMBER NULL, ' \
+ '"Accrued_OP67__c" NUMBER NULL, ' \
+ '"Accrued_PP65__c" NUMBER NULL, ' \
+ '"Accrued_PP67_VPL__c" NUMBER NULL, ' \
+ '"Accrued_PP67__c" NUMBER NULL, ' \
+ '"Accrued_WzP65__c" NUMBER NULL, ' \
+ '"Accrued_WzP67__c" NUMBER NULL, ' \
+ '"ANW__c" NUMBER NULL, ' \
+ '"Base_parttime_percentage__c" CLOB NULL, ' \
+ '"CAP__c" NUMBER NULL, ' \
+ '"COP65__c" NUMBER NULL, ' \
+ '"COP67__c" NUMBER NULL, ' \
+ '"Employer_ID__c" CLOB NULL, ' \
+ '"Number_IKV__c" CLOB NULL, ' \
+ '"OP67exc__c" NUMBER NULL, ' \
+ '"OOP__c" NUMBER NULL, ' \
+ '"Pension_base_disability__c" CLOB NULL, ' \
+ '"Pension_scheme_ID__c" CLOB NULL, ' \
+ '"Person_ID__c" NUMBER NULL, ' \
+ '"PP67exc__c" NUMBER NULL, ' \
+ '"PP67Risk__c" NUMBER NULL, ' \
+ '"PREP_age__c" NUMBER NULL, ' \
+ '"PREP__c" NUMBER NULL, ' \
+ '"Provider_ID__c" NUMBER NULL, ' \
+ '"Reason_of_change__c" CLOB NULL, ' \
+ '"Registration_date__c" DATE NULL, ' \
+ '"Send_date_to_core__c" DATE NULL, ' \
+ '"Sequence_number_employment__c" CLOB NULL, ' \
+ '"Sequence_number_part__c" NUMBER NULL, ' \
+ '"Savings_amount__c" NUMBER NULL, ' \
+ '"Version_number_migrated__c" NUMBER NULL, ' \
+ '"WZP67exc__c" NUMBER NULL, ' \
+ '"WZPRisk__c" NUMBER NULL, ' \
+ '"Type_of_VPL__c" CLOB NULL, ' \
+ '"Pension_base_continuation__c" CLOB NULL, ' \
+ '"Value_PP67_Risc_base__c" NUMBER NULL, ' \
+ '"CreatedById" CLOB NULL, ' \
+ '"CreatedDate" CLOB NULL, ' \
+ '"LastModifiedById" CLOB NULL, ' \
+ '"LastModifiedDate" CLOB NULL ' \
+ ')'
print(sql_script)
#query = text(sql_script)
# Execute the SQL script
cursortarget.execute(sql_script)
cursortarget.close()

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'hr' 
PASSWORD = 'AAaa11!!' 
HOST = '192.168.178.6' 
PORT = 1521 
SERVICE = 'orcl' 
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
print(ENGINE_PATH_WIN_AUTH)
engine = create_engine(ENGINE_PATH_WIN_AUTH, echo=False)

df.info()
row_count = len(df)
nmbr_cycles = math.floor(row_count / 100000) + 1
print('aantal cycles ' + str(nmbr_cycles))

for i in range(0,nmbr_cycles):
    rijtje = df.iloc[i * 100000:(i + 1 ) * 100000, 0:len(df.columns)]
    print('cyclus ' + str(i))
#    print(rijtje.iloc[:2,])
    rijtje.to_sql(name=table_name, con=engine, if_exists='append', schema='HR')