[python-win32] driving MS Access from python
eric.powell at srs.gov
eric.powell at srs.gov
Mon Mar 13 21:42:51 CET 2006
Exactly.
Once you set up the iDispatch to point to the ADODB object library, you
can then use the ADO methods and objects.
One advantage of ADO is that it SHOULD be installed as part of Windows (I
think...I have never run into a case where is wasn't, anyone on the list
want to correct me on this????), so things should work out of the box (so
to speak).
To get the dimensions of the recordset you could use the Fields and Rows
count count methods in ADO
Open Connection stuff...
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sqlstatement, conn,1 ,3)
FC = rs.Fields.Count
RC = rs.Rows.Count
Further for navigating between rows, you have:
rs.MoveFirst
rs.MoveLast
rs.MoveNext
rs.MovePrevious ???? (I think you can iterate backward).
To get the value of a given field, descend through the
Recordset->Fields->Item() chain as follows:
val = rs.Fields.Item(1).Value
Where the iterator in item can be either an ineteger or a fieldname:
val = rs.Fields.Item("Fred").Value
ADO can also access/fire an existing action query or return the records
from a stored query.
More info:
http://www.w3schools.com/ado/default.asp
http://www.mayukhbose.com/python/ado/ado-python.php
HTH,
Eric
Eric B. Powell
BSRI
Electronic Aids
(803)208-6207
Phill Atwood <me at phillatwood.name>
03/13/2006 03:28 PM
To
eric.powell at srs.gov
cc
python-win32 at python.org
Subject
Re: [python-win32] driving MS Access from python
Eric,
Thanks. This seems like a good idea. I noticed also ADOdb for Python.
Would this work do you know? Since I'm doing win/ms access I think I'll
need to use mxodbc but I don't know how to install that on Windows.
In your example below it is not clear (to me) how to get the dimensions
of the recordset and navigate thru (eg. print) all the resultant
fields. Is there something similar to the ADOdb for
Python
while not cursor.EOF:
print cursor.fields
cursor.MoveNext()
cursor.close()
eric.powell at srs.gov wrote:
>
> I would suggest using ADO (Microsoft Active X Data Objects) for this.
> Much cleaner (and more flexible) than trying to use the Access
> executable.
>
> import win32com.client, string
>
> #Establish the ADO DB Connection
> class fields:
> def __init__(self, dbpath, tblName):
> self.dbpath = dbpath
> self.tblName=tblName
> def add(self, sqlstatement):
> conn = win32com.client.Dispatch(r'ADODB.Connection')
> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=' +
> self.dbpath + ';'
> conn.Open(DSN)
>
> #Query the recordset - should be in module with establishing
> connection stuff
> rs = win32com.client.Dispatch(r'ADODB.Recordset')
> print sqlstatement
> try:
> rs.Open(sqlstatement, conn,1 ,3)
> except:
> print 'DB Error'
>
> conn.Close()
> #Update 1 record in the database. Fieldlist is a list of tuples
> consisting of (Name, Value)
> def update(self, attdata, fieldlist, wherecl):
> conn = win32com.client.Dispatch(r'ADODB.Connection')
> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=' +
> self.dbpath + ';'
> conn.Open(DSN)
>
> rs = win32com.client.Dispatch(r'ADODB.Recordset')
> rs_name = 'UPDATE ' + self.tblName + ' SET '
>
> listlen = len(attdata)
> print 'List is ' + str(listlen) + " items long"
> for index in range (0,listlen):
> field = attdata[index]
> ftype = fieldlist[index][1]
> #Add function to de-capitalize string,
> if string.capitalize(ftype[0:7]) == 'Varchar':
> try:
> data = '"' + str(field[1]) + '"'
> except:
> data = '"' + field[1] + '"'
> elif string.capitalize(ftype[0:4]) == "Date":
> try:
> data = '#' + str(field[1]) + '#'
> except:
> data = '#' + field[1] + '#'
> else:
> data = field[1]
>
> if index == 0:
> rs_name = rs_name + self.tblName+ '.' + field[0] + ' =
> ' + data
> else:
> try:
> rs_name = rs_name + ', ' + self.tblName+ '.' +
> field[0] + ' = ' + data
> except:
> print 'Error in data type match, debug data
> follows: '
> print rs_name
> print self.tblName
> print field[0]
> print data
> rs_name = rs_name + ' ' + wherecl + ';'
> rs.Open(rs_name, conn,1 ,3)
> conn.Close()
>
> This script:
>
> 1) Opens a database conenction
> 2) Allows execution of an append query (supplied as a SQL string)
> 3) Allows exceution of an update query.
>
> Other sources on info:
>
> MSDN query on ADO
>
> There is an ADO / Python page out there (check google) but I can't
> remember the URL.
>
> HTH,
>
> Eric
>
> Eric B. Powell
> BSRI
> Electronic Aids
> (803)208-6207
>
>
>
> *Phill Atwood <me at phillatwood.name>*
> Sent by: python-win32-bounces at python.org
>
> 03/12/2006 05:19 PM
>
>
> To
> python-win32 at python.org
> cc
>
> Subject
> [python-win32] driving MS Access from python
>
>
>
>
>
>
>
>
>
>
>
> Need to programatically reproduce some activities a user does in MS
> Access.
> Need to open a mdb file. Run some queries. Look at some tables.
>
> So far
>
> import win32com.client
> a = win32com.client.Dispatch("Access.Application")
> a.Visible = 1
> db = a.OpenCurrentDatabase(filename)
>
> Which seems to work so far. But now I need to run 2 of 3 named
> queries. Then switch
> to the Tables view and collect the info that has now populated the
> tables and extract them
> into python code. I having a hard time scaring up appropriate docs on
> the MS Access
> win32com API for this. I've been looking for VBA style docs or anything
> but I'm still so
> far just sniffing the corners...
>
> Any help is appreciated.
>
> Phill
>
> _______________________________________________
> Python-win32 mailing list
> Python-win32 at python.org
> http://mail.python.org/mailman/listinfo/python-win32
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/python-win32/attachments/20060313/1b63aa8a/attachment.html
More information about the Python-win32
mailing list