Thursday, September 22, 2011

Query Access Database (MDB) from Python

Ever wish that you could quickly run a query against an Access database (MDB) from a command line? If you're already working within a command line this can be very efficient to do a quick lookup. Here's how this can be accomplished.

  1. Install pyodbc
  2. Copy the lookup.py code into a file and edit the MDB and SQL variables to suit your needs
  3. Copy the lookup.bat file into a file (you may need to edit the path to point to lookup.py)
  4. You should save lookup.bat to a folder that is within your Windows Path

lookup.py

import sys, csv
import pyodbc

# Handle command line arguments
if len(sys.argv) == 1:
    print "Enter usage notes here"
    print "(use % for wildcard)"
    sys.exit()
print "Searching for '%s'" % sys.argv[1]
print ""

# Query the CLLI MDB
MDB  = "PATH TO MDB"
DRV  = "{Microsoft Access Driver (*.mdb)}"
conn = pyodbc.connect("DRIVER=%s;DBQ=%s" % (DRV,MDB))
curs = conn.cursor()
SQL  = "SELECT * FROM table WHERE col='%s'" % sys.argv[1]
curs.execute(SQL)
rows = curs.fetchall()
curs.close()
conn.close()

# Print out results
i = 1
for row in rows:
    print "Result #%s" % i
    print "--------------------------"
    for col in row:
        print col
    print ""
    i = i + 1

lookup.bat

@call python.exe lookup.py %1


No comments:

Post a Comment