def update(self, table, values, where=None, returning=None):
"""
>>> db = connection()
>>> db.insert('doctest_t1',{'name':'xxx'})
1
>>> db.update('doctest_t1',{'name':'yyy','active':False},{'name':'xxx'})
1
>>> db.update('doctest_t1',values={'count__add':1},where={'name':'yyy'},returning='count')
[[1]]
>>> db.update('doctest_t1',values={'count__add':1},where={'name':'yyy'},returning='count')
[[2]]
>>> db.update('doctest_t1',values={'count__func':'floor(pi()*count)'},where={'name':'yyy'},returning='count')
[[6]]
>>> db.update('doctest_t1',values={'count__sub':6},where={'name':'yyy'},returning='count')
[[0]]
>>> db.delete('doctest_t1',{'name':'yyy'})
1
"""
sql = 'UPDATE %s SET %s' % (table, sqlop.update(values))
sql = self.cursor.mogrify(sql, values)
if where:
sql += self.cursor.mogrify(sqlop.where(where), where)
if returning:
sql += ' RETURNING %s' % returning
return self.query(sql)
else:
return self.execute(sql)
def delete(self, table, where=None, returning=None):
"""
>>> db = connection()
>>> db.insert('doctest_t1',{'name':'xxx'})
1
>>> db.insert('doctest_t1',{'name':'xxx'})
1
>>> db.delete('doctest_t1',where={'name':'xxx'},returning='name')
[['xxx'], ['xxx']]
"""
sql = 'DELETE FROM %s' % table + sqlop.where(where)
if returning:
sql += ' RETURNING %s' % returning
return self.query(sql, where)
else:
return self.execute(sql, where)
def _build_join(self, tables, where, on, order, columns, limit, offset):
on = on or [None] * len(tables)
return 'SELECT %s FROM %s ' % (sqlop.columns(columns), tables[0]) + \
" ".join(['JOIN %s ON %s' % (tables[i], sqlop.on((tables[0], tables[i]), on[i - 1]))
for i in range(1, len(tables))]) + \
sqlop.where(where) + sqlop.order(order) + sqlop.limit(limit) + sqlop.offset(offset)
def _build_select(self, table, where, order, columns, limit, offset, update):
return 'SELECT %s FROM %s' % (sqlop.columns(columns), table) \
+ sqlop.where(where) + sqlop.order(order) + sqlop.limit(limit) \
+ sqlop.offset(offset) + sqlop.for_update(update)