Connecting to MSSQL from Fedora (and then Python 2.7)

source: http://blog.mdda.net/oss/2015/02/11/mssql-from-fedora-with-python/

With a heavy heart, I find myself having to talk to an MSSQL database. Fortunately, I can do this from a Linux (Fedora) VPS, so all is not lost.
For the following write-up (which are really just notes to myself), these links were helpful :

• ububtu instructions - clear but not directly applicable
• Helpful hints from Stackoverflow
• Fedora notes - wrong target language, though

Step 1 : Check there’s no firewall in the way

Try the port to check that the response differs from one with no server sitting on it :

telnet 192.168.x.y 1433

Step 2 : Install FreeTDS

The FreeTDS package is the one that Fedora uses :

yum install freetds freetds-devel unixODBC unixODBC-devel

Step 3 : Check that simple queries run (command line - direct)

Using the tsql utility (from FreeTDS), test that the basic connection works (each SQL command needs to be followed by ‘go’ on a separate line to get it to execute) :

tsql -H hostname.of.the.server -p 1433 -U username-for-db
#(enter password here not on command line to avoid it appearing in history, or in process list)

When the tsql prompt comes up,

# Do a fully-qualified query 
select count(*) from DATABASENAME.dbo.TABLENAME

# Do the same, drilled down
use DATABASENAME
select count(*) from TABLENAME

# Get a listing of the tables available
SELECT * FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE'

Step 4 : Check that simple queries run (command line - named server)

In order to connect to the database by ‘name’, add it as an entry into /etc/freetds.conf :

[arbitrary-tds-server-title]
  host = hostname.of.the.server
  port = 1433
  tds version = 7.0

Then queries can be run using the given name (which will then be able to pick out the appropriate hostname and port from the configuration file) :

tsql -S arbitrary-tds-server-title -U username-for-db
#(enter password, not-in-history)

Step 5 : Set up ODBC configurations

Firstly, find the driver locations (on disk!) to put into /etc/odbcinst.ini :

find / -iname 'libtds*.so'

Then, create a suitable entry in /etc/odbcinst.ini, so that ODBC know to talk to the FreeTDS drivers :

[FreeTDS]
Description = MS SQL database access with Free TDS
Driver64    = /usr/lib64/libtdsodbc.so
Setup64     = /usr/lib64/libtdsS.so
FileUsage   = 1

Then, create an entry in /etc/odbc.ini (which may have to be created) :

[sqlserverdatasource-name-is-arbitrary]
Driver      = FreeTDS
Description = ODBC connection via FreeTDS
Trace       = No
Servername  = arbitrary-tds-server-title
#Database    = <name of your database - may be useful to restrict usage>

Step 6 : Set up Python connection to ODBC

yum install pyodbc

And then one can use it in the Python shell :

python
>>> import pyodbc
>>> dsn='sqlserverdatasource-name-is-arbitrary'
>>> user='username-for-db'
>>> password='XXXXXXXX'
>>> database='DATABASENAME'
>>> con_string='DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
>>> cnxn = pyodbc.connect(con_string)
>>> cursor = cnxn.cursor()
>>> cursor.execute("select count(*) from TABLENAME")
<pyodbc.Cursor object at 0x7fe4fd2a0b10>
>>> row=cursor.fetchone()
>>> row
(249619, )

Step 7 : Read up on ODBC databases in Python

For more, see the PyODBC getting started guide.

https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/

This topic presents a code sample written in Python. The sample runs on a Windows computer. The sample and connects to Azure SQL Database by using the pymssql driver.

Requirements

• Python 2.7.6

Install the required modules

Install pymssql.
Make sure you choose the correct whl file.
For example : If you are using Python 2.7 on a 64 bit machine choose : pymssql‑2.1.1‑cp27‑none‑win_amd64.whl. Once you download the .whl file place it in the the C:/Python27 folder.
Now install the pymssql driver using pip from command line. cd into C:/Python27 and run the following

pip install pymssql‑2.1.1‑cp27‑none‑win_amd64.whl

Instructions to enable the use pip can be found here

Create a database and retrieve your connection string

See the Getting Started Topic to learn how to create a sample database and retrieve your connection string. It is important you follow the guide to create an AdventureWorks database template. The samples shown below only work with the AdventureWorks schema.

Connect to your SQL Database

The pymssql.connect function is used to connect to SQL Database.

import pymssql
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')

Execute an SQL SELECT statement

The cursor.execute function can be used to retrieve a result set from a query against SQL Database. This function essentially accepts any query and returns a result set which can be iterated over with the use of cursor.fetchone().

import pymssql
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')
cursor = conn.cursor()
cursor.execute('SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;')
row = cursor.fetchone()
while row:
    print str(row[0]) + " " + str(row[1]) + " " + str(row[2])   
    row = cursor.fetchone()

Insert a row, pass parameters, and retrieve the generated primary key

In SQL Database the IDENTITY property and the SEQUENCE object can be used to auto-generate primary key values.

import pymssql
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')
cursor = conn.cursor()
cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express', 'SQLEXPRESS', 0, 0, CURRENT_TIMESTAMP)")
row = cursor.fetchone()
while row:
    print "Inserted Product ID : " +str(row[0])
    row = cursor.fetchone()

Transactions

This code example demonstrates the use of transactions in which you:
-Begin a transaction
-Insert a row of data
-Rollback your transaction to undo the insert

import pymssql
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')
cursor = conn.cursor()
cursor.execute("BEGIN TRANSACTION")
cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express New', 'SQLEXPRESS New', 0, 0, CURRENT_TIMESTAMP)")
cnxn.rollback()

Next steps

For more information, see the Python Developer Center.