Zhixian's Tech Blog

2013-06-23

How to connect to Microsoft Sql Server from Ubuntu using pyODBC

This is continuation of my previous 3-part blog post that list out the steps carried out to establish a connection to Sql Server via ODBC on Ubuntu.

Assumptions

  1. The version of Ubuntu used in this guide is Ubuntu 12.04.2 LTS (Precise Pangoline) 32-bit.
  2. unixodbc is installed on the Ubuntu system.
  3. freetds is installed on the Ubuntu system.
  4. You are trying to connect to Microsoft Sql Server 2008 or later.
  5. You know how to use a terminal session in Ubuntu.

Required materiels

  1. pyODBC (latest version is 3.0.6 as of writing)
    (downloadable from http://code.google.com/p/pyodbc/downloads/list)

Download source vs apt-get

The apt-get utility in Ubuntu does have a version of pyODBC. (version 2.1.7)
However, it is badly out-of-date (2.1.7 vs 3.0.6) and may not work well with the newer versions of unixODBC and freetds.
This is especially important if you are trying to connect to later versions of Microsoft Sql Server (2008 onwards).
It is recommended that you use the latest versions of unixODBC, freetds and pyODBC when working with the latest Microsoft Sql Server instead of relying on packages in apt-get.

PyODBC Installation Overview

The steps for installing pyODBC are as follows:

  1. Download the source zip file. (I’m assuming you know how to do this)
  2. Extract the contents of the package.
  3. Install the contents of the package.
  4. Configure your system environment variables.

Extracting contents from pyODBC zip file

1) Assuming you downloaded the pyODBC zip into the Downloads folder of your Home directory, you should see a screen like the below on your file browser.

image_thumb[1]

2) To extract the contents of the package, right-click on it. A popup menu should appear.
Select the menu item “Extract Here”.

image_thumb[3]

3) After you extracted the contents, your file browser should look like the below:

image_thumb[5]

 

Installing pyODBC

1) Start a new terminal session.
Navigate to the location where you extracted the pyODBC source files by typing cd ~/Downloads/pyODBC-3.0.6/ at the command line.

image

2a) Proceed to install pyODBC by typing python ./setup.py install on the command line.

image

If you received a screen like the below, it means that your user account do not have the necessary permissions to install pyODBC onto the system.
Type sudo python ./setup.py install on the command line to use a superuser account to perform the installation.

image

3) If the installation succeed, you should see a screen like the below:

image

 

Configure system environment variables

pyODBC has a dependency on the file libodbc.so.
This is the library of functions that pyODBC use to perform ODBC operations.
If pyODBC cannot find this file, you may see the following message:

ImportError: <libodbc.so name> cannot open shared object file: No such file or directory

The actual name <libodbc.so name> may varied from system to system. A sample screen is as follows:

image

To help pyODBC find the library, we need to declare an environment variable LD_LIBRARY_PATH and specify the location of the libodbc.so in this variable.

Assuming my libodbc.so is located at  the directory /usr/local/lib

1) Type the following at the command line, to set the variable:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

image

2) Type the following to set the variable as a global variable so that the executing environment can access this variable.
export LD_LIBRARY_PATH

image

 

[Optional]

The changes made in the previous step are not persistent.
This means you need to carry out the same steps every time you start a new terminal session.

To make this change persistent, I recommend you put these 2 commands in your shell’s startup file.
If you are using the default bash shell, this would be your ~/.bashrc file.
Put the following 2 lines at the bottom of the file and save the file.

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

 

Testing pyODBC

For testing pyODBC, I have the following python script:

import pyodbc

database = pyodbc.connect('DRIVER=FreeTDS;SERVER=SARA\\SQLEXPRESS;UID=dbadmin;PWD=password;DATABASE=Arbalest;TDS_Version=8.0;',unicode_results=True)
cursor = database.cursor() encoding = 'utf-8' sql = "SELECT * FROM Arbalest.dbo.test;" cursor.execute(sql) for row in cursor:     print row

image

This python script assumes:

  1. I am trying to connect to the “SQLEXPRESS” Sql Server instance on a machine with the hostname of SARA
  2. The user account that I am using to log into the database is “dbadmin”
  3. The password to my user account is “password”
  4. The database that I am connecting to is “Arbalest”

For Sql Server later than 2005, having the parameter TDS Version=8.0 is recommended.

If this parameter is not defined in the connection string, the pyODBC may work correctly.

Assuming I save this script to a file call “test.py”, I would be able to run the script by typing the following on the command line:

python test.py

image

Note that the connection string that I used above in the pyodbc.connect statement is what is referred to as DSN-less connection string.

With this you should be able to connect to Sql Server from python using pyODBC.

Advertisements

2 Comments »

  1. Hi, I follow your instructions very carefully, but I have a questio: How can I create a DNS in odbc.ini?

    I folllow this and all are fine:

    1. https://zxtech.wordpress.com/2013/06/22/how-to-connect-to-microsoft-sql-server-from-ubuntu-using-odbc/
    2. https://zxtech.wordpress.com/2013/06/22/how-to-connect-to-microsoft-sql-server-from-ubuntu-using-odbc-part-2-of-3/
    3. https://zxtech.wordpress.com/2013/06/22/how-to-connect-to-microsoft-sql-server-from-ubuntu-using-odbc-part-3-of-3/

    $ cat /usr/local/etc/odbcinst.ini
    [FreeTDS]
    Description = FreeTDS unixODBC Driver
    Driver = /usr/local/lib/libtdsodbc.so
    Setup = /usr/local/lib/libtdsodbc.so

    $ tsql -C
    Compile-time settings (established with the “configure” script)
    Version: freetds vdev.0.92.377
    freetds.conf directory: /usr/local/etc
    MS db-lib source compatibility: no
    Sybase binary compatibility: no
    Thread safety: yes
    iconv library: yes
    TDS version: 5.0
    iODBC: no
    unixodbc: yes
    SSPI “trusted” logins: no
    Kerberos: no

    $ tsql -LH 10.108.149.137
    ServerName DQUINTER2
    InstanceName DWH
    IsClustered No
    Version 10.50.4000.0
    tcp 1433

    $ cat /usr/local/etc/freetds.conf
    [global]
    tds version = 8.0

    $ tsql -S DQUINTER2\\DWH -U sa
    Password:
    locale is “es_MX.UTF-8”
    locale charset is “UTF-8”
    using default charset “UTF-8”
    1>

    But, in odbc.in I have the follow:
    $ cat /usr/local/etc/odbc.ini
    [DWH]
    Driver=FreeTDS
    Description=Base de datos para DWH
    Server=DQUINTER2\\DWH
    Port=1433
    Database=DWH

    DWH is my database.

    I get the follow if I use isql:
    $isql -v DWH sa passwd
    [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
    [01000][unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
    [01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
    [ISQL]ERROR: Could not SQLConnect

    What I am dping wrong? :/

    Thanks a lot

    Comment by Daniel CAZ (@LordQuo) — 2013-11-20 @ 06:33:11 am

    • Hi,

      Sorry for the late reply.
      In case you have yet resolve your issue, can I suggest that in your odbc.ini you add a line that says ‘TDS_VERSION=8.0’
      That is to say the contents of your odbc.ini should read as follows:

      [DWH]
      Driver=FreeTDS
      Description=Base de datos para DWH
      Server=DQUINTER2\\DWH
      Port=1433
      Database=DWH
      TDS_Version=8.0

      Comment by Zhixian — 2013-11-23 @ 08:20:11 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: