Pyodbc is a great python sql db 2.0 interface to odbc. It is a mature and well written library.
It's also a little hard to setup on linux due to all the pieces involved, but never fear, here are the steps on Ubuntu 8.04. The following will show you how to setup the dsn-less setup. This way you don't have to setup DSNs for each connection. Thanks Guillermo for the tip.
sudo aptitude install unixodbc unixodbc-dev freetds-dev tdsodbc python-dev
Change /etc/odbcinst.ini to
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
Now unixodbc and freetds are setup.
On to pyodbc itself.
Download the current version of pyodbc
Unzip the file.
Do a
sudo python setup.py install
All done.
Test it with
python
>>> import pyodbc
>>> conn = pyodbc.connect("DRIVER={FreeTDS};SERVER=dns_or_ip_of_server;UID=username;PWD=password;DATABASE=database_name")
No errors and it is installed and working!
More details here and here.
Subscribe to:
Post Comments (Atom)
14 comments:
Thanks, very helpfull. Only needed to edit /etc/odbcinst.ini to connect dsn-less against a SQL Server from Ubuntu.
Thanks! That is very good to know.
Hi Paul,
When I try to run the following pyodbc code:
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=myserver;DATABASE=master;UID=sa;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("create database test")
I get the ERROR:
cursor.execute("create database test")
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server
Driver][SQL Server]CREATE DATABASE statement not allowed within multi-
statement transaction. (226) (SQLExecDirectW)')
Any ideas why this error happens?
That is a good question sysarch.
Unfortunately, I don't have the answer for you. Perhaps on the pyodbc mailing list you will find someone who knows more about it than I?
For the MSSQL error on "create database" above, try putting a semicolon on the end of your sql statement.
good luck.
I was unable to install pyodbc-2.1.6 without first installing unixodbc-dev. You may want to add that package to the list above.
Thanks Seriously. I updated the post.
I just realized that I forgot to say thank you for the post. It was quite helpful. Thank you.
Cheers,
Benjamin
Thanks for this tutorial, it works on Ubuntu 9.10 Karmic as well.
Any idea how to get this going without unixodbc-dev package? I'm on OSX Snow Leopard.
I've needed to set up pyodbc many times now, and I've found this page the easiest to understand out of any others. Thanks!
Jae, happy to hear that this post was helpful!
Paul
Two years after posting it - I've found it very useful. Thanks a lot.
Post a Comment