def test_issue40():
# make sure identifier lists in subselects are grouped
p = bsqlparse.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 = bsqlparse.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 = bsqlparse.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 main(args=None):
parser = create_parser()
args = parser.parse_args(args)
if args.filename == '-': # read from stdin
if PY2:
data = getreader(args.encoding)(sys.stdin).read()
else:
data = TextIOWrapper(sys.stdin.buffer,
encoding=args.encoding).read()
else:
try:
data = ''.join(open(args.filename, 'r', args.encoding).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', args.encoding)
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 = bsqlparse.formatter.validate_options(formatter_opts)
except bsqlparseError as e:
return _error(u'Invalid options: {0}'.format(e))
s = bsqlparse.format(data, **formatter_opts)
stream.write(s)
stream.flush()
return 0
def test_issue207_runaway_format():
sql = 'select 1 from (select 1 as one, 2 as two, 3 from dual) t0'
p = bsqlparse.format(sql, reindent=True)
assert p == '\n'.join([
"select 1", "from", " (select 1 as one,", " 2 as two,",
" 3", " from dual) t0"
])
def test_format_accepts_encoding(load_file):
# issue20
sql = load_file('test_cp1251.sql', 'cp1251')
formatted = bsqlparse.format(sql, reindent=True, encoding='cp1251')
tformatted = u'insert into foo\nvalues (1); -- Песня про надежду'
assert formatted == tformatted
def test_except_formatting():
sql = 'SELECT 1 FROM foo WHERE 2 = 3 EXCEPT SELECT 2 FROM bar WHERE 1 = 2'
formatted = bsqlparse.format(sql, reindent=True)
tformatted = '\n'.join([
'SELECT 1', 'FROM foo', 'WHERE 2 = 3', 'EXCEPT', 'SELECT 2',
'FROM bar', 'WHERE 1 = 2'
])
assert formatted == tformatted
def test_parse_sql_with_binary():
# See https://github.com/andialbrecht/bsqlparse/pull/88
# digest = '|ËêplL4¡høN{'
digest = '\x82|\xcb\x0e\xea\x8aplL4\xa1h\x91\xf8N{'
sql = "select * from foo where bar = '{0}'".format(digest)
formatted = bsqlparse.format(sql, reindent=True)
tformatted = "select *\nfrom foo\nwhere bar = '{0}'".format(digest)
if PY2:
tformatted = tformatted.decode('unicode-escape')
assert formatted == tformatted
def test_issue90():
sql = ('UPDATE "gallery_photo" SET "owner_id" = 4018, "deleted_at" = NULL,'
' "width" = NULL, "height" = NULL, "rating_votes" = 0,'
' "rating_score" = 0, "thumbnail_width" = NULL,'
' "thumbnail_height" = NULL, "price" = 1, "description" = NULL')
formatted = bsqlparse.format(sql, reindent=True)
tformatted = '\n'.join([
'UPDATE "gallery_photo"', 'SET "owner_id" = 4018,',
' "deleted_at" = NULL,', ' "width" = NULL,',
' "height" = NULL,', ' "rating_votes" = 0,',
' "rating_score" = 0,', ' "thumbnail_width" = NULL,',
' "thumbnail_height" = NULL,', ' "price" = 1,',
' "description" = NULL'
])
assert formatted == tformatted
def test_issue315_utf8_by_default():
# Make sure the lexer can handle utf-8 string by default correctly
# digest = '齐天大圣.カラフルな雲.사랑해요'
# The digest contains Chinese, Japanese and Korean characters
# All in 'utf-8' encoding.
digest = (
'\xe9\xbd\x90\xe5\xa4\xa9\xe5\xa4\xa7\xe5\x9c\xa3.'
'\xe3\x82\xab\xe3\x83\xa9\xe3\x83\x95\xe3\x83\xab\xe3\x81\xaa\xe9'
'\x9b\xb2.'
'\xec\x82\xac\xeb\x9e\x91\xed\x95\xb4\xec\x9a\x94')
sql = "select * from foo where bar = '{0}'".format(digest)
formatted = bsqlparse.format(sql, reindent=True)
tformatted = "select *\nfrom foo\nwhere bar = '{0}'".format(digest)
if PY2:
tformatted = tformatted.decode('utf-8')
assert formatted == tformatted
def test_issue38():
sql = bsqlparse.format("SELECT foo; -- comment", strip_comments=True)
assert sql == "SELECT foo;"
sql = bsqlparse.format("/* foo */", strip_comments=True)
assert sql == ""
def test_issue35():
# missing space before LIMIT. Updated for #321
sql = bsqlparse.format("select * from foo where bar = 1 limit 1",
reindent=True)
assert sql == "\n".join(
["select *", "from foo", "where bar = 1", "limit 1"])
def test_issue213_leadingws():
sql = " select * from foo"
assert bsqlparse.format(sql, strip_whitespace=True) == "select * from foo"
def test_null_with_as():
sql = 'SELECT NULL AS c1, NULL AS c2 FROM t1'
formatted = bsqlparse.format(sql, reindent=True)
tformatted = '\n'.join(
['SELECT NULL AS c1,', ' NULL AS c2', 'FROM t1'])
assert formatted == tformatted
def test_comment_encoding_when_reindent():
# There was an UnicodeEncodeError in the reindent filter that
# casted every comment followed by a keyword to str.
sql = u'select foo -- Comment containing Ümläuts\nfrom bar'
formatted = bsqlparse.format(sql, reindent=True)
assert formatted == sql
