Python, UnixODBC, MDBTools and Microsoft Access on Fedora 12

This is funny to me in light of my last post. I have installed UnixODBC, I installed the gui tool to manage it and then I installed MDBTools to allow me to connect to a JET database. (This is all on my Fedora 12 machine by the way for those just joining us.) The funny part is that the time I spent getting the gui tool was wasted as it wont work with the driver anyway, at least not the way I’ve set it up. Not sure why, not sure that I care. Here is what I did to get it to work. First I checked for the driver ( /usr/lib/libmdbodbc.so.0 in my case ). It was there so I edited /etc/odbcinst.ini and added the following entry.

[MDBToolsODBC]
Description = MDB Tools ODBC
Driver = /usr/lib/libmdbodbc.so.0
Setup =
FileUsage =
CPTimeout =
CPReuse =



That takes care of telling UnixODBC about the driver. I wonder if there is a file from MDBTools that I can use for the setup part. Not sure, may look into it at some point. The next step was creating a DSN. That involved adding the following lines to /etc/odbc.ini (Which existed but was empty because I hadn’t created any entries yet.)

[lintry]
Description = Microsoft Access Try DB
Driver = MDBToolsODBC
Database = /home/jr.peck/Documents/lintry.mdb
Servername = localhost
Username =
Password =
port = 5432




Here is what a connection in Python could look like.

import pyodbc


sql = 'Select * from foo'

conn = pyodbc.connect('DSN=lintry')
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()

for record in rows:
    print record.FirstName + ' ' + record.LastName
  
cursor.close()
conn.close()



In my database the table foo contains three columns and the first two are FirstName and LastName. So anyone messing this will obviously need to change it to match their table name, column names, etc. This last piece of getting things to work on Linux wasn’t really necessary but it didn’t take all that long to figure out and I’m glad I did, in case I do need it down the road. I imagine this would work just the same for any other programming language that could take advantage of UnixODBC.

Posted in Database, Geeks In Action, Programming Tagged with: , ,
6 comments on “Python, UnixODBC, MDBTools and Microsoft Access on Fedora 12
  1. Tony says:

    Awesome! Works great! Thanks!

    Having a little trouble connecting over the network to an MDB file on a Windows machine, however. I set up a share via nautilus and set the Database path to /home/tony/.gvs/files\ on\ 192.168.1.5/test.mdb, but I get “Unable to locate database”. I can browse there in the shell without a problem. Any thoughts?

  2. JR says:

    How are you connecting to make the share – is it a connection to a windows machine using Samba?

  3. Tony says:

    Yes.

  4. JR says:

    I’ve never used Samba. I’ve done some quick checking around and I’m not finding a lot. I found an ubuntu forum post that looks to be along the same lines, but I’m not connecting what they told him and his description of the solution. It looks like he just wasn’t mounting the share. But it sounds like you’ve got that happening.

    This is really a natural approach to take for this kind of thing, so I’ll probably mess around a bit and see if I can figure it out. If you do, I’d appreciate hearing about it. Otherwise, if I get it working I’ll post what I did.

  5. Tony says:

    Will do, thanks!

  6. JR says:

    I’m thinking the issue may be permissions or in the way that nautilus is setting up the share. I’m not sure, but it’s just a guess. Here is what I just did (after I installed samba).

    I created a share on a laptop running xp. This is not on a domain, I wanted to throw this together quickly. It’s in a work group with a regular user/password setup. I created a directory right off c:\ called smbmdbcheck and then shared it. I did this by just turning on sharing.

    On my Fedora box, which is connected to the laptop via a hub I created a directory /mnt/smbcheck and made it wide open. chmod 777 /mnt/smbcheck

    I followed the stuff above and created all my odbc stuff. (This is a new machine since I wrote this post. It’s 64 bit and so the mdbtools install put my driver in /usr/lib64/ – just a heads up for anyone in the same boat.)

    I added an entry in /etc/hosts for the laptop which is called jr-laptop.

    I ran, as root, the command: (all on one line – the comment formatting is breaking it up)

    mount //jr-laptop/smbmdbcheck /mnt/smbcheck -t cifs -o username=myname,password=mypassword,rw

    Then I checked with ls /mnt/smbcheck and saw my mdb file was visible.

    Then I ran the code above and it worked as expected. To make this connection persistent I would just need to add a record to /etc/fstab and mount that drive when I start a session.

    I just did all this and then typed this up real quick – so I may have missed something or it might not be that clear. Let me know either way. I’m about to head home for the evening. I’ll check back in on things over the week-end and hopefully you’ll have it going.

Leave a Reply

Your email address will not be published. Required fields are marked *

*