{"id":3301,"date":"2020-02-16T16:35:51","date_gmt":"2020-02-16T15:35:51","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3301"},"modified":"2020-02-16T16:35:51","modified_gmt":"2020-02-16T15:35:51","slug":"using-python-to-read-and-write-in-a-sql-server-database","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3301","title":{"rendered":"Using Python to read and write in a SQL Server database"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Below, I provide a snippet to read data from an Azure SQL Server database. Subsequently, it is written to another table in the same database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The trick here is to read the data, store them in a matrix which will be written out to a second table. Elements within the matrix can be accessed with results[i][j].<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import pyodbc \nconn = pyodbc.connect('DSN=AzureSQL;UID=tomvanmaanen;PWD=********')\ndata = []\ncursor = conn.cursor()\ncursor.execute('SELECT [CODE],[DESCRIPTION],[START_DATE],[END_DATE] FROM STAGINGZONE.T01_brp_country_code')\ncolumns = [column[0] for column in cursor.description]\nresults = [columns] + [row for row in cursor.fetchall()]\nfor x in range(len(results)):\n   Sql_insert_query = \"INSERT INTO dbo.ff (test) VALUES ('\" + results[x][1].replace(\"'\", \" \") + \"')\"\n   cursor.execute(Sql_insert_query)\nconn.commit()\ncursor.close()<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Below, I provide a snippet to read data from an Azure SQL Server database. Subsequently, it is written to another table in the same database. The trick here is to read the data, store them in a matrix which will be written out to a second table. Elements within the matrix can be accessed with [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3301","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3301","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=3301"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3301\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3301"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}