470,612 Members | 2,434 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,612 developers. It's quick & easy.

Python - Microsoft Access

Is there any way to read and query data from Microsoft Access?

Actually, i had anapplication which build using Python, wxPython, NumArray and etc. Now i need to find a way to retrieve data from Microsoft Access to display it on my application's interface (drop down menu etc).

I have found 2 ways:

1) Using Mark Hammond's PyWin32
2) Using mxODBC

I read some material related to it and i decided to use PyWin32 since it's free. But i don't know where i should start from.

Could any one help me on this?

Thanks. =)
Apr 25 '07 #1
3 27283
Try this (just made it now and started using it in a project - it is working for me so far):

Expand|Select|Wrap|Line Numbers
  1. """
  2. Basically just an API wrapped around Douglas Savitsky's code from http://www.ecp.cc/pyado.html
  3. Recordset iterator taken from excel.py in Nicolas Lehuen's code from http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440661
  4. """
  5. AD_OPEN_KEYSET = 1
  6. AD_LOCK_OPTIMISTIC = 3
  7. import win32com.client
  8.  
  9. class AccessDb(object):
  10.     """An Access connection"""    
  11.     def connect(self, data_source, user, pwd, mdw):
  12.         """Returns a connection to the jet database
  13.         NB use .Close() to close (NB title case unlike closing a file)"""
  14.         connAccess = win32com.client.Dispatch(r'ADODB.Connection')
  15.         # DSN syntax - http://support.microsoft.com/kb/193332 and http://www.codeproject.com/database/connectionstrings.asp?df=100&forumid=3917&exp=0&select=1598401
  16.         DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;USER ID=%s;PASSWORD=%s;Jet OLEDB:System Database=%s;" % (data_source, user, pwd, mdw)
  17.         #print DSN
  18.         connAccess.Open(DSN)
  19.         return connAccess
  20.  
  21.     def getRecordset(self, connAccess, SQL_statement):
  22.         return Recordset(connAccess, SQL_statement)
  23.  
  24. class Recordset(object):
  25.     """Recordset created from a query"""
  26.     def __init__ (self, connAccess, SQL_statement):
  27.         rs = win32com.client.Dispatch(r'ADODB.Recordset')
  28.         rs.Open(SQL_statement, connAccess, AD_OPEN_KEYSET, AD_LOCK_OPTIMISTIC)
  29.         self.rs = rs
  30.  
  31.     def getFields(self):
  32.         """Get list of field names"""
  33.         fields = [field.Name for field in self.rs.Fields]
  34.         return fields
  35.  
  36.     def __iter__(self):
  37.         """ Returns a paged iterator by default. See paged().
  38.         """
  39.         return self.paged()
  40.  
  41.     def paged(self,pagesize=128):
  42.         """ Returns an iterator on the data contained in the sheet. Each row
  43.             is returned as a dictionary with row headers as keys. pagesize is
  44.             the size of the buffer of rows ; it is an implementation detail but
  45.             could have an impact on the speed of the iterator. Use pagesize=-1
  46.             to buffer the whole sheet in memory.
  47.         """
  48.         try:
  49.             fields = self.getFields()
  50.             #fields = [self.encoding(field.Name) for field in recordset.Fields]
  51.             ok = True
  52.             while ok:
  53.                 # Thanks to Rogier Steehouder for the transposing tip 
  54.                 rows = zip(*self.rs.GetRows(pagesize))
  55.  
  56.                 if self.rs.EOF:
  57.                     # close the recordset as soon as possible
  58.                     self.rs.Close()
  59.                     self.rs = None
  60.                     ok = False
  61.  
  62.                 for row in rows:
  63.                     yield dict(zip(fields, row))
  64.         except:
  65.             if self.rs is not None:
  66.                 self.rs.Close()
  67.                 del self.rs
  68.             raise
  69.  
Which you can use like this

Expand|Select|Wrap|Line Numbers
  1. import msaccess
  2. accessdb = msaccess.AccessDb()
  3. connAccess = accessdb.connect("<mdb path here>", ",user name here>", "<password here>", "<mdw file path here>")
  4.  
  5. SQL_statement = "SELECT * FROM tblClient"
  6. rs = accessdb.getRecordset(connAccess, SQL_statement)
  7. fields = rs.getFields()
  8. print fields
  9.  
  10. for item in rs:
  11.     print item
  12.  
  13. connAccess.Close()
  14.  
Aug 3 '07 #2
bartonc
6,596 Expert 4TB
Is there any way to read and query data from Microsoft Access?

Actually, i had anapplication which build using Python, wxPython, NumArray and etc. Now i need to find a way to retrieve data from Microsoft Access to display it on my application's interface (drop down menu etc).

I have found 2 ways:

1) Using Mark Hammond's PyWin32
2) Using mxODBC

I read some material related to it and i decided to use PyWin32 since it's free. But i don't know where i should start from.

Could any one help me on this?

Thanks. =)
I really like that a new member has provided a working solution. That's what makes this site great!

I've been using mxODBC for about a year now. I just discovered that it is no longer free. This situaltion makes the win32com.client option very attractive. But since you already have mx, you just need to set up a DataSource in you Windows control panel and import the Windows ODBC from mx. There are many examples using wxPython in the Articles section.

Keep in touch,
Barton
Aug 3 '07 #3
bartonc
6,596 Expert 4TB
I really like that a new member has provided a working solution. That's what makes this site great!

I've been using mxODBC for about a year now. I just discovered that it is no longer free. This situaltion makes the win32com.client option very attractive. But since you already have mx, you just need to set up a DataSource in you Windows control panel and import the Windows ODBC from mx. There are many examples using wxPython in the Articles section.

Keep in touch,
Barton
Great news on this front!!!
PyODBC looks to be the Python ODBC interface of the future. 2.4 and 2.5 are both currently supported!
Oct 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by Pam Ammond | last post: by
4 posts views Thread by bbdobuddy | last post: by
3 posts views Thread by jeffhg582003 | last post: by
5 posts views Thread by somersbar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.