{"id":3843,"date":"2024-03-07T11:54:20","date_gmt":"2024-03-07T11:54:20","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3843"},"modified":"2024-03-07T11:54:20","modified_gmt":"2024-03-07T11:54:20","slug":"met-python-van-sql-server-naar-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3843","title":{"rendered":"Met Python van SQL Server naar Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Plse find enclosed a script to write records from SQL Server to Oracle:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import pyodbc\nimport datetime\n\nconn = pyodbc.connect('DSN=SQLServer;UID=sa;PWD=**')\nconntarget = pyodbc.connect('DSN=Rekendoos;UID=hr;PWD=**')\ntable_name = 'MIGRATED_DIVORCE_SETTLEMENT__C'\n\ncursor = conn.cursor()\ncursortarget = conntarget.cursor()\n\ncursor.execute('select * from Rapportage.' + tabel)\ncolumns = [column[0] for column in cursor.description]\nkolommen = ''\nfor x in range(len(columns) - 1):\n    kolommen = kolommen + columns[x] + ', '\nkolommen = kolommen + columns[len(columns) - 1]\nresults = [columns] + [row for row in cursor.fetchall()]\n\nzoek_query = \"select count(table_name) from user_tables where lower(table_name)=lower('\" + table_name + \"')\"\ncursortarget.execute(zoek_query)\nif cursortarget.fetchone()[0]== 1:\n    cursortarget.execute(\"drop table \" + table_name )\n\nstmt = 'CREATE TABLE \"HR\".\"MIGRATED_DIVORCE_SETTLEMENT__C\" ' \\\n+   '(\t\"ID\" CLOB, ' \\\n+   '\t\"BPP65_AMOUNT__C\" NUMBER,' \\\n+   '\t\"BPP67_AMOUNT__C\" NUMBER, ' \\\n+   '\t\"BPP67_VPL_AMOUNT__C\" NUMBER, ' \\\n+   '\t\"END_DATE_SETTLEMENT_PERIOD__C\" DATE, ' \\\n+   '\t\"PENSION_SCHEME_ID__C\" CLOB, ' \\\n+   '\t\"PERSON_ID_1__C\" CLOB, ' \\\n+   '\t\"PERSON_ID_2__C\" CLOB, ' \\\n+   '\t\"PROVIDER_ID__C\" CLOB, ' \\\n+   '\t\"REGISTRATION_DATE__C\" DATE, ' \\\n+   '\t\"SEND_DATE_TO_CORE__C\" DATE, ' \\\n+   '\t\"SETTLEMENT_DATE__C\" DATE, ' \\\n+   '\t\"START_DATE_RELATIONSHIP__C\" DATE,' \\\n+   '\t\"START_DATE_SETTLEMENT__C\" CLOB,' \\\n+   '\t\"VERSION_NUMBER_SETTLEMENT__C\" CLOB, ' \\\n+   '\t\"VOP65_AMOUNT__C\" NUMBER, ' \\\n+   '\t\"VOP67_AMOUNT__C\" NUMBER, ' \\\n+   '\t\"VOP67_VPL_AMOUNT__C\" NUMBER,' \\\n+   '\t\"VPREP__C\" NUMBER, ' \\\n+   '\t\"VOPHOOG__C\" NUMBER, ' \\\n+   '\t\"VTOP_TOT_AOW__C\" NUMBER, ' \\\n+   '\t\"CREATEDBYID\" CLOB, ' \\\n+   '\t\"CREATEDDATE\" CLOB, ' \\\n+   '\t\"LASTMODIFIEDBYID\" CLOB, ' \\\n+   '\t\"LASTMODIFIEDDATE\" CLOB' \\\n+   '   ) '\ncursortarget.execute(stmt)\n\nfor i in range(1,len(results)):\n    Sql_insert_query1 = \"INSERT INTO \" + table_name + \"  (\" + kolommen + \") VALUES (\" \n    Sql_insert_query2 = ''\n    for j in range(len(columns)):\n        uitkomst = results[i][j]\n        if isinstance(uitkomst, str):\n            uitkomst = uitkomst.replace(\"'\",\" \") \n            uitkomst = \"\\'\" + uitkomst + \"\\'\"\n        if uitkomst is None:\n            uitkomst = ''\n            uitkomst = \"\\'\" + uitkomst + \"\\'\"\n        if isinstance(uitkomst, (int, float)):\n            uitkomst = str(uitkomst)\n        if isinstance(uitkomst, (datetime.date, datetime.datetime) ):\n#            print(str(j) + 'date')\n            uitkomst = str(uitkomst)\n            uitkomst = uitkomst[0:10]\n            uitkomst = \"to_date('\" + uitkomst + \"','yyyy-mm-dd')\"\n        Sql_insert_query2 = Sql_insert_query2  + uitkomst \n        if j &lt; int(len(columns)) - 1:\n            Sql_insert_query2 = Sql_insert_query2 + \", \"\n    Sql_insert_queryn =  \")\"\n    Sql_insert_query = Sql_insert_query1+Sql_insert_query2+Sql_insert_queryn\n    if i % 1000 == 0:\n        print(str(i) + ' Rijen gelezen')\n        conntarget.commit()\n#    print(Sql_insert_query)\n    cursortarget.execute(Sql_insert_query)\nconntarget.commit()\n\nprint('Lezen gereed')\ncursor.close()\nconntarget.close()\n\n\n\n\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here, the results are written to a list.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Plse find enclosed a script to write records from SQL Server to Oracle: import pyodbc import datetime conn = pyodbc.connect(&#8216;DSN=SQLServer;UID=sa;PWD=**&#8217;) conntarget = pyodbc.connect(&#8216;DSN=Rekendoos;UID=hr;PWD=**&#8217;) table_name = &#8216;MIGRATED_DIVORCE_SETTLEMENT__C&#8217; cursor = conn.cursor() cursortarget = conntarget.cursor() cursor.execute(&#8216;select * from Rapportage.&#8217; + tabel) columns = [column[0] for column in cursor.description] kolommen = &#8221; for x in range(len(columns) &#8211; 1): kolommen [&hellip;]<\/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-3843","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\/3843","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=3843"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3843\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3843"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}