mxODBC ODBC Database Interface for Python Veersion rsion 3 .3 3.
Copyright 1997-2000 by IKDS Marc-André Lemburg, Langenfeld Copyright 2000-2015 by eGenix.com GmbH, Langenfeld All rights reserved. No part of this work may be reproduced or used in a any form or by any means without written permission of the publisher. All product names and logos are trademarks of their respective owners.
Contents Contents 1. Introduction..........................................................1 1.1 Technical Overview............................................................. 1 1.2 Features............................................................................... 2 1.3 Requirements ...................................................................... 4 Windows.......................................................................................... 4 Unix ...................................
mxODBC - Python ODBC Database Interface Step 2 ............................................................................................. 10 Step 3 ............................................................................................. 10 Step 4 ............................................................................................. 10 2.2.4 Uninstall .............................................................................................. 10 2.
Contents 3.2.1 Looking for Windows ODBC Drivers ? ................................................ 19 3.2.2 Installing Windows ODBC Drivers ...................................................... 20 3.2.3 Setting up an ODBC Data Source ........................................................ 20 ODBC on 64-bit Windows Versions ............................................... 20 3.2.4 ODBC Configuration Files ................................................................... 20 ODBC.
mxODBC - Python ODBC Database Interface 3.3.6 Available Data Source Types (DSNs).................................................... 29 User Data Sources (User-DSN)....................................................... 29 System Data Sources (System-DSN) ............................................... 29 File Data Sources (File-DSN)........................................................... 30 3.3.7 DSN-less Connections .........................................................................
Contents Access Violations............................................................................ 41 Distributed Transaction Managers .................................................. 41 Kerberos / Windows Integrated Authentication............................... 41 MS SQL Server Native Client for Windows................................ 41 MS SQL Server Native Client for Linux ...................................... 42 EasySoft SQL Server Driver for Linux .........................................
mxODBC - Python ODBC Database Interface Static vs. forward-only Cursors ....................................................... 48 4.5 Sybase ASE........................................................................ 49 4.5.1 Available ODBC Drivers ...................................................................... 49 Sybase ASE ODBC driver ................................................................ 49 NULL issues with Sybase ASE ODBC driver ...............................
Contents Warnings when deleting/update more than one row at a time........ 56 4.9 Teradata ............................................................................ 56 4.9.1 Available ODBC Drivers ...................................................................... 56 Teradata ODBC Driver ................................................................... 56 Driver Notes ............................................................................. 57 Example Configuration for Unix.................
mxODBC - Python ODBC Database Interface 5.4 Thread Safety & Thread Friendliness ................................. 66 5.4.1 Connections and Cursors..................................................................... 66 5.4.2 Unlocking the Python Global Interpreter Lock (GIL)............................. 66 5.4.3 Threading Support ............................................................................... 66 5.5 Transaction Support ..........................................................
Contents 5.7.1 Database Schema Introspection........................................................... 76 5.7.2 Result Set Introspection ....................................................................... 76 Introspection via cursor.execute() .................................................. 77 Introspection via cursor.prepare() .................................................. 77 The cursor.description attribute...................................................... 77 name [0] ..................
mxODBC - Python ODBC Database Interface 5.10 mxODBC Subpackages ..................................................... 85 5.10.1 One API for all Subpackages ................................................................ 86 6. mxODBC Connection Objects........................... 87 6.1 Subpackage Support ......................................................... 87 6.2 Connection Type Object ................................................... 87 6.3 Connection Object Constructors ................
Contents .setconnectoption(option, value) ............................................... 93 .__enter__()............................................................................... 94 .__exit__(exc_type, exc_value, exc_tb) ...................................... 94 6.8 Connection Object Attributes............................................ 94 .autocommit.............................................................................. 94 .bindmethod .......................................................
mxODBC - Python ODBC Database Interface 6.8.1 Additional Attributes .......................................................................... 101 7. mxODBC Cursor Objects................................. 102 7.1 Relationship between Cursors and Connections ............. 102 7.1.1 Dependency on the Connection Object ............................................. 102 7.1.2 Using multiple Cursor Objects on a single Connection ...................... 102 7.2 Subpackage Support .......................
Contents .setinputsizes(sizes) ................................................................ 113 .setoutputsize(size[, column]) ................................................. 113 .__iter__()................................................................................ 113 .__enter__()............................................................................. 113 .__exit__(exc_type, exc_value, exc_tb) .................................... 113 7.6.1 Catalog Methods .................................
mxODBC - Python ODBC Database Interface 7.7 Cursor Object Attributes................................................. 134 8. Data Types supported by mxODBC ................ 139 8.1 mxODBC Parameter Binding .......................................... 139 8.1.1 Parameter Binding Styles ................................................................... 140 8.2 mxODBC Input Binding Modes ...................................... 141 8.2.1 Adjusting the Type Binding Mode ................................
Contents 8.6 Output Type Converter Functions ................................... 151 8.6.1 Converter Function Signatures........................................................... 152 position ................................................................................... 152 sqltype .................................................................................... 152 sqllen ...................................................................................... 152 binddata .......................
mxODBC - Python ODBC Database Interface ProgrammingError ................................................................... 159 NotSupportedError .................................................................. 159 10.2 SQL Error Mappings........................................................ 160 10.3 Exception Value Format .................................................. 160 10.4 Error Handlers ................................................................ 161 errorclass....................
Contents 12. mxODBC Globals and Constants .....................168 12.1 Subpackage Globals and Constants ................................. 168 12.2 mx.ODBC Globals and Constants ................................... 170 13. mx.ODBC.Misc.RowFactory Module................171 BIND_USING_SQLTYPE, BIND_USING_PYTHONTYPE ...........
mxODBC - Python ODBC Database Interface 14. mx.ODBC Driver/Manager Packages............... 173 14.1 Driver/Manager Subpackage Notes ................................. 173 14.1.1 Windows Platform Notes ................................................................... 173 14.1.2 Unix Platform Notes .......................................................................... 173 14.2 mx.ODBC.Manager -- Generic ODBC Driver Manager ... 173 Windows Platforms ..............................................
Contents 15. Hints & Links to other Resources.....................182 15.1 Running mxODBC from a CGI script............................... 182 15.2 Running mxODBC with mod_wsgi.................................. 182 mod_wsgi and Python 2.7 ............................................................ 182 Manifest work-around ............................................................. 183 15.3 Freezing mxODBC using py2exe ..................................... 183 15.4 More Sources of Information...
mxODBC - Python ODBC Database Interface 21. Copyright & License .........................................
1. Introduction 1. Introduction mxODBC has proven to be the most stable and versatile ODBC interface available for Python. It has been in active use for more than a decade and is actively maintained by eGenix.com to meet the requirements of modern database applications which our customers have built on top of mxODBC. This manual will give you an in-depth overview of mxODBC's capabilities and features. It is written as technical manual, so background in Python and database programming is needed.
mxODBC - Python ODBC Database Interface 1.2 Features • Python Database API 2.0 Compliance: the mxODBC API is fully Python DB-API 2.0 compatible and implements a large number of powerful extensions. • Support for all recent ODBC Version : mxODBC works with ODBC drivers implementing the ODBC version specifications 2.0 - 3.8. • Uses ODBC 3 APIs: provided the ODBC driver/manager is capable of using ODBC 3 APIs, mxODBC will use them for added efficiency.
1. Introduction • 1 o cursor.scroll() to scroll the cursor in result sets without actually fetching data. o cursor.prepare() to prepare SQL statements for execution, without actually running them. This allows creating pools of cursors for dedicated purposes. o connection.autocommit autocommit feature o cursor and connection objects usable as context managers o cursor.executemany() accepts iterators/generators as parameter "sequence". o cursor.
mxODBC - Python ODBC Database Interface • Highly Portable Codebase: in addition to the already supported platforms for mxODBC, eGenix.com provides custom porting services for more exotic platforms. • Easy installation: using Windows installers, .egg file package or our Python distutils compatible prebuilt Python packages.
1. Introduction mx.ODBC.Manager prefers iODBC over unixODBC over DataDirect if more than one ODBC driver manager is installed. • You should setup at least one configured and running ODBC data source for testing purposes. Mac OS X • Mac OS X 10.4/10.5 Intel and PPC 32-bit and Mac OS X 10.6 Intel 64-bit are supported. • Python 2.4 or later needs to be installed and working. • Mac OS X uses a variant of iODBC as system ODBC manager. On Mac OS X 10.4 and 10.5 this comes pre-installed with the system.
mxODBC - Python ODBC Database Interface 2. Installation The mxODBC database package is distributed as add-on for the eGenix.com mx Base Distribution (egenix-mx-base). Please visit the eGenix.com web-site to download the latest versions of both the eGenix.com mx Base Distribution and the eGenix.com mxODBC distribution for your platform and Python version.
2. Installation Please make sure that you download the right version for your Python installation. If you get import errors or notices of failed initialization, you likely have the wrong product version installed. These parameters make a difference: Operating System Platform All recent versions of these operating systems are supported: • Windows • Linux • Mac OS X • FreeBSD Please check the eGenix.com web-site for the detailed list of available downloads for these platforms.
mxODBC - Python ODBC Database Interface To find out which variant your Python version was compiled with, run the following command: python -c 'print "UCS%s"%len(u"x".encode("unicode-internal"))' This will either print out “UCS2” or “UCS4”. 2.2 Installation using Windows installers The installers provided by eGenix.com for use on Windows only include the mx.ODBC.Windows subpackage of mxODBC.
2. Installation License Files In order to use mxODBC, you will need license files from eGenix.com. If you want to test the product before buying it, you can request evaluation licenses via the eGenix.com web-site at http://www.egenix.com/. When buying licenses from the eGenix.com online shop (http://shop.egenix.com/), you will receive the license files immediately after purchase.
mxODBC - Python ODBC Database Interface Note: Depending on your Python installation, you may need admin privileges on Windows to successfully complete the installation. Step 2 The installer will then ask you to accept the license, choose the Python version and then to start the install process. If the listbox showing the installed Python versions is empty, it is likely that you have chosen the wrong Windows installer for your Python version. Please go back to the eGenix.
2. Installation 2.3 Installation using egg package archives We assume that you have already have setuptools and easy_install installed in your Python installation. The examples in this section refer to a Unix or Mac OS X installation, but it is also possible to install .egg packages on Windows.
mxODBC - Python ODBC Database Interface In both cases, the license files are sent to the email address you specified during the purchase process or from which you wrote the evaluation license request in form of a ZIP license archive attached to the license email – usually named licenses.zip. The license archive licenses.zip contains one subdirectory per license you bought. The directories are named after the license key for each license.
2. Installation Note that you may need to have admin or root privileges in order to successfully complete the following step, unless you are using a virtualenv-based setup. If you got UCS2 in step 1, run the following command using the easy_install script from the Python installation you intend to use: easy_install -i http://downloads.egenix.com/python/index/ucs2/ \ egenix-mxodbc If you got UCS4 in step 1, use this command: easy_install -i http://downloads.egenix.
mxODBC - Python ODBC Database Interface 2.3.4 Uninstall Since setuptools doesn't provide an uninstall command you have to manually remove the installation: 1. remove the egenix-mxodbc.* egg directory from your Python sitepackages/ directory and 2. edit the file easy-install.pth in that directory to remove the corresponding egg entry. 2.
2. Installation In both cases, the license files are sent to the email address you specified during the purchase process or from which you wrote the evaluation license request in form of a ZIP license archive attached to the license email – usually named licenses.zip. The license archive licenses.zip contains one subdirectory per license you bought. The directories are named after the license key for each license.
mxODBC - Python ODBC Database Interface Note that you may need to have admin or root privileges in order to successfully complete the following step, unless you are using a virtualenv-based setup. 1. First, unzip the downloaded prebuilt package archive to a temporary directory. 2. Then run the following command using the Python installation you intend to use in the package directory egenix-mxodbc-3.3.*/: python setup.py install Note that you can use the standard distutils install command options, e.g.
2. Installation python setup.py uninstall Manual Uninstall If you no longer have the older installation package, just remove the sitepackages/mx/ODBC directory with all its subdirectories.
mxODBC - Python ODBC Database Interface 3. Access Databases using mxODBC mxODBC provides an easy to use way of accessing the ODBC API of ODBC managers and drivers from Python. Together with a suitable ODBC driver installed on the machine where you are running the Python application, you can connect to your databases with a single Python call. 3.
3. Access Databases using mxODBC • If you are running a 64-bit Python application, you will also have to have a 64-bit ODBC manager and ODBC driver installed. • If you are running a 32-bit Python application, you need an 32-bit ODBC manager and ODBC driver. Note that the ODBC manager may be capable of translating 32-bit or 64-bit function calls to whatever the ODBC driver supports (this is called thunking). Please check the documentation of your ODBC manager for details. 3.
mxODBC - Python ODBC Database Interface 3.2.2 Installing Windows ODBC Drivers Please consult the documentation of your database for ODBC driver installation instructions. These are usually installed in the same way as any other application on Windows, but their respective setup wizards and options are usually different in layout and depend on the target database. 3.2.3 Setting up an ODBC Data Source Data sources are setup using the Windows ODBC Manager on Windows.
3. Access Databases using mxODBC ODBC.INI - ODBC Data Source Configuration This INI-file provides data source information using one INI-section per data source. In addition to the data source sections, there are also a number of higher-level sections: [ODBC] This section is used to configure driver related ODBC manager settings such as ODBC call tracing. The settings in this section apply to all data sources.
mxODBC - Python ODBC Database Interface 3.2.5 Available Data Source Types (DSNs) There are three kinds of data sources that you can install on Windows machines: 1. User Data Sources (User-DSN) 2. System Data Sources (System-DSN) 3. File Data Sources (File-DSN) User Data Sources (User-DSN) User data sources are only visible to the user creating them. Other users normally do not have access to these data source definitions.
3. Access Databases using mxODBC See the ODBC File Data Source documentation for more details. 3.2.6 DSN-less Connections If you don't want to bother setting up a data source in the ODBC manager, you can also use a DSN-less connection setup. Pros and Cons of using DSN-less Connections These setups include all required driver and connection information in the connection string itself.
mxODBC - Python ODBC Database Interface 3.3 Accessing Databases from Unix mxODBC is often used to access databases across a network. A very typical use case is that of connecting to MS SQL Server, Oracle or DB2 from a Unix machine. eGenix.com has collected some information in the next section 4. Accessing Popular Databases which may help you in finding the right solution for this kind of setup. We recommend that you always use an ODBC manager on Unix to access these driver setups, e.g.
3. Access Databases using mxODBC administrative overhead, eGenix has developed a general solution to the problem for Python applications, the client-server product called eGenix mxODBC Connect. eGenix mxODBC Connect provides a highly portable client side Python interface module mxODBC Connect Client which connects to a server side service application called mxODBC Connect Server.
mxODBC - Python ODBC Database Interface The GUI tools can typically be found in the System part of the menu. For more details description please see the ODBC manager manuals: • unixODBC User Manual • iODBC User Manual • DataDirect User Manual • Mac OS X ODBC Administrator Manual2 Since the layout and operation of these tools is often similar to the Windows ODBC manager, you can also have a look at the Windows ODBC documentation for details.
3. Access Databases using mxODBC iODBC needs this section in the odbc.ini file. unixODBC in the odbcinst.ini file. More recent DataDirect ODBC manager versions accept the section in both files, older version need it in the odbc.ini file. [ODBC Data Sources] This section contains one entry per configured data source, mapping the data source name to a description. Example: [ODBC] Trace = 0 TraceFile = /tmp/odbc.log [ODBC Data Sources] sybasease12 = Sybase ASE 12 on sybasease12.example.
mxODBC - Python ODBC Database Interface iODBC needs this section in the odbc.ini file. unixODBC in the odbcinst.ini file. More recent DataDirect ODBC manager versions accept the section in both files, older version need it in the odbc.ini file. [ODBC Drivers] This section contains one entry per configured and installed ODBC driver, mapping the driver name to the string "Installed". Example: [ODBC] Trace = 0 TraceFile = /tmp/odbc.
3. Access Databases using mxODBC Note that some driver manager do not support this environment variable: unixODBC and iODBC support the variable, the DataDirect ODBC manager doesn't. 3.3.6 Available Data Source Types (DSNs) There are three kinds of data sources that you can install on Windows machines: 1. User Data Sources (User-DSN) 2. System Data Sources (System-DSN) 3. File Data Sources (File-DSN) User Data Sources (User-DSN) User data sources are only visible to the user creating them.
mxODBC - Python ODBC Database Interface File Data Sources (File-DSN) File data sources are special in the sense that they store the data source connection information and options in a dedicated file rather than in the registry. This can be useful if you want to manage data sources across many servers and keep the data source files on a central file server. You create such DSN files using the ODBC manager (if supported) or by using a text editor.
3. Access Databases using mxODBC you upgrade an ODBC driver to a newer version, you may have to change all DSN-less connection setups due to changes in the ODBC driver name. We recommend to only use DSN-less setups if absolutely necessary or in cases where access to the ODBC configuration files is otherwise not possible. DNS-less Connection String A DNS-less connection provides all configuration information you'd normally place into the ~/.odbc.
mxODBC - Python ODBC Database Interface 4. Accessing Popular Databases This section provides information on available ODBC drivers for various popular database as well as notes regarding setup, functionality or available workarounds for compatibility problems eGenix.com found with the drivers. We have also included the resp. version information of the drivers we have tested successfully with mxODBC. 4.1 MS SQL Server 4.1.
4. Accessing Popular Databases Supports SQL Server 2005, 2008, 2008R2, 2012. This version no longer supports SQL Server 2000. • Microsoft SQL Server 2008 R2 SP1 Feature Pack Supports SQL Server 2000, 2005, 2008, 2008R2. • Microsoft SQL Server 2008 Service Pack 2 Feature Pack Supports SQL Server 2000, 2005, 2008. • Microsoft SQL Server 2005 Feature Pack Downloads Supports SQL Server 2000 and 2005.
mxODBC - Python ODBC Database Interface See http://msdn.microsoft.com/en-us/library/ms188635.aspx for details on the various formats and how to configure them. Multiple active result sets (MARS) on a single connection The SQL Server Native Client per default does not support having more than one active result set per connection. This means that you cannot have two cursors on the same connection, which both have active result sets.
4. Accessing Popular Databases Due to the way the SQL Server Native Client works, the data from the result sets is sent to the client before the data for the output parameters. See e.g. the IBM Knowledge Center or StackOverflow for details. Since mxODBC returns the output parameters immediately after executing a statement via the cursor.callproc(), cursors.execute() or cursor.executedirect() APIs, the changes send to the client after the result sets are not seen by mxODBC. The Python DB-API 2.
mxODBC - Python ODBC Database Interface The new Microsoft SQL Server Native Client for Linux is a port of the SQL Server Native Client for Windows to Linux. It provides an ODBC driver for SQL Server 2008 R2 and 2012 on Linux, but also works with SQL Server 2005 and 2008. At the moment, only a 64-bit version is available and then only for RedHat RHEL 5 and 6 systems. Most of the comments for the Windows driver also apply to the Linux driver. This driver also supports the MARS feature.
4. Accessing Popular Databases [ODBC Drivers] MSNativeClient = Installed [MSNativeClient] Driver = /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 Description = MS SQL Server Native Client 11 Threading = 1 • Add a data source to the /etc/odbc.ini (or ~/.odbc.ini): [mssqlserver2008] Driver = /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.
mxODBC - Python ODBC Database Interface • Most other operations work as expected, but please note that the driver is still under heavy development in some areas. You should test it thoroughly before using it on a production system. • The FreeTDS website mentions that the driver has some restrictions. Please see the FreeTDS user guide for details. • Be sure to use the same ODBC manager with FreeTDS as the one you have compiled it with. If you mix e.g.
4. Accessing Popular Databases [mssql] Driver = /usr/local/freetds/lib/libtdsodbc.so Description = MS SQL Server 2008 running on Monet Trace = 0 Servername = MONET Database = tempdb Note that the libtdsodbc.so file may be located in a different directory on your machine. • Using these settings, you can then connect to SQL Server using a simple connection string such as: "DSN=mssql;UID=username;PWD=password" 4.1.
mxODBC - Python ODBC Database Interface mxODBC addresses this by defaulting to connection.timestampresolution = 1000000 for SQL Server 2005 and earlier. For SQL Server 2008 and later, mxODBC uses connection.timestampresolution = 100. This allows using timestamp values with SQL Server which use higher precision values without running into errors from the database such as: HY104 - [Microsoft][SQL precision value. Server Native Client 11.
4. Accessing Popular Databases Access Violations If you are experiencing problems related to access violations, like e.g. ProgrammingError: ('37000', 0, '[Microsoft][ODBC Driver]Syntax error or access violation', 4498) SQL Server a possible reason could be that you are using a function or stored procedure which is generating output using PRINT or that it uses RAISEERROR to report an error with the parameters or values.
mxODBC - Python ODBC Database Interface string. Please see this article for details: Service Principal Name (SPN) Support in Client Connections MS SQL Server Native Client for Linux Please see this article for details on how to setup the driver to use Kerberos authentication: Using Integrated Authentication Also see the EasySoft documentation below for some added details around the setup of Kerberos authentication on Linux.
4. Accessing Popular Databases 4.2 MS Access Database 4.2.1 Available ODBC Drivers MS Access ODBC Driver Tested with MDAC 2.8 SP1 Access ODBC driver. MS Access ships with an ODBC driver for the database which is then installed on the same machine as MS Access (or Office). The drivers are also available separately as part of the MDAC package. MDAC 2.8 SP1 can be downloaded from this page: http://www.microsoft.com/downloads/details.
mxODBC - Python ODBC Database Interface Driver Notes • eGenix.com has had reports about memory leaks occurring with the Oracle driver when used in long running applications. mxODBC itself does not have any known memory leaks and there are no problems with other available drivers for Oracle. • Oracle regards empty strings as NULL values. As a result inserting an empty string into a VARCHAR column can result in the Oracle driver returning NULL for that column when fetching data.
4. Accessing Popular Databases Driver = /opt/oracle/instantclient_11_2/libsqora.so.11.
mxODBC - Python ODBC Database Interface 4.3.2 General Notes Oracle tnsnames.ora file When connecting to Oracle database you typically have to provide a ~/.tnsnames.ora file which has the network connection information of your Oracle database servers. If you want to use a different file location, be sure to set the environment variable TNS_ADMIN to the path of the tnsnames.ora file. 4.4 IBM DB2 4.4.1 Available ODBC Drivers IBM ODBC Driver for Unix/Windows DB2 servers Tested with IBM DB2 9.7 ODBC driver.
4. Accessing Popular Databases IBM ODBC Driver for iSeries / AS/400 DB2 servers IBM has a Linux ODBC driver which makes this setup possible. See their webpage on the "iSeries ODBC driver for Linux" for details: http://www-03.ibm.com/systems/i/software/access/linux/guide/index.html OpenLink ODBC Driver for DB2 Homepage: http://www.openlinksw.com/ DataDirect ODBC Driver for DB2 Homepage: http://www.datadirect.com/ 4.4.
mxODBC - Python ODBC Database Interface the environment variables. Without having these set, mxODBC will fail to load and give you a traceback: Traceback (most recent call last): ... from mxODBC import * ImportError: initialization of module mxODBC failed (mxODBC.InterfaceError:failed to retrieve error information (line 6778, rc=-1)) Linker Paths Unfortunately, the provided db2profile / db2cshrs shell scripts are buggy in some versions of DB2, so simply sourcing them won't necessarily work.
4. Accessing Popular Databases 4.5 Sybase ASE 4.5.1 Available ODBC Drivers Sybase ASE ODBC driver Homepage: http://www.sybase.com/ Tested with Sybase ASE 15.5 and 15.7 ODBC drivers Sybase ASE ships with ODBC drivers for both 32-bit and 64-bit platforms. The drivers are part of the ASE server packages. The ASE 15.5 ODBC driver can also be used to connect to a Sybase ASE 12.x server database. In fact, this setup is recommended, since the 15.
mxODBC - Python ODBC Database Interface Since this is a bug in the ODBC driver, future ESDs may fix the issue. In any case, please carefully check for this problem before using BIGINT columns with the driver. Driver Notes • In mxODBC 3.3, we switched the default parameter binding method from Python type to SQL type. This resolves issues with the Sybase ODBC driver complaining about the wrong Python object type being used for certain database data types.
4. Accessing Popular Databases EasySoft ODBC Driver for Sybase Homepage: http://www.easysoft.com/ OpenLink ODBC Driver for Sybase Homepage: http://www.openlinksw.com/ DataDirect ODBC Driver for Sybase Homepage: http://www.datadirect.com/ Actual Technologies Mac OS X ODBC Driver for Sybase Homepage: http://www.actualtech.com/ When using the driver on Mac OS X 10.6 (Snow Leopard), be sure to use version 3.0.9 or higher, since earlier versions had a problem with fetching data. 4.6 PostgreSQL 4.6.
mxODBC - Python ODBC Database Interface • Unicode data is supported. It works best with the NATIVE_UNICODE_STRINGFORMAT mode. You can also use the autotranscoding feature of mxODBC with UTF-8 as encoding. • The driver only supports forward scrolling with relative increments of +1. Other values result in a driver error. As a result, only cursor.scroll(+1) can be used. Example Configuration for Unix • Add a PostgreSQL driver section to the ~/.odbcinst.
4. Accessing Popular Databases Actual Technologies Mac OS X ODBC Driver for PostgreSQL Homepage: http://www.actualtech.com/ When using the driver on Mac OS X 10.6 (Snow Leopard), be sure to use version 3.0.9 or higher, since earlier versions had a problem with fetching data. 4.7 MySQL 4.7.1 Available ODBC Drivers MySQL ODBC Driver Homepage: http://dev.mysql.com/downloads/connector/odbc/ Tested with MySQL ODBC driver 5.2.6 and MySQL 5.5 and 5.6.
mxODBC - Python ODBC Database Interface • The MySQL ODBC driver does not always update the .rownumber to the correct value, especially when using .scroll(). For client side cursors, mxODBC corrects this using an emulation for .rownumber. • When using the ODBC driver RPMs available from www.mysql.com, please be sure to also have the MySQL shared libs RPM and the MySQL development RPM installed. • Some older MySQL + ODBC driver setups eGenix.
4. Accessing Popular Databases Actual Technologies Mac OS X ODBC Driver for MySQL Homepage: http://www.actualtech.com/ When using the driver on Mac OS X 10.6 (Snow Leopard), be sure to use version 3.0.9 or higher, since earlier versions had a problem with fetching data. 4.7.2 General Notes Depending on whether you use a transactional MySQL storage backend or not, clearing the auto-commit flag at connection time, which is normally done per default by the connection constructors, will not work.
mxODBC - Python ODBC Database Interface Driver = /usr/local/maxdb/lib/libsdbodbcw.so Description = MaxDB ODBC Driver • Edit your ~/.odbc.ini file and add a MaxDB section (the location of the driver may be different on your system; be sure to use the Unicode variant which ends with '…w.so'). It is also necessary to point LD_LIBRARY_PATH to the directory where the driver itself is located. [maxdb] DRIVER = /usr/local/maxdb/lib/libsdbodbcw.so ServerDB = MYDB ServerNode = maxdb.example.
4. Accessing Popular Databases Driver Notes • The mx.ODBC.DataDirect package is currently only available for Linux 32-bit and 64-bit systems. If you need the package on other platforms, please write to support@egenix.com for assistance. • The DataDirect ODBC driver manager is included in the same directory as the Teradata ODBC driver itself. If you setup LD_LIBRARY_PATH to the directory where the driver is located, mxODBC will automatically use the right DataDirect ODBC driver manager, e.g.
mxODBC - Python ODBC Database Interface • Teradata has the tendency to return non-ordered result sets in random order. This is due to the way the database works internally. If you need to rely on a reproducible result set order, please add an ORDER BY clause to the SELECT statements as necessary. Example Configuration for Unix • Setup your OS environment so that the ODBC manager can find and load the driver (this is for the version 14.
4. Accessing Popular Databases # SessionMode can be Teradata or ANSI SessionMode = # Cursor open checks StCheckLevel = 0 # Enable TCP_NODELAY ? TCPNoDelay = Yes # Port to use on the database servers TDMSTPortNumber = # Use BLOB and CLOB ? UseNativeLOBSupport = Yes Also see the above driver notes regarding how to format the .odbc.ini file.
mxODBC - Python ODBC Database Interface Netezza and Unicode Unicode data exchange doesn't work well when using the Netezza driver with the DataDirect manager. The unixODBC manager interface does not have these issues and works fine with Unicode if the Netezza driver is configured for UTF-16 data using the UnicodeTranslationOption = utf16 driver configuration option. Example Configuration for Unix • Edit your ~/.odbcinst.
4. Accessing Popular Databases DataDirect ODBC Driver for Netezza Homepage: http://www.datadirect.com/ 4.11 Other Databases If you want to run mxODBC in a Unix environment and your database doesn't provide an Unix ODBC driver, you can try the drivers sold by these ODBC driver specialists: 4.11.1 EasySoft ODBC Driver Packages Homepage: http://www.easysoft.com/ EasySoft also maintains the open source ODBC manager unixODBC. 4.11.2 OpenLink Homepage: http://www.openlinksw.
mxODBC - Python ODBC Database Interface 4.11.5 Alternative solution: mxODBC Connect If you would like to connect to a database for which you don't have a Unix ODBC driver, you can also try our mxODBC Connect Python Database Interface which just needs an ODBC driver on the server side and provides a cross-platform networked interface to this for the client side. This makes it very easy to connect to e.g. a Windows-based database from Unix, BSD or Mac OS X.
5. mxODBC Overview 5. mxODBC Overview mxODBC is structured as Python package to support interfaces to many different ODBC managers and drivers. Each of these interfaces is accessible as subpackage of the mx.ODBC Python package, e.g. on Windows you'd normally use the mx.ODBC.Windows subpackage to access the Windows ODBC manager; on Unix this would typically be the mx.ODBC.iODBC, mx.ODBC.unixODBC or the mx.ODBC.DataDirect package depending on which of these Unix ODBC managers you have installed.
mxODBC - Python ODBC Database Interface • db.setinputsizes() • The type objects / constructors (formerly found in the dbi module defined by DB API 1.0) are only needed if you want to write database independent code. • The connection constructor is available under three different names: ODBC() (DB API 1.0), connect() (DB API 2.0) and Connect() (mxODBC specific). See the next section for details on the used parameters.
5. mxODBC Overview You also can access the MS ODBC online reference from the Microsoft MDAC web-site. Please note that not all ODBC drivers and databases support the complete set of available introspection parameters. When using them, please make sure that the databases supported by your application do implement the parameters used in your application. 5.3 Supported ODBC Versions mxODBC can be configured to use ODBC 2.x or 3.x interfaces by setting the ODBCVER symbol in mxODBC.h to the needed value.
mxODBC - Python ODBC Database Interface 5.4 Thread Safety & Thread Friendliness mxODBC itself is written in a thread safe way. There are no module globals being used and thus no locking is necessary. 5.4.1 Connections and Cursors In general when using a separate database connection for each thread, you shouldn't run into threading problems. If you do, it is more likely that the ODBC driver is not 100% thread safe and thus not 100% ODBC compatible.
5. mxODBC Overview those cases where you would really back out of a certain modification, e.g. due to an unexpected error in your program. mxODBC turns off auto-commit whenever it creates a new connection, ie. it runs the connection in manual commit mode -- unless the connection constructor flag clear_auto_commit is set to 0 or the database does not provide transactions. You can adjust the connection's commit mode after creating it using the connection.autocommit attribute. See 5.5.
mxODBC - Python ODBC Database Interface Some databases for which mxODBC provides special subpackages such as MySQL don't have transaction support, since the database does not provide transaction support. For these subpackages, the .rollback() connection method is not available at all (i.e. calling it produces an AttributeError) and the clear_auto_commit flag on connection constructors defaults to 0. 5.5.
5. mxODBC Overview Results can be retrieved through output parameter, input/output parameters, or result sets. Depending on the database backend, it is also possible to combine both. Retrieving output parameters from stored procedures When not providing the optional parametertypes parameter as in the above example, all parameters are considered to be input parameters, so results will be a list copy of parameters.
mxODBC - Python ODBC Database Interface The ODBC syntax for calling a stored procedure is as follows: {call procedure-name [([parameter][,[parameter]]...)]} For stored functions or procedures with return status, the ODBC syntax is as follows: {? = call function-name [([parameter][,[parameter]]...)]} Using the above syntax, you can call stored procedures through one of the .execute*() calls, e.g. results = cursor.
5. mxODBC Overview parameters, parametertypes) The return value from the function will be passed back as first parameter. Accordingly, the first entry in parametertypes must be set to SQL.PARAM_OUTPUT. Example: from mx.ODBC.unixODBC import SQL … results = cursor.execute( '{? = call function_params(?,?)}', [0, 1, 0], parametertypes=(SQL.PARAM_OUTPUT, SQL.PARAM_INPUT, SQL.PARAM_OUTPUT)) if results == (4, 1, 2): print 'Works.
mxODBC - Python ODBC Database Interface SQL.PARAM_INPUT The parameter is an input parameter. Output values are not allowed and may raise a database error or simply be ignored (this depends on the ODBC driver and database). SQL.PARAM_OUTPUT The parameter is an output parameter. Input values are ignored.
5. mxODBC Overview This generates a result set with column COLUMN_TYPE which has the needed information. Please see the documentation in section cursor.procedurecolumns() for details. 7.6.1 Catalog Methods for 5.6.4 Special constraints of some ODBC drivers Mixing output parameters and output result sets Some ODBC drivers, most notably, the MS SQL Server Native Client, send the output parameter data only after any output result sets which the stored procedure may have created.
mxODBC - Python ODBC Database Interface Especially for numeric data, this may both be inefficient and inconvenient, so it's better to pass in a value which matches the output parameter type such as 0 for integer or 0.0 for floating point data. 5.6.5 Using Result Sets for passing back Output Data It is also possible and to pass back data from the stored procedure via standard result sets which can be fetched from mxODBC using the cursor.execute*() methods.
5. mxODBC Overview SELECT @a * 3; Oracle Ref Cursors as Output Parameters Oracle has the concept of reference cursors, which provide a similar way to pass cursors to the stored procedure caller. Instead of defining an output variable or a set of output variables, you simply define a REF CURSOR as output variable in your stored procedure. You can then access the open cursor after calling the stored procedure by simply using the standard .fetch*() and .nextset() APIs to access the results.
mxODBC - Python ODBC Database Interface (a INTEGER, rs OUT refcursor) AS $$ BEGIN OPEN rs FOR SELECT a * 3; END; $$ LANGUAGE plpgsql; 5.6.6 SQL Output Statements in Stored Procedures You should not use any output SQL statements such as "PRINT" in the stored procedures, since this will cause at least some ODBC drivers (notably the MS SQL Server one) to turn the output into an SQL error which causes the execution to fail.
5. mxODBC Overview Introspection via cursor.execute() In mxODBC this can be done right after executing a SQL statement using one of the cursor.execute*() methods by looking at the cursor.description attribute. Introspection via cursor.prepare() Alternatively, you can use the cursor.prepare() method to just prepare execution of a SQL statement - without actually executing it. This may be desirable in case the result set is not immediately needed or the query would require a long time to execute.
mxODBC - Python ODBC Database Interface null_ok [6] Returns 1 if the column can contain NULL values (which are returned as None in Python). The cursor.getcolattribute() method The cursor.getcolattribute() method provides more information about the result set columns than the Python DB-API compatible cursor.description sequence. It also allows querying for auto-increment columns, the base column and table name, the database specific type name, etc. Please see the document for cursor.
5. mxODBC Overview SQL.CURSOR_STATIC The result set is made static by creating a static copy of the result set after opening the cursor. As a result, any changes to the result set after opening the cursor will not be visible to the client. Databases typically require setting the cursor type to static to support backwards scrolling in the result set via the cursor.scroll() call. Please note that creating a static copy can result in a significant performance degradation, esp.
mxODBC - Python ODBC Database Interface Backwards Compatibility Notice: Please note that mxODBC 3.2 used to set the default cursor type depending on whether the database supports static cursors or not. For those that do, it used static cursors, for all others, it used forward-only cursors. In mxODBC 3.3, we have changed this back to defaulting to forward-only cursors for all databases due to performance issues with static cursors. 5.8.3 Effects of the Cursor Type on cursor.
5. mxODBC Overview PostgreSQL mxODBC defaults to forward only cursors, since the driver becomes unusable with other settings. IBM DB2 Just like MS SQL Server, IBM DB2 supports static cursors as well, so you can enable these, if you need scrolling support or .rowcount information. There is a performance penalty of about 2x in using static cursors. connection = mx.ODBC.Windows.DriverConnect(…) connection.cursortype = mx.ODBC.Windows.SQL.
mxODBC - Python ODBC Database Interface Setting the value has an immediate effect on subsequent cursor.fetch*() calls. mxODBC does not reset this attribute after the fetch operation, so the setting persists until set to another constructor or None. Setting cursor.row to None resets the row constructor to the mxODBC default of using Python tuples. Examples: # Have mxODBC return lists for rows instead of tuples: cursor.row = list result_set = cursor.fetchall() # Have mxODBC return tuples again: cursor.
5. mxODBC Overview This makes it easy to define your own factory functions to programmatically define row classes based on the cursor.description or other cursor parameters. Row Factories and multiple Result Sets If you are using multiple result sets, the cursor.rowfactory is called for the first fetch in each of the result sets you are reading from the database.
mxODBC - Python ODBC Database Interface cursor.rowfactory = RowFactory.ListRowFactory cursor.execute('select x, Y, z from mytable') row = cursor.fetchone() print (row[0], row[1], row[2], row.x, row.y, row.z) row[0] = 10 row[1] = 'abc' print (row[:2]) # will print [10, 'abc'] row.x = 20 row.y = 'def' print (row[:2]) # will print [20, 'def'] The row objects are also usable as input for the cursor.execute*() methods. Example: cursor.execute('insert into mytable values (x, Y, z)', row) RowFactory.
5. mxODBC Overview Factory created Row Classes and pickle Because of the way these row classes are dynamically created, they are by default not pickleable. In order to be pickleable, they would have to be saved to a module namespace, so that pickle can recreate them at load time. Since the creation parameters depend on the cursor state at creation time, this is not easily possible.
mxODBC - Python ODBC Database Interface 5.10.1 One API for all Subpackages To make applications portable between ODBC database backends, each of these subpackages use the same names and API signatures, in fact, the same mxODBC implementation is used for each of the subpackages, customized to meet the respective ODBC driver/manager's specific requirements. As an example, say if you are using the mx.ODBC.Windows subpackage, then the constructor to call would be mx.ODBC.Windows.DriverConnect().
6. mxODBC Connection Objects 6. mxODBC Connection Objects Connection objects provide the communication link between your Python application and the database. They are also the scope of transactions you perform. Each connection can be setup to your specific needs, multiple connections may be opened at the same time. 6.1 Subpackage Support Connection objects are supported by all subpackages included in mxODBC.
mxODBC - Python ODBC Database Interface If you connect to the database through an ODBC manager, you should use the DriverConnect() API since this allows passing more configuration information to the manager and thus provides more flexibility over this interface. See the following section 6.4 Default Transaction Settings for details on clear_auto_commit. connection_options may be given as list of (option, value) tuples to set pre-connect ODBC connection options.
6. mxODBC Connection Objects The DriverConnect() API is only available if the ODBC driver or ODBC driver manager supports this. It is available on all supported ODBC driver manager subpackages such as the one for Windows and iODBC/unixODBC/DataDirect on Unix platforms. See the subpackages section for details. ODBC(dsn, user='', password='', clear_auto_commit=1, errorhandler=None) Is just an alias for Connect() needed for Python DB API 1.0 compliance. 6.
mxODBC - Python ODBC Database Interface 6.4.2 Errors due to missing Transaction Support If you get an exception during connect telling you that the driver is not capable or does not support transactions, e.g. mxODBC.NotSupportedError: ('S1C00', 84, '[Microsoft][ODBC Excel Driver]Driver not capable ', 4226), try to connect with clear_auto_commit set to 0. mxODBC will then keep autocommit switched on and the connection will operate in auto-commit mode. 6.5 Connection objects as context managers 6.5.
6. mxODBC Connection Objects Cursors also support the context manager API, so the above could be simplified even more to: with connection: with connection.cursor() as cursor: cursor.execute('INSERT INTO table VALUES (?, ?)', (1, 2)) … other tasks … 6.6 Unicode/ANSI Connections Starting with mxODBC 3.1, it is possible to tell the ODBC driver manager whether to use the Unicode ODBC interface of a supporting ODBC driver or the ANSI (8bit string) ODBC interface at connection time. 6.6.
mxODBC - Python ODBC Database Interface # string db = mx.ODBC.Windows.DriverConnect('DSN=mydb;UID=uid;PWD=pwd') 6.7 Connection Object Methods .close() Close the connection now (rather than automatically at garbage collection time). The connection will be unusable from this point on; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection. .
6. mxODBC Connection Objects The method returns a tuple (integer, string) giving an integer decoding (in native integer byte order) of the first bytes of the API's result as well as the raw buffer data as string. It is up to the caller to decode the data (e.g. using the struct module). This API gives you a very wide range of information about the underlying database and its capabilities. See the ODBC SQLGetInfo API Documentation for more information. .
mxODBC - Python ODBC Database Interface .__enter__() Returns the connection itself. This method makes connection objects usable as context manager (together with the .__exit__() method) and is called when entering a with-block (new in Python 2.5). .__exit__(exc_type, exc_value, exc_tb) Returns True in case exc_type is set to None (no exception set) and commits the current transaction. Returns False in case exc_type is set to an exception and rolls back the current transaction.
6. mxODBC Connection Objects .cursortype Read/write attribute that sets the default ODBC cursor type for cursors created on this connection. Possible values are: SQL.CURSOR_FORWARD_ONLY The cursor only scrolls forward. This is the default setting for all databases.12 SQL.CURSOR_STATIC The result set is made static by creating a static copy of the result set after opening the cursor. As a result, any changes to the result set after opening the cursor will not be visible to the client. 12 SQL.
mxODBC - Python ODBC Database Interface TUPLE_DATETIMEFORMAT Python tuples as defined in the Supported Data Types section. STRING_DATETIMEFORMAT Python strings. The format used depends on the internal settings of the database. See your database's manuals for the exact format and ways to change it. We strongly suggest always using the DateTime/DateTimeDelta instances. Note that changing the values of this attribute will not change the date/time format for existing cursors using this connection.
6. mxODBC Connection Objects The connection.encoding is used on connection related APIs and also passed to cursors created on the connection at creation time. Cursors store the encoding in cursor.encoding and cursor related APIs will use the cursor setting instead of the connection setting. .errorhandler Read/write attribute which defines the error handler function to use. If set to None, the default handling is used, i.e. errors and warnings all raise an exception and get appended to the .messages list.
mxODBC - Python ODBC Database Interface Example: 'SELECT * FROM MyTable WHERE A=? AND B=?' used with a parameter tuple (1, 2) would result in the database executing the query 'SELECT * FROM MyTable WHERE A=1 AND B=2'. 'named' The 'named' parameter binding style is used by the native database interfaces of e.g. Oracle. Parameters in SQL statements used on cursor.execute*() methods are marked with a colon followed by a name, e.g. ':a' or ':1'.
6. mxODBC Connection Objects On input, Python 8-bit strings are passed to the ODBC driver as-is. Unicode objects are converted to Python 8-bit strings assuming the cursor's encoding setting (see the cursor.encoding attribute) prior to passing them to the ODBC driver. On output, all string columns are fetched as strings and passed back as Python 8-bit string objects. Unicode data from the database is converted to Python 8-bit string objects assuming the cursor's encoding setting (see the cursor.
mxODBC - Python ODBC Database Interface type 'S1003' is raised whenever trying to read data from the database in this .stringformat mode. You can use the included mx/ODBC/Misc/test.pyc script to find out whether the database backend support Unicode or not. Binary and other plain data columns will still use 8-bit strings for interfacing, since storing this data in Unicode objects would cause trouble.
6. mxODBC Connection Objects The DB-API 2.0 mandates that database warnings must raised as mx.ODBC.Warning exception, but mostly because at the time of writing, the Python warning module did not yet exist. For some applications it may be more useful to report warnings via Python warnings. The application could then use the standard Python warning filters to report or filter the warnings in an appropriate way. Another alternative is to simply ignore such warnings.
mxODBC - Python ODBC Database Interface 7. mxODBC Cursor Objects These objects represent a database cursor: an object which is used to manage the context of a database query operation. This includes preparing and parsing the query or command to be executed on the connection, executing the query or command one or multiple times and providing a pointer into the result set or sets generated by queries. 7.1 Relationship between Cursors and Connections 7.1.
7. mxODBC Cursor Objects 7.2 Subpackage Support Cursor objects are supported by all subpackages included in mxODBC. The extent to which the functionality and number of methods is supported may differ from subpackage to subpackage, so you have to verify the functionality of the used methods (esp. the catalog methods) for each subpackage and database that you intend to use. 7.3 Cursor objects as context managers Please see section 6.5.1.
mxODBC - Python ODBC Database Interface 7.5 Cursor Object Constructors Cursor objects are created using the connection method connection.cursor(). connection.cursor(name=None, cursor_options=()) Constructs a new Cursor Object with the given name using the connection and initializes any provided cursor options. If no name is given, the ODBC driver or database backend will create one dynamically. Please see section 6.7 Connection Object Methods for details. 7.
7. mxODBC Cursor Objects Depending on the current .paramstyle setting, parameters must be provided as sequence14 or mapping: 'qmark'(default) With the 'qmark' parameter style (default) a sequence is expected and parameters be bound to variables found in the sqlcmd string on a positional basis. Variables in the sqlcmd string are specified using the ODBC variable question mark placeholder '?', e.g.
mxODBC - Python ODBC Database Interface If parametertypes are given, the method returns a tuple copy of the parameters sequence, with output and input/output parameter values replaced by the updated values from the database. Without parametertypes, the method returns None. .executedirect(sqlcmd, parameters=(), parametertypes=None) This method works just like .execute(), except that no prepare step is issued and the sqlcmd is not cached.
7. mxODBC Cursor Objects The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor's .arraysize determines the number of rows to be fetched. The method will try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned. An Error (or subclass) exception is raised if the previous call to .execute*() did not produce any result set or no call was issued yet. .
mxODBC - Python ODBC Database Interface Option Comment Returns an integer value: SQL.TRUE - column is auto-increment SQL.FALSE - column is not an auto-increment column or not numeric SQL.DESC_BASE_COLUMN_NAME Base column name of the result set column. If the base column name cannot be determined, e.g. for expressions, an empty string is returned. Returns a string.. SQL.DESC_BASE_TABLE_NAME Base table name of the result set column. If the base table name cannot be determined, e.g.
7. mxODBC Cursor Objects Option Comment SQL.DESC_TYPE_NAME Data source dependent type name of the result set column or an empty string if the value cannot be determined. Returns a string.. SQL.DESC_UNSIGNED Checks whether the result set column is unsigned numeric data or not. Returns an integer value: SQL.TRUE - column data is unsigned or not numeric SQL.
mxODBC - Python ODBC Database Interface Option Comment SQL.ATTR_MAX_LENGTH Returns the length limit for fetching column data. Possible values: Any positive integer or SQL.MAX_LENGTH_DEFAULT (no limit) SQL.ATTR_MAX_ROWS Returns the maximum number of rows a .fetchall() command would return from the result set. Possible values: Any positive integer or SQL.MAX_ROWS_DEFAULT (no limit) SQL.ATTR_NOSCAN Check whether the ODBC driver will scan the SQL commands for ODBC escape sequences or not.
7. mxODBC Cursor Objects .prepare(sqlcmd) Prepare a database operation (query or command) statement for later execution and set cursor.command. To later execute a prepared statement, pass cursor.command to one of the .execute*() methods. cursor.prepare(sqlcmd) can also be used to check sqlcmd for syntax errors, or to inspect the result set structure of a query without executing the sqlcmd operation, by looking at cursor.description after calling cursor.prepare().
mxODBC - Python ODBC Database Interface .setcursoroption(option, value) Sets a cursor option to a new value. Only a subset of the possible option values defined by ODBC are available since this method could otherwise easily cause mxODBC to segfault – it makes changes possible which effect the way mxODBC interfaces to the ODBC driver. Only options with numeric values are currently supported. Option Comment SQL.ATTR_QUERY_TIMEOUT Sets the query timeout in seconds used for the cursor.
7. mxODBC Cursor Objects Option Comment interpret them as case-sensitive SQL search patterns. Possible values: SQL.TRUE - case-insensitive identifers SQL.FALSE - case-sensitive search patterns (default) SQL.ATTR_NOSCAN Tell the ODBC driver not to scan the SQL commands and unescape (expand) any ODBC escape sequences it finds. Default is to scan for them. Possible values: SQL.NOSCAN_OFF (default) SQL.NOSCAN_ON SQL.NOSCAN_DEFAULT .
mxODBC - Python ODBC Database Interface Some ODBC drivers do not support all of these methods or return unusable data. As a result, you should verify correct operation for your target data sources prior to relying on these methods. Common Interface All of the following catalog methods use the same interface: they do an implicit call to cursor.execute() and return their output in form of a list of rows which that can be fetched with the cursor.fetch*() methods in the usual way.
7. mxODBC Cursor Objects Switching between Search Patterns and Identifier Matching Some ODBC drivers support adjusting the catalog method interface to interpret the parameters as case-insensitive identifiers instead. In mxODBC, this can be enabled using: cursor.setcursoroption(SQL.ATTR_METADATA_ID, SQL.TRUE) The setting persists on the cursor. It can be switched off again using: cursor.setcursoroption(SQL.ATTR_METADATA_ID, SQL.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment COLUMN_NAME VARCHAR(128) not NULL Name of the column of the specified table, view, alias, or synonym. DATA_TYPE SMALLINT not NULL SQL data type of column identified by COLUMN_NAME. TYPE_NAME VARCHAR(128) not NULL Character string representing the name of the data type corresponding to DATA_TYPE.
7. mxODBC Cursor Objects Column Name Column Datatype Comment accept NULL values. REMARKS VARCHAR(254) May contain descriptive information about the column or NULL. It is possible that no usable information is returned in this column (due to optimizations). COLUMN_DEF VARCHAR(254) The column's default value. If the default value is a numeric literal, then this column contains the character representation of the numeric literal with no enclosing single quotes.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment otherwise. .columnprivileges(qualifier=None, owner=None, table=None, column=None) Query the database schema for information on column privileges for the given table. This is useful to determine the authorizations granted to a table or column. column allows restricting the results to a single column of a table. Note that the table parameter is mandatory.
7. mxODBC Cursor Objects .foreignkeys(primary_qualifier=None, primary_owner=None, pimary_table=None, foreign_qualifier=None, foreign_owner=None, foreign_table=None) Query the database schema for information on foreign keys. The method has two modes of operation, depending on which parameter is set: primary_table The method returns a list of foreign key columns in other tables that refer to the primary key column of the given table and the primary key column of the given table itself.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment the SQL operation is UPDATE: SQL.RESTRICT, SQL.NO_ACTION, SQL.CASCADE, SQL.SET_NULL. DELETE_RULE SMALLINT Action to be applied to the foreign key when the SQL operation is DELETE: SQL.CASCADE, SQL.NO_ACTION, SQL.RESTRICT, SQL.SET_DEFAULT, SQL.SET_NULL FK_NAME VARCHAR(128) Foreign key identifier. NULL if not applicable to the data source. PK_NAME VARCHAR(128) Primary key identifier.
7. mxODBC Cursor Objects Column Name Column Datatype Comment character. For numeric data types, this is either the total number of digits, or the total number of bits allowed in the column, depending on the value in the NUM_PREC_RADIX column in the result set. LITERAL_PREFIX VARCHAR(128) Prefix for a literal of this data type. This column is NULL for data types where a literal prefix is not applicable. LITERAL_SUFFIX VARCHAR(128) Suffix for a literal of this data type.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment SQL.LIKE_ONLY: if the data type can be used in a WHERE clause only with the LIKE predicate. SQL.ALL_EXCEPT_LIKE: if the data type can be used in a WHERE clause with all comparison operators except LIKE. SQL.SEARCHABLE: if the data type can be used in a WHERE clause with any comparison operator. UNSIGNED_ATTRIBUTE SMALLINT Indicates where the data type is unsigned. The valid values are: SQL.TRUE, SQL.FALSE or NULL.
7. mxODBC Cursor Objects Column Name Column Datatype Comment SQL_DATA_TYPE SMALLINT not NULL SQL data type. This column is the same as the DATA_TYPE column. SQL_DATETIME_SUB SMALLINT The subtype code for datetime data types: SQL.CODE_DATE, SQL.CODE_TIME, SQL.CODE_TIMESTAMP. For all other data types this column returns NULL. NUM_PREC_RADIX SMALLINT Either 10 or 2 or NULL.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment NULL ORDINAL_POSITION SMALLINT not NULL Column sequence number in the primary key, starting with 1. PK_NAME Primary key identifier. NULL if not applicable to the data source. VARCHAR(128) .procedures(qualifier=None, owner=None, procedure=None) Query the data source for information on procedures stored in a data source. procedure can be used to limit the results to a set of procedures or a single procedure.
7. mxODBC Cursor Objects Column Name Column Datatype Comment SQL.PT_PROCEDURE: The returned object is a procedure; that is, it does not have a return value. SQL.PT_FUNCTION: The returned object is a function; that is, it has a return value. .procedurecolumns(qualifier=None, owner=None, procedure=None, column=None) Query the data source for information on parameter details of procedures stored in a data source. procedure can be used to limit the results to a set of procedures or a single procedure.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment SQL.PARAM_OUTPUT: this parameter is an output parameter. SQL.RETURN_VALUE: the procedure column is the return value of the procedure. SQL.RESULT_COL: this parameter is actually a column in the result set. DATA_TYPE SMALLINT not NULL SQL data type of column. TYPE_NAME VARCHAR(128) not NULL Character string representing the name of the data type corresponding to DATA_TYPE.
7. mxODBC Cursor Objects Column Name Column Datatype Comment For numeric data types, the database can return a NUM_PREC_RADIX of either 10 or 2. NULLABLE SMALLINT not NULL SQL.NO_NULLS if the column does not accept NULL values. REMARKS VARCHAR(254) May contain descriptive information about the column or NULL. It is possible that no usable information is returned in this column (due to optimizations). COLUMN_DEF VARCHAR(3) The column's default value.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment IS_NULLABLE VARCHAR(254) Contains the string "NO" if the column is known to be not nullable, "" if this cannot be determined, or "YES" if it is known to be nullable. .specialcolumns(qualifier=None, owner=None, table=None, coltype=SQL.BEST_ROWID, scope=SQL.SCOPE_SESSION, nullable=SQL.NO_NULLS) Query the data source for information on "special" columns of a given table. The table parameter is mandatory.
7. mxODBC Cursor Objects The method is useful to determine columns that can be used as to determine query columns that allow retrieving rows which have been inserted in a table without primary key or in a table with a primary key which is defined as autoincrement column. The catalog method generates a result set having the following schema: Column Name Column Datatype Comment SCOPE SMALLINT The duration for which the name in COLUMN_NAME is guaranteed to point to the same row.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment data types where scale is not applicable. PSEUDO_COLUMN SMALLINT Indicates whether or not the column is a pseudo-column. Possible values: SQL.PC_NOT_PSEUDO, SQL.PC_UNKNOWN, SQL.PC_PSEUDO. .statistics(qualifier=None, owner=None, table=None, unique=SQL.INDEX_ALL, accuracy=SQL.QUICK) Query the data source for information on statistics and available indexes for a given table. The table parameter is mandatory.
7. mxODBC Cursor Objects Column Name Column Datatype Comment NON_UNIQUE SMALLINT Indicates whether the index prohibits duplicate values. Returns: SQL.TRUE if the index allows duplicate values. SQL.FALSE if the index values must be unique. NULL is returned if the TYPE column indicates that this row is SQL.TABLE_STAT (statistics information on the table itself). INDEX_QUALIFIER VARCHAR(128) The string that would be used to qualify the index name in the DROP INDEX statement. Appending a period (.
mxODBC - Python ODBC Database Interface Column Name Column Datatype Comment CARDINALITY INTEGER If the TYPE column contains the value SQL.TABLE_STAT, this column contains the number of rows in the table. If the TYPE column value is not SQL.TABLE_STAT, this column contains the number of unique values in the index. A NULL value is returned if the information cannot be determined. PAGES INTEGER If the TYPE column contains the value SQL.
7. mxODBC Cursor Objects Column Name Column Datatype Comment TABLE_TYPE VARCHAR(128) Identifies the type given by the name in the TABLE_NAME column. It can have the string values "TABLE", "VIEW", "INOPERATIVE VIEW", "SYSTEM TABLE", "ALIAS", or "SYNONYM". REMARKS VARCHAR(254) Contains the descriptive information about the table. .tableprivileges(qualifier=None, owner=None, table=None) Query the data source for information on table privileges associated with database tables.
mxODBC - Python ODBC Database Interface 7.7 Cursor Object Attributes .arraysize This read/write attribute specifies the number of rows to fetch at a time with .fetchmany(). It defaults to 1 meaning to fetch a single row at a time. mxODBC uses this value as default for the number of rows to fetch with .fetchmany() method. .bindmethod Attribute to query and set the input variable binding method used by the cursor. This can either be BIND_USING_PYTHONTYPE of BIND_USING_SQLTYPE (see the Constants section 10.
7. mxODBC Cursor Objects .connection Connection object on which the cursor operates. .converter Read-only access to the converter function set using the cursor.setconverter()method or inherited from the connection.converter attribute at cursor creation time. The attribute is None in case no converter function was set or inherited. mxODBC will then use the default type conversions when fetching data from the database. See section 8.5 Output Conversions for details. .
mxODBC - Python ODBC Database Interface Supported Data Types and are available through the SQL singleton defined at module level. Please see section 5.7.2 Result Set Introspection for more information on this attribute and how to use it. .encoding Read/write attribute which defines the encoding to use for converting Unicode to 8-bit strings and vice-versa. If set to None (default), Python's default encoding will be used, otherwise it has to be a string providing a valid encoding name, e.g.
7. mxODBC Cursor Objects these parameter locations using a positional mapping. Parameter values for a SQL statement must be specified as sequence, normally a list or a tuple. Example: 'SELECT * FROM MyTable WHERE A=? AND B=?' used with a parameter tuple (1, 2) would result in the database executing the query 'SELECT * FROM MyTable WHERE A=1 AND B=2'. 'named' The 'named' parameter binding style is used by the native database interfaces of e.g. Oracle. Parameters in SQL statements used on cursor.
mxODBC - Python ODBC Database Interface .rowcount This read-only attribute specifies the number of rows that the last .execute*() produced (for DQL statements like select) or affected (for SQL DML statements like update or insert). The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface or the database. You should check whether the database you are interfacing to supports .
8. Data Types supported by mxODBC 8. Data Types supported by mxODBC mxODBC tries to maintain as much of the available information across the Python-ODBC bridge as possible. In order to implement this, mxODBC converts between the ODBC and the Python world by using native data types in both worlds. You should note however, that some ODBC drivers return data using different types than the ones accepted for input, e.g.
mxODBC - Python ODBC Database Interface statement with embedded literal parameters to a normalized form and then find that it already has an access plan. If you plan to run the same statement over and over again or use cursor.executemany(), then the ODBC driver only has to pass the SQL statement and the list of parameters to the database, rather than build and send hundreds of statements across the wire to the database.
8. Data Types supported by mxODBC print cursor.fetchall() More information about the connection and cursor attribute .paramstyle is available in section 6.8 Connection Object Attributesand 7.7 Cursor Object Attributes. 8.2 mxODBC Input Binding Modes When passing parameters to the .execute*() methods of a cursor, mxODBC has to apply type conversions to the parameters in order to send them to the database in an appropriate form. This process is called binding a variable.
mxODBC - Python ODBC Database Interface Please note that mxODBC will try to use SQL type binding if possible, but always falls back to Python type binding mode in case it cannot access the needed type information from the ODBC driver or database. This even applies if the binding mode is set to SQL type binding. 8.2.1 Adjusting the Type Binding Mode As for many other attributes, mxODBC provide ways of defining the binding method on a per connection or a per cursor basis.
8. Data Types supported by mxODBC 8.3 SQL Type Input Binding The following data types are used for SQL type input binding mode cursor.bindmethod (inherited from connection.bindmethod) set to BIND_USING_SQLTYPE. The SQL type is what the database ODBC driver expects from mxODBC. The interface then tries to convert the Python input objects to the Python type given in the table before passing it on to the ODBC driver. SQL Type Python Type Comments SQL.CHAR, SQL.VARCHAR, SQL.
mxODBC - Python ODBC Database Interface SQL Type Python Type SQL.WLONGVARCHAR (TEXT, BLOB or LONG in SQL) stringified object Comments Non-string objects are passed through unicode(obj, cursor.encoding) to convert them to Unicode objects, except numbers, which are passed through unicode(obj), i.e. without using the connection encoding. The handling of Unicode depends on the setting of the .stringformat attribute.
8. Data Types supported by mxODBC SQL Type Python Type Comments SQL.INTEGER, SQL.BIT converted to a Python integer the ODBC driver/manager, so expect the usual truncations. SQL.BIGINT Long integer or any other object which can be converted to a Python long integer Conversion to and from the Python long integer is done directly, if possible, or via the string representation if the C data types are not sufficient to hold the numeric data.
mxODBC - Python ODBC Database Interface SQL Type Python Type Comments with float second values which sometimes cannot be represented with full accuracy. SQL.TIMESTAMP DateTime instance or datetime.datetime instance or datetime.date instance or a tuple (year,month,day, hour,minute,second) or String/Unicode or a ticks value as Python number While you should use DateTime instances, the module also accepts Python datetime.datetime instances, datetime.
8. Data Types supported by mxODBC 8.4 Python Type Input Binding The following mappings are used for input variables in Python type input binding mode - cursor.bindmethod (inherited from connection.bindmethod) set to BIND_USING_PYTHONTYPE. The table shows how the different Python types are converted to SQL types. Python Type SQL Type Comments String SQL.VARCHAR, SQL.LONGVARCHAR, SQL.VARBINARY, SQL.LONGVARBINARY The conversion truncates the string at the SQL field length.
mxODBC - Python ODBC Database Interface Python Type Long Integer SQL Type Comments (signed long) driver/manager, so expect the usual truncations. SQL.CHAR Conversion from the Python long integer is done via the string representation since there usually is no C type with enough precision to hold the value. SQL.DOUBLE Conversion from the Python float (a C double) to the SQL column type is left to the ODBC driver/manager, so expect the usual truncations. SQL.VARCHAR, SQL.
8. Data Types supported by mxODBC Python Type SQL Type Comments Server 2008 introduced a date column type. datetime.time SQL.TIME Converts the datetime.time instance into a TIME struct defined by the ODBC standard. datetime.datetime SQL.TIMESTAMP Converts the datetime.datetime instance into a TIMESTAMP struct defined by the ODBC standard. Seconds are rounded to the nearest nanosecond in order to avoid issues with float second values which sometimes cannot be represented with full accuracy.
mxODBC - Python ODBC Database Interface SQL Type Python Type Comments connection's encoding setting. SQL.WCHAR, SQL.WVARCHAR, SQL.WLONGVARCHAR (TEXT, BLOB or LONG in SQL) String or Unicode Whether a Python string or Unicode object is returned depends on the setting of the .stringformat attribute of the cursor fetching the data. In EIGHTBIT_STRINGFORMAT mode, the Unicode data is converted to a Python string object based on the connection's encoding setting. SQL.BINARY, String SQL.VARBINARY, SQL.
8. Data Types supported by mxODBC SQL Type Python Type Comments information. Default is to return DateTime instances. SQL.TIME DateTimeDelta instance or datetime.time instance or tocks or (hour,minute,second) or String The type of the return values depends on the setting of cursor.datetimeformat and whether the ODBC driver/manager does return the value with proper type information. Default is to return DateTimeDelta instances. SQL.TIMESTAMP DateTime instance or datetime.
mxODBC - Python ODBC Database Interface 8.6.1 Converter Function Signatures You can modify this mapping on-the-fly by defining a cursor converter function which takes three arguments and has to return a 2-tuple: def converter(position,sqltype,sqllen): # modify sqltype and sqllen as appropriate return sqltype,sqllen # Now tell the cursor to use this converter: cursor.
8. Data Types supported by mxODBC 8.6.2 Adjusting/Querying the Converter Function Cursor objects will use the connection's .converter attribute as default converter. It defaults to None, meaning that no converter function is in effect. None can also be used to disable the converter function on a cursor: # Don't use a converter function on the cursor cursor.setconverter(None) You can switch converter functions even in between fetches. mxODBC will then reallocate and rebind the column buffers for you.
mxODBC - Python ODBC Database Interface 8.8 Unicode and String Data Encodings mxODBC also supports Unicode objects to interface with databases. As more databases and ODBC drivers support Unicode natively, using Unicode for text data stored in database becomes more attractive than ever and allows you to avoid the problems you typically face when having to deal with different text encodings and code pages in databases.
8. Data Types supported by mxODBC configured with Unicode support. It also supports SQL commands given as Unicode data. However, it does not handle Unicode at the schema interface level, that is e.g. cursor.description will not return Unicode objects for the column names. This may be added to a future version of mxODBC, but is currently not supported by the package. 8.
mxODBC - Python ODBC Database Interface 9. DB-API Type Objects and Constructors Since many database have problems recognizing some column's or parameter's type beforehand (e.g. for LONGs and date/time values), the Python DB-API provides a set of standard constructors to create objects that can hold special values. When passed to the cursor methods, the module can then detect the proper type of the input parameter and bind it accordingly.
9. DB-API Type Objects and Constructors TimeFromTicks(ticks) This function constructs an mxDateTime DateTimeDelta object holding a time value from the given ticks value (number of seconds since the epoch; see the documentation of the standard Python time module for details).
mxODBC - Python ODBC Database Interface 10. mxODBC Exceptions and Error Handling The mxODBC package and all its subpackages use the DB API 2.0 exceptions layout. All exceptions are defined in the submodule mx.ODBC.Error but also imported into the top-level package module mx.ODBC as well as all sub-packages. Note that all sub-packages use the same exception classes, so writing crossdatabase applications is simplified this way.
10. mxODBC Exceptions and Error Handling Error is a subclass of exceptions.StandardError. Warning Exception raised for important warnings like data truncations while inserting, etc. Warning is a subclass of exceptions.StandardError. This may change in a future release to some other baseclass indicating warnings. InterfaceError Exception raised for errors that are related to the interface rather than the database itself. DatabaseError Exception raised for errors that are related to the database.
mxODBC - Python ODBC Database Interface |__OperationalError |__IntegrityError |__InternalError |__ProgrammingError |__NotSupportedError 10.2 SQL Error Mappings If you are interested in the exact mapping of SQL error codes to exception classes, have a look at the errorclass dictionary which is defined at subpackage scope, e.g. mx.ODBC.Windows.errorclass.
10. mxODBC Exceptions and Error Handling 10.4 Error Handlers If you want to provide your own error handler, e.g. to mask database warnings or debug connection setups, you can do so by assigning to the .errorhandler attribute of connections and cursors or passing a callback function to the connection constructors at connection creation time using the errorhandler keyword argument.
mxODBC - Python ODBC Database Interface 10.4.2 Examples Here's an example of an error handler that allows to flexibly ignore warnings or only record messages. # Error handler configuration record_messages_only = 0 ignore_warnings = 0 # Error handler function def myerrorhandler(connection, cursor, errorclass, errorvalue): """ Default mxODBC error handler. The default error handler reports all errors and warnings using exceptions and also records these in connection.
10. mxODBC Exceptions and Error Handling 10.5 Warning Classes The Python DB-API 2.0 does not define a warning class hierarchy. At the time the DB-API 2.0 was defined, the Python warning was not yet in existence. It is expected that a future revision will add such a hierarchy. Until then mxODBC uses it's own warning hierarchy which currently just has one warning class: DatabaseWarning Warning issued for important warnings like data truncations while inserting, etc.
mxODBC - Python ODBC Database Interface These mx.ODBC constants are available for the .warningformat attribute: ERROR_WARNINGFORMAT (default) Report warnings in the usual DB-API 2.0 way and raise a Warning exception. WARN_WARNINGFORMAT Instead of raising a Warning exception, issue a mx.ODBC.DatabaseWarning which is a Python Warning subclass and can be filtered using the standard Python warnings module mechanisms. IGNORE_WARNINGFORMAT Silently ignore the database warning.
10.
mxODBC - Python ODBC Database Interface 11. mxODBC Functions mxODBC includes a few helper functions and generic APIs which aid in everyday ODBC database programming or allow introspection at the ODBC manager level. The next sections describe these functions in detail. 11.1 Subpackage Functions For some subpackages, mxODBC also defines a few helpers which you can use to query additional information from the ODBC driver or manager. These are available through the subpackage, e.g. as mx.ODBC.Windows.
11. mxODBC Functions This function is only available for ODBC 3.x compatible managers and ODBC drivers. Note: The function allows setting environment attributes which mxODBC itself uses to define the way it interfaces to the database. Changing these attributes can result in unwanted behavior or even segmentation faults. USE AT YOUR OWN RISK ! statistics() Returns a tuple (connections, cursors) stating the number currently open connections and cursors for this subpackage.
mxODBC - Python ODBC Database Interface 12. mxODBC Globals and Constants 12.1 Subpackage Globals and Constants Each mxODBC subpackage exports the following globals and constants: BIND_USING_SQLTYPE, BIND_USING_PYTHONTYPE Integer values returned by or used for setting connection.bindmethod and cursor.bindmethod.
12. mxODBC Globals and Constants EIGHTBIT_STRINGFORMAT, MIXED_STRINGFORMAT, UNICODE_STRINGFORMAT, NATIVE_UNICODE_STRINGFORMAT Integer values which are used by connection.stringformat and cursor.stringformat. mxODBC can handle different string conversion methods on a per connection and per cursor basis. See the documentation of the two attributes for more information. ERROR_WARNINGFORMAT, WARN_WARNINGFORMAT, IGNORE_WARNINGFORMAT Integer values which are used by connection.warningformat and cursor.
mxODBC - Python ODBC Database Interface If you need to specify your own SQLSTATE to exception class mappings, you can assign to this dictionary. Changes will become visible immediately. license String with the license information for the installed mxODBC license. paramstyle String constant stating the type of parameter marker formatting expected by the interface. This is set to 'qmark', since the ODBC default is to use '?' to be used as positional placeholder for variables in an SQL statement.
13. mx.ODBC.Misc.RowFactory Module 13. mx.ODBC.Misc.RowFactory Module This module defines a set of factory functions which can be used together with cursor.rowfactory to customize the row objects returned by the cursor.fetch*() methods in mxODBC. This section describes the available module APIs. Please see section 5.9 Custom Cursor Row Objects and Row Factory Functions for details on how to use these row factory functions.
mxODBC - Python ODBC Database Interface In addition to the sequence protocol, they also allow mapping access as well as named attribute access based on the lower-cased column names read from cursor.description. Rows created by this factory are mutable. cursor has to be an mxODBC Cursor object.
14. mx.ODBC Driver/Manager Packages 14. mx.ODBC Driver/Manager Packages This section includes specific notes for preconfigured subpackages and setups. 14.1 Driver/Manager Subpackage Notes The following sections provide hints that apply to all mx.ODBC subpackages. Please read carefully. 14.1.1 Windows Platform Notes You should always use the mx.ODBC.Windows subpackage and access the databases through the MS ODBC Driver Manager. The other packages provide Unix based interfaces to the databases. 14.1.
mxODBC - Python ODBC Database Interface Windows Platforms mxODBC selects the subpackage by trying to import the available ODBC driver subpackages in the following order: 1. mx.ODBC.Windows No other subpackage is currently tried, since the Windows ODBC manager is always present in all recent Windows versions. Unix Platforms mxODBC selects the subpackage by trying to import the available ODBC driver subpackages in the following order: 1. mx.ODBC.unixODBC 2. mx.ODBC.iODBC 3. mx.ODBC.DataDirect The mx.ODBC.
14. mx.ODBC Driver/Manager Packages 14.3.2 Supported Datatypes The subpackage defaults to SQL type binding mode (see the Datatypes section for details), but reverts to Python type binding in case the connection does not support the ODBC SQLDescribeParam() API. MS Access is one candidate for which this API is not useable. 14.3.
mxODBC - Python ODBC Database Interface • When interfacing to MySQL using the MySQL ODBC driver, we have observed problems with using Unicode statements passed to cursor.execute() when using iODBC 3.52.5. These problems appear to be related to iODBC. As work-around, you can use unixODBC, which works fine with Unicode statements. • You may experience problems when trying to connect to MySQL via MyODBC hooked to iODBC in case you are using the binary RPMs available.
14. mx.ODBC Driver/Manager Packages 14.5.1 Notes General Recommendations • Please always use the DriverConnect() API to connect to the data source if you need to pass in extra configuration information such as names of log files, etc. • Between unixODBC 2.3.0 and unixODBC 2.3.1, the unixODBC project switched the library name of the ODBC manager library from libodbc.so.1 to libodbc.so.2 to signal the change in their ABI on 64-bit platforms (see below).
mxODBC - Python ODBC Database Interface Example: def debug_errorhandler(connection, cursor, errorclass, errorvalue): sys.stderr.write('debug_errorhandler: %s: %r\n' % (errorclass, errorvalue)) db = DriverConnect('DSN=drivernotfound;UID=sa;PWD=test', errorhandler=debug_errorhandler) The error handler will be called for all messages coming from the ODBC driver and manager, including warnings which are then ignored by mxODBC, and print these to stderr for review.
14. mx.ODBC Driver/Manager Packages Threading • In unixODBC versions 2.3.0 and below, the ODBC manager used a little known odbc.ini setting called "Threading" which determined the default thread level protection of the ODBC data source. • The default used to be lock level 3 (the ODBC driver does not allow multiple threads to use it and everything is serialized). This could result in the application using mxODBC and unixODBC to hang during long running queries.
mxODBC - Python ODBC Database Interface eGenix.com provides binary subpackages for this ODBC driver manager only on Linux x86 and x64 platforms. If you need the subpackage on other platforms as well, please contact support@egenix.com for help. 14.6.1 Notes General Recommendations • Please always use the DriverConnect() API to connect to the data source if you need to pass in extra configuration information such as names of log files, etc.
14. mx.ODBC Driver/Manager Packages Since these setups caused a lot of support requests due to configuration problems and version mismatches between the driver versions we used to build the binary mxODBC distribution and the ones deployed at customer sites, we have decided to drop general support for these additional subpackages. It is usually better to use one of the available ODBC driver manager packages to configure and manage the data sources.
mxODBC - Python ODBC Database Interface 15. Hints & Links to other Resources 15.1 Running mxODBC from a CGI script ODBC drivers and managers are usually compiled as a shared library. When running CGI scripts most HTTP daemons (or web servers) don't pass through the path for the dynamic loader (e.g. LD_LIBRARY_PATH) to the script, thus importing the mxODBC C extension will fail with unresolved symbols because the loader doesn't find the ODBC driver/manager's libs.
15. Hints & Links to other Resources Since this affects not only mxODBC, but other Python C extensions as well, you may want to use a work-around until either Apache or the mod_wsgi team solves the problem: Manifest work-around Adding the VC++ manifests to the Apache process is explained in this posting. You will also have to install the MS VC++ 2008 CRT SP1 redistributable package on the server running Apache. With those changes in place, mxODBC should load without problems. 15.
mxODBC - Python ODBC Database Interface Microsoft MDAC Site Microsoft is constantly developing new forms of database access. For a close up on what they have come up recently take a look at their ODBC site. Note that they now call their ODBC SDK "Microsoft Data Access Components SDK" (MDAC). It does not only focus on ODBC but also on OLE DB and ADO. Note: If you are not happy about the size of the SDK download (over 31MB), you can also grab the older 3.
16. Examples 16. Examples Here is a very simple example of how to use mxODBC. More elaborate examples of using Python Database API compatible database interfaces can be found in the Database Topic Guide on http://www.python.org/. Andrew Kuchling's introduction to the Python Database API is an especially good reading. There are also a few books on using Python DB API compatible interfaces, some of them cover mxODBC explicitly. On Unix: >>> import mx.ODBC.iODBC >>> db = mx.ODBC.iODBC.
mxODBC - Python ODBC Database Interface Note: When connecting to a database with transaction support, you should explicitly do a .rollback() or .commit() prior to closing the connection. In the example this was omitted since the used MySQL database backend does not support transactions and we were only reading from the database.
17. Testing the Database Connection 17. Testing the Database Connection The package includes a test script that checks some of the database's features. As side effect this also provides a good regression test for the mxODBC interface. To start the test, simply run the script in mx/ODBC/Misc/test.pyc. python mx/ODBC/Misc/test.
mxODBC - Python ODBC Database Interface 18. mxODBC Package Structure This is the Python package structure setup when installing mxODBC: [ODBC] Doc/ [Misc] proc.py test.pyc [DataDirect] dbi.py dbtypes.py [Manager] [Windows] dbi.py dbtypes.py [iODBC] dbi.py dbtypes.py [unixODBC] dbi.py dbtypes.py LazyModule.py ODBC.py Entries enclosed in brackets are packages (i.e. they are directories that include a __init__.py file). Ones with slashes are just simple subdirectories that are not accessible via import.
19. Support 19. Support eGenix.com is provides commercial support for this package, including adapting it to special needs for use in customer projects. If you are interested in receiving information about this service please contact support@egenix.com for details. This section describes methods which are useful to track down interoperability problems with ODBC drivers. eGenix support may ask you to apply some of the methods when working with you to resolve driver-related problems. 19.
mxODBC - Python ODBC Database Interface 19.1.2 iODBC Driver Manager To enable ODBC level tracing, open the /etc/odbc.ini or ~/.odbc.ini file and add this section to it: [ODBC] Trace = 1 TraceFile = /tmp/odbc.log If you already have such entries in the [ODBC] section, make sure that the settings are correct and that Trace is set to 1. After that is done, start you application or script using mxODBC and run the code that is causing problems with the driver in question.
19. Support 19.1.5 Mac OS X ODBC Driver Manager Open the Mac OS X ODBC Administrator. This can be found under Applications/Utilities. Go to the Tracing tab and select a trace output file under Log File and then click the checkbox Enable Tracing to enable ODBC tracing output. Click on Apply have the change take effect. After that is done, start you application or script using mxODBC and run the code that is causing problems with the driver in question.
mxODBC - Python ODBC Database Interface 20. History & Changes Please visit the change log on the mxODBC product page for a list of changes in the various product versions.
21. Copyright & License 21. Copyright & License © 1997-2000, Copyright by IKDS Marc-André Lemburg; All Rights Reserved. mailto: mal@lemburg.com © 2000-2015, Copyright by eGenix.com Software GmbH, Langenfeld, Germany; All Rights Reserved. mailto: info@egenix.com This software is covered by the eGenix.com Commercial License Agreement, which is included in the following section. The text of the license is also included as file "LICENSE" in the package's main directory.
mxODBC - Python ODBC Database Interface EGENIX.COM COMMERCIAL LICENSE AGREEMENT Version 1.3.0 1. Introduction This “License Agreement” is between eGenix.com Software, Skills and Services GmbH (“eGenix.com”), having an office at Pastor-Loeh-Str. 48, D-40764 Langenfeld, Germany, and the Individual or Organization (“Licensee”) accessing and otherwise using this software in source or binary form and its associated documentation (“the Software”). 2.
21. Copyright & License “Commercial Environment” refers to any application environment which is aimed at directly or indirectly generating profit. This includes, without limitation, forprofit organizations, private educational institutions, work as independent contractor, consultant and other profit generating relationships with organizations or individuals. Governments and related agencies or organizations are also regarded as being Commercial Environments.
mxODBC - Python ODBC Database Interface 5. Modifications Software modifications may only be distributed in form of patches to the original files contained in the Software Distribution. The patches must be accompanied by a legend of origin and ownership and a visible message stating that the patches are not original Software delivered by eGenix.com, nor that eGenix.com can be held liable for possible damages related directly or indirectly to the patches if they are applied to the Software. 6.
21. Copyright & License THEREOF, EVEN IF ADVISED OF THE POSSIBILITY THEREOF; OR (II) ANY AMOUNTS IN EXCESS OF THE AGGREGATE AMOUNTS PAID TO EGENIX.COM UNDER THIS LICENSE AGREEMENT DURING THE TWELVE (12) MONTH PERIOD PRECEEDING THE DATE THE CAUSE OF ACTION AROSE. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OR LIMITATION OF INCIDENTAL OR CONSEQUENTIAL DAMAGES, SO THE ABOVE EXCLUSION OR LIMITATION MAY NOT APPLY TO LICENSEE. 10.
mxODBC - Python ODBC Database Interface 14. General Nothing in this License Agreement affects any statutory rights of consumers that cannot be waived or limited by contract. Nothing in this License Agreement shall be deemed to create any relationship of agency, partnership, or joint venture between eGenix.com and Licensee.
21. Copyright & License EGENIX.COM PROOF OF AUTHORIZATION 1 CPU License (Example) This is an example of a "Proof of Authorization" for a 1 CPU License. These proofs are either wet-signed by the eGenix.com staff or digitally PGP-signed using an official eGenix.com PGP-key. 1. License Grant eGenix.com Software, Skills and Services GmbH (“eGenix.com”), having an office at Pastor-Loeh-Str.
mxODBC - Python ODBC Database Interface 4.
21. Copyright & License EGENIX.COM PROOF OF AUTHORIZATION 1 Developer CPU License (Example) This is an example of a "Proof of Authorization" for a 1 Developer CPU License. These proofs are either wet-signed by the eGenix.com staff or digitally PGP-signed using an official eGenix.com PGP-key. 5. License Grant eGenix.com Software, Skills and Services GmbH (“eGenix.com”), having an office at Pastor-Loeh-Str.
mxODBC - Python ODBC Database Interface 7.2 Redistribution eGenix.com hereby authorizes Licensee to redistribute the Software bundled with a product developed by Licensee on the Developer Installation Targets ("the Product") subject to the terms and conditions of this License Agreement for installation and use in combination with the Product on the following Redistribution Installation Targets, provided that: 1.
21.