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