Notes on sqlalchemy w/ pyodbc, freetds on Ubuntu

Date: 2011-09-28 | Modified: 2013-03-06 | Tags: linux, notestoself, persistence, python, sql, ubuntu | 4 Comments

These are my notes for connecting to a MS SQL Server using Python on Linux. My developement environment is Ubuntu 10.10 Maverick (64-bit). The production environment is Redhat/Centos-based Scientific Linux 6. Here is the layers of stuff:

FreeTDS: an open source ODBC driver. Started with version 0.82 and moved to 0.91. Other options: Microsoft ODBC driver for Linux Free, currently only for 64-bit Redhat. Easysoft ODBC driver Commercial, not free.
unixODBC: driver manager, a thin wrapper around the ODBC driver. Version 2.2 (may move to version 2.3 if we use the Microsoft driver).
pyodbc: python library. Started with 2.1.11, currently using 3.0.5. I chose pyodbc over pymssql because, after minimal research, it seemed to be the preferred choice. Other options: pymssql (GPL, doesn't use ODBC), mxODBC (commercial, much better documentation, may be more robust)
SQLAlchemy: wraps pyodbc, provides an ORM and better interface. I chose to use sqlalchemy because it supported named parameters. pyodbc only supported the 'qmark' paramstyle and pymssql returns 'pyformat' as the paramstyle, but it seems to actually be 'format'. See PEP-0249 for more information on paramstyle. See http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.text for more information on using named parameters.

Contents¶

Install (on Ubuntu)
Try to connect using tsql (FreeTDS)
Try to connect using Python
Error inserting unicode
Pyodbc SELECT -1 bug
How to list tables in the database with sqlalchemy
pyodbc/FreeTDS truncates a varchar(max) upon insert instead of raising an error (Install FreeTDS 0.91 from source)
Segfault error when using Free TDS 0.91 and pyodbc 2.1.11
What is the MS SQL server equivalent of MySQL's LIMIT in a SELECT
How to get the versions of stuff
Install the Microsoft ODBC driver for Linux
Install mxodbc
unixODBC GUI tools
Install SQL Developer on Ubuntu
Calling sprocs from sqlalchemy and pyodbc
Other misc errors
Install pyodbc on Scientific Linux 6
See also
How to find the location of the odbcinst.ini file

Install (on Ubuntu)¶

Set up odbc, freetds. Notes thanks to http://www.pauldeden.com/2008/12/how-to-setup-pyodbc-to-connect-to-mssql.html

$ sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc

Edit /etc/odbcinst.ini:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8

UPDATE 2012-07-09: On Ubuntu 12.04 (64-bit), the libraries are in a different location. Also, Ubuntu 12.04 includes FreeTDS version 0.91.

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8

Install pyodbc

$ pip install pyodbc

Install sqlalchemy

$ pip install SQLAlchemy

This installed the following versions:

$ apt-cache show tdsodbc | grep Version: 
Version: 0.82-7

$ odbcinst --version 
unixODBC 2.2.14

$ pip freeze | grep pyodbc 
pyodbc==2.1.11

$ pip freeze | grep Alchemy
SQLAlchemy==0.7.3

Try to connect using tsql (FreeTDS)¶

Run the following command to ensure you can connect using tsql. For more information see: http://www.freetds.org/userguide/confirminstall.htm See also: FreeTDS User Guide: Chapter 8. Troubleshooting: Is the server there?

$ tsql -S 10.2.14.18 -U myusername -P mypassword
locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=en_US.UTF-8;LC_TIME=en_US.UTF-8;LC_COLLATE=C;LC_MONETARY=en_US.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=en_US.UTF-8;LC_ADDRESS=en_US.UTF-8;LC_TELEPHONE=en_US.UTF-8;LC_MEASUREMENT=en_US.UTF-8;LC_IDENTIFICATION=en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

Try to connect using Python¶

pyodbc - this works

import pyodbc
conn = pyodbc.connect(
    'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
    'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
    'TDS_Version=8.0;')
print conn

sqlalchemy - this doesn't work

import sqlalchemy
engine = sqlalchemy.create_engine(
    'mssql+pyodbc://MyUsername:[email protected]:1234/MyDatabase')
conn = engine.connect()
print conn

Traceback (most recent call last):
  File "debug_sqlalchemy.py", line 22, in 
    conn = engine.connect()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 2310, in connect
    return self._connection_cls(self, **kwargs)
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 872, in __init__
    self.__connection = connection or engine.raw_connection()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 2396, in raw_connection
    return self.pool.unique_connection()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 169, in unique_connection
    return _ConnectionFairy(self).checkout()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 370, in __init__
    rec = self._connection_record = pool._do_get()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 696, in _do_get
    con = self._create_connection()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 174, in _create_connection
    return _ConnectionRecord(self)
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 255, in __init__
    self.connection = self.__connect()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 315, in __connect
    connection = self.__pool._creator()
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 275, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnectW)') None None

sqlalchemy - this works

import sqlalchemy
import urllib
engine = sqlalchemy.create_engine(
    'mssql+pyodbc:///?odbc_connect=%s' % (
        urllib.quote_plus(
            'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
            'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
            'TDS_Version=8.0;')))
conn = engine.connect()
print conn

sqlalchemy - this works also

import pyodbc
import sqlalchemy
def connect():
    return pyodbc.connect(
        'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
        'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
        'TDS_Version=8.0;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn

You can also connect by specifying the path to the driver directly and bypass /etc/odbcinst.ini. Not sure of the disadvantages of doing this.

SQL_SERVER_HOST = '10.5.18.11'
SQL_SERVER_UID = 'myusername'
SQL_SERVER_PWD = 'mypassword'
FREETDS_DRIVER = '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so'

before_quoted = ';'.join(
    [
        'DRIVER={}'.format(FREETDS_DRIVER),
        'SERVER={}'.format(SQL_SERVER_HOST),
        'DATABASE=mydatabase',
        'UID={}'.format(SQL_SERVER_UID),
        'PWD={}'.format(SQL_SERVER_PWD),
        'PORT=1433',
        'TDS_VERSION=7.2'
        ]
    )
print before_quoted + '\n'
connection_string = 'mssql+pyodbc:///?odbc_connect={}'.format(
    urllib.quote_plus(before_quoted))
print connection_string + '\n'
engine = sqlalchemy.create_engine(connection_string)
print engine

DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so;SERVER=10.5.18.11;DATABASE=mydatabase;UID=myusername;PWD=mypassword;PORT=1433;TDS_VERSION=7.2

mssql+pyodbc:///?odbc_connect=DRIVER%3D%2Fusr%2Flib%2Fx86_64-linux-gnu%2Fodbc%2Flibtdsodbc.so%3BSERVER%3D10.5.18.11%3BDATABASE%3Dmydatabase%3BUID%3Dmyusername%3BPWD%3Dmypassword%3BPORT%3D1433%3BTDS_VERSION%3D7.2

Engine(mssql+pyodbc:///?odbc_connect=DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so;SERVER=10.5.18.11;DATABASE=mydatabase;UID=myusername;PWD=mypassword;PORT=1433;TDS_VERSION=7.2)

References

Error inserting unicode¶

If you get this "Error: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect." error during insert, it may be due to unicode in your data.

Traceback (most recent call last):
  File "tmp.py", line 10, in 
    (unicode(u'some unicode data: \xa1').encode("utf-8"), '123', '123456'),
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3: The parameter name is invalid. (8005) (SQLExecDirectW)')

The fix for me was to change the TDS version number. "7.0" and "8.0" did not work. "7.1", "7.2", "9.0", and some others worked as well. There is this FreeTDS user guide page on version numbers, and also this other user guide page, but I'm not sure I'm clear on it. The following works for me:

import pyodbc

conn = pyodbc.connect(
    'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
    'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
    'TDS_Version=7.2;')
cursor = conn.cursor()
cursor.execute(
    """exec MyStoredProc @Param1 = ?, @Param2 = ?, @Param3 = ?""",
    (unicode(u'some unicode data: \xa1').encode("utf-8"), '123', '123456'),
    )
conn.commit()

`Unrecognized server version info` warning

When I changed TDS_Version to 7.2, I got the following warning:

/myvirtualenv/lib/python2.6/site-packages/sqlalchemy/connectors/pyodbc.py:119: SAWarning: Unrecognized server version info '95.10.13055'.   Version specific behaviors may not function properly.   If using ODBC with FreeTDS, ensure server version 7.0 or 8.0, not 4.2, is configured in the FreeTDS configuration.

This bothered me for a while, but later, when I upgraded freetds to 0.91, this warning went away.

Select -1 bug¶

Lin alerted me to this bug in pyodbc 2.1.11: Issue 157: Negative integers are retrieved as positive integers Executing a SELECT -1 returns 2^32-1 (4294967295). Solution is to apply this patch or upgrade to 3.0.x of pyodbc. I had trouble installing some versions of pyodbc. This is what worked for me:

$ pip install --upgrade -e git+https://code.google.com/p/pyodbc#egg=pyodbc

This gave me:

$ pip freeze | grep pyodbc 
-e git+https://code.google.com/p/pyodbc@404a3ba414ab15863c34db9c49ff0a02caa63600#egg=pyodbc-dev

$ python
Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'3.0.4-beta01'

How to list the tables in the database¶

import urllib

import sqlalchemy


# Recommended to be a module-level variable
# http://www.sqlalchemy.org/docs/core/connections.html
engine = sqlalchemy.create_engine(
    'mssql+pyodbc:///?odbc_connect=%s' % (
        urllib.quote_plus(
            'DRIVER={FreeTDS};SERVER=myserver.mydomain.com;'
            'DATABASE=MyDatabase;UID=myusername;PWD=mypassword;port=12345;'
            'TDS_Version=7.2;')))


metadata = sqlalchemy.MetaData()
metadata.bind = engine
metadata.reflect()
for t in metadata.sorted_tables:
    print t.name

pyodbc/FreeTDS truncates a varchar(max) upon insert instead of raising an error¶

When trying to insert a string with more than 100 characters into a varchar(100) field, pyodbc/FreeTDS truncated the string instead of raising an error. This occurred because I was using FreeTDS version 0.82. I found this out from this FreeTDS mailing list thread. The solution is to upgrade to FreeTDS version 0.91. I could not find any APT packages for Ubuntu 10.10. Looks like there is a package for Ubuntu Precise (12.04). So I compiled it from source. This assumes you've installed unixodbc as described above: $ sudo apt-get install unixodbc unixodbc-dev

$ cd /tmp 
$ wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz 
$ tar zxvf freetds-stable.tgz 
$ cd freetds-0.91 
$ ./configure
$ make 
$ sudo make install

Then edit odbcinst.ini to point at the new library /usr/local/lib/libtdsodbc.so:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8

After upgrading, pyodbc now gives me the following error instead of failing silently.

pyodbc.DataError: ('22001', '[22001] [FreeTDS][SQL Server]String or binary data would be truncated. (8152) (SQLExecDirectW)')

References:

Segfault error when using FreeTDS 0.91 and pyodbc 2.1.11¶

Got this error trying to connect to a database on production (Scientific Linux 6)

*** glibc detected *** python: free(): invalid next size (fast): 0x00000000019ed440 ***

Solution was to upgrade pyodbc to 3.0.5 because there was a bug.

What is the MS SQL server equivalent of MySQL's LIMIT in a SELECT¶

TOP See http://www.w3schools.com/sql/sql_top.asp

SELECT TOP * FROM mytable

How to get the versions of stuff¶

unixODBC

$ odbcinst --version
unixODBC 2.3.0

$ odbc_config --version
2.3.0

freetds

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no

pyodbc

$ python
Python 2.7.0+ (r27:82500, Sep 15 2010, 18:14:55) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'3.0.5'

Trying out the Microsoft ODBC driver for Linux¶

This was installed on Scientific Linux 6. Ubuntu is not supported.

Install make

$ yum install make

Followed instructions to install the unixODBC driver manager and the driver. It installed OK.

$ sudo su root
$ tar xvf sqlncli-11.0.1720.0.tar.gz 
$ cd sqlncli-11.0.1720.0
$ ./build_dm.sh --help
$ ./build_dm.sh --download-url=file://../unixODBC-2.3.0.tar.gz
$ cd /tmp/unixODBC.8922.6568.26033/unixODBC-2.3.0; make install

$ sudo su root
$ tar xvf sqlncli-11.0.1720.0.tar.gz 
$ cd sqlncli-11.0.1720.0
$ ./install.sh --help
$ cp /etc/odbcinst.ini /etc/odbcinst.ini.bak
$ ./install.sh verify
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 or later installed .................... OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND

$ ./install.sh install
...
Enter YES to accept the license or anything else to terminate the installation:  YES

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 or later installed .................... OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED

Install log created at /tmp/sqlncli.31091.8029.6563/install.log.

One or more steps may have an *. See README for more information regarding
these steps.

$ odbcinst -q -d -n "SQL Server Native Client 11.0"
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
UsageCount=1

Connection string: Driver is "SQL Server Native Client 11.0". Port is specified with a comma after the host. Thanks to http://www.connectionstrings.com/sql-server-2008

        connection = pyodbc.connect(
            ';'.join([
                    'DRIVER={SQL Server Native Client 11.0}',
                    'SERVER=%s,%s' % (db_profile['host'], db_profile['port']),
                    'DATABASE=%s' % db_profile['name'],
                    'UID=%s' % db_profile['user'],
                    'PWD=%s' % db_profile['password'],
                    ]) + ';')

Ran my app and got the following error message:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0' : file not found (0) (SQLDriverConnect)")

That's a dumb error message. /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0 existed and it had the correct permissions. Turns out it was missing dependencies. Google help: http://mailman.unixodbc.org/pipermail/unixodbc-support/2011-July/002902.html and http://www.linuxquestions.org/questions/fedora-35/how-to-install-libcrypto-so-4-a-594511/.

$ ldd /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
        linux-vdso.so.1 =>  (0x00007fff5258d000)
        libcrypto.so.6 => not found
        libdl.so.2 => /lib64/libdl.so.2 (0x00007feec91fd000)
        libodbc.so.1 => /usr/lib64/libodbc.so.1 (0x00007feec8f90000)
        librt.so.1 => /lib64/librt.so.1 (0x00007feec8d88000)
        libssl.so.6 => not found
        libuuid.so.1 => /lib64/libuuid.so.1 (0x00007feec8b83000)
        libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007feec896c000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007feec868c000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007feec844b000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007feec8144000)
        libm.so.6 => /lib64/libm.so.6 (0x00007feec7ebf000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007feec7ca9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007feec7a8d000)
        libc.so.6 => /lib64/libc.so.6 (0x00007feec76eb000)
        /lib64/ld-linux-x86-64.so.2 (0x00007feec97b0000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007feec74bf000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007feec72bc000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007feec70b1000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007feec6eaf000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007feec6c95000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007feec6a75000)

libcrypto.so.6 and libssl.so.6 are missing.

$ yum install openssl098e
$ ldd /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0                 
        linux-vdso.so.1 =>  (0x00007fff5c7fe000)
        libcrypto.so.6 => /usr/lib64/libcrypto.so.6 (0x00007f7ebcdd8000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f7ebcbd4000)
        libodbc.so.1 => /usr/lib64/libodbc.so.1 (0x00007f7ebc966000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f7ebc75e000)
        libssl.so.6 => /usr/lib64/libssl.so.6 (0x00007f7ebc50f000)
        libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f7ebc30a000)
        libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007f7ebc0f3000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f7ebbe14000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f7ebbbd1000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f7ebb8ca000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f7ebb646000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7ebb42f000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7ebb213000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7ebae72000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f7ebac5c000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f7ebd4e3000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f7ebaa59000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f7eba82d000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f7eba621000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f7eba41f000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7eba205000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f7eb9fe5000)

Good. It works.

Install mxodbc¶

$ python -c 'print("UCS%s"%len(u"x".encode("unicode-internal")))'
UCS4
$ pip install http://downloads.egenix.com/python/egenix-mx-base-3.2.3.linux-x86_64-py2.7_ucs4.prebuilt.zip 
$ pip install http://downloads.egenix.com/python/egenix-mxodbc-3.1.1.linux-x86_64-py2.7_ucs4.prebuilt.zip 
$ #copy license files to "site-packages" directory

unixODBC GUI tools¶

I didn't end up actually using this. I think this is useful if you need to set up DSNs (e.g. if you're using mxodbc).

$ sudo apt-get install unixodbc-bin

Description: Graphical tools for ODBC management and browsing This package contains three graphical applications for use with unixODBC, the Open DataBase Connectivity suite: ODBCConfig, a graphical configuration tool for managing database drivers and access to individual databases; DataManager, a simple browser and query tool for ODBC databases; and odbctest, a tool for testing the ODBC API itself.

Provides:

/usr/bin/DataManager
/usr/bin/DataManagerII
/usr/bin/ODBCConfig
/usr/bin/odbctest

Installing SQL Developer on Ubuntu¶

For a GUI interface to MS SQL Server, I like Oracle's SQL Developer better than Eclipse. Thanks Kris. (Too bad it doesn't execute stored procedures.)

 - $ sudo apt-get install openjdk-6-jdk
 - Create an Oracle account
 - Download "Oracle SQL Developer for other platforms" from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
 - $ unzip sqldeveloper-3.1.07.42-no-jre.zip
 - $ cd sqldeveloper
 - $ sh sqldeveloper.sh 
Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk
usr/lib/jvm/java-6-openjdk

http://djiang.wordpress.com/2007/02/27/using-oracle-sqldeveloper-to-access-sqlserver/
Download jTDS from here: http://sourceforge.net/projects/jtds/
mkdir jtds-1.2.5
mv jtds-1.2.5-dist.zip jtds-1.2.5
cd jtds-1.2.5
unzip jtds-1.2.5-dist.zip
 - Start Oracle’s SQLDeveloper, Tools->Preferences->Database->Third Party JDBC Drivers. Click “Add Entry” and point to the jtds-1.2.jar
 - File -> New... -> Database Connection -> choose SQLServer tab, type in hostname, port, username and password. It appears that the initial connection name has to be the same as the database and you can click the “Retrieve database” button. Once you found the database, you can rename the connection.

Calling stored procs from sqlalchemy and pyodbc¶

connection_string = ';'.join([
        'DRIVER={FreeTDS}',
        'SERVER=myhost.com',
        'DATABASE=my_database',
        'UID=myusername',
        'PWD=mypassword',
        'port=1433',
        'TDS_Version=7.2',
        ]) + ';'
print connection_string


def use_pyodbc():
    import pyodbc

    connection = pyodbc.connect(connection_string)
    print connection

    sql = " ".join([
            "EXEC [dbo].my_sproc",
            "@my_param = ?",
            ])
    print sql
    cursor = connection.cursor()
    result = cursor.execute(sql, ('my_value',))
    for r in result.fetchall():
        print r
        print r.a_param
    connection.commit()


def use_sqlalchemy():
    import urllib
    import sqlalchemy

    engine = sqlalchemy.create_engine(
        'mssql+pyodbc:///?odbc_connect=%s' % (
            urllib.quote_plus(connection_string)))
    print engine
    sql = sqlalchemy.sql.text(" ".join([
            "EXEC [dbo].my_sproc",
            "@my_param = :my_param",
            ]))
    print sql
    result = engine.execute(sql, {'my_param': 'my_value'})
    for r in result.fetchall():
        for k in r.keys():
            try:
                print '%50s %s' % (k, getattr(r, k))
            except sqlalchemy.exc.InvalidRequestError as e:
                print e
        break

Install pyodbc on Scientific Linux 6¶

you will also need freetds installed

$ yum install unixODBC-devel 
$ yum install gcc-c++ 
$ pip install pyodbc

Other misc errors¶

ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL Server]Statement(s) could not be prepared. (8180) (SQLExecDirectW)')

Problem was missing commas.

```
DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None
```
- This occurred when I could not connect to the database server. To troubleshoot this, see: FreeTDS User Guide: Chapter 8. Troubleshooting: Is the server there?
- This also occurred when I did not configure /etc/odbcinst.ini correctly as described in the Install section

How to find the location of the odbcinst.ini file¶

$ odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/saltycrane/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Trace odbc¶

http://www.easysoft.com/support/kb/kb00945.html Add the following to the odbcinst.ini file. Be sure to turn it off after debugging.

[ODBC]
Trace = yes
TraceFile = /tmp/my_odbc_tracfile.log

Comments

#1 Bruno commented on 2012-03-14:

I used :

# ./configure --prefix=/usr --sysconfdir=/etc --with-unixodbc=/usr

to install freetds 0.9.1 to install on ubuntu 11.04. In /etc/odbcinst.ini

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so

worked just fine!

#2 normalex commented on 2012-07-27:

For 10.10 and 12.04 as root do the following and stop editing system files manually

odbcinst -i -d -f /usr/share/libmyodbc/odbcinst.ini

odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini

10.10 has slightly different paths, find with locate or dpkg.

#3 Michael Ekoka commented on 2013-04-12:

If on Ubuntu 12.04 32 bit, note that libtdsodbc.so and libtdsS.so are not located under /usr/lib/x86_64-linux-gnu/odbc/ but rather under /usr/lib/i386-linux-gnu/odbc/

#4 Per Gunnar Hansø commented on 2016-10-04:

Many good tips here. Kudos!

disqus:2932508224

SaltyCrane Blog — Notes on JavaScript and web development

Notes on sqlalchemy w/ pyodbc, freetds on Ubuntu

Contents¶

Install (on Ubuntu)¶

Try to connect using tsql (FreeTDS)¶

Try to connect using Python¶

References

Error inserting unicode¶

`Unrecognized server version info` warning

See also these pages for more on unicode and pyodbc:

Select -1 bug¶

How to list the tables in the database¶

pyodbc/FreeTDS truncates a varchar(max) upon insert instead of raising an error¶

Segfault error when using FreeTDS 0.91 and pyodbc 2.1.11¶

What is the MS SQL server equivalent of MySQL's LIMIT in a SELECT¶

How to get the versions of stuff¶

Trying out the Microsoft ODBC driver for Linux¶

Install mxodbc¶

unixODBC GUI tools¶

Installing SQL Developer on Ubuntu¶

Calling stored procs from sqlalchemy and pyodbc¶

Install pyodbc on Scientific Linux 6¶

Other misc errors¶

See also¶

How to find the location of the odbcinst.ini file¶

Trace odbc¶

Comments

Links

Contents¶

Install (on Ubuntu)¶

Try to connect using tsql (FreeTDS)¶

Try to connect using Python¶

References

Error inserting unicode¶

Unrecognized server version info warning

See also these pages for more on unicode and pyodbc:

Select -1 bug¶

How to list the tables in the database¶

pyodbc/FreeTDS truncates a varchar(max) upon insert instead of raising an error¶

Segfault error when using FreeTDS 0.91 and pyodbc 2.1.11¶

What is the MS SQL server equivalent of MySQL's LIMIT in a SELECT¶

How to get the versions of stuff¶

Trying out the Microsoft ODBC driver for Linux¶

Install mxodbc¶

unixODBC GUI tools¶

Installing SQL Developer on Ubuntu¶

Calling stored procs from sqlalchemy and pyodbc¶

Install pyodbc on Scientific Linux 6¶

Other misc errors¶

See also¶

How to find the location of the odbcinst.ini file¶

Trace odbc¶

Comments

Links

`Unrecognized server version info` warning