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, 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)
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 = zip(*grid)
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)
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, 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 = 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, 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, 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 = 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, e:
Log.error("Problem executing SQL:\n{{sql|indent}}", sql= sql, cause=e, stack_depth=1)
def alarm(
cls,
template,
default_params={},
stack_depth=0,
log_context=None,
**more_params
):
"""
:param template: *string* human readable string with placeholders for parameters
:param default_params: *dict* parameters to fill in template
:param stack_depth: *int* how many calls you want popped off the stack to report the *true* caller
:param log_context: *dict* extra key:value pairs for your convenience
:param more_params: *any more parameters (which will overwrite default_params)
:return:
"""
# USE replace() AS POOR MAN'S CHILD TEMPLATE
template = ("*" * 80) + "\n" + indent(template, prefix="** ").strip() + "\n" + ("*" * 80)
Log.note(
template,
default_params=default_params,
stack_depth=stack_depth + 1,
log_context=set_default({"context": exceptions.ALARM}, log_context),
**more_params
)
def alarm(
cls,
template,
default_params={},
stack_depth=0,
log_context=None,
**more_params
):
"""
:param template: *string* human readable string with placeholders for parameters
:param default_params: *dict* parameters to fill in template
:param stack_depth: *int* how many calls you want popped off the stack to report the *true* caller
:param log_context: *dict* extra key:value pairs for your convenience
:param more_params: *any more parameters (which will overwrite default_params)
:return:
"""
# USE replace() AS POOR MAN'S CHILD TEMPLATE
template = ("*" * 80) + "\n" + indent(template, prefix="** ").strip() + "\n" + ("*" * 80)
Log.note(
template,
default_params=default_params,
stack_depth=stack_depth + 1,
log_context=set_default({"context": exceptions.ALARM}, log_context),
**more_params
)
def alarm(cls, template, params={}, stack_depth=0, **more_params):
# USE replace() AS POOR MAN'S CHILD TEMPLATE
template = ("*" * 80) + "\n" + indent(
template, prefix="** ").strip() + "\n" + ("*" * 80)
Log.note(template,
params=params,
stack_depth=stack_depth + 1,
**more_params)
def _isolate(separator, list):
try:
if len(list) > 1:
return "(\n" + indent((" " + separator + "\n").join(list)) + "\n)"
else:
return list[0]
except Exception, e:
Log.error("Programming problem: separator={{separator}}, list={{list}",
list=list,
separator=separator,
cause=e)
def _isolate(separator, list):
try:
if len(list) > 1:
return "(\n" + indent((" " + separator + "\n").join(list)) + "\n)"
else:
return list[0]
except Exception, e:
Log.error("Programming problem: separator={{separator}}, list={{list}",
list=list,
separator=separator,
cause=e
)
def __str__(self):
output = self.type + ": " + self.template + "\n"
if self.params:
output = expand_template(output, self.params)
if self.trace:
output += indent(format_trace(self.trace))
if self.cause:
cause_strings = []
for c in listwrap(self.cause):
try:
cause_strings.append(unicode(c))
except Exception, e:
pass
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
def __str__(self):
output = self.type + ": " + self.template + "\n"
if self.params:
output = expand_template(output, self.params)
if self.trace:
output += indent(format_trace(self.trace))
if self.cause:
cause_strings = []
for c in listwrap(self.cause):
try:
cause_strings.append(unicode(c))
except Exception, e:
pass
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
def __unicode__(self):
output = self.type + ": " + self.template + "\n"
if self.params:
output = expand_template(output, self.params)
if self.trace:
output += indent(format_trace(self.trace))
if self.cause:
cause_strings = []
for c in listwrap(self.cause):
with suppress_exception:
cause_strings.append(unicode(c))
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
return output
def __unicode__(self):
output = self.type + ": " + self.template + "\n"
if self.params:
output = expand_template(output, self.params)
if self.trace:
output += indent(format_trace(self.trace))
if self.cause:
cause_strings = []
for c in listwrap(self.cause):
with suppress_exception:
cause_strings.append(unicode(c))
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
return output
def alarm(
cls,
template,
default_params={},
stack_depth=0,
log_context=None,
**more_params
):
# USE replace() AS POOR MAN'S CHILD TEMPLATE
template = ("*" * 80) + "\n" + indent(template, prefix="** ").strip() + "\n" + ("*" * 80)
Log.note(
template,
default_params=default_params,
stack_depth=stack_depth + 1,
log_context=set_default({"context": ALARM}, log_context),
**more_params
)
def query(self, sql, param=None):
"""
RETURN RESULTS IN [row_num][column] 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, 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)
def __init__(
self,
host,
port,
username,
password,
debug=False,
schema=None,
preamble=None,
readonly=False,
settings=None
):
"""
OVERRIDE THE settings.schema WITH THE schema PARAMETER
preamble WILL BE USED TO ADD COMMENTS TO THE BEGINNING OF ALL SQL
THE INTENT IS TO HELP ADMINISTRATORS ID THE SQL RUNNING ON THE DATABASE
schema - NAME OF DEFAULT database/schema IN QUERIES
preamble - A COMMENT TO BE ADDED TO EVERY SQL STATEMENT SENT
readonly - USED ONLY TO INDICATE IF A TRANSACTION WILL BE OPENED UPON
USE IN with CLAUSE, YOU CAN STILL SEND UPDATES, BUT MUST OPEN A
TRANSACTION BEFORE YOU DO
"""
all_db.append(self)
self.settings = settings
if preamble == None:
self.preamble = ""
else:
self.preamble = indent(preamble, "# ").strip() + "\n"
self.readonly = readonly
self.debug = coalesce(debug, DEBUG)
if host:
self._open()
def __init__(self,
host,
port,
username,
password,
debug=False,
schema=None,
preamble=None,
readonly=False,
settings=None):
"""
OVERRIDE THE settings.schema WITH THE schema PARAMETER
preamble WILL BE USED TO ADD COMMENTS TO THE BEGINNING OF ALL SQL
THE INTENT IS TO HELP ADMINISTRATORS ID THE SQL RUNNING ON THE DATABASE
schema - NAME OF DEFAULT database/schema IN QUERIES
preamble - A COMMENT TO BE ADDED TO EVERY SQL STATEMENT SENT
readonly - USED ONLY TO INDICATE IF A TRANSACTION WILL BE OPENED UPON
USE IN with CLAUSE, YOU CAN STILL SEND UPDATES, BUT MUST OPEN A
TRANSACTION BEFORE YOU DO
"""
all_db.append(self)
self.settings = settings
if preamble == None:
self.preamble = ""
else:
self.preamble = indent(preamble, "# ").strip() + "\n"
self.readonly = readonly
self.debug = coalesce(debug, DEBUG)
if host:
self._open()
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
bufsize=-1)
if isinstance(sql, unicode):
sql = sql.encode("utf8")
(output, _) = proc.communicate(sql)
except Exception, e:
Log.error("Can not call \"mysql\"", e)
if proc.returncode:
if len(sql) > 10000:
sql = "<" + unicode(len(sql)) + " bytes of sql>"
Log.error(
"Unable to execute sql: return code {{return_code}}, {{output}}:\n {{sql}}\n",
sql=indent(sql),
return_code=proc.returncode,
output=output)
@staticmethod
def execute_file(filename,
host,
username,
password,
schema=None,
param=None,
ignore_errors=False,
settings=None):
# MySQLdb provides no way to execute an entire SQL file in bulk, so we
# have to shell out to the commandline client.
sql = File(filename).read()
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
bufsize=-1
)
if isinstance(sql, unicode):
sql = sql.encode("utf8")
(output, _) = proc.communicate(sql)
except Exception, e:
Log.error("Can not call \"mysql\"", e)
if proc.returncode:
if len(sql) > 10000:
sql = "<" + unicode(len(sql)) + " bytes of sql>"
Log.error("Unable to execute sql: return code {{return_code}}, {{output}}:\n {{sql}}\n",
sql= indent(sql),
return_code= proc.returncode,
output= output
)
@staticmethod
def execute_file(
filename,
host,
username,
password,
schema=None,
param=None,
ignore_errors=False,
settings=None
):
def alarm(cls, template, params={}, stack_depth=0, **more_params):
# USE replace() AS POOR MAN'S CHILD TEMPLATE
template = ("*" * 80) + "\n" + indent(template, prefix="** ").strip() + "\n" + ("*" * 80)
Log.note(template, params=params, stack_depth=stack_depth + 1, **more_params)
