[Chicago] pyodbc to ms access

[Aaron Elmquist]

THANKS for the help everyone.  I finally managed to get a working connect
to the access database.

Here's a quick summary for those following the thread.

1. Connect via ODBC using pyodbc library.
2. Setup a DSN using the ODBC manager located in
C:\Windows\SysWOW64\odbcad32.exe.
3. The 32-bit manager does not come up in the default windows manager found
in the control panel.
4. I have an *.mdb file and used the driver only labeled *.mdb (not
*.accdb, *mdb).
5. A 64-bit python *DOES NOT* work with the 32-bit access driver.
6. Using a 32-bit python resolved the issue.

Code for Connection:

>>> import pyodbc
>>> c = pyodbc.connect(r'DSN=my_db')
>>> crsr = c.cursor()
>>> for t in crsr.tables(tableType='TABLE'):print(t)
>>> c.close()

I also installed both the 2007 and 2010 redistributable drivers.

~Aaron

On Mon, May 9, 2016 at 8:49 PM, Joshua Herman <zitterbewegung at gmail.com>
wrote:

> I looked at microsofts documentation about exporting data from access.
> Apparently you could export your Access database into an Excel spreadsheet
> and then using Excel you could output to CSV and then you could load it
> into pandas or python and then put it into a database. See
> https://support.office.com/en-us/article/Export-data-to-Excel-64e974e6-ae43-4301-a53e-20463655b1a9
>
> On Mon, May 9, 2016 at 4:54 PM Robare, Phillip (TEKSystems) <
> proba at allstate.com> wrote:
>
>> Five years ago I had to pull some data out of Access.  I don’t have the
>> code available any more but I remember I used libraries that came with
>> ActiveState’s Python.  I also remember it took some trial and error.
>> Eventually I got a nice flask + SQLServer site going to replace a buggy
>> multi-user Access system that had grown too big.
>>
>>
>>
>> There were a couple of “recipes” in the second edition of the Python
>> Cookbook that are outdated but still on-line (
>> http://code.activestate.com/recipes/528868-extraction-and-manipulation-class-for-microsoft-ac/
>> and
>> http://code.activestate.com/recipes/52267-reverse-engineer-ms-accessjet-databases/).
>> I found them useful as an example of navigating Access’ data base.  The
>> code itself is so out of date it wouldn’t run 5 years ago so I don’t hold
>> out much hope for it now.
>>
>>
>>
>> I’ve no experience with it but another route may be IronPython since
>> access to Access is probably smoother from the .Net virtual machine.
>> Manning’s “Iron Python in Action” I think covered ODBC and the sample code
>> is available for download from
>> http://www.ironpythoninaction.com/download.html.
>>
>>
>>
>>
>>
>>
>>
>> Phil Robare
>>
>>
>>
>> *From:* Chicago [mailto:chicago-bounces+proba=allstate.com at python.org] *On
>> Behalf Of *Aaron Elmquist
>> *Sent:* Monday, May 09, 2016 6:24 AM
>> *To:* The Chicago Python Users Group <chicago at python.org>
>> *Subject:* [Chicago] pyodbc to ms access
>>
>>
>>
>> Does anyone have some advice or insight on connecting to an MS Access
>> Database using python?
>>
>> Here's my info:
>>
>>
>> Connection String:
>>
>>     r'DRIVER={Microsoft Access Driver (*.mdb,
>> *.accdb)};DBQ=C:\Path\to\DB\db.mdb;'
>>
>> Python Version:
>>
>>     Python 3.5.1 :: Continuum Analytics, Inc. - 64 bit
>>
>> OS
>>
>>     Windows 7
>>
>> Office
>>
>>     32 Bit
>>
>> Here's my error:
>>     Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data
>> source name not found and no default driver specified (0)
>> (SQLDriverConnect)')
>>
>> but...
>>
>> pyodbc.dataSources() returns:
>>     {'Excel Files': 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
>> *.xlsb)',
>>      'MS Access Database': 'Microsoft Access Driver (*.mdb, *.accdb)',
>>      'dBASE Files': 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)'}
>>
>> Do I need to use a 32 bit python to get this done?
>>
>> Can I specify the python bits version to use in a specific conda env?
>>
>> Thanks,
>>
>> Aaron
>> _______________________________________________
>> Chicago mailing list
>> Chicago at python.org
>> https://mail.python.org/mailman/listinfo/chicago
>>
>
> _______________________________________________
> Chicago mailing list
> Chicago at python.org
> https://mail.python.org/mailman/listinfo/chicago
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/chicago/attachments/20160509/a38df8ee/attachment.html>