def _update(self):
"""
Uses SQL UPDATE to update record
"""
values = [getattr(self, f, None) for f in self._changed]
values.append(self._get_pk())
query = "UPDATE %s SET " % self.Meta.table_safe
query += ", ".join(["%s = %s" % (escape(f), self.getPlaceholder(f)) for f in self._changed])
query += " WHERE %s = %s " % (escape(self.Meta.pk), self.getPlaceholder(self.Meta.pk))
query = query % tuple(values)
rc = self.dbconn.executeDBNonQuery(query)
return rc
def mult_order_by(self, **kwargs):
'''
sorts by multiple columns, e.g.:
colum_name1='asc', column_name2='desc'
'''
joinstr = 'ORDER BY'
for kfield in kwargs:
field = escape(kfield)
direction = kwargs[kfield].upper()
self.order = joinstr.join([self.order,
" %s %s" % (field, direction)])
joinstr = ","
return self
def __new__(cls, name, bases, attrs):
if name == "Model":
return super(ModelBase, cls).__new__(cls, name, bases, attrs)
new_class = type.__new__(cls, name, bases, attrs)
if not getattr(new_class, "dbconn", None):
raise StandardError, "no database connection for %s" % name.lower()
new_class.connection = new_class.dbconn.connection
if not getattr(new_class, "Meta", None):
new_class.Meta = Empty()
if getattr(new_class.Meta, "table", None):
name = new_class.Meta.table
else:
name = name.lower()
new_class.Meta.table_safe = escape(name)
# Assume id is the default
if not getattr(new_class.Meta, "pk", None):
new_class.Meta.pk = "id"
# Create function to loop over iterable validations
for k, v in getattr(new_class.Meta, "validations", {}).iteritems():
if isinstance(v, (list, tuple)):
new_class.Meta.validations[k] = ValidatorChain(*v)
# Get field names and data types for columns in table
if getattr(new_class, "connection", None):
qry = new_class.Meta.table_safe.join(["SELECT * FROM ", " LIMIT 1;"])
adaptor = OdbcDataAdapter(qry, new_class.connection)
fields = DataSet()
new_class.connection.Open()
adaptor.Fill(fields)
new_class._fields = [c.ColumnName for c in fields.Tables[0].Columns]
new_class._colDescription = dict(
[(c.ColumnName, (c.DataType.FullName, c.AllowDBNull, c.Unique)) for c in fields.Tables[0].Columns]
)
assert new_class.Meta.pk in new_class._colDescription, (
"\nPrimary Key: %s not in database\n" % new_class.Meta.pk
)
new_class.connection.Close()
cache.add(new_class)
return new_class
def getconditionkey(self, key, value):
if "(" in key and ")" in key:
# allows passing functions, e.g., year(author_birthday)
# note these must be passed to self.conditions manually
# example:
# byear = 'year(author_birthday)'
# qry = Query(model=MyModel)
# qry.conditions[byear] = 1922
# looks for authors born in 1922
n0 = key.find("(")
n1 = key.find(")")
fieldname = key[n0+1:n1]
key = key.replace(fieldname, escape(fieldname))
else:
fieldname = key
key = escape(key)
datatype = self.model.columnDescriptions[fieldname][0]
placeholder = sqlPlaceholder(datatype) if value is not None else "%s"
if isinstance(value, tuple):
return key + value[0].replace("%s", placeholder)
else:
return key + "=%s" % placeholder
def order_by(self, field=None, direction='ASC', **kwargs):
'''
Specify sort criteria: 'column', 'direction', or
multiple column sorts with key words, e.g.:
colum_name1='asc', column_name2='desc'
'''
if field:
kwargs.update({field: direction})
joinstr = ' ORDER BY'
for field in kwargs:
direction = kwargs[field].upper()
field = escape(field)
self.order = joinstr.join([self.order,
" %s %s" % (field, direction)])
joinstr = ","
return self
def _new_save(self, duplicateKey="IGNORE"):
"Uses SQL INSERT to create new record"
# if pk field is set, we want to insert it too
# if pk field is None, we want to auto-create it from lastrowid
auto_pk = 1 and (self._get_pk() is None) or 0
fields = []
values = []
placeholders = []
for f in self._fields:
if f != self.Meta.pk or not auto_pk:
v = getattr(self, f, None)
values.append(v)
fields.append(escape(f))
placeholders.append(self.getPlaceholder(f))
# get sql syntax for "REPLACE", "IGNORE", "FAIL"
# treatment of duplicate keys
insertsql = self.dbconn.insert.get(duplicateKey, self.dbconn.insert["FAIL"])
query = insertsql % (self.Meta.table_safe, ", ".join(fields), ", ".join(placeholders))
query = query % tuple([x if x is not None else "NULL" for x in values])
try:
self.dbconn.executeDBNonQuery(query, CloseIt=False)
except:
print("\nInsert Error for query: %s\n" % query)
return False
else:
if self._get_pk() is None:
lastrowid = self.dbconn.executeDBQuery("SELECT LAST_INSERT_ID();")[0][0]
self._set_pk(lastrowid)
return True