def test_issue40(self):
# make sure identifier lists in subselects are grouped
p = sqlparse.parse(('SELECT id, name FROM '
'(SELECT id, name FROM bar) as foo'))[0]
self.assertEqual(len(p.tokens), 7)
self.assertEqual(p.tokens[2].__class__, sql.IdentifierList)
self.assertEqual(p.tokens[-1].__class__, sql.Identifier)
self.assertEqual(p.tokens[-1].get_name(), u'foo')
sp = p.tokens[-1].tokens[0]
self.assertEqual(sp.tokens[3].__class__, sql.IdentifierList)
# make sure that formatting works as expected
self.ndiffAssertEqual(
sqlparse.format(('SELECT id, name FROM '
'(SELECT id, name FROM bar)'),
reindent=True),
('SELECT id,\n'
' name\n'
'FROM\n'
' (SELECT id,\n'
' name\n'
' FROM bar)'))
self.ndiffAssertEqual(
sqlparse.format(('SELECT id, name FROM '
'(SELECT id, name FROM bar) as foo'),
reindent=True),
('SELECT id,\n'
' name\n'
'FROM\n'
' (SELECT id,\n'
' name\n'
' FROM bar) as foo'))
def new_linelist_from_gfall(new_dbname, select_atom=None):
print "Reading lines from Kurucz gfall"
conn = sqlite3.connect(new_dbname)
conn.create_function('pow', 2, math.pow)
conn.create_function('convert_air2vacuum', 1, convert_air2vacuum)
curs = conn.cursor()
curs.execute(sql_stmts.linelist_create_stmt)
if select_atom is None:
elem_select_stmt = ""
else:
elem_select_stmt = " and elem in (%s)" % (','.join(map(str, select_atom)),)
insert_fromgfall_stmt = sql_stmts.linelist_insert_stmt + sql_stmts.linelist_select_stmt % {'hc':hc, 'where_stmt':elem_select_stmt}
if sqlparse_available:
print sqlparse.format(insert_fromgfall_stmt, reindent=True)
else:
print insert_fromgfall_stmt
curs.execute(insert_fromgfall_stmt)
conn.commit()
print "%d lines in database" % (conn.execute('select count(atom) from lines').fetchone()[0])
print "updating oscillator strengths"
conn.execute(update_oscillator_stmt)
conn.commit()
return conn
def _printSql(self, q): if self.print_sqls: self._printLine() if HAS_SQLPARSE: print sqlparse.format(q, reindent=True, keyword_case='upper') else: print q
def do_query_tag_report(query_tag, i, days, start, end, no_show_sql):
first = True
print('\nSlow report for tag #{} {}\n'.format(i+1,query_tag))
for file in gfiles(days, start, end):
with open(file, 'r') as f:
date, time = get_report_date(f)
qdata, sql = head_match(f, query_tag, 70)
hdata = top_head(f, 70)
if first:
first = False
if not no_show_sql:
try:
print(sqlparse.format('\n'.join(sql), reindent=True, keyword_case='upper'))
except IndexError, e:
print(sqlparse.format('\n'.join(sql), keyword_case='upper'))
print('')
print('{0:10} {1:8} {2:>10} {3:>9} {4:>9} {5:>12} {6:>11s} '
'{7:>6s} {8:>12s} {9:>11s} {10:11s}'.format(
'Date',
'Time',
'Count',
'Exec_Time',
'Lock_Time',
'QAve_Resp',
'%Qexec_time', #6
'%Count',
'Tot_Q_Count',
'Tot_time',
'Tot_ave_resp'
))
format_query_tag(date, time, qdata, hdata)
def test_issue40():
# make sure identifier lists in subselects are grouped
p = sqlparse.parse(('SELECT id, name FROM '
'(SELECT id, name FROM bar) as foo'))[0]
assert len(p.tokens) == 7
assert p.tokens[2].__class__ == sql.IdentifierList
assert p.tokens[-1].__class__ == sql.Identifier
assert p.tokens[-1].get_name() == 'foo'
sp = p.tokens[-1].tokens[0]
assert sp.tokens[3].__class__ == sql.IdentifierList
# make sure that formatting works as expected
s = sqlparse.format('SELECT id == name FROM '
'(SELECT id, name FROM bar)', reindent=True)
assert s == '\n'.join([
'SELECT id == name',
'FROM',
' (SELECT id,',
' name',
' FROM bar)'])
s = sqlparse.format('SELECT id == name FROM '
'(SELECT id, name FROM bar) as foo', reindent=True)
assert s == '\n'.join([
'SELECT id == name',
'FROM',
' (SELECT id,',
' name',
' FROM bar) as foo'])
def test_php(self):
sql = 'select * from foo;'
f = lambda sql: sqlparse.format(sql, output_format='php')
self.ndiffAssertEqual(f(sql), '$sql = "select * from foo;";')
f = lambda sql: sqlparse.format(sql, output_format='php',
reindent=True)
self.ndiffAssertEqual(f(sql), ('$sql = "select * ";\n'
'$sql .= "from foo;";'))
def test_keywordcase(self):
sql = 'select * from bar; -- select foo\n'
res = sqlparse.format(sql, keyword_case='upper')
assert res == 'SELECT * FROM bar; -- select foo\n'
res = sqlparse.format(sql, keyword_case='capitalize')
assert res == 'Select * From bar; -- select foo\n'
res = sqlparse.format(sql.upper(), keyword_case='lower')
assert res == 'select * from BAR; -- SELECT FOO\n'
def test_python(self):
sql = 'select * from foo;'
f = lambda sql: sqlparse.format(sql, output_format='python')
self.ndiffAssertEqual(f(sql), "sql = 'select * from foo;'")
f = lambda sql: sqlparse.format(sql, output_format='python',
reindent=True)
self.ndiffAssertEqual(f(sql), ("sql = ('select * '\n"
" 'from foo;')"))
def test_identifiercase(self):
sql = 'select * from bar; -- select foo\n'
res = sqlparse.format(sql, identifier_case='upper')
assert res == 'select * from BAR; -- select foo\n'
res = sqlparse.format(sql, identifier_case='capitalize')
assert res == 'select * from Bar; -- select foo\n'
res = sqlparse.format(sql.upper(), identifier_case='lower')
assert res == 'SELECT * FROM bar; -- SELECT FOO\n'
def test_php(self):
sql = 'select * from foo;'
f = lambda sql: sqlparse.format(sql, output_format='php')
assert f(sql) == '$sql = "select * from foo;";'
f = lambda sql: sqlparse.format(sql, output_format='php',
reindent=True)
assert f(sql) == '\n'.join([
'$sql = "select * ";',
'$sql .= "from foo;";'])
def test_python(self):
sql = 'select * from foo;'
f = lambda sql: sqlparse.format(sql, output_format='python')
assert f(sql) == "sql = 'select * from foo;'"
f = lambda sql: sqlparse.format(sql, output_format='python',
reindent=True)
assert f(sql) == '\n'.join([
"sql = ('select * '",
" 'from foo;')"])
def print_sql(show_queries=True):
print "Total Run Time: %s"%(datetime.now()-start_time)
print "Total Postgres Time: %s"%sum([float(query['time']) for query in con.queries[query_count:]])
print "Queries: %s"%(len(con.queries)-query_count)
if show_queries:
for query in con.queries[query_count:]:
print "\nPostgres Time: %s"%query['time']
print sqlparse.format(query['sql'], reindent=True, keyword_case='upper')
def test_keywordcase(self):
sql = 'select * from bar; -- select foo\n'
res = sqlparse.format(sql, keyword_case='upper')
self.ndiffAssertEqual(res, 'SELECT * FROM bar; -- select foo\n')
res = sqlparse.format(sql, keyword_case='capitalize')
self.ndiffAssertEqual(res, 'Select * From bar; -- select foo\n')
res = sqlparse.format(sql.upper(), keyword_case='lower')
self.ndiffAssertEqual(res, 'select * from BAR; -- SELECT FOO\n')
self.assertRaises(SQLParseError, sqlparse.format, sql,
keyword_case='foo')
def print_sql(qs):
q = qs.query.as_sql()
statement = q[0] % q[1]
try:
import sqlparse
print sqlparse.format(statement, reindent=True, keyword_case='upper')
except ImportError:
import warnings
warnings.warn("sqlparse not installed")
print statement
def test_strip_comments_single(self):
sql = 'select *-- statement starts here\nfrom foo'
res = sqlparse.format(sql, strip_comments=True)
assert res == 'select * from foo'
sql = 'select * -- statement starts here\nfrom foo'
res = sqlparse.format(sql, strip_comments=True)
assert res == 'select * from foo'
sql = 'select-- foo\nfrom -- bar\nwhere'
res = sqlparse.format(sql, strip_comments=True)
assert res == 'select from where'
def execute(self, sql, params=()):
try:
return self.cursor.execute(sql, params)
finally:
raw_sql = self.db.ops.last_executed_query(self.cursor, sql, params)
if sqlparse:
print sqlparse.format(raw_sql, reindent=True)
else:
print raw_sql
print
def test_strip_comments_single(self):
sql = 'select *-- statement starts here\nfrom foo'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select * from foo')
sql = 'select * -- statement starts here\nfrom foo'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select * from foo')
sql = 'select-- foo\nfrom -- bar\nwhere'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select from where')
self.assertRaises(SQLParseError, sqlparse.format, sql,
strip_comments=None)
def test_identifiercase(self):
sql = 'select * from bar; -- select foo\n'
res = sqlparse.format(sql, identifier_case='upper')
self.ndiffAssertEqual(res, 'select * from BAR; -- select foo\n')
res = sqlparse.format(sql, identifier_case='capitalize')
self.ndiffAssertEqual(res, 'select * from Bar; -- select foo\n')
res = sqlparse.format(sql.upper(), identifier_case='lower')
self.ndiffAssertEqual(res, 'SELECT * FROM bar; -- SELECT FOO\n')
self.assertRaises(SQLParseError, sqlparse.format, sql,
identifier_case='foo')
sql = 'select * from "foo"."bar"'
res = sqlparse.format(sql, identifier_case="upper")
self.ndiffAssertEqual(res, 'select * from "foo"."bar"')
def test_strip_comments_multi(self):
sql = '/* sql starts here */\nselect'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select')
sql = '/* sql starts here */ select'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select')
sql = '/*\n * sql starts here\n */\nselect'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select')
sql = 'select (/* sql starts here */ select 2)'
res = sqlparse.format(sql, strip_comments=True)
self.ndiffAssertEqual(res, 'select (select 2)')
def execute(self, sql, params=()):
starttime = time.time()
try:
return self.cursor.execute(sql, params)
finally:
raw_sql = self.db.ops.last_executed_query(self.cursor, sql, params)
execution_time = time.time() - starttime
if sqlparse:
print sqlparse.format(raw_sql, reindent=True)
else:
print raw_sql
print
print 'Execution time: %.6fs [Database: %s]' % (execution_time, self.db.alias)
print
def execute(self, sql, params=()):
starttime = datetime.now()
try:
return self.cursor.execute(sql, params)
finally:
raw_sql = self.db.ops.last_executed_query(self.cursor, sql, params)
execution_time = datetime.now() - starttime
if sqlparse:
print sqlparse.format(raw_sql, reindent=True)
else:
print raw_sql
print
print 'Execution time: %fs' % execution_time.total_seconds()
print
def execute(self, sql, params=()):
formatted_sql = sql % (params if isinstance(params, dict) else tuple(params))
if self.logger:
message = formatted_sql
if settings.DEVSERVER_FILTER_SQL:
if any(filter_.search(message) for filter_ in settings.DEVSERVER_FILTER_SQL):
message = None
if message is not None:
if settings.DEVSERVER_TRUNCATE_SQL:
message = truncate_sql(message, aggregates=settings.DEVSERVER_TRUNCATE_AGGREGATES)
message = sqlparse.format(message, reindent=True, keyword_case='upper')
self.logger.debug(message)
start = datetime.now()
try:
return super(DatabaseStatTracker, self).execute(sql, params)
finally:
stop = datetime.now()
duration = ms_from_timedelta(stop - start)
if self.logger and (not settings.DEVSERVER_SQL_MIN_DURATION
or duration > settings.DEVSERVER_SQL_MIN_DURATION):
if self.cursor.rowcount >= 0 and message is not None:
self.logger.debug('Found %s matching rows', self.cursor.rowcount, duration=duration)
if not (debug_toolbar or django_settings.DEBUG):
self.db.queries.append({
'sql': formatted_sql,
'time': duration,
})
def test_format_accepts_encoding(load_file):
# issue20
sql = load_file('test_cp1251.sql', 'cp1251')
formatted = sqlparse.format(sql, reindent=True, encoding='cp1251')
tformatted = u'insert into foo\nvalues (1); -- Песня про надежду'
assert formatted == tformatted
def test_issue35(self):
# missing space before LIMIT
sql = sqlparse.format("select * from foo where bar = 1 limit 1",
reindent=True)
self.ndiffAssertEqual(sql, "\n".join(["select *",
"from foo",
"where bar = 1 limit 1"]))
def main(args=None):
parser = create_parser()
args = parser.parse_args(args)
if args.filename == '-': # read from stdin
data = sys.stdin.read()
else:
try:
# TODO: Needs to deal with encoding
data = ''.join(open(args.filename).readlines())
except IOError as e:
return _error(
u'Failed to read {0}: {1}'.format(args.filename, e))
if args.outfile:
try:
stream = open(args.outfile, 'w')
except IOError as e:
return _error(u'Failed to open {0}: {1}'.format(args.outfile, e))
else:
stream = sys.stdout
formatter_opts = vars(args)
try:
formatter_opts = sqlparse.formatter.validate_options(formatter_opts)
except SQLParseError as e:
return _error(u'Invalid options: {0}'.format(e))
s = sqlparse.format(data, **formatter_opts)
if PY2:
s = s.encode('utf-8', 'replace')
stream.write(s)
stream.flush()
return 0
def show_queries(db_alias=None, sqlparse_character_limit=2048):
old_debug_setting = settings.DEBUG
try:
settings.DEBUG = True
# This call to reset_queries ensures that the query list is
# empty before running the wrapped code, and stops the query
# log from just getting bigger and bigger if this context
# manager is used repeatedly.
reset_queries()
yield
queries_after = get_queries(db_alias)[:]
number_of_queries = len(queries_after)
print("--===--")
print("Number of queries: {n}".format(n=number_of_queries))
for i, q in enumerate(queries_after):
query_time = q['time']
query_sql = q['sql']
query_length = len(query_sql)
print(" Query {i} (taking {t}): ".format(i=i, t=query_time))
# Outputting the formatted query takes a very long time
# for large queries (e.g. those that prefetch_related can
# generate with "IN (... thousands of IDs ...)"), so only
# pretty-print queries that are fairly short.
if SQLPARSE_AVAILABLE and query_length <= sqlparse_character_limit:
formatted = sqlparse.format(
query_sql, reindent=True, keyword_case='upper')
print(indent(formatted, 4))
else:
print(indent(query_sql, 4))
print("End of query output.")
finally:
settings.DEBUG = old_debug_setting
def print_html_rule_text_detail_info(page, results, rules):
"""
文本类规则的具体信息
"""
for rule in rules:
for key in results[rule[0]].keys():
if "#" in key:
index_id = key.replace("#", "-")
div_id = rule[0] + "-" + index_id
text_id = div_id + "-text"
sql_fulltext = sqlparse.format(results[rule[0]][key]["sql_text"], reindent=True)
sql_fulltext = json.dumps(sql_fulltext)
stat_info = results[rule[0]][key]["stat"]
temp_stat_columns = []
temp_stat_info = []
stat_id = div_id + "-stat"
if stat_info:
for stat_key in stat_info[0].keys():
temp_stat_columns.append({"title": stat_key})
for stat in stat_info:
temp = []
for stat_key in stat.keys():
temp.append(str(stat[stat_key]))
temp_stat_info.append(temp)
temp_stat_info = json.dumps(temp_stat_info)
temp_stat_columns = json.dumps(temp_stat_columns)
page << "<script>genMultiTextTable('#base', '" + div_id + "', '" + stat_id + "', " + temp_stat_info + ", " + temp_stat_columns + ", '" + text_id + "', " + sql_fulltext + ", '" + div_id + "')</script>"
page << br()
def prepare_sql_script(self, sql, _allow_fallback=False):
"""
Takes a SQL script that may contain multiple lines and returns a list
of statements to feed to successive cursor.execute() calls.
Since few databases are able to process raw SQL scripts in a single
cursor.execute() call and PEP 249 doesn't talk about this use case,
the default implementation is conservative.
"""
# Remove _allow_fallback and keep only 'return ...' in Django 1.9.
try:
# This import must stay inside the method because it's optional.
import sqlparse
except ImportError:
if _allow_fallback:
# Without sqlparse, fall back to the legacy (and buggy) logic.
warnings.warn(
"Providing initial SQL data on a %s database will require "
"sqlparse in Django 1.9." % self.connection.vendor,
RemovedInDjango19Warning)
from django.core.management.sql import _split_statements
return _split_statements(sql)
else:
raise
else:
return [sqlparse.format(statement, strip_comments=True)
for statement in sqlparse.split(sql) if statement]
def format(request):
response = {'status': 0}
statements = request.POST.get('statements', '')
response['formatted_statements'] = sqlparse.format(statements, reindent=True, keyword_case='upper') # SQL only currently
return JsonResponse(response)
def print_sql(sql):
"""Pretty-print a SQL string. Also works with Django Query objects.
>>> qs = User.objects.all()
>>> print_sql(qs.query)
"""
print(sqlparse.format(str(sql), reindent=True))
def test_parenthesis(self):
f = lambda sql: sqlparse.format(sql, reindent=True)
s = 'select count(*) from (select * from foo);'
assert f(s) == '\n'.join(
['select count(*)', 'from', ' (select *', ' from foo);'])
def test_identifiercase_invalid_option(self):
sql = 'select * from bar; -- select foo\n'
with pytest.raises(SQLParseError):
sqlparse.format(sql, identifier_case='foo')
def test_keywordfunctions(self):
# issue36
f = lambda sql: sqlparse.format(sql, reindent=True)
s = 'select max(a) b, foo, bar'
assert f(s) == '\n'.join(
['select max(a) b,', ' foo,', ' bar'])
def test_python_multiple_statements(self):
sql = 'select * from foo; select 1 from dual'
f = lambda sql: sqlparse.format(sql, output_format='python')
assert f(sql) == '\n'.join(
["sql = 'select * from foo; '", "sql2 = 'select 1 from dual'"])
def test_truncate_strings_invalid_option2(option):
with pytest.raises(SQLParseError):
sqlparse.format('foo', truncate_strings=option)
def formatter(sql):
return sqlparse.format(sql, use_space_around_operators=True)
def test_preserve_ws(self):
# preserve at least one whitespace after subgroups
f = lambda sql: sqlparse.format(sql, strip_whitespace=True)
s = 'select\n* /* foo */ from bar '
assert f(s) == 'select * /* foo */ from bar'
def test_strip_ws_invalid_option(self):
s = 'select -- foo\nfrom bar\n'
with pytest.raises(SQLParseError):
sqlparse.format(s, strip_whitespace=None)
def test_strip_ws(self):
f = lambda sql: sqlparse.format(sql, strip_whitespace=True)
s = 'select\n* from foo\n\twhere ( 1 = 2 )\n'
assert f(s) == 'select * from foo where (1 = 2)'
s = 'select -- foo\nfrom bar\n'
assert f(s) == 'select -- foo\nfrom bar'
def test_format_right_margin():
# TODO: Needs better test, only raises exception right now
sqlparse.format('foo', right_margin="79")
def test_format_right_margin_invalid_option(right_margin):
with pytest.raises(SQLParseError):
sqlparse.format('foo', right_margin=right_margin)
def test_strip_comments_invalid_option(self):
sql = 'select-- foo\nfrom -- bar\nwhere'
with pytest.raises(SQLParseError):
sqlparse.format(sql, strip_comments=None)
def test_case2(self):
f = lambda sql: sqlparse.format(sql, reindent=True)
s = 'case(foo) when bar = 1 then 2 else 3 end'
assert f(s) == '\n'.join(
['case(foo)', ' when bar = 1 then 2', ' else 3', 'end'])
def test_keywords(self):
f = lambda sql: sqlparse.format(sql, reindent=True)
s = 'select * from foo union select * from bar;'
assert f(s) == '\n'.join(
['select *', 'from foo', 'union', 'select *', 'from bar;'])
def test_option(self):
with pytest.raises(SQLParseError):
sqlparse.format('foo', reindent=2)
with pytest.raises(SQLParseError):
sqlparse.format('foo', indent_tabs=2)
with pytest.raises(SQLParseError):
sqlparse.format('foo', reindent=True, indent_width='foo')
with pytest.raises(SQLParseError):
sqlparse.format('foo', reindent=True, indent_width=-12)
with pytest.raises(SQLParseError):
sqlparse.format('foo', reindent=True, wrap_after='foo')
with pytest.raises(SQLParseError):
sqlparse.format('foo', reindent=True, wrap_after=-12)
with pytest.raises(SQLParseError):
sqlparse.format('foo', reindent=True, comma_first='foo')
def query( # sqla
self, groupby, metrics,
granularity,
from_dttm, to_dttm,
filter=None, # noqa
is_timeseries=True,
timeseries_limit=15, row_limit=None,
inner_from_dttm=None, inner_to_dttm=None,
extras=None,
columns=None):
"""Querying any sqla table from this common interface"""
# For backward compatibility
if granularity not in self.dttm_cols:
granularity = self.main_dttm_col
cols = {col.column_name: col for col in self.columns}
qry_start_dttm = datetime.now()
if not granularity and is_timeseries:
raise Exception(
"Datetime column not provided as part table configuration "
"and is required by this type of chart")
metrics_exprs = [
literal_column(m.expression).label(m.metric_name)
for m in self.metrics if m.metric_name in metrics]
if metrics:
main_metric_expr = literal_column([
m.expression for m in self.metrics
if m.metric_name == metrics[0]][0])
else:
main_metric_expr = literal_column("COUNT(*)")
select_exprs = []
groupby_exprs = []
if groupby:
select_exprs = []
inner_select_exprs = []
inner_groupby_exprs = []
for s in groupby:
col = cols[s]
expr = col.expression
if expr:
outer = literal_column(expr).label(s)
inner = literal_column(expr).label('__' + s)
else:
outer = column(s).label(s)
inner = column(s).label('__' + s)
groupby_exprs.append(outer)
select_exprs.append(outer)
inner_groupby_exprs.append(inner)
inner_select_exprs.append(inner)
elif columns:
for s in columns:
select_exprs.append(s)
metrics_exprs = []
if granularity:
dttm_expr = cols[granularity].expression or granularity
timestamp = literal_column(dttm_expr).label('timestamp')
# Transforming time grain into an expression based on configuration
time_grain_sqla = extras.get('time_grain_sqla')
if time_grain_sqla:
udf = self.database.grains_dict().get(time_grain_sqla, '{col}')
timestamp_grain = literal_column(
udf.function.format(col=dttm_expr)).label('timestamp')
else:
timestamp_grain = timestamp
if is_timeseries:
select_exprs += [timestamp_grain]
groupby_exprs += [timestamp_grain]
tf = '%Y-%m-%d %H:%M:%S.%f'
time_filter = [
timestamp >= from_dttm.strftime(tf),
timestamp <= to_dttm.strftime(tf),
]
inner_time_filter = copy(time_filter)
if inner_from_dttm:
inner_time_filter[0] = timestamp >= inner_from_dttm.strftime(tf)
if inner_to_dttm:
inner_time_filter[1] = timestamp <= inner_to_dttm.strftime(tf)
select_exprs += metrics_exprs
qry = select(select_exprs)
from_clause = table(self.table_name)
if not columns:
qry = qry.group_by(*groupby_exprs)
where_clause_and = []
having_clause_and = []
for col, op, eq in filter:
col_obj = cols[col]
if op in ('in', 'not in'):
values = eq.split(",")
if col_obj.expression:
cond = ColumnClause(
col_obj.expression, is_literal=True).in_(values)
else:
cond = column(col).in_(values)
if op == 'not in':
cond = ~cond
where_clause_and.append(cond)
if extras and 'where' in extras:
where_clause_and += [text(extras['where'])]
if extras and 'having' in extras:
having_clause_and += [text(extras['having'])]
if granularity:
qry = qry.where(and_(*(time_filter + where_clause_and)))
else:
qry = qry.where(and_(*where_clause_and))
qry = qry.having(and_(*having_clause_and))
if groupby:
qry = qry.order_by(desc(main_metric_expr))
qry = qry.limit(row_limit)
if timeseries_limit and groupby:
subq = select(inner_select_exprs)
subq = subq.select_from(table(self.table_name))
subq = subq.where(and_(*(where_clause_and + inner_time_filter)))
subq = subq.group_by(*inner_groupby_exprs)
subq = subq.order_by(desc(main_metric_expr))
subq = subq.limit(timeseries_limit)
on_clause = []
for i, gb in enumerate(groupby):
on_clause.append(
groupby_exprs[i] == column("__" + gb))
from_clause = from_clause.join(subq.alias(), and_(*on_clause))
qry = qry.select_from(from_clause)
engine = self.database.get_sqla_engine()
sql = "{}".format(
qry.compile(engine, compile_kwargs={"literal_binds": True}))
df = pd.read_sql_query(
sql=sql,
con=engine
)
sql = sqlparse.format(sql, reindent=True)
return QueryResult(
df=df, duration=datetime.now() - qry_start_dttm, query=sql)
def test_keywordcase_invalid_option(self):
sql = 'select * from bar; -- select foo\n'
with pytest.raises(SQLParseError):
sqlparse.format(sql, keyword_case='foo')
def test_invalid_option(self):
sql = 'select * from foo;'
with pytest.raises(SQLParseError):
sqlparse.format(sql, output_format='foo')
def formatter(sql):
return sqlparse.format(sql, reindent_aligned=True)
def test_truncate_strings():
sql = "update foo set value = '{0}';".format('x' * 1000)
formatted = sqlparse.format(sql, truncate_strings=10)
assert formatted == "update foo set value = 'xxxxxxxxxx[...]';"
formatted = sqlparse.format(sql, truncate_strings=3, truncate_char='YYY')
assert formatted == "update foo set value = 'xxxYYY';"
def prettify_statement(statement):
statement = sqlparse.format(statement, keyword_case='upper', reindent=True)
return indent(
highlight(statement, SqlLexer(),
Terminal256Formatter(style='native'))).rstrip()
def test_sql(self):
# "sql" is an allowed option but has no effect
sql = 'select * from foo;'
f = lambda sql: sqlparse.format(sql, output_format='sql')
assert f(sql) == 'select * from foo;'
import sqlparse # pip3 install sqlparse # 分隔SQL sql = 'select * from foo; select * from bar;' sql_list = sqlparse.split(sql) print(sql_list) # 美化SQL sql_per = sqlparse.format(sql, reindent=True, keyword_case='upper') print(sql_per)
def BeautifySQL(sql):
return sqlparse.format(sql, reindent=True, keyword_case='upper')
print('password:'******':', line)
print('host:', host)
print('query', len(query))
print(sqlparse.format(query, strip_comments=True))
print('getting data...')
t1 = time.time()
result = sqlutil.get(query,
db=db,
host=host,
user=user,
password=password,
asDict=True)
querytime = time.time() - t1
if USING_atpy:
result = atpy.Table('postgres',
query=query,
user=user,
def test_identifier_and_functions(self):
# issue45
f = lambda sql: sqlparse.format(sql, reindent=True)
s = 'select foo.bar, nvl(1) from dual'
assert f(s) == '\n'.join(
['select foo.bar,', ' nvl(1)', 'from dual'])
def sql_preprocess(query):
if 'DISTINCT' in query:
dist = True
sql = query.replace('DISTINCT', '')
else:
dist = False
sql = query
sql = sqlparse.format(sql)
stmt = sqlparse.parse(sql)[0]
# Select clauses
attrs = str(stmt.tokens[2])
attrs = attrs.split(',')
attribute = []
for i in range(len(attrs)):
if not '.' in attrs[i]:
attribute.append([attrs[i].strip()])
else:
array = []
new_file = attrs[i].split('.')
array.append(new_file[0].strip())
array.append(new_file[1].strip())
attribute.append(array)
# From clauses
file = str(stmt.tokens[6])
file = file.split(',')
files = []
for j in range(len(file)):
file[j] = file[j].strip()
if not ' ' in file[j]:
files.append([file[j]])
else:
array2 = []
new_file = file[j].split(' ')
array2.append(new_file[0].strip())
array2.append(new_file[1].strip())
files.append(array2)
# Where clause conditions, e.q. 'ID = 3'
if 'WHERE' not in sql:
conds = []
keyword = []
else:
wherestr = str(stmt.tokens[-1])
where = str(re.findall('WHERE (.*);', wherestr))[2:-2]
keyword, condition = get_condition(where)
n = len(files)
if n == 1:
conds = []
for c in condition:
value, opt = split_condition(str(c))
conds.append(value + [opt])
else:
conds = []
for c in condition:
value, opt = split_condition(str(c))
if value[0] == 'NOT':
value.remove('NOT')
opt = reverse_not(opt)
conds.append(value + [opt])
if '(' in keyword and ')' in keyword:
keyword, conds = parentheses(keyword, conds)
return attribute, files, conds, keyword, dist
def test_identifiercase_quotes(self):
sql = 'select * from "foo"."bar"'
res = sqlparse.format(sql, identifier_case="upper")
assert res == 'select * from "foo"."bar"'
def test_truncate_strings_doesnt_truncate_identifiers(sql):
formatted = sqlparse.format(sql, truncate_strings=2)
assert formatted == sql
def test_nested_identifier_list(self):
# issue4
f = lambda sql: sqlparse.format(sql, reindent=True)
s = '(foo as bar, bar1, bar2 as bar3, b4 as b5)'
assert f(s) == '\n'.join(
['(foo as bar,', ' bar1,', ' bar2 as bar3,', ' b4 as b5)'])
