def execute(self, query, args=None): """Executes the given operation Executes the given operation substituting any markers with the given parameters. For example, getting all rows where id is 5: cursor.execute("SELECT * FROM t1 WHERE id = %s", (5,)) :param query: ``str`` sql statement :param args: ``tuple`` or ``list`` of arguments for sql query :returns: ``int``, number of rows that has been produced of affected """ conn = self._get_db() while (yield from self.nextset()): pass if args is not None: query = query % self._escape_args(args, conn) yield from self._query(query) self._executed = query if self._echo: logger.info(query) logger.info("%r", args) return self._rowcount
def executemany(self, query, args): """Execute the given operation multiple times The executemany() method will execute the operation iterating over the list of parameters in seq_params. Example: Inserting 3 new employees and their phone number data = [ ('Jane','555-001'), ('Joe', '555-001'), ('John', '555-003') ] stmt = "INSERT INTO employees (name, phone) VALUES ('%s','%s')" yield from cursor.executemany(stmt, data) INSERT statements are optimized by batching the data, that is using the MySQL multiple rows syntax. :param query: `str`, sql statement :param args: ``tuple`` or ``list`` of arguments for sql query """ if not args: return if self._echo: logger.info("CALL %s", query) logger.info("%r", args) m = RE_INSERT_VALUES.match(query) if m: q_prefix = m.group(1) q_values = m.group(2).rstrip() q_postfix = m.group(3) or "" assert q_values[0] == "(" and q_values[-1] == ")" return ( yield from self._do_execute_many( q_prefix, q_values, q_postfix, args, self.max_stmt_length, self._get_db().encoding ) ) else: rows = 0 for arg in args: yield from self.execute(query, arg) rows += self._rowcount self._rowcount = rows return self._rowcount
def executemany(self, query, args): """Execute the given operation multiple times The executemany() method will execute the operation iterating over the list of parameters in seq_params. Example: Inserting 3 new employees and their phone number data = [ ('Jane','555-001'), ('Joe', '555-001'), ('John', '555-003') ] stmt = "INSERT INTO employees (name, phone) VALUES ('%s','%s')" yield from cursor.executemany(stmt, data) INSERT statements are optimized by batching the data, that is using the MySQL multiple rows syntax. :param query: `str`, sql statement :param args: ``tuple`` or ``list`` of arguments for sql query """ if not args: return if self._echo: logger.info("CALL %s", query) logger.info("%r", args) m = RE_INSERT_VALUES.match(query) if m: q_prefix = m.group(1) q_values = m.group(2).rstrip() q_postfix = m.group(3) or '' assert q_values[0] == '(' and q_values[-1] == ')' return (yield from self._do_execute_many(q_prefix, q_values, q_postfix, args, self.max_stmt_length, self._get_db().encoding)) else: rows = 0 for arg in args: yield from self.execute(query, arg) rows += self._rowcount self._rowcount = rows return self._rowcount
def callproc(self, procname, args=()): """Execute stored procedure procname with args Compatibility warning: PEP-249 specifies that any modified parameters must be returned. This is currently impossible as they are only available by storing them in a server variable and then retrieved by a query. Since stored procedures return zero or more result sets, there is no reliable way to get at OUT or INOUT parameters via callproc. The server variables are named @_procname_n, where procname is the parameter above and n is the position of the parameter (from zero). Once all result sets generated by the procedure have been fetched, you can issue a SELECT @_procname_0, ... query using .execute() to get any OUT or INOUT values. Compatibility warning: The act of calling a stored procedure itself creates an empty result set. This appears after any result sets generated by the procedure. This is non-standard behavior with respect to the DB-API. Be sure to use nextset() to advance through all result sets; otherwise you may get disconnected. :param procname: ``str``, name of procedure to execute on server :param args: `sequence of parameters to use with procedure :returns: the original args. """ conn = self._get_db() if self._echo: logger.info("CALL %s", procname) logger.info("%r", args) for index, arg in enumerate(args): q = "SET @_%s_%d=%s" % (procname, index, conn.escape(arg)) yield from self._query(q) yield from self.nextset() _args = ','.join('@_%s_%d' % (procname, i) for i in range(len(args))) q = "CALL %s(%s)" % (procname, _args) yield from self._query(q) self._executed = q return args
def callproc(self, procname, args=()): """Execute stored procedure procname with args Compatibility warning: PEP-249 specifies that any modified parameters must be returned. This is currently impossible as they are only available by storing them in a server variable and then retrieved by a query. Since stored procedures return zero or more result sets, there is no reliable way to get at OUT or INOUT parameters via callproc. The server variables are named @_procname_n, where procname is the parameter above and n is the position of the parameter (from zero). Once all result sets generated by the procedure have been fetched, you can issue a SELECT @_procname_0, ... query using .execute() to get any OUT or INOUT values. Compatibility warning: The act of calling a stored procedure itself creates an empty result set. This appears after any result sets generated by the procedure. This is non-standard behavior with respect to the DB-API. Be sure to use nextset() to advance through all result sets; otherwise you may get disconnected. :param procname: ``str``, name of procedure to execute on server :param args: `sequence of parameters to use with procedure :returns: the original args. """ conn = self._get_db() if self._echo: logger.info("CALL %s", procname) logger.info("%r", args) for index, arg in enumerate(args): q = "SET @_%s_%d=%s" % (procname, index, conn.escape(arg)) yield from self._query(q) yield from self.nextset() _args = ",".join("@_%s_%d" % (procname, i) for i in range(len(args))) q = "CALL %s(%s)" % (procname, _args) yield from self._query(q) self._executed = q return args