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