def tests_execute_multi(self): tbl = 'myconnpy_execute_multi' stmt = "SELECT 'result', 1; INSERT INTO {0} () VALUES (); " \ "SELECT * FROM {0}".format(tbl) self.setup_table(self.cnx, tbl) multi_cur = CMySQLCursor(self.cnx) results = [] exp = [ (u"SELECT 'result', 1", [(u'result', 1)]), (u"INSERT INTO {0} () VALUES ()".format(tbl), 1, 1), (u"SELECT * FROM {0}".format(tbl), [(1, None, 0)]), ] for cur in multi_cur.execute(stmt, multi=True): if cur.with_rows: results.append((cur.statement, cur.fetchall())) else: results.append( (cur.statement, cur._affected_rows, cur.lastrowid) ) self.assertEqual(exp, results) cur.close() self.cnx.rollback()
def test_statement(self): cur = CMySQLCursor(self.cnx) exp = 'SELECT * FROM ham' cur._executed = exp self.assertEqual(exp, cur.statement) cur._executed = ' ' + exp + ' ' self.assertEqual(exp, cur.statement) cur._executed = b'SELECT * FROM ham' self.assertEqual(exp, cur.statement)
def tests_execute_multi(self): tbl = 'myconnpy_execute_multi' stmt = "SELECT 'result', 1; INSERT INTO {0} () VALUES (); " \ "SELECT * FROM {0}".format(tbl) self.setup_table(self.cnx, tbl) multi_cur = CMySQLCursor(self.cnx) results = [] exp = [ (u"SELECT 'result', 1", [(u'result', 1)]), (u"INSERT INTO {0} () VALUES ()".format(tbl), 1, 1), (u"SELECT * FROM {0}".format(tbl), [(1, None, 0)]), ] for cur in multi_cur.execute(stmt, multi=True): if cur.with_rows: results.append((cur.statement, cur.fetchall())) else: results.append( (cur.statement, cur._affected_rows, cur.lastrowid) ) self.assertEqual(exp, results) cur.close() self.cnx.rollback() cur = self._get_cursor(self.cnx) cur.execute("DROP PROCEDURE IF EXISTS multi_results") procedure = ( "CREATE PROCEDURE multi_results () " "BEGIN SELECT 1; SELECT 'ham'; END" ) cur.execute(procedure) stmt = "CALL multi_results()" if not PY2: stmt = b"CALL multi_results()" exp_result = [[(1,)], [(u'ham',)]] results = [] for result in cur.execute(stmt, multi=True): if result.with_rows: self.assertEqual(stmt, result._executed) results.append(result.fetchall()) self.assertEqual(exp_result, results) cur.execute("DROP PROCEDURE multi_results") cur.close()
def reset_is_current_flags(cursor: CMySQLCursor, rna_ids: List[str], chunk_size: int = 1000) -> None: """Receives a cursor with an active connection and a list of rna_ids and runs an update resetting any is_current flags to false for all the specified rna ids in groups of chunk_size. Arguments: cursor: Database cursor with an active connection. rna_ids: List of strings with the rna ids where we want to reset the is_current flags to false. chunk_size: Size of the groups in which we will process this update. """ rna_ids_groups = partition(rna_ids, chunk_size) total_rows_affected = 0 for rna_ids_group in rna_ids_groups: in_array = ",".join(["%s"] * len(rna_ids_group)) sql_statement = SQL_MLWH_MARK_ALL_SAMPLES_NOT_MOST_RECENT % ("%s", in_array) cursor.execute(sql_statement, [datetime.now()] + rna_ids_group) total_rows_affected += cursor.rowcount logger.info(f"Reset is_current to false for { total_rows_affected } rows.")
def tests_execute_multi(self): tbl = 'myconnpy_execute_multi' stmt = "SELECT 'result', 1; INSERT INTO {0} () VALUES (); " \ "SELECT * FROM {0}".format(tbl) self.setup_table(self.cnx, tbl) multi_cur = CMySQLCursor(self.cnx) results = [] exp = [ (u"SELECT 'result', 1", [(u'result', 1)]), (u"INSERT INTO {0} () VALUES ()".format(tbl), 1, 1), (u"SELECT * FROM {0}".format(tbl), [(1, None, 0)]), ] for cur in multi_cur.execute(stmt, multi=True): if cur.with_rows: results.append((cur.statement, cur.fetchall())) else: results.append( (cur.statement, cur._affected_rows, cur.lastrowid)) self.assertEqual(exp, results) cur.close() self.cnx.rollback()
def _get_cursor(self, cnx=None): if not cnx: cnx = CMySQLConnection(**self.config) return CMySQLCursor(connection=cnx)
def tests_nextset(self): cur = CMySQLCursor(self.cnx) stmt = "SELECT 'result', 1; SELECT 'result', 2; SELECT 'result', 3" cur.execute(stmt) self.assertEqual([('result', 1)], cur.fetchall()) self.assertTrue(cur.nextset()) self.assertEqual([('result', 2)], cur.fetchall()) self.assertTrue(cur.nextset()) self.assertEqual([('result', 3)], cur.fetchall()) self.assertEqual(None, cur.nextset()) tbl = 'myconnpy_nextset' stmt = "SELECT 'result', 1; INSERT INTO {0} () VALUES (); " \ "SELECT * FROM {0}".format(tbl) self.setup_table(self.cnx, tbl) cur.execute(stmt) self.assertEqual([('result', 1)], cur.fetchall()) try: cur.nextset() except errors.Error as exc: self.assertEqual(errorcode.CR_NO_RESULT_SET, exc.errno) self.assertEqual(1, cur._affected_rows) self.assertTrue(cur.nextset()) self.assertEqual([(1, None, 0)], cur.fetchall()) self.assertEqual(None, cur.nextset()) cur.close() self.cnx.rollback()
def test_with_rows(self): cur = CMySQLCursor(self.cnx) self.assertFalse(cur.with_rows) cur._description = ('ham', 'spam') self.assertTrue(cur.with_rows)