Otsi.py

#!/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"