An alternative to a previous post is the programme below. This uses Pandas dataframe to transport data.<\/p>\n\n\n\n
import pandas as pd\nfrom sqlalchemy.engine import create_engine\nfrom sqlalchemy.engine import URL\nfrom sqlalchemy.types import String, Date, DateTime\nfrom sqlalchemy import text\nimport sqlalchemy as sa\nimport pyodbc\nimport oracledb\n\n\ntable_name = 'migrated_pension_accrual__c'\n\nconntarget = oracledb.connect(\n user=\"HR\",\n password=\"AAaa11!!\",\n dsn=\"192.168.178.6\/orcl\")\n\ncursortarget = conntarget.cursor()\nzoek_query = \"select count(table_name) from user_tables where lower(table_name)=lower('\" + table_name + \"')\"\nprint(zoek_query)\ncursortarget.execute(zoek_query)\nif cursortarget.fetchone()[0] > 0:\n cursortarget.execute(\"drop table \" + table_name )\n\nconnection_string = \"DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-8J58OIP;DATABASE=Speel;UID=sa;PWD=AAaa11!!\"\nconnection_url = URL.create(\"mssql+pyodbc\", query={\"odbc_connect\": connection_string})\nengine = create_engine(connection_url)\nwith engine.begin() as conn:\n df = pd.read_sql_query(sa.text(\"SELECT * FROM Rapportage.\" + table_name), conn)\n\n\nsql_script = 'CREATE TABLE ' + table_name + ' (' \\\n+ '\"index\" NUMBER(19,0), ' \\\n+ '\"Id\" CLOB NULL, ' \\\n+ '\"Accrued_OP65__c\" NUMBER NULL, ' \\\n+ '\"Accrued_OP67_VPL__c\" NUMBER NULL, ' \\\n+ '\"Accrued_OP67__c\" NUMBER NULL, ' \\\n+ '\"Accrued_PP65__c\" NUMBER NULL, ' \\\n+ '\"Accrued_PP67_VPL__c\" NUMBER NULL, ' \\\n+ '\"Accrued_PP67__c\" NUMBER NULL, ' \\\n+ '\"Accrued_WzP65__c\" NUMBER NULL, ' \\\n+ '\"Accrued_WzP67__c\" NUMBER NULL, ' \\\n+ '\"ANW__c\" NUMBER NULL, ' \\\n+ '\"Base_parttime_percentage__c\" CLOB NULL, ' \\\n+ '\"CAP__c\" NUMBER NULL, ' \\\n+ '\"COP65__c\" NUMBER NULL, ' \\\n+ '\"COP67__c\" NUMBER NULL, ' \\\n+ '\"Employer_ID__c\" CLOB NULL, ' \\\n+ '\"Number_IKV__c\" CLOB NULL, ' \\\n+ '\"OP67exc__c\" NUMBER NULL, ' \\\n+ '\"OOP__c\" NUMBER NULL, ' \\\n+ '\"Pension_base_disability__c\" CLOB NULL, ' \\\n+ '\"Pension_scheme_ID__c\" CLOB NULL, ' \\\n+ '\"Person_ID__c\" NUMBER NULL, ' \\\n+ '\"PP67exc__c\" NUMBER NULL, ' \\\n+ '\"PP67Risk__c\" NUMBER NULL, ' \\\n+ '\"PREP_age__c\" NUMBER NULL, ' \\\n+ '\"PREP__c\" NUMBER NULL, ' \\\n+ '\"Provider_ID__c\" NUMBER NULL, ' \\\n+ '\"Reason_of_change__c\" CLOB NULL, ' \\\n+ '\"Registration_date__c\" DATE NULL, ' \\\n+ '\"Send_date_to_core__c\" DATE NULL, ' \\\n+ '\"Sequence_number_employment__c\" CLOB NULL, ' \\\n+ '\"Sequence_number_part__c\" NUMBER NULL, ' \\\n+ '\"Savings_amount__c\" NUMBER NULL, ' \\\n+ '\"Version_number_migrated__c\" NUMBER NULL, ' \\\n+ '\"WZP67exc__c\" NUMBER NULL, ' \\\n+ '\"WZPRisk__c\" NUMBER NULL, ' \\\n+ '\"Type_of_VPL__c\" CLOB NULL, ' \\\n+ '\"Pension_base_continuation__c\" CLOB NULL, ' \\\n+ '\"Value_PP67_Risc_base__c\" NUMBER NULL, ' \\\n+ '\"CreatedById\" CLOB NULL, ' \\\n+ '\"CreatedDate\" CLOB NULL, ' \\\n+ '\"LastModifiedById\" CLOB NULL, ' \\\n+ '\"LastModifiedDate\" CLOB NULL ' \\\n+ ')'\nprint(sql_script)\n#query = text(sql_script)\n# Execute the SQL script\ncursortarget.execute(sql_script)\ncursortarget.close()\n\nDIALECT = 'oracle'\nSQL_DRIVER = 'cx_oracle'\nUSERNAME = 'hr' \nPASSWORD = 'AAaa11!!' \nHOST = '192.168.178.6' \nPORT = 1521 \nSERVICE = 'orcl' \nENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + ':\/\/' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '\/?service_name=' + SERVICE\nprint(ENGINE_PATH_WIN_AUTH)\nengine = create_engine(ENGINE_PATH_WIN_AUTH, echo=False)\n\ndf.info()\nrow_count = len(df)\nnmbr_cycles = math.floor(row_count \/ 100000) + 1\nprint('aantal cycles ' + str(nmbr_cycles))\n\nfor i in range(0,nmbr_cycles):\n rijtje = df.iloc[i * 100000:(i + 1 ) * 100000, 0:len(df.columns)]\n print('cyclus ' + str(i))\n# print(rijtje.iloc[:2,])\n rijtje.to_sql(name=table_name, con=engine, if_exists='append', schema='HR')\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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!!”, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-3853","post","type-post","status-publish","format-standard","hentry","category-niet-gecategoriseerd"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3853","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3853"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3853\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3853"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3853"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}