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. =)
3 27687
Try this (just made it now and started using it in a project - it is working for me so far): -
"""
-
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 -
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()
-
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
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Chuck Grimsby |
last post by:
(Repost, due to lack of submissions...)
The Microsoft Access Product Group (the people who build Microsoft
Access) want your help!
One of the main things we're working on for the near future...
|
by: Larry__Weiss |
last post by:
What kind of licensing is needed to be able to use the Microsoft Access product
and distribute the applications? In other words, what version of Microsoft Access
does a developer need to buy in...
|
by: Null Reference |
last post by:
Anybody here who can explain or point me to a link ?
I wish to create a blank MS Access DB file programmatically
using C# .
Thanks, nfs
|
by: Pam Ammond |
last post by:
I need to use Microsoft Access Automation within a Visual Studio 2003 program
written in C# for Windows Forms. When a button is clicked in my VS.NET
program, I want it to run a Microsoft Access...
|
by: bbdobuddy |
last post by:
Hi,
How do I open a Microsoft Access 2003 form from Visual Basic.net
Thanks in advance
bbdobuddy
|
by: jeffhg582003 |
last post by:
Hi,
I am developing a python script which add records to
a microsoft access tables. All my tables have autogenerated number
fields. I am trying to capture the number generated from the insert...
|
by: somersbar |
last post by:
hello all,
im trying to connect to a microsoft access database from an ASP.NET web
form.
i keep getting the following error though:
ERROR Could not use
'(unknown)'; file already in use....
|
by: bazzer |
last post by:
hey,
i am using visual basic.net 2003 and have an ASP.NET webform
application thats accessing a microsoft access 2003 database. i kept
getting the following error when i tried to run it:
ERROR ...
|
by: bazzer |
last post by:
hey,
im trying to access a microsoft access database from an ASP.NET web
application in visual basic 2003.NET. i get the following error when i
try running it:
Server Error in...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |