def upsert(engine, table, row, unique, ensure=True, types={}):
table = get_table(engine, table)
if ensure:
create_index(engine, table, unique)
if not update_row(engine, table, row, unique, ensure=ensure, types=types):
add_row(engine, table, row, ensure=ensure, types=types)
def upsert(engine, table, row, unique, ensure=True, types={}):
table = get_table(engine, table)
if ensure:
create_index(engine, table, unique)
if not update_row(engine, table, row, unique, ensure=ensure, types=types):
add_row(engine, table, row, ensure=ensure, types=types)
def find(engine,
table,
_limit=None,
_step=5000,
_offset=0,
order_by='id',
**kw):
table = get_table(engine, table)
_ensure_columns(engine, table, kw)
order_by = [table.c[order_by].asc()]
qargs = []
try:
for col, val in kw.items():
qargs.append(table.c[col] == val)
except KeyError:
return
for i in count():
qoffset = _offset + (_step * i)
qlimit = _step
if _limit is not None:
qlimit = min(_limit - (_step * i), _step)
if qlimit <= 0:
break
q = table.select(whereclause=and_(*qargs),
limit=qlimit,
offset=qoffset,
order_by=order_by)
#print q
rows = list(resultiter(engine.execute(q)))
if not len(rows):
return
for row in rows:
yield row
def find(engine, table, _limit=None, _step=5000, _offset=0,
order_by='id', **kw):
table = get_table(engine, table)
_ensure_columns(engine, table, kw)
order_by = [table.c[order_by].asc()]
qargs = []
try:
for col, val in kw.items():
qargs.append(table.c[col]==val)
except KeyError:
return
for i in count():
qoffset = _offset + (_step * i)
qlimit = _step
if _limit is not None:
qlimit = min(_limit-(_step*i), _step)
if qlimit <= 0:
break
q = table.select(whereclause=and_(*qargs), limit=qlimit,
offset=qoffset, order_by=order_by)
#print q
rows = list(resultiter(engine.execute(q)))
if not len(rows):
return
for row in rows:
yield row
def add_row(engine, table, row, ensure=True, types={}):
""" Add a row (type: dict). If ``ensure`` is set, any of
the keys of the row are not table columns, they will be type
guessed and created. """
table = get_table(engine, table)
if ensure:
_ensure_columns(engine, table, row, types=types)
engine.execute(table.insert(row))
def update(engine, table, criteria, values, ensure=True, types={}):
table = get_table(engine, table)
if ensure:
_ensure_columns(engine, table, values, types=types)
q = table.update().values(values)
for column, value in criteria.items():
q = q.where(table.c[column]==value)
engine.execute(q)
def add_row(engine, table, row, ensure=True, types={}):
""" Add a row (type: dict). If ``ensure`` is set, any of
the keys of the row are not table columns, they will be type
guessed and created. """
table = get_table(engine, table)
if ensure:
_ensure_columns(engine, table, row, types=types)
engine.execute(table.insert(row))
def update(engine, table, criteria, values, ensure=True, types={}):
table = get_table(engine, table)
if ensure:
_ensure_columns(engine, table, values, types=types)
q = table.update().values(values)
for column, value in criteria.items():
q = q.where(table.c[column] == value)
engine.execute(q)
def update_row(engine, table, row, unique, ensure=True, types={}):
if not len(unique):
return False
table = get_table(engine, table)
clause = dict([(u, row.get(u)) for u in unique])
if ensure:
_ensure_columns(engine, table, row, types=types)
try:
stmt = table.update(_args_to_clause(table, clause), row)
rp = engine.execute(stmt)
return rp.rowcount > 0
except KeyError, ke:
log.warn("UPDATE: '%s' filter column does not exist: %s", table.name, ke)
return False
def distinct(engine, table, *columns, **kw):
table = get_table(engine, table)
qargs = []
try:
columns = [table.c[c] for c in columns]
for col, val in kw.items():
qargs.append(table.c[col]==val)
except KeyError:
return []
q = expression.select(columns, distinct=True,
whereclause=and_(*qargs),
order_by=[c.asc() for c in columns])
return list(resultiter(engine.execute(q)))
def delete(engine, table, **kw):
table = get_table(engine, table)
_ensure_columns(engine, table, kw)
qargs = []
try:
for col, val in kw.items():
qargs.append(table.c[col]==val)
except KeyError:
return
q = table.delete()
for k, v in kw.items():
q= q.where(table.c[k]==v)
engine.execute(q)
def delete(engine, table, **kw):
table = get_table(engine, table)
_ensure_columns(engine, table, kw)
qargs = []
try:
for col, val in kw.items():
qargs.append(table.c[col] == val)
except KeyError:
return
q = table.delete()
for k, v in kw.items():
q = q.where(table.c[k] == v)
engine.execute(q)
def update_row(engine, table, row, unique, ensure=True, types={}):
if not len(unique):
return False
table = get_table(engine, table)
clause = dict([(u, row.get(u)) for u in unique])
if ensure:
_ensure_columns(engine, table, row, types=types)
try:
stmt = table.update(_args_to_clause(table, clause), row)
rp = engine.execute(stmt)
return rp.rowcount > 0
except KeyError, ke:
log.warn("UPDATE: '%s' filter column does not exist: %s", table.name,
ke)
return False
def distinct(engine, table, *columns, **kw):
table = get_table(engine, table)
qargs = []
try:
columns = [table.c[c] for c in columns]
for col, val in kw.items():
qargs.append(table.c[col] == val)
except KeyError:
return []
q = expression.select(columns,
distinct=True,
whereclause=and_(*qargs),
order_by=[c.asc() for c in columns])
return list(resultiter(engine.execute(q)))
def find_one(engine, table, **kw):
table = get_table(engine, table)
res = list(find(engine, table, _limit=1, **kw))
if not len(res):
return None
return res[0]
def get_table(self, table_name):
return Table(self.engine, get_table(self.engine, table_name))
def find_one(engine, table, **kw):
table = get_table(engine, table)
res = list(find(engine, table, _limit=1, **kw))
if not len(res):
return None
return res[0]
