Skip to content

Instantly share code, notes, and snippets.

@kriwil
Forked from rduplain/README.md
Last active June 11, 2021 16:24
Show Gist options
  • Save kriwil/b49da8a5bc14504f73fe to your computer and use it in GitHub Desktop.
Save kriwil/b49da8a5bc14504f73fe to your computer and use it in GitHub Desktop.
ubuntu connect to mssql server using pyodbc
"Proof connection at pyodbc level."
# Test pyodbc connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.
import pyodbc
conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
cursor = conn.cursor()
for row in cursor.execute('select 6 * 7 as [Result];'):
print row.Result
"Proof connection at SQLAlchemy level, on top of pyodbc."
# Test SQLAlchemy connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.
import urllib
from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc:///?odbc_connect=' +
urllib.quote_plus('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
)
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result

Goal: Connect to MSSQL using FreeTDS / ODBC in Python.

Host: Ubuntu 12.04 x86_64

Install:

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
pip install pyodbc sqlalchemy

In /etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment