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  27684 
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: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
| |
