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 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.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.