sqlparse==0.4.4 causes a ValueError

[jon-rolfe]

The version of sqlparse released a few days ago seems to cause clickhouse-cli (0.3.8) to not start, giving a ValueErrror.

Full traceback:

  File "/home/jrolfe/.pyenv/versions/3.11.3/bin/clickhouse-cli", line 33, in <module>
    sys.exit(load_entry_point('clickhouse-cli==0.3.8', 'console_scripts', 'clickhouse-cli')())
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/clickhouse_cli/cli.py", line 580, in run_cli
    cli.run(query, data_input)
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/clickhouse_cli/cli.py", line 201, in run
    if not self.connect():
           ^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/clickhouse_cli/cli.py", line 114, in connect
    response = self.client.query('SELECT version();', fmt='TabSeparated')
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/clickhouse_cli/clickhouse/client.py", line 156, in query
    query = sqlparse.format(query, strip_comments=True).rstrip(';')
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/__init__.py", line 59, in format
    return ''.join(stack.run(sql, encoding))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/engine/filter_stack.py", line 26, in run
    stream = lexer.tokenize(sql, encoding)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/lexer.py", line 155, in tokenize
    return Lexer.get_default_instance().get_tokens(sql, encoding)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/lexer.py", line 52, in get_default_instance
    cls._default_intance.default_initialization()
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/lexer.py", line 59, in default_initialization
    self.set_SQL_REGEX(keywords.SQL_REGEX)
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/lexer.py", line 78, in set_SQL_REGEX
    self._SQL_REGEX = [
                      ^
  File "/home/jrolfe/.pyenv/versions/3.11.3/lib/python3.11/site-packages/sqlparse/lexer.py", line 80, in <listcomp>
    for rx, tt in SQL_REGEX
        ^^^^^^
ValueError: too many values to unpack (expected 2)

Easily solved with a pip install sqlparse==0.4.3, but probably deserves a proper fix.

[mattdeboard]

Thanks, this solved a problem of mine as well.

[bobrik]

Running the same query through sqlparse succeeds:

ivan@work: ~/projects/sqlparse on (HEAD detached at 0.4.4)
$ python
Python 3.10.11 (main, May  2 2023, 17:51:13) [Clang 11.1.0 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlparse
>>> sqlparse.format('SELECT version();', strip_comments=True)
'SELECT version();'

I think CHLexer is incompatible here for some reason.

[tango0o]

I have same issue occurred. I found SQL_REGEX property contains many values.Maybe difference data types that expected list, but found dict. I have printed SQL_REGEX and output:

ValueError: too many values to unpack (expected 2)
dict_keys(['root', 'multiline-comments', 'string', 'quoted-ident'])

DEBUG infos: key -> values

root -> [('\\s+', Token.Text), ('(--\\s*).*?\\n', Token.Comment), ('/\\*', Token.Comment.Multiline, 'multiline-comments'), ('[0-9]+', Token.Literal.Number)
, ('[0-9]*\\.[0-9]+(e[+-][0-9]+)', Token.Literal.Number), ("'(\\\\\\\\|\\\\'|''|[^'])*'", Token.Literal.String), ('"(\\\\\\\\|\\\\"|""|[^"])*"', Token.Lite
ral.String), ('`(\\\\\\\\|\\\\`|``|[^`])*`', Token.Literal.String), ('[+*/<>=~!@#%^&|`?-]', Token.Operator), (<pygments.lexer.words object at 0x7f30f7d46d1
0>, Token.Keyword), (<pygments.lexer.words object at 0x7f30f7d46ce0>, Token.Keyword.Type), (<pygments.lexer.words object at 0x7f30f7d471c0>, Token.Name.Lab
el), (<pygments.lexer.words object at 0x7f30f7d47250>, <function bygroups.<locals>.callback at 0x7f30f7d888b0>), (<pygments.lexer.words object at 0x7f30f7d
475b0>, Token.Name.Function), (<pygments.lexer.words object at 0x7f30f7d47640>, <function bygroups.<locals>.callback at 0x7f30f7d8b760>), (<pygments.lexer.
words object at 0x7f30f7d478b0>, Token.Keyword), ('^\\\\(\\?|\\w+)', Token.Text), ('(?i)[a-z_]\\w*', Token.Text), ('(?i)[;:()\\[\\],.]', Token.Punctuation)
, ("'", Token.Literal.String.Single, 'string'), ('[a-z_]\\w*', Token.Name), ('[;:()\\[\\]{},.]', Token.Punctuation)]

multiline-comments -> [('/\\*', Token.Comment.Multiline, 'multiline-comments'), ('\\*/', Token.Comment.Multiline, '#pop'), ('[^/*]+', Token.Comment.Multili
ne), ('[/*]', Token.Comment.Multiline)]

string -> [("[^']+", Token.Literal.String.Single), ("''", Token.Literal.String.Single), ("'", Token.Literal.String.Single, '#pop')]

quoted-ident -> [('[^"]+', Token.Literal.String.Name), ('""', Token.Literal.String.Name), ('"', Token.Literal.String.Name, '#pop')]

I have tried to modify some code block to ensure clickhouse-cli can be run normally. like this:

for rx, tt in filter(lambda it: len(it) == 2 and isinstance(it, str), SQL_REGEX['root'])

python3.10 -m clickhouse_cli.cli -h my_db_ip --port my_db_port -u default --database default --password
Password:
clickhouse-cli version: 0.3.8
Connecting to my_db_ip:my_db_port
Connected to ClickHouse server v22.3.20.
:) 

[deric]

This looks a bit unfortunate:

sqlparse.keywords.SQL_REGEX = CHLexer.tokens
sqlparse.keywords.KEYWORDS = KEYWORDS
sqlparse.keywords.KEYWORDS_COMMON = {}
sqlparse.keywords.KEYWORDS_ORACLE = {}

Here's the commit in sqlparse that broke the patching (introduced in 0.4.4).

I guess moving forward it would be best to remove monkey-patching and switch to the new API, which might look like this (WIP)

[hatarist]

Thanks a lot, merged @deric's fixes to the master branch & released the 0.3.9 version.
Will take a look at it to the new API.

It was so long ago when I made this, I know the code is horrible 🤪

[osv]

Hi, any plans to update support of sqlparse to 0.4.4?