def forall(self, sql, param=None, _execute=None):
assert _execute
num = 0
self._execute_backlog()
try:
old_cursor = self.cursor
if not old_cursor: # ALLOW NON-TRANSACTIONAL READS
self.cursor = self.db.cursor()
if param:
sql = expand_template(sql, quote_param(param))
sql = self.preamble + outdent(sql)
self.debug and Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
columns = tuple(
[utf8_to_unicode(d[0]) for d in self.cursor.description])
for r in self.cursor:
num += 1
_execute(
wrap(dict(zip(columns, [utf8_to_unicode(c) for c in r]))))
if not old_cursor: # CLEANUP AFTER NON-TRANSACTIONAL READS
self.cursor.close()
self.cursor = None
except Exception as e:
Log.error("Problem executing SQL:\n{{sql|indent}}",
sql=sql,
cause=e,
stack_depth=1)
return num
def column_query(self, sql, param=None):
"""
RETURN RESULTS IN [column][row_num] GRID
"""
self._execute_backlog()
try:
old_cursor = self.cursor
if not old_cursor: # ALLOW NON-TRANSACTIONAL READS
self.cursor = self.db.cursor()
self.cursor.execute("SET TIME_ZONE='+00:00'")
self.cursor.close()
self.cursor = self.db.cursor()
if param:
sql = expand_template(sql, quote_param(param))
sql = self.preamble + outdent(sql)
self.debug and Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
grid = [[utf8_to_unicode(c) for c in row] for row in self.cursor]
# columns = [utf8_to_unicode(d[0]) for d in coalesce(self.cursor.description, [])]
result = transpose(*grid)
if not old_cursor: # CLEANUP AFTER NON-TRANSACTIONAL READS
self.cursor.close()
self.cursor = None
return result
except Exception as e:
if isinstance(e, InterfaceError) or e.message.find("InterfaceError") >= 0:
Log.error("Did you close the db connection?", e)
Log.error("Problem executing SQL:\n{{sql|indent}}", sql=sql, cause=e, stack_depth=1)
def forall(self, sql, param=None, _execute=None):
assert _execute
num = 0
self._execute_backlog()
try:
old_cursor = self.cursor
if not old_cursor: # ALLOW NON-TRANSACTIONAL READS
self.cursor = self.db.cursor()
if param:
sql = expand_template(sql, quote_param(param))
sql = self.preamble + outdent(sql)
self.debug and Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
columns = tuple([utf8_to_unicode(d[0]) for d in self.cursor.description])
for r in self.cursor:
num += 1
_execute(wrap(dict(zip(columns, [utf8_to_unicode(c) for c in r]))))
if not old_cursor: # CLEANUP AFTER NON-TRANSACTIONAL READS
self.cursor.close()
self.cursor = None
except Exception as e:
Log.error("Problem executing SQL:\n{{sql|indent}}", sql=sql, cause=e, stack_depth=1)
return num
def query(self, sql, param=None, stream=False, row_tuples=False):
"""
RETURN LIST OF dicts
"""
if not self.cursor: # ALLOW NON-TRANSACTIONAL READS
Log.error("must perform all queries inside a transaction")
self._execute_backlog()
try:
if param:
sql = expand_template(sql, quote_param(param))
sql = self.preamble + outdent(sql)
self.debug and Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
if row_tuples:
if stream:
result = self.cursor
else:
result = wrap(list(self.cursor))
else:
columns = [utf8_to_unicode(d[0]) for d in coalesce(self.cursor.description, [])]
if stream:
result = (wrap({c: utf8_to_unicode(v) for c, v in zip(columns, row)}) for row in self.cursor)
else:
result = wrap([{c: utf8_to_unicode(v) for c, v in zip(columns, row)} for row in self.cursor])
return result
except Exception as e:
e = Except.wrap(e)
if "InterfaceError" in e:
Log.error("Did you close the db connection?", e)
Log.error("Problem executing SQL:\n{{sql|indent}}", sql=sql, cause=e, stack_depth=1)
def column_query(self, sql, param=None):
"""
RETURN RESULTS IN [column][row_num] GRID
"""
self._execute_backlog()
try:
old_cursor = self.cursor
if not old_cursor: # ALLOW NON-TRANSACTIONAL READS
self.cursor = self.db.cursor()
self.cursor.execute("SET TIME_ZONE='+00:00'")
self.cursor.close()
self.cursor = self.db.cursor()
if param:
sql = expand_template(sql, quote_param(param))
sql = self.preamble + outdent(sql)
self.debug and Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
grid = [[utf8_to_unicode(c) for c in row] for row in self.cursor]
# columns = [utf8_to_unicode(d[0]) for d in coalesce(self.cursor.description, [])]
result = transpose(*grid)
if not old_cursor: # CLEANUP AFTER NON-TRANSACTIONAL READS
self.cursor.close()
self.cursor = None
return result
except Exception as e:
if isinstance(e, InterfaceError) or e.message.find("InterfaceError") >= 0:
Log.error("Did you close the db connection?", e)
Log.error("Problem executing SQL:\n{{sql|indent}}", sql=sql, cause=e, stack_depth=1)
def execute(self, sql, param=None):
if self.transaction_level == 0:
Log.error("Expecting transaction to be started before issuing queries")
if param:
sql = expand_template(sql, quote_param(param))
sql = outdent(sql)
self.backlog.append(sql)
if self.debug or len(self.backlog) >= MAX_BATCH_SIZE:
self._execute_backlog()
def execute(self, sql, param=None):
if self.transaction_level == 0:
Log.error("Expecting transaction to be started before issuing queries")
if param:
sql = expand_template(text(sql), quote_param(param))
sql = outdent(sql)
self.backlog.append(sql)
if self.debug or len(self.backlog) >= MAX_BATCH_SIZE:
self._execute_backlog()
def query(self, sql, param=None, stream=False, row_tuples=False):
"""
RETURN A LIST OF dicts
:param sql: SQL TEMPLATE TO SEND
:param param: PARAMETERS TO INJECT INTO SQL TEMPLATE
:param stream: STREAM OUTPUT
:param row_tuples: DO NOT RETURN dicts
"""
if not self.cursor: # ALLOW NON-TRANSACTIONAL READS
Log.error("must perform all queries inside a transaction")
self._execute_backlog()
try:
if isinstance(sql, SQL):
sql = text(sql)
if param:
sql = expand_template(sql, quote_param(param))
sql = self.preamble + outdent(sql)
self.debug and Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
if row_tuples:
if stream:
result = self.cursor
else:
result = wrap(list(self.cursor))
else:
columns = tuple(
utf8_to_unicode(d[0])
for d in coalesce(self.cursor.description, []))
def streamer():
for row in self.cursor:
output = Data()
for c, v in zip(columns, row):
output[c] = v
yield output
if stream:
result = streamer()
else:
result = wrap(streamer())
return result
except Exception as e:
e = Except.wrap(e)
if "InterfaceError" in e:
Log.error("Did you close the db connection?", e)
Log.error("Problem executing SQL:\n{{sql|indent}}",
sql=sql,
cause=e,
stack_depth=1)
def query(self, sql, param=None, stream=False, row_tuples=False):
"""
RETURN LIST OF dicts
"""
if not self.cursor: # ALLOW NON-TRANSACTIONAL READS
Log.error("must perform all queries inside a transaction")
self._execute_backlog()
try:
if param:
sql = expand_template(sql, self.quote_param(param))
sql = self.preamble + outdent(sql)
if self.debug:
Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
if row_tuples:
if stream:
result = self.cursor
else:
result = wrap(list(self.cursor))
else:
columns = [
utf8_to_unicode(d[0])
for d in coalesce(self.cursor.description, [])
]
if stream:
result = (wrap(
{c: utf8_to_unicode(v)
for c, v in zip(columns, row)})
for row in self.cursor)
else:
result = wrap(
[{c: utf8_to_unicode(v)
for c, v in zip(columns, row)}
for row in self.cursor])
return result
except Exception as e:
if isinstance(
e,
InterfaceError) or e.message.find("InterfaceError") >= 0:
Log.error("Did you close the db connection?", e)
Log.error("Problem executing SQL:\n{{sql|indent}}",
sql=sql,
cause=e,
stack_depth=1)
def query(self, sql, param=None):
"""
RETURN LIST OF dicts
"""
self._execute_backlog()
try:
old_cursor = self.cursor
if not old_cursor: # ALLOW NON-TRANSACTIONAL READS
self.cursor = self.db.cursor()
self.cursor.execute("SET TIME_ZONE='+00:00'")
self.cursor.close()
self.cursor = self.db.cursor()
if param:
sql = expand_template(sql, self.quote_param(param))
sql = self.preamble + outdent(sql)
if self.debug:
Log.note("Execute SQL:\n{{sql}}", sql=indent(sql))
self.cursor.execute(sql)
columns = [
utf8_to_unicode(d[0])
for d in coalesce(self.cursor.description, [])
]
fixed = [[utf8_to_unicode(c) for c in row] for row in self.cursor]
result = convert.table2list(columns, fixed)
if not old_cursor: # CLEANUP AFTER NON-TRANSACTIONAL READS
self.cursor.close()
self.cursor = None
return result
except Exception, e:
if isinstance(
e,
InterfaceError) or e.message.find("InterfaceError") >= 0:
Log.error("Did you close the db connection?", e)
Log.error("Problem executing SQL:\n{{sql|indent}}",
sql=sql,
cause=e,
stack_depth=1)