Skip to content

Instantly share code, notes, and snippets.

@rduplain

rduplain/README.md

Created October 17, 2011 20:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rduplain/1293636 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rduplain/1293636 to your computer and use it in GitHub Desktop.
Download ZIP
Connect to MSSQL using FreeTDS / ODBC in Python.
Raw
README.md

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

Host: Ubuntu 11.10 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/odbc/libtdsodbc.so Setup=/usr/lib/odbc/libtdsS.so
Raw
hello_pyodbc.py
"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
Raw
hello_sqlalchemy.py
"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
@mypantz0r
Copy link

mypantz0r commented Feb 18, 2020

Hi, tanks a lot for your post, makes sense to me as I told earlier.
but my
/etc/ssl/openssl.cnf
does not have entries or values TLSv1.2or SECLEVEL=2
can you place here the part or parts ot the .cnf file including group header where these values should reside.

Thanks

What OS are you using? Mine is Debian 10. That will determine where this config lives.
@alexandrerua
Copy link

alexandrerua commented Feb 18, 2020

Hi, tanks a lot for your post, makes sense to me as I told earlier.
but my
/etc/ssl/openssl.cnf
does not have entries or values TLSv1.2or SECLEVEL=2
can you place here the part or parts ot the .cnf file including group header where these values should reside.
Thanks

What OS are you using? Mine is Debian 10. That will determine where this config lives.

Arch linux, and I do have /etc/ssl/openssl.cnf

what i don't have is the text portions your are replacing in your sed commands.
@mypantz0r
Copy link

mypantz0r commented Feb 18, 2020 •
edited
Loading

Ok - I would look into downgrading the openssl package to 1.0.x (what ever is highest) as a first step. Looks like Arch vs Debian has different openssl packages\configs openssl version should return something like OpenSSL 1.1.1d 10 Sep 2019
@vnktsh
Copy link

vnktsh commented Feb 21, 2020 •
edited
Loading

You can now use pymssql library for python 3.4+ . Just pip install pymssql
https://pymssql.readthedocs.io/en/latest/intro.html
@Slasktra
Copy link

Slasktra commented Apr 22, 2020

I have an issue with Python3, pyodbc and FreeTDS sending Latin_1 to MSSQL. Something that has been working perfectly with Python2. Maybe some of you have faced the same problem. Instead of posting my issue twice I post a link to the other post. mkleehammer/pyodbc#749
Thanks for your attention.
@sauravpratihar
Copy link

sauravpratihar commented May 26, 2020

Thanks, man. you saved my day :)
@BSCdfdff
Copy link

BSCdfdff commented Dec 30, 2020 •
edited
Loading

To confirm what was said above:

Using:

  1. Container: Docker image: https://hub.docker.com/r/ifnazar/sybase_15_7
  2. My Os: Debian Buster
conda install -c conda-forge pyodbc conda install -c conda-forge pymssql sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc sudo dpkg-reconfigure tdsodbc

This worked for me! (access Sybase database using python 3.8.6)
@thehappycheese
Copy link

thehappycheese commented Oct 18, 2021

Thank you op all and all commenters this was a huge help to me :)
@YuriTAngelico
Copy link

YuriTAngelico commented Oct 20, 2021

Hello, this helped me a lot and worked! But for another SQL Server I got this error:

('08S01', '[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)')

What can i do?

This is my conection string from the program:

cnxn = pyodbc.connect(f'DRIVER=FreeTDS;SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password};TDS_Version=8.0;') cursor = cnxn.cursor()

For one server is perfect but fot the other one no. Can this be some missing configuration in the MSSQL?

Thanks!
@marcin2x4
Copy link

marcin2x4 commented Dec 3, 2021

I need such setup for AWS' Lambda. Could someone help on how to create zip package with rduplain's code?
@sahil-sawhney
Copy link

sahil-sawhney commented Jan 26, 2022

@YuriTAngelico I am facing same problem as you, any solutions you came across?
@TiVenter
Copy link

TiVenter commented Feb 14, 2022

Good day.

I'm trying to connect my raspberry pi 3 to my Azure Sql db.
I came across FreeTDS and it looks promising. I tried the steps above with no luck.

I'm running Raspberry Pi Os(32 - bit) with Debian 11.

Error im currently at is: conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=cmpg-sql-db.database.windows.net;PORT=1433;DATABASE=CMPG_323;UID=Beertjie007;PWD=Beertjie@@7;TDS_Version=8.0;')
pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I'm also trying to change the .ini file as prescribed with no luck maybe that is my problem but i cant due to access denied.

Is there any ways to approach this differently or use another way to connect from Raspberry pi to azure.

Sorry I'm new to this and could use all the help and detail.

Please help........!!!!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment