This HTML5 document contains 33 embedded RDF statements represented using HTML+Microdata notation.

The embedded RDF content will be recognized by any processor of HTML5 Microdata.

Subject Item

n2:IODBCPythonHOWTO

rdf:type

sioct:Comment atom:Entry

dcterms:created

2016-09-14T12:28:33.787095

dcterms:modified

2016-09-14T12:28:33.787095

rdfs:label

IODBCPythonHOWTO

foaf:maker

n16:this

n19:has_services

n20:item

dc:title

IODBCPythonHOWTO

sioc:link

n2:IODBCPythonHOWTO

sioc:id

d842e0e2d131e3d2d25f4f75d730c78a

opl:isDescribedUsing

n7:rdf

sioc:links_to

n8: n9:htm n2:ODBCOnUnix n10: n11: n13: n14: n17: n18:htm n21:htm n23:com n26:org n28:OpenLink

sioc:has_creator

n25:this

sioc:has_container

n4:iodbcWiki

atom:source

n4:iodbcWiki

atom:title

IODBCPythonHOWTO

atom:author

n16:this

atom:published

2016-09-14T12:28:33Z

atom:updated

2016-09-14T12:28:33Z

sioc:content

---+iODBC Python HOWTO %TOC% ---++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&#64;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 <verbatim> gzip -cd &lt; Python-2.2.3.tgz | tar xvpf - </verbatim> Enter the build directory, and run configure, specifying any optional configurations as desired: <verbatim> 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=&lt;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 </verbatim> ---++ Installing iODBC If you do not already have iODBC installed, either install an RPM from [[http://www.iodbc.org/][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: <verbatim> 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' </verbatim> 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: <verbatim> [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 </verbatim> It is advisable to export the environment variable ODBCINI to point to this file, too - depending on your shell, either: <verbatim> echo 'export ODBCINI=$HOME/.odbc.ini' >> .bashrc </verbatim> or <verbatim> echo 'setenv ODBCINI $HOME/.odbc.ini' >> .tcshrc </verbatim> as appropriate. You can now make a test connection with the _iodbctest_ utility, thus: <verbatim> 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&gt; </verbatim> If you see the SQL&gt; 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: <verbatim> zsh, purple 4:21PM egenix-mx-commercial-2.0.6/ % python setup.py install </verbatim> This requires root privileges, and will install in your python site-packages directory, thus: <verbatim> 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/ </verbatim> ---++ 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. <verbatim> #!/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 </verbatim> On running it, you should see the following output: <verbatim> 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') </verbatim> ---++ References * [[http://www.iodbc.org][iODBC]] * History of [[ODBCOnUnix][ODBC on Unix]] * [[http://www.openlinksw.com/][OpenLink Software]] - [[http://www.openlinksw.com/support/suppindx.htm][Support]] , [[http://www.openlinksw.com/support/teclinks.htm][links]] and [[http://www.openlinksw.com/info/docs/odbcwhp/tableof.htm][white-papers]]

sioc:topic

n4:iodbcWiki