Introduction
to Python Programming
Assignment
4 : querydb.py
Connect
to ODBC Windows data source and make a query using SQL, then print report.
Use
supplied nwind.mdb and setup as Microsoft Access Data Source
C:\pythonProjects\UBCPython>python
querydb.py
Error: missing arguments
Usage: querydb.py [-h|--help]
[-c|--column <item>,<item>,...] <database>
<table>
Arguments:
<database>
database
<table>
table to query
Options:
-h|--help
-c|--column <column items> if more than one,
enclose in string and separate with commas
******************************************************************
C:\pythonProjects\UBCPython>python
querydb.py nwins products
database = nwins, table = products
Error: cannot connect to database
*******************************************************************
C:\pythonProjects\UBCPython>python
querydb.py nwind producs
database = nwind, table = producs
SELECT * FROM producs
Error: invalid query
********************************************************************
C:\pythonProjects\UBCPython>python querydb.py
nwind products
database = nwind, table = products
SELECT * FROM products
55
Pâté chinois
25
6
24.0
115
0
20
0
-------------------------------------------
56
Gnocchi di nonna
26
5
24 - 250 g pkgs.
38.0
21
10
30
0
-------------------------------------------
57
Ravioli Angelo
26
5
24 - 250 g pkgs.
19.5
36
0
20
0
-------------------------------------------
58
Escargots de Bourgogne
27
8
24 pieces
13.25
62
0
20
0
-------------------------------------------
C:\pythonProjects\UBCPython>python
querydb.py -c "productid,productname" nwind
products
productid,productname
['productid', 'productname']
database = nwind, table = products
SELECT
productid,productname FROM
products
productid 55
productname Pâté chinois
-------------------------------------------
productid 56
productname Gnocchi di nonna
-------------------------------------------
productid 57
productname Ravioli
Angelo
-------------------------------------------
productid 58
productname Escargots
de
-------------------------------------------
productid 59
productname Raclette Courdavault
#!/usr/bin/env
python
# querydb.py
#
#
list records from database query
#
import sys,os
import string
import getopt
import mx.ODBC.Windows
def usage(name):
print
"""
Usage: %s [-h|--help] [-c|--column
<item>,<item>,...] <database>
<table>
Arguments:
<database> database
<table> table to query
Options:
-h|--help
-c|--column <column items> if more than one,
enclose
in string and separate with commas
"""%name
def
main():
# process
command line arguments
try:
opts, args = getopt.getopt(sys.argv[1:], 'hc:', ['help','column'])
except getopt.GetoptError:
print 'Error: invalid
argument'
usage(sys.argv[0])
sys.exit(1)
# parse
options
colItems
= '*' # set default column items
items = ['*']
for o, a in opts:
if o in ('-h', '--help'):
usage(sys.argv[0])
sys.exit()
if o in ('-c', '--column'):
colItems
= a
items = colItems.split(',')
print colItems
print
items
if (len(args) < 2):
print 'Error: missing
arguments'
usage(sys.argv[0])
sys.exit(1)
else:
database = args[0]
table = args[1]
print 'database =
%s, table = %s' % (database, table)
dbConstr
= 'DSN=%s;UID=;PWD=' % database
# be sure there is valid
database connection
try:
db = mx.ODBC.Windows.DriverConnect(dbConstr)
c = db.cursor()
except:
print 'Error: cannot
connect to database'
sys.exit(2)
# make
the sql SELECT query string and then do the query
sqlSelect
= 'SELECT %s FROM %s' % (colItems,table)
print sqlSelect
# but make sure its a good query
try:
c.execute(sqlSelect)
except:
print 'Error: invalid
query'
sys.exit(3)
# list the record items
while(1):
row = c.fetchone()
if row == None:
break
for i in range(0,len(row)):
if (colItems == '*'):
print
' %s' % row[i]
else:
print
items[i].ljust(20),row[i]
print '-------------------------------------------'
if
__name__ == '__main__':
main()