Welcome, guest | Sign In | My Account | Store | Cart

When installing database applications that use ODBC, I wanted to create the required ODBC data sources from my python install script. With the help of the ctypes module, it's simple.

Python, 30 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import ctypes

ODBC_ADD_DSN = 1        # Add data source
ODBC_CONFIG_DSN = 2     # Configure (edit) data source
ODBC_REMOVE_DSN = 3     # Remove data source
ODBC_ADD_SYS_DSN = 4    # add a system DSN
ODBC_CONFIG_SYS_DSN = 5 # Configure a system DSN
ODBC_REMOVE_SYS_DSN = 6 # remove a system DSN

def create_sys_dsn(driver, **kw):
    """Create a  system DSN
    Parameters:
        driver - ODBC driver name
        kw - Driver attributes
    Returns:
        0 - DSN not created
        1 - DSN created
    """
    nul = chr(0)
    attributes = []
    for attr in kw.keys():
        attributes.append("%s=%s" % (attr, kw[attr]))
    
    return ctypes.windll.ODBCCP32.SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, driver, nul.join(attributes))

if __name__ == "__main__":
    if create_sys_dsn("SQL Server",SERVER="(local)", DESCRIPTION="Northwind SQL Server DSN", DSN="NorthwindDSN", Database="Northwind", Trusted_Connection="Yes"):
        print "DSN created"
    else:
        print "DSN not created"

7 comments

du yan 18 years, 10 months ago  # | flag

u can also do it by _winreg. you can add/update/delete a odbc data source just by adding/updating/delete the Value of the Key from HKEY_CURRENT_USER\Software\ODBC.

but this method may be rude.

Anthony Dycks 18 years, 10 months ago  # | flag

ctypes Module Not Found Error running Pgm under ActiveState Python V2.2. Received the following Traceback Error information running under Active State Python V2.2 ...

Traceback (most recent call last):

File "C:\Python22\Lib\site- packages\Pythonwin\pywin\framework\scriptutils.py", line 301, in RunScript

exec codeObject in __main__.__dict__

File "C:\Storage\Python\source\CrtODBCDsn.py", line 20, in ? import ctypes

ImportError: No module named ctypes

Is ctypes part of a supplemental Python package?

Anthony Dycks 18 years, 10 months ago  # | flag

ctypes missing module resolution; ctypes V0.6.3 installed; ok; Can .EXE Be Built? Located an older version of ctypes on Source Forge for Python V2.2.

Code ran ok after installing this module.

Is there a way to build a Python Win32 .EXE so that this functionality could be run by someone who doesn't have a Python installation?

Thanks,

panfisher

Mark Rees (author) 18 years, 9 months ago  # | flag

It is easy to create a wrapper script that processes an argument list from the commandline and use py2exe to create an executable.

wang chentai 18 years, 6 months ago  # | flag

i have installed python 2.4 and ctypes 0.9.6, and run this script, but it can not create ODBC for SQL server or DB2. os: windows xp + sp2 db: sql server 2000, db2 8.2 what is the problem?

wang chentai 18 years, 6 months ago  # | flag

and what is the value of parameter "SERVER" for creating ODBC for DB2 database? system name or instance name? or other? thanks.

wang chentai 18 years, 6 months ago  # | flag

now odbc for sql server can be created. but for db2 still can not be created. i used following parameters: create_sys_dsn("IBM DB2 ODBC DRIVER",SERVER="DB2", DESCRIPTION="db2 DSN", DSN="brkDSN", Database="brk")

Created by Mark Rees on Sun, 22 May 2005 (PSF)
Python recipes (4591)
Mark Rees's recipes (1)

Required Modules

Other Information and Tasks