iODBC ODBC Driver Manager

  • Topic
  • Discussion
  • iodbcWiki.IODBCPythonHOWTO(Last) -- Iodbc? , 2016-09-14 12:28:33 Edit 2016-09-14 12:28:33

    iODBC Python HOWTO

    Disclaimer

    The following is a HOWTO document for installing Python with iODBC on Linux or Unix. Feel free to criticize, suggest modifications, or ask further questions. It is currently maintained by Tim Haynes of Openlink Software (iodbc@openlinksw.com)

    Prerequisites include basic Unix familiarity, such as creating directories and users, using an editor, etc.

    This HOWTO is intended to assist in connecting python to back-end databases via ODBC in a development environment and should not take the place of thorough testing before deployment on a production system.

    ODBC Overview

    ODBC (Open Database Connectivity) is an operating system- and database- independent communication API for database connectivity. It enables ODBC compliant client applications to connect transparently to back-end databases via ODBC function calls which are implemented by ODBC Drivers for target back-end databases.

    ODBC provides your applications with database-independence; consequentially, you no longer have to incur the development and maintenance cost of inextricably binding your application to backend database engines via their proprietary data-access (aka native) APIs.

    ODBC connections involve an ODBC-compliant Application or Data Access Layer, ODBC Driver Manager, ODBC Driver, and back-end Database. The ODBC Driver Manager for Microsoft Windows platforms is administered via the ODBC Administrator Control Panel applet at setup and configuration time. The Driver Manager registers a set of ODBC driver connection parameters called a Data Source Name (DSN), and maintains (in persistent form) a relationship between the DSN and an underlying ODBC Driver that will honor data access request via that DSN.

    At runtime an application looks to the driver manager for a DSN, and then passes the connection parameters specified in the DSN to the appropriate driver, which makes the actual database connection. Under non-Windows platforms you may need to install a Driver Manager if this isn't delivered as an integral part of your operating environment. Platform independent ODBC (aka iODBC) is an Open Source ODBC project (dual license LGPL / BSD)for non Windows platforms maintained by OpenLink Software that consists of an ODBC SDK (libraries and header files) and ODBC Runtime components (Administrator and Driver Manager).

    Preface

    You will also need an ODBC Driver and Database to complete the architecture.

    If you need ODBC drivers to connect to a third-party database on the same or another machine, OpenLink ODBC Drivers are available, and may be downloaded from http://www.openlinksw.com

    The Virtuoso database may also be downloaded from http://virtuoso.openlinksw.com/

    Both sets of ODBC Drivers are available on a free 30 day evaluation basis.

    Support for setting up the OpenLink Drivers may be obtained at http://support.openlinksw.com/

    Installing Python

    If you already have Python installed and running, you probably do not need to rebuild it. Otherwise, you can compile it from source if you wish, thus:

    First, download the latest source distribution from http://www.python.org/ - currently this is version 2.2.3.

    Unpack it into a build directory with the command

    gzip -cd < Python-2.2.3.tgz | tar xvpf -
    

    Enter the build directory, and run configure, specifying any optional configurations as desired:

    cd Python-2.2.3/
    zsh, purple  2:50PM Python-2.2.3/ % ./configure --help
    Usage: configure [options] [host]
    Options: [defaults in brackets after descriptions]
    Configuration:
    --cache-file=FILE       cache test results in FILE
    [snip]
    Directory and file names:
    --prefix=PREFIX         install architecture-independent files in PREFIX
    [/usr/local]
    [snip]
    --with-libs='lib1 ...'          link against additional libs
    --with-signal-module            disable/enable signal module
    --with-dec-threads              use DEC Alpha/OSF1 thread-safe libraries
    --with(out)-threads[=DIRECTORY] disable/enable thread support
    [snip]
    zsh, purple  2:50PM Python-2.2.3/ % ./configure --prefix=/usr/local/stow/python-2.2.3
    creating cache ./config.cache
    checking MACHDEP... linux2
    checking for --without-gcc... no
    checking for --with-cxx=<compiler>... no
    checking for c++... g++
    checking whether the C++ compiler (g++  ) works... yes
    checking whether the C++ compiler (g++  ) is a cross-compiler... no
    [snip]
    creating Makefile.pre
    creating Modules/Setup.config
    creating pyconfig.h
    creating Setup
    creating Setup.local
    creating Makefile
    zsh, purple  2:52PM Python-2.2.3/ % make
    gcc -c -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -I. -I./Include -DHAVE_CONFIG_H  -o Modules/python.o Modules/python.c
    gcc -c -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -I. -I./Include -DHAVE_CONFIG_H  -o Parser/acceler.o Parser/acceler.c
    running build_scripts
    creating build/scripts-2.2
    copying and adjusting /home/tim/public_html/docs/python-HOWTO/Python-2.2.3/Tools/scripts/pydoc -> build/scripts-2.2
    zsh, purple  2:59PM Python-2.2.3/ % su
    bash-2.05b# make install
    [snip]
    Creating directory /usr/local/stow/python-2.2.3/man
    Creating directory /usr/local/stow/python-2.2.3/man/man1
    /bin/install -c -m 644 ./Misc/python.man        /usr/local/stow/python-2.2.3/man/man1/python.1
    

    Installing iODBC

    If you do not already have iODBC installed, either install an RPM from iODBC.org, or install from source:

    Compiling iODBC from source

    Requirements: C-compiler; optionally gtk+-1.2 (required if building from CVS).

    As before, unpack the iODBC sources, enter the build directory, configure, make and make install:

    zsh, purple  4:13PM C/ % tar xvpfz libiodbc-3.51.1.tar.gz
    libiodbc-3.51.1/
    libiodbc-3.51.1/admin/
    libiodbc-3.51.1/admin/Makefile.am
    libiodbc-3.51.1/admin/Makefile.in
    libiodbc-3.51.1/admin/acinclude.m4
    [snip]
    libiodbc-3.51.1/samples/Makefile.in
    libiodbc-3.51.1/samples/iodbctest.c
    zsh, purple  4:14PM C/ % cd libiodbc-3.51.1
    zsh, purple  4:14PM libiodbc-3.51.1/ % ./configure --prefix=/usr/local/stow/iodbc-3.51.1
    checking for a BSD-compatible install... /bin/install -c
    checking whether build environment is sane... yes
    checking for gawk... gawk
    [snip]
    config.status: executing depfiles commands
    config.status: executing default commands
    zsh, purple  4:15PM libiodbc-3.51.1/ % make
    Making all in admin
    make[1]: Entering directory `/home/tim/C/libiodbc-3.51.1/admin'
    [snip]
    make[1]: Leaving directory `/home/tim/C/libiodbc-3.51.1'
    zsh, purple  4:15PM libiodbc-3.51.1/ % su root -c 'make install'
    

    It's advisable to install into /usr/local, or stow your installation into /usr/local, as that is searched by most other applications trying to locate iODBC.

    Testing ODBC

    Now is a good time to configure iODBC, by adding a DSN - create a file ~/.odbc.ini, edit it to look something like this:

    [ODBC Data Sources]
    PostgreSQL native localhost = PostgreSQL native driver
    Local Virtuoso Demo = localhost virtuoso (demo instance)
    Local Virtuoso = localhost virtuoso
    
    [Local Virtuoso Demo]
    Description = Virtuoso 3.1
    Driver      = /home/tim/virtuoso/lib/virtodbc31.so
    Address     = localhost:1112
    UserName    = dba
    User        = dba
    
    [Local Virtuoso]
    Description = Virtuoso 3.1
    Driver      = /home/tim/virtuoso/lib/virtodbc31.so
    Address     = localhost:1111
    UserName    = dba
    User        = dba
    
    [PostgreSQL native localhost]
    Driver     = /usr/lib/postgresql/lib/libodbcpsql.so
    Host       = localhost
    Server     = localhost
    ServerName = localhost
    Database   = tim
    UserName   = tim
    UID        = tim
    Port       = 5432
    

    It is advisable to export the environment variable ODBCINI to point to this file, too - depending on your shell, either:

    echo 'export ODBCINI=$HOME/.odbc.ini' >> .bashrc
    

    or

    echo 'setenv ODBCINI $HOME/.odbc.ini' >> .tcshrc
    

    as appropriate.

    You can now make a test connection with the iodbctest utility, thus:

    zsh, purple  4:54PM libiodbc-3.51.1/ % which iodbctest
    /usr/local/bin//iodbctest
    zsh, purple  4:54PM libiodbc-3.51.1/ % iodbctest      
    iODBC Demonstration program
    This program shows an interactive SQL processor
    Driver Manager: 03.51.0001.0908
    
    Enter ODBC connect string (? shows list): ?
    
    DSN                            | Description                   
    ---------------------------------------------------------------
    Local Virtuoso                 | localhost virtuoso 
    Local Virtuoso Demo            | localhost virtuoso (demo instance)
    MySQL                          | MySQL native driver           
    PostgreSQL native localhost    | PostgreSQL native driver      
    PostgreSQL OpenLink localhost  | PostgreSQL over OpenLink multi-tier
    Virtuoso30                     | OpenLink Virtuoso 3.0         
    
    Enter ODBC connect string (? shows list): DSN=Local Virtuoso Demo;UID=dba;PWD=cens0red
    Driver: 03.50.2505 OpenLink Virtuoso ODBC Driver
    
    SQL>
    

    If you see the SQL> prompt there, then all has gone well.

    Building Egenix' mxODBC module

    The last infrastructure hurdle to overcome is the building of Egenix's mxODBC module; download the sources from http://www.egenix.com/files/python/mxODBC.html, and run the following:

    zsh, purple  4:21PM egenix-mx-commercial-2.0.6/ % python setup.py install
    

    This requires root privileges, and will install in your python site-packages directory, thus:

    zsh, purple  4:24PM tim/ % ls /usr/lib/python2.2/site-packages/mx/ODBC 
    COPYRIGHT  LazyModule.py   Misc/     ODBC.pyo     __init__.pyc  unixODBC/
    Doc/       LazyModule.pyc  ODBC.py   README       __init__.pyo
    LICENSE    LazyModule.pyo  ODBC.pyc  __init__.py  iODBC/
    

    Sample script to use the module

    Finally, a small script to link it all together. You can use this to test simple functionality of iODBC, mxODBC and Python.

    #!/usr/bin/python
    
    import mx.ODBC.iODBC
    
    dsn="Local Virtuoso Demo"
    
    conn=mx.ODBC.iODBC.Connect (dsn, "dba", "cens0red")
    
    print "Content-Type: text/plain
    
    "
    print "Database Type: " + conn.getinfo (17)[1] + "
    "
    
    curshandle=conn.cursor()
    
    print "Top 10 in Shippers table:"
    curshandle.execute ("select top 10 * from Demo.demo.Shippers")
    for i in curshandle.fetchall():
    print i
    
    #print curshandle.fetchall()
    
    print "
    Creating and populating timtest table:"
    
    try:
    curshandle.execute ("drop table timtest") 
    except: 
    pass
    
    curshandle.execute ("create table timtest (id integer, str varchar (255))")
    curshandle.execute ("insert into timtest values (99, 'testing')")
    curshandle.execute ("select * from timtest")
    
    for i in curshandle.fetchall():
    print i
    

    On running it, you should see the following output:

    zsh, purple 11:22AM python/ % ./dbi-test.py
    Database Type: OpenLink Virtuoso
    
    Top 10 in Shippers table:
    (1, 'Speedy Express', '(503) 555-9831')
    (2, 'United Package', '(503) 555-3199')
    (3, 'Federal Shipping', '(503) 555-9931')
    
    Creating and populating timtest table:
    (99, 'testing')
    

    References