odbc and python

[Thomas Heller]

Giles Brown schrieb:
> MM wrote:
> 
>>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
> 
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