Mon, Jun 25, 2012
Free tools are great, but the world ain't all sunshine and rainbows. Sometimes, we may need to connect to a Microsoft SQL Server database from one of our Python applications running under Linux. Fortunately, there are ways to achieve this.
I am assuming we got this:
Without further ado, here are the steps you should follow to get this working.
Your SQL Server installation must be setup to allow external connections. If the DB is not administered by you this might not be a problem, but in case you do have administrator level access and need to do it yourself, read here.
Now you must have setup a port in which SQL Server is listening. Remember it.
Make sure you are not blocked by Windows firewall or such when you attempt to connect to the Windows computer. Attempting a telnet will help us check if there are connection problems. For example, try running
telnet <sqlserverpc> <port> from Ubuntu and check the connection doesn't fail.
Regarding authentication, I have only tried this with the
sa login enabled (ie. not using Windows Authentication). You may read on how to do that here.
These are the things we are going to need:
From a terminal, run:
From the Virtualenv of our Python application (if you are not using one, you should!) run
pip install pyodbc.
Edit the file
/etc/freetds/freetds.conf and replace placeholders appropriately. Note that we are calling our server
After this you can test the connection with this command:
Then run some SQL Server command to make sure everything works fine. For example you may run a DB query like this:
If it worked, it will print the results of the query. Quit with Ctrl+D.
odbcinst -j to know where our configuration files are located. We will need to edit two files: the "drivers" and "system data source". I assume they are
/etc/odbc.ini respectively, but the output of the command will tell you this.
/etc/odbcinst.ini like this:
If the paths for Driver and Setup do not work in your installation, you can find where these files are located by running
find / -name "libtds*".
/etc/odbc.ini like this, to add a data source named
Now you may test the connection to out data source works by running
isql -v sqlserverdatasource <username> <password>.
If everything is fine, with the help of pyodbc it should be really easy! You may try the following snippet:
That should be it :-)