Python 2.7 – Working with MS-SQL Server databases

Overview

In the previous post Working With MySql databases  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  get a list of all the tables in a database and dump out each of their structures and indexes.

The library we are going to use is pyodbc 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:

pip install pyodbc

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.

PyODBC Usage

The pyODBC package has the following main classes:

  • pyodbc.connect – setups and creates an ODBC connection. Also handles transaction commits
  • pyodbc.cursor – handles the retrieval of any data from a fetch operation.
  • pyodbc.row – the data structure to store the retrieval results from a cursor operation

You can find the detailed documentation here: https://github.com/mkleehammer/pyodbc/wiki/Objects

 

Sample Code

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  does not do things like string escaping or parameterization.

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:

  • SELECT TABLE_NAME FROM <dbname>.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
  • select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='<tablename>’
  • EXEC sp_helpindex ‘[<dbname>].[dbo].[<tablename>]’

import pyodbc

"""
Get list of all tables in the database
"""
def getTableList(conn, dbName):
	sql = "SELECT TABLE_NAME FROM " + dbName + ".INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
	cursor = conn.cursor()
	cursor.execute(sql)
	rows = cursor.fetchall()
	return rows
"""
Get details of all columns in a table
"""	
def getTableInfo(conn, tableName):
	sql = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'"
	cursor = conn.cursor()
	cursor.execute(sql)
	rows = cursor.fetchall()
	return rows
"""
Get info of all indexes created for a table
"""	
def getIndexesInfo(conn, dbName, tableName):
	sql = "EXEC sp_helpindex '[" + dbName + "].[dbo].[" + tableName + "]'"
	cursor = conn.cursor()
	cursor.execute(sql)
	rows = cursor.fetchall()
	return rows
		
#######################################
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=mydb;UID=sa;PWD=xxxxx')
tables = getTableList(conn, "mydb")
print tables
for row in tables:
    print "\n"
    print "========================================"
    print "Table: ", row.TABLE_NAME.upper()
    print "\n...........COLUMNS............................."
    tableRows = getTableInfo(conn, row.TABLE_NAME)
    indexRows = getIndexesInfo(conn, "mydb", row.TABLE_NAME)
    for trow in tableRows:
	    if (trow.IS_NULLABLE == "YES"):
			isNullable = "Null=yes"
	    else:
			isNullable = "Null=no"
	    if trow.CHARACTER_MAXIMUM_LENGTH == None :
			maxLength =""
	    else:
			maxLength = "size=" + str(trow.CHARACTER_MAXIMUM_LENGTH)
	    print trow.COLUMN_NAME.upper(), ",", trow.DATA_TYPE,",", isNullable, ",", maxLength
		
    print "\n..............INDEXES..................."
    for i in indexRows:
        print i.index_name,",", i.index_description,",",i.index_keys	
		
print "DONE"



Some of the output is given below:

========================================
Table: ORDERLOGS

...........COLUMNS.............................
ID , bigint , Null=no ,
ORDERID , bigint , Null=no ,
URL , varchar , Null=yes , size=255
STARTDATE , datetime , Null=yes ,
ENDDATE , datetime , Null=yes ,
PROXYIP , varchar , Null=yes , size=30
PROXYID , bigint , Null=yes ,
RESPONSE , varchar , Null=yes , size=150
ACTUALVIEWDURATION , int , Null=yes ,
REFERER , varchar , Null=yes , size=255

..............INDEXES...................
IX_OrderId , nonclustered located on PRIMARY , OrderId
IX_OrderIdID , nonclustered located on PRIMARY , OrderId, ID
PK_OrderLogs , clustered, unique, primary key located on PRIMARY , ID


========================================
Table: PROXYLIST

...........COLUMNS.............................
ID , bigint , Null=no ,
NAME , varchar , Null=no , size=100
FILEPATH , varchar , Null=no , size=255
USELOGIN , smallint , Null=yes ,

..............INDEXES...................
PK_ProxyList , clustered, unique, primary key located on PRIMARY , ID


========================================
Table: GLOBALSETTINGS

...........COLUMNS.............................
ID , bigint , Null=no ,
PLATFORMS , varchar , Null=yes , size=255
WEBSITETRAFFICREFERERS , text , Null=yes , size=2147483647
YOUTUBEVIEWSEXTERNALREFERERS , text , Null=yes , size=2147483647
BROWSERIE , text , Null=yes , size=2147483647
BROWSERFF , text , Null=yes , size=2147483647
BROWSERCH , text , Null=yes , size=2147483647
BROWSERSF , text , Null=yes , size=2147483647
YTSUGGESTEDATMINTIME , int , Null=yes ,
YTSUGGESTEDATMAXTIME , int , Null=yes ,
YTEXTERNALATMINTIME , int , Null=yes ,
YTEXTERNALATMAXTIME , int , Null=yes ,
YTSUGGESTEDP , int , Null=yes ,
YTEXTERNALP , int , Null=yes ,
YTSEARCHP , int , Null=yes ,
YTPLAYLISTP , int , Null=yes ,
YTOTHERCHANNELSP , int , Null=yes ,
YTBROWSEP , int , Null=yes ,
WEBSITESOCIALMEDIA , text , Null=yes , size=2147483647
ADMINPWD , varchar , Null=yes , size=25

..............INDEXES...................
PK_GlobalSettings , clustered, unique, primary key located on PRIMARY , ID


1 Trackback / Pingback

  1. Python 2.7 – Working with MySQL databases – Truelogic Blog

Leave a Reply

Your email address will not be published.


*