def save(self): dbh = dbstuff.getRW(self.dbh_key) try: c = dbh.cursor() cols,vals = [],[] for k,v in self.sqlfields.items(): if v > 1 or k==self.idfield: try: vals.append(self.__getattribute__("_sql_"+k)) cols.append('`' + k + '`') except AttributeError: # just skip missing attrs pass if not self.selected: query = "INSERT INTO " + self.table + " (" + ','.join(cols) + ") VALUES (" + ','.join(['%s' for v in vals]) + ")" if (DEBUG): print query c.execute(query, tuple(vals)) dbh.commit() if not self.id: self.__setattr__(self.SQLId, c.lastrowid) self.selected = True else: if cols: query = "UPDATE " + self.table + " SET " + ','.join(["%s=%%s" % k for k in cols]) + " WHERE " + self.idfield + "=%s" if (DEBUG): print query c.execute(query, tuple(vals + [self.id])) dbh.commit() c.close() finally: dbstuff.release(dbh, self.dbh_key)
def __init__(self, id=0, dbh=None, fields=[], dbh_key = "default"): object.__setattr__(self, "sqlfields", {}) self.idfield = self.SQLId self.table = self.SQLTable self.sqlfields = schemas.get(self.table,{}) self.dbh_key = dbh_key self.selected = False release = False if dbh is None: dbh = dbstuff.getRW(self.dbh_key) release = True try: if self.sqlfields=={}: query = "DESCRIBE " + self.table c = dbh.cursor() c.execute(query) for column,coltype,null,key,default,extra in c: self.sqlfields[column] = 1 self.__setattr__("_sql_" + column, default) schemas[self.table] = self.sqlfields self.id = id if self.id: query = "SELECT " if len(fields)>0: query += ",".join(fields) + " " else: query += "* " query += "FROM " + self.table + " WHERE " + self.idfield + "=%s" if (DEBUG): print query c = dbh.cursor(MySQLdb.cursors.DictCursor) c.execute( query, id ) self.selected = True # else object not found if c.rowcount>0: d = c.fetchone() for k,v in d.items(): self.__setattr__("_sql_" + k, v) self.sqlfields[self.idfield] = 1 else: raise ObjectNotFound( self.table + " " + str(id) ) else: for k in self.sqlfields: self.sqlfields[k] = 2 finally: if release: dbstuff.release(dbh, self.dbh_key)
def delete(self): """Remove the database representation of this object. You shouldn't attempt to mess with this object after calling delete(); probably something bad will happen. """ query = "DELETE FROM " + self.table + " WHERE " + self.idfield + "=%s" dbh = dbstuff.getRW(self.dbh_key) try: c = dbh.cursor() c.execute(query, self.id) c.close() dbh.commit() finally: dbstuff.release(dbh,self.dbh_key)
def SQLFactory(cls, keys={}, sort=[], multi=False, dbh=None, dbh_key="default"): """Pull an instance of a class from the database.""" rv = None if multi: rv = [] release = False if dbh is None: release = True dbh = dbstuff.getRO(dbh_key) try: whereclause = [] wherevalues = [] for k,v in keys.items(): if k[0]=='<': whereclause.append('%s<%%s' % k[1:]) elif k[0]=='>': whereclause.append('%s>%%s' % k[1:]) else: whereclause.append('%s=%%s' % k) wherevalues.append(v) query = "SELECT " + cls.SQLId + " FROM " + cls.SQLTable if whereclause: query += " WHERE " + ' AND '.join(whereclause) if sort: if type(sort)!=type([]): sort = [sort] query += " ORDER BY " + ','.join([' `%s` %s' % (k,d) for k,d in sort]) c = dbh.cursor() if (DEBUG): print query c.execute( query, tuple(wherevalues) ) if c.rowcount>0: if multi: for id, in c: rv.append(cls(id,dbh)) else: #print "ROW COUNT" #print c.rowcount (id,) = c.fetchone() rv = cls(id, dbh) c.close() finally: if release: dbstuff.release(dbh,dbh_key) return rv
def SQLNewFactory(cls, data, dbh=None, dbh_key = "default"): """Create a new instance of a class.""" release = False if dbh is None: release = True dbh = dbstuff.getRW(dbh_key) try: columns = [] values = [] for k,v in data.items(): columns.append('`' + k + '`') values.append(v) query = "INSERT INTO " + cls.SQLTable + " (" + ",".join(columns) + ") VALUES (" + ",".join(["%s" for v in values]) + ")" c = dbh.cursor() if (DEBUG): print query c.execute( query, tuple(values) ) id = c.lastrowid c.close() dbh.commit() finally: if release: dbstuff.release(dbh,dbh_key) return cls(id, dbh)