{"id":3859,"date":"2024-03-21T11:43:25","date_gmt":"2024-03-21T11:43:25","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3859"},"modified":"2024-03-21T11:43:25","modified_gmt":"2024-03-21T11:43:25","slug":"alternative-way-to-read-data-from-sql-server","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3859","title":{"rendered":"Alternative way to read data from SQL server"},"content":{"rendered":"\n<pre class=\"wp-block-preformatted\">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\nimport math\n\ntable_name = 'payment__c'\n\nconntarget = oracledb.connect(\n    user=\"HR\",\n    password=\"**\",\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;**\"\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+ '\"ID\" CLOB NULL, ' \\\n+ '\"Person_ID__c\" CLOB NULL, ' \\\n+ '\"Indication_Processed__c\" CLOB NULL, ' \\\n+ '\"CreatedById\" CLOB NULL, ' \\\n+ '\"LastModifiedById\" CLOB NULL, ' \\\n+ '\"CreatedDate\" CLOB NULL, ' \\\n+ '\"LastModifiedDate\" CLOB NULL, ' \\\n+ '\"Provider_ID__c\" CLOB NULL ' \\\n+ ')'\nprint(sql_script)\n#query = text(sql_script)\n# Execute the SQL script\ncursortarget.execute(sql_script)\n\ndf.info()\nrow_count = len(df)\nnmbr_cycles = math.floor(row_count \/ 100000) + 1\nprint('aantal cycles ' + str(nmbr_cycles))\n\nfor index, row in df.iterrows():\n    cursortarget.execute('INSERT INTO ' + table_name + '(\"ID\", \"Person_ID__c\", \"Indication_Processed__c\", \"CreatedById\", \"LastModifiedById\", \"CreatedDate\", \"LastModifiedDate\", \"Provider_ID__c\") VALUES (:1, :2, :3, :4, :5, :6, :7, :8)', tuple(row))\n# Commit changes to Oracle database\n    if index % 100000 == 0:\n        print(str(index) + ' Rijen gelezen')\n    if index % 1000 == 0:\n        conntarget.commit()\nconntarget.commit()\n\ncursortarget.close()\n\nprint('*******Prog geeindigd*******')\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 import math table_name = &#8216;payment__c&#8217; conntarget = oracledb.connect( user=&#8221;HR&#8221;, password=&#8221;**&#8221;, dsn=&#8221;192.168.178.6\/orcl&#8221;) cursortarget = conntarget.cursor() zoek_query = &#8220;select count(table_name) from user_tables where lower(table_name)=lower(&#8216;&#8221; + table_name + [&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-3859","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\/3859","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=3859"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3859\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3859"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3859"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3859"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}