[SR-Users] kamcli db sqlite / posgresql error
Daniel-Constantin Mierla
miconda at gmail.com
Wed Apr 21 15:10:42 CEST 2021
Hello,
On 18.04.21 22:56, beer Ll wrote:
> Hi ,
>
> I have a server test with
>
> Debian Buster 10.9
> kamailio 5.4.5
> kamcli 2.0.0 (similar result with 3.0.0)
>
> I'm not able to use kamcli
> with sqlite : insert and select doesn't work
> with pgsql : insert works , select of empty table works , select
> table with entry doesn't work
>
>
> TEST WITH SQLITE
> root at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.sqlite subscriber
> add test password
> Traceback (most recent call last):
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 1706, in _execute_context
> cursor, statement, parameters, context
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py",
> line 716, in do_execute
> cursor.execute(statement, parameters)
> sqlite3.OperationalError: near "%": syntax error
>
> The above exception was the direct cause of the following exception:
>
> Traceback (most recent call last):
> File "/usr/bin/kamcli", line 11, in <module>
> load_entry_point('kamcli==2.0.0', 'console_scripts', 'kamcli')()
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 829, in __call__
> return self.main(*args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 782, in main
> rv = self.invoke(ctx)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1259, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1259, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1066, in invoke
> return ctx.invoke(self.callback, **ctx.params)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 610, in invoke
> return callback(*args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/decorators.py",
> line 73, in new_func
> return ctx.invoke(f, obj, *args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 610, in invoke
> return callback(*args, **kwargs)
> File
> "/usr/lib/python3/dist-packages/kamcli/commands/cmd_subscriber.py",
> line 43, in subscriber_add
> e.execute('insert into subscriber (username, domain, password,
> ha1, ha1b) values (%s, %s, %s, %s, %s)', udata['username'],
> udata['domain'], password, ha1, ha1b)
> File "<string>", line 2, in execute
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/deprecations.py",
> line 390, in warned
> return fn(*args, **kwargs)
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 3037, in execute
> return connection.execute(statement, *multiparams, **params)
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 1190, in execute
> future=False,
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 1491, in _exec_driver_sql
> distilled_parameters,
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 1749, in _execute_context
> e, statement, parameters, cursor, context
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 1930, in _handle_dbapi_exception
> sqlalchemy_exception, with_traceback=exc_info[2], from_=e
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/compat.py",
> line 211, in raise_
> raise exception
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py",
> line 1706, in _execute_context
> cursor, statement, parameters, context
> File
> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py",
> line 716, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "%":
> syntax error
> [SQL: insert into subscriber (username, domain, password, ha1, ha1b)
> values (%s, %s, %s, %s, %s)]
> [parameters: ('test', 'sip.test.dev <http://sip.test.dev>',
> 'password', '4380ea9069e888b5fc92996495a0f481',
> '3a36aeb0df7f4504cc5d9b97dab540e5')]
> (Background on this error at: http://sqlalche.me/e/14/e3q8
> <http://sqlalche.me/e/14/e3q8>)
>
> root at kamailio-BT:~# kamctl add test password
> -e sqlite_query: /usr/bin/sqlite3 /etc/kamailio/kamailio.db insert
> into subscriber (username, domain,ha1,ha1b,password) values
> ('test','sip.test.dev
> <http://sip.test.dev>','4380ea9069e888b5fc92996495a0f481','3a36aeb0df7f4504cc5d9b97dab540e5','password');
> -e new user 'test' added
>
> root at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.sqlite subscriber
> show
> Traceback (most recent call last):
> File "/usr/bin/kamcli", line 11, in <module>
> load_entry_point('kamcli==2.0.0', 'console_scripts', 'kamcli')()
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 829, in __call__
> return self.main(*args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 782, in main
> rv = self.invoke(ctx)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1259, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1259, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1066, in invoke
> return ctx.invoke(self.callback, **ctx.params)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 610, in invoke
> return callback(*args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/decorators.py",
> line 73, in new_func
> return ctx.invoke(f, obj, *args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 610, in invoke
> return callback(*args, **kwargs)
> File
> "/usr/lib/python3/dist-packages/kamcli/commands/cmd_subscriber.py",
> line 118, in subscriber_show
> ioutils_dbres_print(ctx, oformat, ostyle, res)
> File "/usr/lib/python3/dist-packages/kamcli/ioutils.py", line 45, in
> ioutils_dbres_print
> gstring = tabulate(allrows, headers=res.keys(), tablefmt=ostyle)
> File "/usr/local/lib/python3.7/dist-packages/tabulate.py", line
> 1529, in tabulate
> tabular_data, headers, showindex=showindex
> File "/usr/local/lib/python3.7/dist-packages/tabulate.py", line
> 1162, in _normalize_tabular_data
> "headers for a list of dicts is not a dict or a keyword"
> ValueError: headers for a list of dicts is not a dict or a keyword
>
>
> TEST WITH PGSQL
> root at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.pgsql subscriber show
> +------+------------+----------+------------+-------+--------+
> | id | username | domain | password | ha1 | ha1b |
> +======+============+==========+============+=======+========+
> +------+------------+----------+------------+-------+--------+
> root at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.pgsql subscriber
> add test password
> root at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.pgsql subscriber show
> Traceback (most recent call last):
> File "/usr/bin/kamcli", line 11, in <module>
> load_entry_point('kamcli==2.0.0', 'console_scripts', 'kamcli')()
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 829, in __call__
> return self.main(*args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 782, in main
> rv = self.invoke(ctx)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1259, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1259, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 1066, in invoke
> return ctx.invoke(self.callback, **ctx.params)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 610, in invoke
> return callback(*args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/decorators.py",
> line 73, in new_func
> return ctx.invoke(f, obj, *args, **kwargs)
> File "/usr/local/lib/python3.7/dist-packages/click/core.py", line
> 610, in invoke
> return callback(*args, **kwargs)
> File
> "/usr/lib/python3/dist-packages/kamcli/commands/cmd_subscriber.py",
> line 118, in subscriber_show
> ioutils_dbres_print(ctx, oformat, ostyle, res)
> File "/usr/lib/python3/dist-packages/kamcli/ioutils.py", line 45, in
> ioutils_dbres_print
> gstring = tabulate(allrows, headers=res.keys(), tablefmt=ostyle)
> File "/usr/local/lib/python3.7/dist-packages/tabulate.py", line
> 1529, in tabulate
> tabular_data, headers, showindex=showindex
> File "/usr/local/lib/python3.7/dist-packages/tabulate.py", line
> 1162, in _normalize_tabular_data
> "headers for a list of dicts is not a dict or a keyword"
> ValueError: headers for a list of dicts is not a dict or a keyword
>
> root at kamailio-BT:~# kamctl show test
> 1|test|sip.test.dev
> <http://sip.test.dev>|password|4380ea9069e888b5fc92996495a0f481|3a36aeb0df7f4504cc5d9b97dab540e5
thanks for reporting!
I could reproduce the sqlite problems, they seemed to be related to the
sql driver for sqlalchemy that was not supporting variadic number of
parameters to the sql query execution function. I replaced with string
formatting and now seems to work. Try with latest git version and report
back if there are still issues.
The one for postgres I couldn't reproduce, but it was after a commit
related to output style default value. Although it didn't seem related,
it worked what I tried for postgres. Again, test now with latest git
version and if there is still a problem, provide updated traces.
Cheers,
Daniel
--
Daniel-Constantin Mierla -- www.asipto.com
www.twitter.com/miconda -- www.linkedin.com/in/miconda
Kamailio Advanced Training - Online
May 17-20, 2021 (Europe Timezone) - June 7-10, 2021 (America Timezone)
* https://www.asipto.com/sw/kamailio-advanced-training-online/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210421/98915f7e/attachment.htm>
More information about the sr-users
mailing list