[python-win32] odbc under windows 7?

[Vernon Cole]

I think Tim's last suggestion is the way to go, and you should NOT need
ACCESS installed.

Microsoft invented ODBC.

Everybody saw it  was a great idea and adopted it.

So Microsoft had to invent something even newer, which everybody else does
not support.  That's called ADO. ADO defaults to ODBC mode, so usually you
can happily forget it is there, and use ODBC drivers for everything. But in
native ADO mode, you don't use *drivers*, you use *providers*, (along with a
connection string which is completely different from odbc.)

The provider for Access-type data bases is called JET.  It should be
included on any NT-based Windows version. ACCESS is not required.

The setup I use for testing ado's ability to read and write an ACCESS .mdb
file is something like this:

<Python>
import adodbapi

_databasename = "Test.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s'  % _databasename
_table_name= 'Products'

#create the connection
con = adodbapi.connect(constr)

#make a cursor on the connection
c = con.cursor()

#run an SQL statement on the cursor
sql = 'select * from %s' % _table_name
c.execute(sql)

#get the results
db = c.fetchmany(5)

#print them
for rec in db:
    print rec

print
print 'repr() of next row is...'
print repr(c.next())
print

c.close()
con.close()
</Python>

Running the script (on my 32-bit Vista laptop with no Microsoft Office
components installed) gives:

<console dump>
('1', 'Widgit', '5.0', '15.1234', '2009-01-29 13:05:30')
('2', 'Thingamajig, Standard', '505.0', '0.1', '2009-01-29 15:05:19')
('3', 'Left Handed Smoke Shifter', '1.0', '1000000', '2008-04-01 12:00:00')
('4', 'Gravel (Bulk)', '100.25', '32.4567', '2009-01-29 13:05:31')
('5', 'Tube, Drinking, Plastic, For cold liquids', '500000.0', '0.0013',
'2009-01-29 13:05:32')

repr() of next row is...
<SQLrow={ItemNumber:6, ItemName:u'Annoy-A-Tron', UnitsOnHand:1.0,
CostPerUnit:Decimal('12.95'), LastOrdered:datetime.datetime(2009, 1, 29, 13,
5, 33)}>
</console dump>
-------------------

Notes:
* Test.mdb is in the /test folder of the adodbapi source distribution.

* to get that fancy row object with the column names, you must be running
the latest version of adodbapi from
http://sourceforge.net/projects/adodbapi/.  The version which ships with
pywin32 at will just give a normal tuple -- until the next release of
pywin32.

* I could not locate a 64 bit Windows 7 box to test this on.  I tried.
Sorry. Please report back whether or not this works for you.
--
Vernon


On Mon, Jan 24, 2011 at 11:46 AM, Tim Roberts <timr at probo.com> wrote:

> Gary L Smith wrote:
> >
> >
> >
> > I need to access data in an MS Access 2003 database using Python 2.5
> > or 2.6 running in Windows7.  Windows7 doesn’t seem to have appropriate
> > drivers for ODBC.  My error messages consistently contain the phrase,
> > “Data source name not found and no default driver specified.”
> >
> >
> >
> > While I see that others have run into the same problem, my web
> > searches have only deepened my confusion – questions are routinely
> > answered with suggestions that turn out to not work in Windows7.
> >
> >
> >
> > Suggestions such as SQL Alchemy or pyODBC don’t solve the problem,
> > because it still boils down to the missing drivers.
> >
> >
> >
> > This is a volunteer effort, so even the Egenix product, mxODBC, at $69
> > is a little much.
> >
> >
> >
> > Are there suitable open-source codes to get at Access 2003 data in
> > Windows7?  Please point me in a productive direction, or, it it’s just
> > not possible, put me out of my misery.
> >
>
> Do you have Access installed on this machine?  That's always been one of
> the downsides of Access -- you have to have the Access run-time
> installed to use an Access database.
>
> Note that ODBC does not really have anything to do with Access.  ODBC is
> just a generic database layer.  If you don't have the Access runtime,
> then even mxODBC won't get you into the data.  From the message, it
> sounds like you are getting into ODBC just fine, but there is no ODBC
> Access driver installed.
>
> Have you tried ADODB and direct DAO access?  Both of them can be used
> with Access, although again without the runtime, I think you're in trouble.
>
> --
> Tim Roberts, timr at probo.com
> Providenza & Boekelheide, Inc.
>
> _______________________________________________
> 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/20110124/1ff276e9/attachment-0001.html>