Entry

SQL class for connecting to MySQL database

Jul 5th, 2000 10:01
Nathan Wallace, unknown unknown, Hans Nowak, Snippet 206, Boudewijn Rempt

"""
Packages: database
"""

#!/usr/bin/env python

import types
import MySQL

class Sql:

  def __init__(self, dbc):
    self.dbc=dbc

  def __wherelist(self, keys):
    wherelist = ''
    for key in keys.keys():
      if keys[key][0] <> None and keys[key][0] <> "":
        value=keys[key]
        if type(value[0])==types.StringType:
          wherelist = wherelist + " and " + key + " like " + "'" + \
          MySQL.escape(value[0]) + "'"
        else:
          wherelist = wherelist + " and " + key + " = " + str(value[0])
    if wherelist == '':
      wherelist = " and 1=1"
      
    return wherelist[4:]
  
  def __updatelist(self, values):
    updatelist = ''
    for key in values.keys():
      updatelist = updatelist + ", " + key + "="
      value=values[key]
      if type(value[0])==types.StringType:
        updatelist = updatelist + "'" + MySQL.escape(value[0]) + "'"
      else:
        updatelist = updatelist + str(value[0])
    return updatelist[1:]
 
  def wherelist(self, keys):
    return self.__wherelist(keys)
  
  def seqnr_nextval(self, table, keys):
    seqnr=self.select(table, keys ,"max(seqnr)")
    if seqnr[0][0] == None:
      return 1
    else:
      return seqnr[0][0] + 1

  def insert(self, table, fields):
    if self.dbc != None:
      
      #
      # Determine primary key
      #
      pk_field = table[4:] + 'nr'
      pk = self.dbc.do("select max("+pk_field+") from " + table)
      if pk[0][0]==None:
        pk_nr = 1
      else:
        pk_nr = pk [0][0] + 1
      #
      # Construct insert statement
      #
      fieldlist = pk_field
      valuelist = str(pk_nr)
      
      for field in fields.keys():
        fieldlist = fieldlist + ',' + field
        value = fields[field]
        
        if type(value[0])==types.StringType:
          valuelist=valuelist +",'" + MySQL.escape(value[0]) + "'"
        else:
          valuelist=valuelist +"," + str(value[0])
      self.dbc.do ("insert into " + table + " ( " + fieldlist + ") values ( " 
      + valuelist + ")")

      return pk_nr

  def delete(self, table, keys):
    #
    # Extend with ranges etc.
    #
    if self.dbc !=None:
      self.dbc.do (" delete from " + 
                     table + 
                     " where " + 
                     self.__wherelist(keys)
                  )
    
  def update(self, table, values, keys):
   if self.dbc !=None:
     self.dbc.do ("update " + table + 
                   " set " + 
                   self.__updatelist(values) + 
                   " where " + 
                   self.__wherelist(keys)
                 )
                 
  def select(self, table, keys, fieldstring):
   if self.dbc !=None:
     if keys != None:
       return self.dbc.qry ("select " + fieldstring +
                            " from  " + table +
                            " where " +
                            self.__wherelist(keys)
                           )
     else:
       return self.dbc.qry ("select " + fieldstring +
                            " from  " + table
                           )

   
def main():
  import db
  dbc=db.Connection('essle','boud','tst')
  sql=Sql(dbc)
  sql.insert('test',{'a':('testval','Y')
                    ,'b':('testval2','N')
                    ,'c':(1,'Y')
                    }
            )

  print sql.select( 'test',{'b':('testval2','N') 
                           ,'c':(1,'Y')
                           }
                          ,'*'
                  )
                    
  sql.delete('test',{'b':('testval2','N')
                    ,'c':(1,'Y')
                    }
            )

  dbc.disconnect()
  
if __name__=='__main__':
  main()