def _str_query(self, sql):
# Check if Pygments is available for coloring
try:
import pygments
from pygments.lexers import SqlLexer
from pygments.formatters import TerminalTrueColorFormatter
except ImportError:
pygments = None
# Check if sqlparse is available for indentation
try:
import sqlparse
except ImportError:
sqlparse = None
# Remove leading and trailing whitespaces
if sqlparse:
# Indent the SQL query
sql = sqlparse.format(sql, reindent=True)
if pygments:
# Highlight the SQL query
sql = pygments.highlight(
sql,
SqlLexer(),
#TerminalTrueColorFormatter(style='monokai')
TerminalTrueColorFormatter(style='monokai'))
return sql
def _str_query(sql, params):
def escape(v):
if type(v) in [
uuid.UUID,
str,
]:
return "'%s'" % v
return v
if type(params) == dict:
params_ex = {k: escape(v) for k, v in params.items()}
sql = sql % params_ex
# Borrowed by morlandi from sant527
# See: https://github.com/bradmontgomery/django-querycount/issues/22
if prettify:
sql = sqlparse.format(sql, reindent=reindent)
if colorize and pygments:
# Highlight the SQL query
sql = pygments.highlight(
sql, SqlLexer(),
TerminalTrueColorFormatter(style='monokai')
#TerminalTrueColorFormatter()
)
return sql
def format_sql(query, args):
if not HAVE_PYGMENTS:
return decode_text(query)
return Markup(
highlight(query, SqlLexer(),
HtmlFormatter(noclasses=True, style=PYGMENT_STYLE)))
def pp_sql_query_any(qs):
from django.db import connections
# Get a cursor tied to the database of queryset
cursor = connections[qs.db].cursor()
# Get the query SQL and parameters to be passed into psycopg2
query, params = qs.query.sql_with_params()
# Execute the sql
cursor.execute('EXPLAIN ' + query, params)
# then get the last executed sql query
sql = str(cursor.db.ops.last_executed_query(cursor, query, params))
# Just for confirmation
assert sql.startswith('EXPLAIN ')
# Then format it using sqlparser and color it using pygment
import sqlparse
import pygments
from pygments.lexers import SqlLexer
from pygments.formatters import TerminalTrueColorFormatter
# format using sqlparser
sql = sqlparse.format(sql, reindent=True)
# color it using pygments
sql = pygments.highlight(sql,SqlLexer(),TerminalTrueColorFormatter())
#print it
return sql
def format_sql(query, args):
if not HAVE_PYGMENTS:
return query
return highlight(
query, SqlLexer(encoding='utf-8'),
HtmlFormatter(encoding='utf-8', noclasses=True, style=PYGMENT_STYLE))
def short_text(self, query):
if pygments:
return highlight(
query.text[:50], SqlLexer(),
HtmlFormatter(style=VisualStudioStyle, noclasses=True))
else:
return query.text[:50]
def format(self, record):
# Check if Pygments is available for coloring
try:
import pygments
from pygments.lexers import SqlLexer
from pygments.formatters import TerminalTrueColorFormatter
except ImportError:
pygments = None
# Check if sqlparse is available for indentation
try:
import sqlparse
except ImportError:
sqlparse = None
# Remove leading and trailing whitespaces
sql = record.sql.strip()
if sqlparse:
# Indent the SQL query
sql = sqlparse.format(sql, reindent=True)
if pygments:
# Highlight the SQL query
sql = pygments.highlight(
sql,
SqlLexer(),
TerminalTrueColorFormatter(style='monokai')
)
# Set the record's statement to the formatted query
record.statement = sql
return super(SQLFormatter, self).format(record)
def _str_query(self,sql):
# Borrowed by morlandi from sant527
# See: https://github.com/bradmontgomery/django-querycount/issues/22
# Check if Pygments is available for coloring
try:
import pygments
from pygments.lexers import SqlLexer
from pygments.formatters import TerminalTrueColorFormatter
except ImportError:
pygments = None
# Check if sqlparse is available for indentation
try:
import sqlparse
except ImportError:
sqlparse = None
# Remove leading and trailing whitespaces
if sqlparse:
# Indent the SQL query
sql = sqlparse.format(sql, reindent=True)
if pygments:
# Highlight the SQL query
sql = pygments.highlight(
sql,
SqlLexer(),
TerminalTrueColorFormatter(style=QC_SETTINGS['COLOR_FORMATTER_STYLE'])
#TerminalTrueColorFormatter()
)
return sql
def dump_query(query, filter_columns=None):
"""
Small helper function to dump a SqlAlchemy SQL query + parameters.
:param query:
:param filter_columns:
:return:
"""
# Number of rows to display.
num_rows = 5
if hasattr(query, "statement"):
sql_query = str(
query.statement.compile(dialect=None,
compile_kwargs={"literal_binds": True}))
else:
sql_query = str(query)
formatted_sql_query = sqlparse.format(sql_query,
reindent=True,
keyword_case="upper")
highlighted_sql_query = highlight(formatted_sql_query, SqlLexer(),
TerminalFormatter())
print("Query:")
print(f"{'-' * 15}\n{highlighted_sql_query}{'-' * 15}")
data_frame = pd.read_sql(query.statement, CVE_DB_ENGINE)
if filter_columns:
data_frame = data_frame[filter_columns]
print(f"Results: {data_frame.shape[0]}; showing first {num_rows}:")
print(data_frame.head(num_rows))
def format_query(sql):
# Check if Pygments is available for coloring
try:
import pygments
from pygments.lexers import SqlLexer
from pygments.formatters import TerminalTrueColorFormatter
except ImportError:
pygments = None
# Check if sqlparse is available for indentation
try:
import sqlparse
except ImportError:
sqlparse = None
# Remove leading and trailing whitespaces
if sqlparse:
# Indent the SQL query
sql = sqlparse.format(sql, reindent=True)
if pygments:
# Highlight the SQL query
sql = pygments.highlight(
sql, SqlLexer(),
TerminalTrueColorFormatter(
style=ACTUAL_QUERYCOUNT_SETTINGS['COLOR_FORMATTER_STYLE'])
#TerminalTrueColorFormatter()
)
return sql
def dump_query(query, display_columns=None):
"""Small helper function to dump a SqlAlchemy SQL query + parameters.
:param query:
:return:
"""
# Number of rows to display.
num_rows = 5
if hasattr(query, 'statement'):
sql_query = str(
query.statement.compile(dialect=None,
compile_kwargs={'literal_binds': True}))
else:
sql_query = str(query)
formatted_sql_query = sqlparse.format(sql_query,
reindent=True,
keyword_case='upper')
highlighted_sql_query = highlight(formatted_sql_query, SqlLexer(),
TerminalFormatter())
print('Query:')
print('-' * 15 + '\n%s' % highlighted_sql_query + '-' * 15)
df = pd.read_sql(query.statement, CVE_DB_ENGINE)
if display_columns:
df = df[display_columns]
print('Results: {}; showing first {}:'.format(df.shape[0], num_rows))
print(df.head(num_rows))
def format_sql(query):
if not query:
return ""
sql_lexer = SqlLexer()
html_formatter = HtmlFormatter()
reindent = len(query) > 80
query = sqlparse.format(query, reindent=reindent, keyword_case='upper')
return highlight(query, sql_lexer, html_formatter)
def filter(self, record):
sql = (
# SQLAlchemy
getattr(record, "sql_statement", None)
# Django
or getattr(record, "sql", None)
or ""
)
if sql:
if self.multiline_queries:
sql = sqlparse.format(sql, reindent=True, keyword_case="upper").strip()
else:
sql = " ".join(
l.strip()
for l in sqlparse.format(
sql, reindent=True, keyword_case="upper"
).splitlines()
).strip()
if sql and not sql.endswith(";"):
sql = sql + ";"
if hasattr(record, "sql_parameters"):
params_dict = record.sql_parameters
params = json.dumps(record.sql_parameters, cls=JsonEncoder).strip()
else:
params_dict = {}
params = ""
if self.colorize_queries:
if sql:
sql = pygments.highlight(
sql, SqlLexer(), Terminal256Formatter(style="monokai")
).strip()
if params:
params = pygments.highlight(
params,
pygments.lexers.get_lexer_for_mimetype("application/json"),
Terminal256Formatter(style="monokai"),
).strip()
if params and params_dict:
record.sql = "{} with params {}".format(sql, params)
else:
record.sql = sql or "SQL"
duration = getattr(record, "sql_duration_ms", None) or getattr(
record, "duration", None
)
if duration:
record.sql_duration = "{:.2f} ms".format(duration)
else:
record.sql_duration = "_.___ ms"
return super().filter(record)
def highlight_sql(sql):
if USE_PYGMENTS:
sql = mark_safe(
highlight(
textwrap.fill(sql, WRAP), SqlLexer(encoding='utf-8'),
HtmlFormatter(encoding='utf-8', noclasses=True, style=STYLE)))
else:
sql = reformat_sql(sql)
return sql
def format_sql(query):
"""Format the given SQL query."""
if not HAVE_PYGMENTS: # pragma: no cover
return query
return highlight(
query, SqlLexer(encoding='utf-8'),
HtmlFormatter(encoding='utf-8', noclasses=True,
style=SQL_STYLE)).decode('utf-8')
def orm_debug():
"""
Django template tag for displaying SQL queries generated with ORM.
You need jquery framework if you want use EXPLAIN feature.
"""
try:
from pygments import highlight
from pygments.lexers import SqlLexer
from pygments.formatters import HtmlFormatter
pygments_installed = True
except ImportError:
pygments_installed = False
from django.db import connection
queries = connection.queries
query_time = 0
query_count = 0
for count, query in enumerate(queries):
query['count'] = count
query_time += float(query['time'])
query_count += int(1)
query['sql'] = query['sql'] #.decode('utf-8')
query['original_sql'] = query['sql']
# Following actions make SQL qeries more pretty looking
parts = re.split(
'.(?:(?=FROM)|(?=WHERE)|(?=INNER)|(?=ON)|(?=ORDER BY))',
query['sql'])
for x in xrange(len(parts)):
parts[x] = re.sub(r'^FROM ', 'FROM ', parts[x])
parts[x] = re.sub(r'^WHERE ', 'WHERE ', parts[x])
parts[x] = re.sub(r'^INNER ', 'INNER ', parts[x])
parts[x] = re.sub(r'^ON ', 'ON ', parts[x])
parts[x] = re.sub(r'^ORDER BY ', 'ORDER BY ', parts[x])
parts[x] = re.sub(r'( OR | AND )', r'\n \1', parts[x])
parts[x] = re.sub(r',', r',\n ', parts[x])
query['sql'] = '\n'.join(parts)
if pygments_installed:
formatter = HtmlFormatter()
query['sql'] = highlight(query['sql'], SqlLexer(), formatter)
pygments_css = formatter.get_style_defs()
else:
pygments_css = ''
return {
'pygments_css': pygments_css,
'pygments_installed': pygments_installed,
'query_time': query_time,
'query_count': query_count,
'queries': queries
}
def pp_sql_sql(sql):
import sqlparse
import pygments
from pygments.lexers import SqlLexer
from pygments.formatters import TerminalTrueColorFormatter
# format using sqlparser
sql = sqlparse.format(sql, reindent=True)
# color it using pygments
sql = pygments.highlight(sql,SqlLexer(),TerminalTrueColorFormatter(style='monokai'))
return sql
def data_set(flask_report, id_):
flask_report.try_edit_data_set()
data_set = DataSet(flask_report, id_)
SQL_html = highlight(query_to_sql(data_set.query), SqlLexer(),
HtmlFormatter())
params = dict(data_set=data_set, SQL=SQL_html)
extra_params = flask_report.data_set_template_param(data_set)
if extra_params:
params.update(extra_params)
return render_template("report____/data-set.html", **params)
def format_sql(query):
if not HAVE_PYGMENTS: # pragma: no cover
return text_(query)
return text_(
highlight(
query, SqlLexer(encoding='utf-8'),
HtmlFormatter(encoding='utf-8',
noclasses=True,
style=PYGMENT_STYLE)))
def format_sql(query, args):
if HAVE_SQLPARSE:
query = sqlparse.format(query, reindent=True, keyword_case="upper")
if not HAVE_PYGMENTS:
return decode_text(query)
return Markup(
highlight(query, SqlLexer(),
HtmlFormatter(noclasses=True, style=PYGMENT_STYLE)))
def report(flask_report, id_=None):
flask_report.try_view_report()
if id_ is not None:
report = Report(flask_report, id_)
code = report.raw_filter_condition
SQL_html = highlight(query_to_sql(report.query), SqlLexer(),
HtmlFormatter())
params = dict(report=report, SQL=SQL_html)
if code is not None:
customized_filter_condition = highlight(code, PythonLexer(),
HtmlFormatter())
params['customized_filter_condition'] = \
customized_filter_condition
extra_params = flask_report.report_template_param(report)
if extra_params:
params.update(extra_params)
return report.html_template.render(**params)
else: # create report
form = _ReportForm(flask_report, request.form)
if form.validate():
name = form.name.data
id = None
if request.args.get('preview'):
name += '(' + _('Preview') + ')'
id = 0
filter_map = {}
for filter_ in json.loads(form.filters.data):
filter_map.setdefault(filter_['col'], []).append({
'operator':
filter_['op'],
'value':
filter_['val'],
'synthetic':
filter_['synthetic']
})
report_id = create_report(form.data_set,
name=name,
creator=form.creator.data,
description=form.description.data,
id=id,
columns=form.columns.data,
filters=filter_map)
return jsonify({
'id': report_id,
'name': form.name.data,
'url': url_for('.report', id_=report_id)
})
else:
return jsonify({'errors': form.errors}), 403
def display_sql(sql_code):
"""
Formatting for show queries.
"""
formatted_sql_code = sqlparse.format(sql_code,
reindent=False,
keyword_case='upper',
identifier_case='upper')
highlighted_formatted_sql_code = highlight(
formatted_sql_code, SqlLexer(),
HtmlFormatter(noclasses=True, style=YourStyle))
display(Markdown(highlighted_formatted_sql_code))
def __init__(self, *args, **kwargs):
self.highlight = kwargs.pop('highlight', True)
self.style = kwargs.pop('style', 'default')
self.parse = kwargs.pop('parse', True)
self.reindent = kwargs.pop('reindent', True)
self.keyword_case = kwargs.pop('keyword_case', 'upper')
self._lexer = SqlLexer()
self._formatter = Terminal256Formatter(style=self.style)
super(SqlFormatter, self).__init__(*args, **kwargs)
def __init__(self):
gtk.TextView.__init__(self)
self.buf = buf = self.get_buffer()
self.hl_style = None
self._generated_styles = set()
self.set_style(ColorfulStyle)
self.set_lexer(SqlLexer())
self._changehandler = buf.connect_after('changed', self._on_change)
self.modify_font(pango.FontDescription('monospace'))
def _str_query(sql):
# Borrowed by morlandi from sant527
# See: https://github.com/bradmontgomery/django-querycount/issues/22
if prettify:
sql = sqlparse.format(sql, reindent=True)
if colorize and pygments:
# Highlight the SQL query
sql = pygments.highlight(
sql, SqlLexer(),
TerminalTrueColorFormatter(style='monokai')
#TerminalTrueColorFormatter()
)
return sql
def test_sqlformatter(self):
sqlformatter = SqlFormatter().format(self.model)
sql_compare = 'SELECT "django_migrations"."app","django_migrations"."name" FROM "django_migrations"'
sql_compare = sqlparse.format(sql_compare, reindent=True, keyword_case='upper')
sql_compare = "(0.005ms) {0}".format(sql_compare)
sql_compare = highlight(
sql_compare,
SqlLexer(),
Terminal256Formatter(style='default')
)
self.assertEqual(sql_compare, sqlformatter)
def format(self, record):
# 줄끝 공백 제거
sql = record.sql.strip()
# 쿼리 정렬
sql = sqlparse.format(sql, reindent=True, comma_first=True)
# Highlight
# pygments.STYLE_MAP 참조
sql = pygments.highlight(sql, SqlLexer(),
TerminalTrueColorFormatter(style='manni'))
record.statement = sql
return super(SQLFormatter, self).format(record)
def _get_lexer(codesyntax):
if codesyntax in ('cpp', 'javascript'):
return JavascriptLexer()
elif codesyntax == 'python':
return PythonLexer()
elif codesyntax == 'xml' or codesyntax == 'html':
return HtmlLexer()
elif codesyntax == 'css':
return CssLexer()
elif codesyntax == 'sql':
return SqlLexer()
elif codesyntax:
raise NotImplementedError(codesyntax)
else:
return TextLexer()
def reformat_sql(sql):
sql = sql.replace('`,`', '`, `')
sql = sql.replace('SELECT ', 'SELECT\n\t')
sql = sql.replace('` FROM ', '`\nFROM\n\t')
sql = sql.replace(' WHERE ', '\nWHERE\n\t')
sql = sql.replace(' INNER JOIN ', '\nINNER JOIN\n\t')
sql = sql.replace(' OUTER JOIN ', '\nOUTER JOIN\n\t')
sql = sql.replace(' ORDER BY ', '\nORDER BY\n\t')
# Use Pygments to highlight SQL if it's available
try:
from pygments import highlight
from pygments.lexers import SqlLexer
from pygments.formatters import HtmlFormatter
sql = highlight(sql, SqlLexer(), HtmlFormatter())
except ImportError:
pass
return sql
def highlight(text):
# Generated HTML contains unnecessary newline at the end
# before </pre> closing tag.
# We need to remove that newline because it's screwing up
# QTextEdit formatting and is being displayed
# as a non-editable whitespace.
highlighted_text = _highlight(text, SqlLexer(), HtmlFormatter()).strip()
# Split generated HTML by last newline in it
# argument 1 indicates that we only want to split the string
# by one specified delimiter from the right.
parts = highlighted_text.rsplit("\n", 1)
# Glue back 2 split parts to get the HTML without last
# unnecessary newline
highlighted_text_no_last_newline = "".join(parts)
return highlighted_text_no_last_newline
