sqlutil Code

class record (object):
  # manca gestione errori sql !!!!!!!
  def __init__( self, tblname=None, lkeyname=None, lfld=None, akey=-1 ):
    self.tabella = tblname
    self.lkeyname = lkeyname or []
    self.lfld = lfld or []
    self.akey = akey        # key autoincrement
  #def dbtrans( self, dbp, metodo, *args, **kw ):
  #  return dbp.runInteraction( metodo, *args, **kw )
  #def dbquery( self, dbp, *args, **kw ):
  #  return dbp.runQuery( *args, **kw )
  def show( self ):
    q = str( self ) + '=[' 
    for f in self.lfld: q += '%s: %s,' % (f, getattr( self, f, None ))
    return q + ']'
  def __str__( self ):
    return 'record %s - key %s' % (self.tabella or '',self.keystr())  
  def lkey( self ): 
    return [getattr( self, k ) for k in self.lkey()] 
  def keystr( self ): 
    return '|'.join( [('%s' % k) for k in self.lkey()] )
  def sqlwhere( self ): 
    q = ' AND '.join( [('%s=%%s' % nm) for nm in self.lkeyname] )
    return ' WHERE ' + q
  def delete( self, crs ):    
    q = 'DELETE FROM %s ' % self.tabella 
    q += self.sqlwhere()
    return crs.execute( q, self.lkey() )
  def readfield( self, crs, lfld, lkey=None, swhere=None ):
    if swhere == None: swhere = self.sqlwhere()
    if lkey == None: lkey = self.lkey()
    q = 'SELECT ' + ','.join( lfld ) 
    q += (' FROM %s' % self.tabella) + swhere
    return crs.execute( q, lkey )
  def setlvalue( self, lval, lfld ):
    if getattr( lval, '__iter__', None ): 
      for i, fld in enumerate( lfld ): setattr( self, fld, lval[i] )
    else:
      for fld in lfld: setattr( self, fld, lval )
    return self
  def reread( self, crs, lfld=None, swhere=None, lkey=None ):
    if lfld == None: lfld = self.lfld
    qta = self.readfield( crs, lfld, lkey, swhere )
    if qta <= 0: self.setlvalue( None, lfld )
    else: self.setlvalue( crs.fetchone(), lfld )
    return self
  def update( self, crs, lval, lfld=None, lkey=None ):
    if lfld == None: lfld = self.lfld
    if lkey == None: lkey = self.lkey()
    q = 'UPDATE %s SET ' % self.tabella  
    q += ','.join( [('%s=%%s' % nm) for nm in lfld] )
    q += self.sqlwhere()
    tval = list(lval) + list(lkey)
    crs.execute( q, tval )
    self.setlvalue( lval, lfld ) 
    return self
  def insert( self, crs, lval=None, lfld=None ):
    'ulteriori campi da avvalorare oltre alla chiave'
    qfld = list( self.lkeyname )
    qval = self.lkey()
    if lval != None:
      qval += list( lval )
      if lfld != None: qfld += list( lfld )
      else:            qfld += list( self.lfld ) 
    q = 'INSERT INTO %s ' % self.tabella 
    q += '(' + ','.join( qfld ) + ')'
    q += ' VALUES (' + ','.join( ['%s' for nm in qfld] ) + ')'
    # eventualmente intercettare chiavi duplicate
    crs.execute( q, qval )
    if self.akey >= 0: 
      setattr( self, self.lkeyname[self.akey], crs.lastrowid )
    return self
  def insert_qta( self, crs, lval, qta=1, lfld=None ):
    ''' inserisce il record da una lista di valori dei campi in lettura '''
    if qta <= 0: return self
    #insert aggiorna la akey => se non la azzero ho una duplicate key
    #assicura che la chiave automatica valga None prima dell'inserimento
    if self.akey >= 0: setattr( self, self.lkeyname[self.akey], None )
    self.insert( crs, lval, lfld )  # insert deve aggiornare la chiave
    #self.update( crs, lval, lfld ) 
    return self.insert_qta( crs, lval, qta - 1, lfld ) 
  def copy( self, crs, lfld, lfromkey ):
    'copia una lista di campi da lfromkey a se stesso'
    qta = self.readfield( crs, lfld, lfromkey )
    #for i in qta:
    if qta > 0:
      val = crs.fetchone()
      self.update( crs, val, lfld )
    return self

  #def storepassword( self, dbp, npass, fpass='pass' ):
  #  'deferred che salva la nuova password dell\'utente'
  #  q = 'UPDATE %s SET %s=' % (self.tabella,fpass) 
  #  q += 'MD5(%s)' + self.sqlwhere()
  #  lval = [npass] + list(self.lkey())
  # return dbp.runOperation( q, lval )
 #def getpassword( self, typedpass, typeduser, tbl='utenti' ):
  #  q = 'SELECT MD5(%s),pass FROM utenti WHERE user=%s'
  #  return self.runQuery( q, (typedpass,typeduser) )
 

def getcolvalue( crs, c=0, null=None, aval=None ):
  try: r = crs.fetchone() ; val = r[c] 
  except: val = null
  if aval: val += aval
  return val 

#def getepoca():
#  if self.epoca == None: self.epoca = datetime.date.today()
#  return self.epoca
#def date_xrange( self, fldiniz=None, fldfine=None, epoca=None ):
#  if epoca == None: epoca = self.getepoca()
#  q = ' AND %s <= \'%s\' AND (%s IS NULL OR %s >= \'%s\')'  
#  return q % (fldiniz,epoca,fldfine,fldfine,epoca)
def date_range( fldiniz, fldfine ):
  q = fldiniz +'<=%s'
  q += ' AND (' + fldiniz + ' IS NULL OR ' + fldfine + '>=%s)'
  return q