Manipulate MS SQL database using SQLAlchemy

-
There are many chances that you have to communicate with your MS SQL server using your python app. Here is a simple setup in a Ubuntu (14.04 x64) server that you can apply to your project:

1. Install FreeTDS driver and ODBC:

$ sudo apt-get install tdsodbc

You may also want to install the FreeTDS testing tool tsql:

$ sudo apt-get install freetds-bin


2. Configure FreeTDS driver:

 $ sudo nano /etc/freetds/freetds.conf

Add your MS SQL info block:

...
[yourmssql]
        host = your.mssql.com
        Port = 1433
        tds version = 4.2


3. Setup ODBC:

$ sudo nano /etc/odbcinst.ini

Add the following FreeTDS info:

[FreeTDS]
Description=FreeTDS Driver v0.91
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
fileusage=1
dontdlclose=1
UsageCount=1
client charset = utf-8


$ sudo nano /etc/odbc.ini

Add these following lines:

[yourdsn]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = yourmssql
Port = 1433
Database = yourdatabase
TDS_Version = 8.0

Note: for some reasons, I have to set TDS_Version as 8.0 to make it worked with my MS SQL Server 2008.

3. Test the connection to your MS SQL server:

$ tsql -S yourmssql -U youruser -D yourdatabase -P yourpwd

4. Manipulate MS SQL database using SQL Alchemy:

(your-virtualenv)$ pip install SQLAlchemy

* MS SQL utility class, mssql_utils.py:


* Define your table model, for example MyTable:


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class MyTable(Base):
    __tablename__ = 'MyTable'
    __table_args__ = {"schema": "MySchema"} # (*) (**)

     ID = Column(Integer, primary_key=True)
    Name = Column(String(100))
    PictureName = Column(String(300))

     def __repr__(self):
        return "Name: %s" % self.Name


(*): In case your MS SQL is still using the legacy schema structure
(**): You will get this warning message in SQLAlchemy 1.0.9 if you do not indicate the 'legacy_schema_aliasing=True' when initializing the db engine:

legacy_schema_aliasing flag is defaulted to True; some schema-qualified queries may not function correctly. Consider setting this flag to False for modern SQL Server versions; this flag will default to False in version 1.1
  "legacy_schema_aliasing flag is defaulted to True; "

For more information about the flag, please read this.

* Query your MS SQL database:

from mssql_utils import MSSQL
from urllib import quote_plus as urlquote

 DSN = 'yourdsn'
USER = 'youruser'
PWD = 'yourpwd'
CONN_STR = "mssql+pyodbc://%s:%s@%s" % (USER, urlquote(PWD), DSN)

 db = MSSQL(CONN_STR)
sess = db.get_session(CONN_STR)

 mydata = sess.query(MyTable).filter_by(ID=100).first()



References: