configuring ODBC DSN with a python script

[omission9]

"Robert Brewer" <fumanchu at amor.org> wrote in message news:<mailman.1211.1075922693.12720.python-list at python.org>...
> Jani Yusef wrote:
> > I am building an application which, unfortunately, *must* use an
> > access db. The users will have the relevane mdb file installed on
> > their system. As part of the installer. Now, the part I am stuck on is
> > how do I configure the ODBC dsn without having the user open up the
> > ODBC control panel which will in all honestly just scare and confuse
> > them. ;)
> > Has anyone approached this problem before? What did you do?
> > Thanks!!
> 
> I haven't ever run into this (I tend to write apps that will be
> installed by someone with a modicum of technical skill ;). However, the
> thought crosses my mind that ODBC entries are merely a lump of registry
> keys--you might just create or even copy those programatically. That
> would be  my  first attempt.
> 
> 
> Robert Brewer
> MIS
> Amor Ministries
> fumanchu at amor.org

Below is some code which more or less implements this. I recently had
to do a similar thing. I manually created a system dsn and then just
wrote code to recreate what I saw in regedit. Below is for a system
dsn for an access db called test2 which has a single table named
'puke'. Obviously you'll need to change a few strings but this should
more or less work as is.
I import _winreg to do the regedits but use mxODBC for the actual
querying
http://www.egenix.com/files/python/mxODBC.html
One thing is that the system dsn created works perfectly fine but for
whatever reason doesn't show up in the odbc control panel. I am not
very well versed in Windows specific programming so am not sure why
that is but would like to know why. Anybody know why that is?


------------------------- 
import mx.ODBC.Windows   

import _winreg as wreg

class access_interface:

    def __init__(self):  
        self.dsn_name="test2"
        self.dbq="C:\\cygwin\\home\\ar881\\development\\btsurveillance\\console\\db1.mdb"
        self.driver_path="C:\\WINNT\\System32\\odbcjt32.dll"
        self.driver_id=25
	self.fil="MS Access;"
	self.safe_transactions=0
	self.uid=""
	
	self.implicit_commit_sync=""
	self.max_buffer_size=2048
	self.page_timeout=5
	self.threads=3
	self.user_commit_sync="Yes"
	
	self.dsn_reg_key=None
        self.dsn_keyname="Software\\ODBC\\ODBC.INI\\%s" %
(self.dsn_name)
        
        self.engine_reg_key=None        
        self.engine_keyname="Software\\ODBC\\ODBC.INI\\%s\\Engines\\Jet"
% (self.dsn_name)
        
    def createDSN(self):
        try:
	    self.dsn_reg_key=wreg.OpenKey(wreg.HKEY_LOCAL_MACHINE,self.dsn_keyname)
	except:
            self.dsn_reg_key=wreg.CreateKey(wreg.HKEY_LOCAL_MACHINE,self.dsn_keyname)
        try:
	    self.engine_reg_key=wreg.OpenKey(wreg.HKEY_LOCAL_MACHINE,self.engine_keyname)
	except:
            self.engine_reg_key=wreg.CreateKey(wreg.HKEY_LOCAL_MACHINE,self.engine_keyname)
            
        if self.dsn_reg_key!=None and  self.engine_reg_key!=None:  
            self.setRegSZ(self.dsn_reg_key,"DBQ",self.dbq)
            self.setRegSZ(self.dsn_reg_key,"Driver",self.driver_path)
            self.setRegDWORD(self.dsn_reg_key,"DriverId",self.driver_id)
            self.setRegSZ(self.dsn_reg_key,"FIL",self.fil)
            self.setRegDWORD(self.dsn_reg_key,"SafeTransactions",self.safe_transactions)
            self.setRegSZ(self.dsn_reg_key,"UID",self.uid)
            wreg.CloseKey(self.dsn_reg_key)
            
            self.setRegSZ(self.engine_reg_key,"ImplicitCommitSync",self.implicit_commit_sync)
	    self.setRegDWORD(self.engine_reg_key,"MaxBufferSize",self.max_buffer_size)
	    self.setRegDWORD(self.engine_reg_key,"PageTimeout",self.page_timeout)
	    self.setRegDWORD(self.engine_reg_key,"Threads",self.threads)
            self.setRegSZ(self.engine_reg_key,"UserCommitSync",self.user_commit_sync)
            wreg.CloseKey(self.engine_reg_key)
    
    def setRegSZ(self,reg_key,reg_name,reg_value):
        wreg.SetValueEx(reg_key,reg_name,0,wreg.REG_SZ,reg_value)
        
    def setRegDWORD(self,reg_key,reg_name,reg_value):
        wreg.SetValueEx(reg_key,reg_name,0,wreg.REG_DWORD,reg_value)
    
    def getCursor(self):
        db=mx.ODBC.Windows.DriverConnect('DSN=test2;UID=user;PWD=passwd')
        c=db.cursor()
        return c 
        
    def getRows(self):
        c=self.getCursor()
        c.execute('select * from puke')
        mx.ODBC.print_resultset(c)
        
if __name__=="__main__":
    ai=access_interface()
    #ai.createDSN() 
    ai.getRows()