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
Leave a Reply