Python - Microsoft Access

SimYing

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. =)

snakelet

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

 
"""

Basically just an API wrapped around Douglas Savitsky's code from http://www.ecp.cc/pyado.html

Recordset iterator taken from excel.py in Nicolas Lehuen's code from http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440661

"""

AD_OPEN_KEYSET = 1

AD_LOCK_OPTIMISTIC = 3

import win32com.client
 
class AccessDb(object):

    """An Access connection"""    

    def connect(self, data_source, user, pwd, mdw):

        """Returns a connection to the jet database

        NB use .Close() to close (NB title case unlike closing a file)"""

        connAccess = win32com.client.Dispatch(r'ADODB.Connection')

        # DSN syntax - http://support.microsoft.com/kb/193332 and http://www.codeproject.com/database/connectionstrings.asp?df=100&forumid=3917&exp=0&select=1598401

        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)

        #print DSN

        connAccess.Open(DSN)

        return connAccess
 
    def getRecordset(self, connAccess, SQL_statement):

        return Recordset(connAccess, SQL_statement)
 
class Recordset(object):

    """Recordset created from a query"""

    def __init__ (self, connAccess, SQL_statement):

        rs = win32com.client.Dispatch(r'ADODB.Recordset')

        rs.Open(SQL_statement, connAccess, AD_OPEN_KEYSET, AD_LOCK_OPTIMISTIC)

        self.rs = rs
 
    def getFields(self):

        """Get list of field names"""

        fields = [field.Name for field in self.rs.Fields]

        return fields
 
    def __iter__(self):

        """ Returns a paged iterator by default. See paged().

        """

        return self.paged()
 
    def paged(self,pagesize=128):

        """ Returns an iterator on the data contained in the sheet. Each row

            is returned as a dictionary with row headers as keys. pagesize is

            the size of the buffer of rows ; it is an implementation detail but

            could have an impact on the speed of the iterator. Use pagesize=-1

            to buffer the whole sheet in memory.

        """

        try:

            fields = self.getFields()

            #fields = [self.encoding(field.Name) for field in recordset.Fields]

            ok = True

            while ok:

                # Thanks to Rogier Steehouder for the transposing tip 

                rows = zip(*self.rs.GetRows(pagesize))
 
                if self.rs.EOF:

                    # close the recordset as soon as possible

                    self.rs.Close()

                    self.rs = None

                    ok = False
 
                for row in rows:

                    yield dict(zip(fields, row))

        except:

            if self.rs is not None:

                self.rs.Close()

                del self.rs

            raise

Which you can use like this

Expand|Select|Wrap|Line Numbers

 
import msaccess

accessdb = msaccess.AccessDb()

connAccess = accessdb.connect("<mdb path here>", ",user name here>", "<password here>", "<mdw file path here>")
 
SQL_statement = "SELECT * FROM tblClient"

rs = accessdb.getRecordset(connAccess, SQL_statement)

fields = rs.getFields()

print fields
 
for item in rs:

    print item
 
connAccess.Close()

bartonc

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

bartonc

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!

Python - Microsoft Access

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ