def test(db, logging=False): cursor = db.cursor() if logging: cursor.log = self.env.log cursor.execute('SELECT 1 AS ' + db.quote(name)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.execute('SELECT %s AS ' + db.quote(name), (42,)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.executemany("UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", []) cursor.executemany("UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", [('42',), ('43',)])
def test(logging=False): with self.env.db_query as db: cursor = db.cursor() if logging: cursor.log = self.env.log cursor.execute('SELECT 1 AS ' + db.quote(name)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.execute('SELECT %s AS ' + db.quote(name), (42, )) self.assertEqual(name, get_column_names(cursor)[0]) stmt = """ UPDATE {0} SET value=%s WHERE 1=(SELECT 0 AS {1}) """.format(db.quote('system'), db.quote(name)) cursor.executemany(stmt, []) cursor.executemany(stmt, [('42', ), ('43', )])
def test_quote(self): with self.env.db_query as db: cursor = db.cursor() cursor.execute('SELECT 1 AS %s' % \ db.quote(r'alpha\`\"\'\\beta``gamma""delta')) self.assertEqual(r'alpha\`\"\'\\beta``gamma""delta', get_column_names(cursor)[0])
def copy_table(db, cursor, table): src_cursor.execute('SELECT * FROM ' + src_db.quote(table)) columns = get_column_names(src_cursor) n_rows = 100 if multirows_insert and max_parameters: n_rows = min(n_rows, int(max_parameters // len(columns))) quoted_table = db.quote(table) holders = '(%s)' % ','.join(['%s'] * len(columns)) count = 0 cursor.execute('DELETE FROM ' + quoted_table) while True: rows = src_cursor.fetchmany(n_rows) if not rows: break count += len(rows) if progress: printfout("%d records\r %s table... ", count, table, newline=False) if replace_cast is not None and table == 'report': rows = replace_report_query(rows, columns, replace_cast) query = 'INSERT INTO %s (%s) VALUES ' % \ (quoted_table, ','.join(map(db.quote, columns))) if multirows_insert: cursor.execute(query + ','.join([holders] * len(rows)), sum(rows, ())) else: cursor.executemany(query + holders, rows) return count
def _verify_table_status(self, db): from trac.db_default import schema tables = [t.name for t in schema] cursor = db.cursor() cursor.execute( "SHOW TABLE STATUS WHERE name IN (%s)" % ','.join( ('%s', ) * len(tables)), tables) cols = get_column_names(cursor) rows = [dict(zip(cols, row)) for row in cursor] engines = [ row['Name'] for row in rows if row['Engine'] in self.UNSUPPORTED_ENGINES ] if engines: raise TracError( _( "All tables must be created as InnoDB or NDB storage engine " "to support transactions. The following tables have been " "created as storage engine which doesn't support " "transactions: %(tables)s", tables=', '.join(engines))) non_utf8bin = [ row['Name'] for row in rows if row['Collation'] not in ('utf8_bin', 'utf8mb4_bin', None) ] if non_utf8bin: raise TracError( _( "All tables must be created with utf8_bin or " "utf8mb4_bin as collation. The following tables " "don't have the collations: %(tables)s", tables=', '.join(non_utf8bin)))
def _get_pie_graph_stats(env, factor, query=None): """ Return a dict in which the keys are the factors and the values are the number of tickets of each factor. Example: >>> _get_pie_graph_stats(env, 'milestone') {'milestone1' : 20, 'milestone2' : 12, } """ sql, args = _get_query_sql(env, query, required_columns=[ factor, ]) db = env.get_db_cnx() cursor = db.cursor() cursor.execute(sql, args) query_columns = get_column_names(cursor) factor_index = query_columns.index(factor) ticket_stats = defaultdict(lambda: 0) for row in cursor: factor_value = row[factor_index] ticket_stats[factor_value] += 1 return ticket_stats
def _get_stacked_bar_chart_stats(env, key, x_axis, query): required_columns = [x_axis] if key is not None: required_columns.append(key) sql, args = _get_query_sql(env, query, required_columns) db = env.get_db_cnx() cursor = db.cursor() cursor.execute(sql, args) query_columns = get_column_names(cursor) # Returning all the keys is kind of an optimization (so that we won't look # for all keys later on) keys = [] x_axis_field_index = query_columns.index(x_axis) # Handle cases in which key is missing. In such cases, we'll form bar charts. if key: key_field_index = query_columns.index(key) # Stacked bar charts are represented by a dict of dicts: # {'x_axis_value' : {'key_value' : number_of_tickets}} # Regular bar charts will be here the same, but 'key_value' will be # replaced with None. ticket_stats = defaultdict(lambda: defaultdict(lambda: 0), {}) for row in cursor: if key: key_field_value = row[key_field_index] else: key_field_value = None x_axis_field_value = row[x_axis_field_index] ticket_stats[x_axis_field_value][key_field_value] += 1 keys.append(key_field_value) return ticket_stats, _unique_list(keys)
def _show_index(self, table): with self.env.db_query as db: cursor = db.cursor() cursor.execute("SHOW INDEX FROM " + db.quote(table)) columns = get_column_names(cursor) rows = [dict(zip(columns, row)) for row in cursor] results = {} for index, group in itertools.groupby(rows, lambda v: v['Key_name']): group = list(group) results[index] = { 'unique': not group[0]['Non_unique'], 'columns': [row['Column_name'] for row in group], } return results
def copy(db): cursor = db.cursor() printout(' %s table... ' % table, newline=False) src_cursor.execute('SELECT * FROM ' + src_db.quote(table)) columns = get_column_names(src_cursor) query = 'INSERT INTO ' + db.quote(table) + \ ' (' + ','.join(db.quote(c) for c in columns) + ')' + \ ' VALUES (' + ','.join(['%s'] * len(columns)) + ')' cursor.execute('DELETE FROM ' + db.quote(table)) count = 0 while True: rows = src_cursor.fetchmany(100) if not rows: break cursor.executemany(query, rows) count += len(rows) printout('%d records.' % count)
def _verify_curr_schema(self): self.assertFalse(self.votes.environment_needs_upgrade(self.db)) cursor = self.db.cursor() cursor.execute('SELECT * FROM votes') cols = get_column_names(cursor) self.assertTrue('resource' not in cols) self.assertEquals([ 'realm', 'resource_id', 'version', 'username', 'vote', 'time', 'changetime' ], cols) cursor.execute(""" SELECT value FROM system WHERE name='vote_version' """) schema_ver = int(cursor.fetchone()[0]) self.assertEquals(self.votes.schema_version, schema_ver)
def drop_column(self, table, column): cursor = pymysql.cursors.Cursor(self.cnx) if column in self.get_column_names(table): quoted_table = self.quote(table) cursor.execute("SHOW INDEX FROM %s" % quoted_table) columns = get_column_names(cursor) keys = {} for row in cursor.fetchall(): row = dict(zip(columns, row)) keys.setdefault(row['Key_name'], []).append(row['Column_name']) # drop all composite indices which in the given column is involved for key, columns in keys.iteritems(): if len(columns) > 1 and column in columns: if key == 'PRIMARY': cursor.execute("ALTER TABLE %s DROP PRIMARY KEY" % quoted_table) else: cursor.execute("ALTER TABLE %s DROP KEY %s" % (quoted_table, self.quote(key))) cursor.execute("ALTER TABLE %s DROP COLUMN %s " % (quoted_table, self.quote(column)))
def _get_all_records(self, env): def primary(row, columns): if len(columns) == 1: return row[columns[0]] else: return tuple(row[col] for col in columns) records = {} with env.db_query as db: cursor = db.cursor() for table in db_default.schema: primary_cols = ','.join(db.quote(col) for col in table.key) query = "SELECT * FROM %s ORDER BY %s" \ % (db.quote(table.name), primary_cols) cursor.execute(query) columns = get_column_names(cursor) rows = {} for row in cursor: row = dict(zip(columns, row)) rows[primary(row, table.key)] = row records[table.name] = rows return records
def execute_paginated_report(self, req, id, sql, args, limit=0, offset=0): """ :param req: `Request` object. :param id: Integer id of the report. :param sql: SQL query that generates the report. :param args: SQL query arguments. :param limit: Maximum number of results to return (optional). :param offset: Offset to start of results (optional). """ sql, args, missing_args = self.sql_sub_vars(sql, args) if not sql: raise TracError(_("Report {%(num)s} has no SQL query.", num=id)) self.log.debug('Report {%d} with SQL "%s"', id, sql) self.log.debug('Request args: %r', req.args) rows = None num_items = 0 order_by = [] limit_offset = None base_sql = sql.replace(SORT_COLUMN, '1').replace(LIMIT_OFFSET, '') with self.env.db_query as db: cursor = db.cursor() if id == self.REPORT_LIST_ID or limit == 0: sql = base_sql else: # The number of tickets is obtained count_sql = 'SELECT COUNT(*) FROM (\n%s\n) AS tab' % base_sql self.log.debug("Report {%d} SQL (count): %s", id, count_sql) try: cursor.execute(count_sql, args) except Exception as e: self.log.warning('Exception caught while executing ' 'Report {%d}: %r, args %r%s', id, count_sql, args, exception_to_unicode(e, traceback=True)) return e, count_sql num_items = cursor.fetchone()[0] # The column names are obtained colnames_sql = 'SELECT * FROM (\n%s\n) AS tab LIMIT 1' \ % base_sql self.log.debug("Report {%d} SQL (col names): %s", id, colnames_sql) try: cursor.execute(colnames_sql, args) except Exception as e: self.log.warning('Exception caught while executing ' 'Report {%d}: args %r%s', id, colnames_sql, args, exception_to_unicode(e, traceback=True)) return e, colnames_sql cols = get_column_names(cursor) # The ORDER BY columns are inserted sort_col = req.args.get('sort', '') asc = req.args.getint('asc', 0, min=0, max=1) self.log.debug("%r %s (%s)", cols, sort_col, '^' if asc else 'v') order_cols = [] if sort_col and sort_col not in cols: raise TracError(_('Query parameter "sort=%(sort_col)s" ' ' is invalid', sort_col=sort_col)) skel = None if '__group__' in cols: order_cols.append('__group__') if sort_col: sort_col = '%s %s' % (db.quote(sort_col), 'ASC' if asc else 'DESC') if SORT_COLUMN in sql: # Method 1: insert sort_col at specified position sql = sql.replace(SORT_COLUMN, sort_col or '1') elif sort_col: # Method 2: automagically insert sort_col (and __group__ # before it, if __group__ was specified) as first criteria if '__group__' in cols: order_by.append('__group__ ASC') order_by.append(sort_col) # is there already an ORDER BY in the original sql? skel = sql_skeleton(sql) before, after = split_sql(sql, _order_by_re, skel) if after: # there were some other criteria, keep them order_by.append(after) sql = ' '.join([before, 'ORDER BY', ', '.join(order_by)]) # Add LIMIT/OFFSET if pagination needed limit_offset = '' if num_items > limit: limit_offset = ' '.join(['LIMIT', str(limit), 'OFFSET', str(offset)]) if LIMIT_OFFSET in sql: # Method 1: insert LIMIT/OFFSET at specified position sql = sql.replace(LIMIT_OFFSET, limit_offset) else: # Method 2: limit/offset is added unless already present skel = skel or sql_skeleton(sql) if 'LIMIT' not in skel.upper(): sql = ' '.join([sql, limit_offset]) self.log.debug("Report {%d} SQL (order + limit): %s", id, sql) try: cursor.execute(sql, args) except Exception as e: self.log.warning('Exception caught while executing Report ' '{%d}: %r, args %r%s', id, sql, args, exception_to_unicode(e, traceback=True)) if order_by or limit_offset: add_notice(req, _("Hint: if the report failed due to" " automatic modification of the ORDER" " BY clause or the addition of" " LIMIT/OFFSET, please look up" " %(sort_column)s and %(limit_offset)s" " in TracReports to see how to gain" " complete control over report" " rewriting.", sort_column=SORT_COLUMN, limit_offset=LIMIT_OFFSET)) return e, sql rows = cursor.fetchall() or [] cols = get_column_names(cursor) return cols, rows, num_items, missing_args, limit_offset