odbc and python

[MM]

Are there any other odbc packages other than the win32all and mxodbc
ones? The win32all odbc.pyd can't access table structure info like
SQLColumns, and mxobdc requires a commercial license which is
unjustifiable for this tiny project. Any other OS alternatives for
win32?. Thanks.

 

[John Machin]

Are there any other odbc packages other than the win32all and mxodbc
ones? The win32all odbc.pyd can't access table structure info like
SQLColumns, and mxobdc requires a commercial license which is
unjustifiable for this tiny project.

Presumably you really mean 'are there any other Python DP API 2.0
compliant packages' ...

Try this:

http://www.python.org/pypi/adodbapi/2.0

Any other OS alternatives for
win32?

My parser failed on that one ... please rewrite and retry.

 

[Giles Brown]

Are there any other odbc packages other than the win32all and mxodbc
ones? The win32all odbc.pyd can't access table structure info like
SQLColumns, and mxobdc requires a commercial license which is
unjustifiable for this tiny project. Any other OS alternatives for
win32?. Thanks.

You could potentially make the ODBC calls using ctypes a la:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/303667

Not tried this myself and imagine it could be a bit tedious.

Cheers,
Giles

 

[Thomas Heller]

You could potentially make the ODBC calls using ctypes a la:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/303667

Not tried this myself and imagine it could be a bit tedious.

Cheers,
Giles

If someone wants to try this approach (personally, I don't use
databases), it seems that the tools provided with ctypes, if you have
gccxml (and MSVC) installed, should give a good start. Running

h2xml windows.h sql.h -o sql.xml -c
and
xml2py sql.xml -rSQL.* -o sql.py -lodbc32 -d

creates a 830 lines Python module, containing a lot of useful SQL stuff,
which can be hacked on. To give an impression, the file starts with the
following lines, so a lot of constants and datatypes are already defined:

# generated by 'xml2py'
# flags 'sql.xml -rSQL.* -o sql.py -lodbc32 -d -m ctypes.com'
from ctypes import *
SQL_DATETIME = 9 # Variable c_int
SQL_MAX_USER_NAME_LEN = 107 # Variable c_int
SQL_DEFAULT_TXN_ISOLATION = 26 # Variable c_int
SQL_API_SQLFREEHANDLE = 1006 # Variable c_int
SQL_ALTER_TABLE = 86 # Variable c_int
SQL_IS_DAY_TO_SECOND = 10
SQL_API_SQLCOLUMNS = 40 # Variable c_int
SQL_TXN_READ_UNCOMMITTED = 1 # Variable c_long
SQL_TRANSACTION_READ_UNCOMMITTED = SQL_TXN_READ_UNCOMMITTED # alias
SQL_DBMS_NAME = 17 # Variable c_int
SQLSMALLINT = c_short
SQLRETURN = SQLSMALLINT
SQLHANDLE = c_void_p
SQLHDBC = SQLHANDLE
SQLUSMALLINT = c_ushort

@ stdcall(SQLRETURN, 'odbc32',
[SQLHDBC, SQLUSMALLINT, POINTER(SQLUSMALLINT)])
def SQLGetFunctions(p1, p2, p3):
# C:/PROGRA~1/MICROS~3.NET/Vc7/PLATFO~1/Include/sql.h 735
return SQLGetFunctions._api_(p1, p2, p3)

SQL_TRUE = 1 # Variable c_int
SQLHSTMT = SQLHANDLE
SQLCHAR = c_ubyte
UDWORD = c_ulong

@ stdcall(SQLRETURN, 'odbc32',
[SQLHSTMT, SQLUSMALLINT, POINTER(SQLCHAR), SQLSMALLINT,
POINTER(SQLSMALLINT), POINTER(SQLSMALLINT),
POINTER(UDWORD),POINTER(SQLSMALLINT), POINTER(SQLSMALLINT)])
def SQLDescribeCol(p1, p2, p3, p4, p5, p6, p7, p8, p9):
# C:/PROGRA~1/MICROS~3.NET/Vc7/PLATFO~1/Include/sql.h 650
return SQLDescribeCol._api_(p1, p2, p3, p4, p5, p6, p7, p8, p9)

SQL_DROP = 1 # Variable c_int
SQL_DATA_SOURCE_NAME = 2 # Variable c_int
SQL_TXN_SERIALIZABLE = 8 # Variable c_long
SQL_TRANSACTION_SERIALIZABLE = SQL_TXN_SERIALIZABLE # alias



Thomas

 

[Scott David Daniels]

Are there any other odbc packages other than the win32all and mxodbc
ones? The win32all odbc.pyd can't access table structure info like
SQLColumns, and mxobdc requires a commercial license which is
unjustifiable for this tiny project. Any other OS alternatives for
win32?. Thanks.

The mxODBC package contains a lot of grunt work making access to
metadata work smoothly for many targets. I never found the license
too expensive for the work (and thought) that it saved me. If you
are targeting a single DBMS, or already have a strong understanding
of your DB's schema, some other solution may do you as well. I'd be
careful you don't spend far more effort getting corner cases to work
right with a cobbled-together system than you'll save by using mxODBC.

I am not connected to mxODBC other than as a very satisfied customer.

--Scott David Daniels
(e-mail address removed)

 

[Michele Petrazzo]

You could potentially make the ODBC calls using ctypes a la:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/303667

Not tried this myself and imagine it could be a bit tedious.

Cheers,
Giles

This is the new version that work very well into my projects:

http://unipex.it/vario/RealPyOdbc.py

This can work on all the platforms where ctypes work.
In the future I'll make it db-api 2.0 compliant.
If you have some question, email me directly.

Bye,
Michele