Otsi.py
Allikas: Lambda
#!/usr/bin/python import sys import cgi import sqlite3 as sqlite databasefile = 'data.db' tables = ['t0','t1','t2','t3','t4'] fields = ['id','salvestaja','f0','f1','f2','f3','f4','f5','f6','f7','f8','f9','f10','f11','f12','f13','f14','f15','f16','f17','f18','f19'] # ---- abifunktsioonid ---- def formatdata(data,format): if format=='xml': return xmlstr(data) else: return jsonstr(data) def jsonstr(data): if data: try: data = str(data) except Exception, e: data = data.encode('UTF-8') return "\""+str(data)+"\"" else: return "\"\"" def xmlstr(data): if data and data!='': data=str(data) data=data.replace('<','<') data=data.replace('>','>') data=data.replace('&','&') return "<data>"+data+"</data>" else: return "<data/>" # --- formaat ja content-type --- try: form = cgi.FieldStorage() format='json' #format='xml' if form.has_key('format'): format=form["format"].value if format=='xml': print 'Content-type: text/xml' else: print 'Content-type: text/plain' print # -- sql select lause moodustamine --- # add first field query = "select %s" % fields[0] # add other fields w/ comma for field in fields[1:]: query += ", %s" % field # add table name and always true clause for searching all fields if form.has_key('table') and form['table'].value in tables: tablename=form['table'].value else: print "0" sys.exit(0) query += " from %s where 1=1 """ % tablename # --- otsivaljad ---- for field in fields: if form.has_key(field): query += " and %s='%s'" % (field, form[field].value.replace('"', ' ').replace("'", " ")) # -- sorteerimine --- order="id" direction="desc" if form.has_key('order'): order = form["order"].value if form.has_key('direction'): direction = form["direction"].value query += " order by %s %s" % (order, direction) # -- otsingu tegemine -- #print query # uncomment to debug con = sqlite.connect(databasefile) cur = con.cursor() cur.execute(query) # --- otsingutulemuste votmine --- fromrow=0 getrows=100 if form.has_key('fromrow'): fromrow=int(form["fromrow"].value) if form.has_key('getrows'): getrows=int(form["getrows"].value) res=[] i=0 j=0 cnt=0 while 1: row=cur.fetchone() if not row: break i=i+1 if i>fromrow: cnt=cnt+1 if cnt>getrows: break res=res+[row] # --- otsingutulemuste vormistamine ---- l=len(res) i=0 if format=='xml': print "<table>" else: print "[" for row in res: if format=='xml': print "<row>" else: print "{", counter=0 for data in row[0:-1]: print "\""+fields[counter]+"\":",formatdata(data,format), counter=counter+1 if format!='xml': print ",", print "\""+fields[counter]+"\":",formatdata(data,format), if format=='xml': print print "</row>" else: print "}", if format!='xml' and i<l-1: print "," i=i+1 if format=='xml': print "</table>" else: print print "]" except Exception, e: #print e # uncommment for debugging print "0"