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 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 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 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: more parameters (which will overwrite default_params)
:return:
"""
timestamp = datetime.utcnow()
format = ("*" * 80) + CR + indent(template, prefix="** ").strip() + CR + ("*" * 80)
Log._annotate(
LogItem(
context=exceptions.ALARM,
format=format,
template=template,
params=dict(default_params, **more_params)
),
timestamp,
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: more parameters (which will overwrite default_params)
:return:
"""
timestamp = datetime.utcnow()
format = (("*" * 80) + CR + indent(template, prefix="** ").strip() +
CR + ("*" * 80))
Log._annotate(
LogItem(
context=exceptions.ALARM,
format=format,
template=template,
params=dict(default_params, **more_params),
),
timestamp,
stack_depth + 1,
)
def __str__(self):
output = ["{"]
for k, v in self.__dict__.items():
value = str(v)
output.append(strings.indent(strings.quote(k) + ":" + value))
output.append("}")
return "\n".join(output)
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 _isolate(separator, list):
try:
if len(list) > 1:
return "(\n" + indent((" " + separator + "\n").join(list)) + "\n)"
else:
return list[0]
except Exception as e:
Log.error("Programming problem: separator={{separator}}, list={{list}",
list=list,
separator=separator,
cause=e)
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 _isolate(separator, list):
try:
if len(list) > 1:
return "(\n" + indent((" " + separator + "\n").join(list)) + "\n)"
else:
return list[0]
except Exception as e:
Log.error("Programming problem: separator={{separator}}, list={{list}",
list=list,
separator=separator,
cause=e
)
def execute_sql(
host,
username,
password,
sql,
schema=None,
param=None,
kwargs=None
):
"""EXECUTE MANY LINES OF SQL (FROM SQLDUMP FILE, MAYBE?"""
kwargs.schema = coalesce(kwargs.schema, kwargs.database)
if param:
with MySQL(kwargs) as temp:
sql = expand_template(sql, quote_param(param))
# We have no way to execute an entire SQL file in bulk, so we
# have to shell out to the commandline client.
args = [
"mysql",
"-h{0}".format(host),
"-u{0}".format(username),
"-p{0}".format(password)
]
if schema:
args.append("{0}".format(schema))
try:
proc = subprocess.Popen(
args,
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
bufsize=-1
)
if is_text(sql):
sql = sql.encode("utf8")
(output, _) = proc.communicate(sql)
except Exception as e:
raise Log.error("Can not call \"mysql\"", e)
if proc.returncode:
if len(sql) > 10000:
sql = "<" + text(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
)
def execute_sql(
host,
username,
password,
sql,
schema=None,
param=None,
kwargs=None
):
"""EXECUTE MANY LINES OF SQL (FROM SQLDUMP FILE, MAYBE?"""
kwargs.schema = coalesce(kwargs.schema, kwargs.database)
if param:
with MySQL(kwargs) as temp:
sql = expand_template(sql, quote_param(param))
# We have no way to execute an entire SQL file in bulk, so we
# have to shell out to the commandline client.
args = [
"mysql",
"-h{0}".format(host),
"-u{0}".format(username),
"-p{0}".format(password)
]
if schema:
args.append("{0}".format(schema))
try:
proc = subprocess.Popen(
args,
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
bufsize=-1
)
if is_text(sql):
sql = sql.encode("utf8")
(output, _) = proc.communicate(sql)
except Exception as e:
raise Log.error("Can not call \"mysql\"", e)
if proc.returncode:
if len(sql) > 10000:
sql = "<" + text_type(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
)
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 __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(text_type(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(text_type(c))
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
return output
def __unicode__(self):
output = self.context + ": " + self.template + CR
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(text_type(c))
except Exception as e:
sys.stderr("Problem serializing cause"+text_type(c))
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
return output
def __unicode__(self):
output = self.context + ": " + self.template + CR
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(text_type(c))
except Exception as e:
sys.stderr("Problem serializing cause" + text_type(c))
output += "caused by\n\t" + "and caused by\n\t".join(cause_strings)
return output
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)
def __init__(
self,
host, # CAN ALSO BE SET TO mysql://username:password@host:optional_port/database_name
username=None,
password=None,
port=3306,
debug=False,
schema=None,
preamble=None,
readonly=False,
kwargs=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 = kwargs
self.cursor = None
self.query_cursor = None
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,
username,
password,
port=3306,
debug=False,
schema=None,
preamble=None,
readonly=False,
kwargs=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 = kwargs
self.cursor = None
self.query_cursor = None
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:
raise 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
@override
def execute_file(filename,
host,
username,
password,
schema=None,
param=None,
ignore_errors=False,
kwargs=None):
# MySQLdb provides no way to execute an entire SQL file in bulk, so we
# have to shell out to the commandline client.
