{"id":3184,"date":"2018-07-04T03:24:40","date_gmt":"2018-07-04T03:24:40","guid":{"rendered":"http:\/\/truelogic.org\/wordpress\/?p=3184"},"modified":"2018-07-04T03:24:40","modified_gmt":"2018-07-04T03:24:40","slug":"python-2-7-working-with-ms-sql-server-databases","status":"publish","type":"post","link":"https:\/\/truelogic.org\/wordpress\/2018\/07\/04\/python-2-7-working-with-ms-sql-server-databases\/","title":{"rendered":"Python 2.7 &#8211; Working with MS-SQL Server databases"},"content":{"rendered":"            <script type=\"text\/javascript\" src=\"https:\/\/truelogic.org\/wordpress\/wp-content\/plugins\/wordpress-code-snippet\/scripts\/shBrushPython.js\"><\/script>\n<h3>Overview<\/h3>\n<p>In the previous post <a href=\"https:\/\/truelogic.org\/wordpress\/2018\/06\/26\/python-2-7-working-with-mysql-databases\/\" target=\"_blank\" rel=\"noopener\">Working With MySql databases\u00a0<\/a> we saw how Python can be used to work with databases. This post is going to be very similar to that post in the sense that the sample code here will\u00a0 get a list of all the tables in a database and dump out each of their structures and indexes.<\/p>\n<p>The library we are going to use is <strong>pyodbc <\/strong>which lets Python work with ODBC databases. It is assumed that your SQL Server databases has an ODBC connection set up in the hosted Windows computer. To install pyodbc use pip:<\/p>\n<pre>pip install pyodbc\r\n<\/pre>\n<p>There is also another library called pymssql which lets Python work with MS SQL Server but that requires an additional dependency called FreeTDS which has to separately installed for Windows. PyODBC does not need anything extra and works straight out of the box.<\/p>\n<h3>PyODBC Usage<\/h3>\n<p>The pyODBC package has the following main classes:<\/p>\n<ul>\n<li>pyodbc.connect &#8211; setups and creates an ODBC connection. Also handles transaction commits<\/li>\n<li>pyodbc.cursor &#8211; handles the retrieval of any data from a fetch operation.<\/li>\n<li>pyodbc.row &#8211; the data structure to store the retrieval results from a cursor operation<\/li>\n<\/ul>\n<p>You can find the detailed documentation here: <a href=\"https:\/\/github.com\/mkleehammer\/pyodbc\/wiki\/Objects\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/mkleehammer\/pyodbc\/wiki\/Objects<\/a><\/p>\n<p>&nbsp;<\/p>\n<h3>Sample Code<\/h3>\n<p>Given below is sample code which shows how to interact with MS SQL Server. Basically it is your responsibility to create the SQL queries and sanitize the data before passing it to the server since pyodbc\u00a0 does not do things like string escaping or parameterization.<\/p>\n<p>What we have below is code which takes in a database connection and dumps out the details of all the tables within that database. It executes and parses the following SQL commands:<\/p>\n<ul>\n<li><span style=\"color: #808080;\">SELECT TABLE_NAME FROM &lt;dbname&gt;.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = &#8216;BASE TABLE&#8217;<\/span><\/li>\n<li><span style=\"color: #808080;\">select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='&lt;tablename&gt;&#8217;<\/span><\/li>\n<li><span style=\"color: #808080;\">EXEC sp_helpindex &#8216;[&lt;dbname&gt;].[dbo].[&lt;tablename&gt;]&#8217;<\/span><\/li>\n<\/ul>\n<p><pre class=\"brush: python\">import pyodbc\r\n\r\n&quot;&quot;&quot;\r\nGet list of all tables in the database\r\n&quot;&quot;&quot;\r\ndef getTableList(conn, dbName):\r\n\tsql = &quot;SELECT TABLE_NAME FROM &quot; + dbName + &quot;.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = &#039;BASE TABLE&#039;&quot;\r\n\tcursor = conn.cursor()\r\n\tcursor.execute(sql)\r\n\trows = cursor.fetchall()\r\n\treturn rows\r\n&quot;&quot;&quot;\r\nGet details of all columns in a table\r\n&quot;&quot;&quot;\t\r\ndef getTableInfo(conn, tableName):\r\n\tsql = &quot;select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=&#039;&quot; + tableName + &quot;&#039;&quot;\r\n\tcursor = conn.cursor()\r\n\tcursor.execute(sql)\r\n\trows = cursor.fetchall()\r\n\treturn rows\r\n&quot;&quot;&quot;\r\nGet info of all indexes created for a table\r\n&quot;&quot;&quot;\t\r\ndef getIndexesInfo(conn, dbName, tableName):\r\n\tsql = &quot;EXEC sp_helpindex &#039;[&quot; + dbName + &quot;].[dbo].[&quot; + tableName + &quot;]&#039;&quot;\r\n\tcursor = conn.cursor()\r\n\tcursor.execute(sql)\r\n\trows = cursor.fetchall()\r\n\treturn rows\r\n\t\t\r\n#######################################\r\nconn = pyodbc.connect(&#039;DRIVER={SQL Server};SERVER=localhost;DATABASE=mydb;UID=sa;PWD=xxxxx&#039;)\r\ntables = getTableList(conn, &quot;mydb&quot;)\r\nprint tables\r\nfor row in tables:\r\n    print &quot;\\n&quot;\r\n    print &quot;========================================&quot;\r\n    print &quot;Table: &quot;, row.TABLE_NAME.upper()\r\n    print &quot;\\n...........COLUMNS.............................&quot;\r\n    tableRows = getTableInfo(conn, row.TABLE_NAME)\r\n    indexRows = getIndexesInfo(conn, &quot;mydb&quot;, row.TABLE_NAME)\r\n    for trow in tableRows:\r\n\t    if (trow.IS_NULLABLE == &quot;YES&quot;):\r\n\t\t\tisNullable = &quot;Null=yes&quot;\r\n\t    else:\r\n\t\t\tisNullable = &quot;Null=no&quot;\r\n\t    if trow.CHARACTER_MAXIMUM_LENGTH == None :\r\n\t\t\tmaxLength =&quot;&quot;\r\n\t    else:\r\n\t\t\tmaxLength = &quot;size=&quot; + str(trow.CHARACTER_MAXIMUM_LENGTH)\r\n\t    print trow.COLUMN_NAME.upper(), &quot;,&quot;, trow.DATA_TYPE,&quot;,&quot;, isNullable, &quot;,&quot;, maxLength\r\n\t\t\r\n    print &quot;\\n..............INDEXES...................&quot;\r\n    for i in indexRows:\r\n        print i.index_name,&quot;,&quot;, i.index_description,&quot;,&quot;,i.index_keys\t\r\n\t\t\r\nprint &quot;DONE&quot;\r\n\r\n\r\n<\/pre><br \/>\nSome of the output is given below:<\/p>\n<pre>========================================\r\nTable: ORDERLOGS\r\n\r\n...........COLUMNS.............................\r\nID , bigint , Null=no ,\r\nORDERID , bigint , Null=no ,\r\nURL , varchar , Null=yes , size=255\r\nSTARTDATE , datetime , Null=yes ,\r\nENDDATE , datetime , Null=yes ,\r\nPROXYIP , varchar , Null=yes , size=30\r\nPROXYID , bigint , Null=yes ,\r\nRESPONSE , varchar , Null=yes , size=150\r\nACTUALVIEWDURATION , int , Null=yes ,\r\nREFERER , varchar , Null=yes , size=255\r\n\r\n..............INDEXES...................\r\nIX_OrderId , nonclustered located on PRIMARY , OrderId\r\nIX_OrderIdID , nonclustered located on PRIMARY , OrderId, ID\r\nPK_OrderLogs , clustered, unique, primary key located on PRIMARY , ID\r\n\r\n\r\n========================================\r\nTable: PROXYLIST\r\n\r\n...........COLUMNS.............................\r\nID , bigint , Null=no ,\r\nNAME , varchar , Null=no , size=100\r\nFILEPATH , varchar , Null=no , size=255\r\nUSELOGIN , smallint , Null=yes ,\r\n\r\n..............INDEXES...................\r\nPK_ProxyList , clustered, unique, primary key located on PRIMARY , ID\r\n\r\n\r\n========================================\r\nTable: GLOBALSETTINGS\r\n\r\n...........COLUMNS.............................\r\nID , bigint , Null=no ,\r\nPLATFORMS , varchar , Null=yes , size=255\r\nWEBSITETRAFFICREFERERS , text , Null=yes , size=2147483647\r\nYOUTUBEVIEWSEXTERNALREFERERS , text , Null=yes , size=2147483647\r\nBROWSERIE , text , Null=yes , size=2147483647\r\nBROWSERFF , text , Null=yes , size=2147483647\r\nBROWSERCH , text , Null=yes , size=2147483647\r\nBROWSERSF , text , Null=yes , size=2147483647\r\nYTSUGGESTEDATMINTIME , int , Null=yes ,\r\nYTSUGGESTEDATMAXTIME , int , Null=yes ,\r\nYTEXTERNALATMINTIME , int , Null=yes ,\r\nYTEXTERNALATMAXTIME , int , Null=yes ,\r\nYTSUGGESTEDP , int , Null=yes ,\r\nYTEXTERNALP , int , Null=yes ,\r\nYTSEARCHP , int , Null=yes ,\r\nYTPLAYLISTP , int , Null=yes ,\r\nYTOTHERCHANNELSP , int , Null=yes ,\r\nYTBROWSEP , int , Null=yes ,\r\nWEBSITESOCIALMEDIA , text , Null=yes , size=2147483647\r\nADMINPWD , varchar , Null=yes , size=25\r\n\r\n..............INDEXES...................\r\nPK_GlobalSettings , clustered, unique, primary key located on PRIMARY , ID\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Overview In the previous post Working With MySql databases\u00a0 we saw how Python can be used to work with databases. This post is going to <a class=\"mh-excerpt-more\" href=\"https:\/\/truelogic.org\/wordpress\/2018\/07\/04\/python-2-7-working-with-ms-sql-server-databases\/\" title=\"Python 2.7 &#8211; Working with MS-SQL Server databases\">[&#8230;]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":2107,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,297],"tags":[],"class_list":["post-3184","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming","category-python"],"_links":{"self":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/comments?post=3184"}],"version-history":[{"count":7,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3184\/revisions"}],"predecessor-version":[{"id":3191,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3184\/revisions\/3191"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media\/2107"}],"wp:attachment":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media?parent=3184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/categories?post=3184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/tags?post=3184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}