Re: [SR-Users] kamcli db sqlite / posgresql error

Hi Daniel many thanks for yours commits in git  kamcli. I try the new version but my errors are the same (venv) kamailio-BT:/usr/src/kamclienv/kamcli # kamcli -c /etc/kamcli/kamcli.ini.sqlite subscriber show Traceback (most recent call last):   File "/usr/src/kamclienv/venv/bin/kamcli", line 33, in <module>     sys.exit(load_entry_point('kamcli', 'console_scripts', 'kamcli')())   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 829, in __call__     return self.main(*args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 782, in main     rv = self.invoke(ctx)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 1259, in invoke     return _process_result(sub_ctx.command.invoke(sub_ctx))   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 1259, in invoke     return _process_result(sub_ctx.command.invoke(sub_ctx))   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 1066, in invoke     return ctx.invoke(self.callback, **ctx.params)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 610, in invoke     return callback(*args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/decorators.py", line 73, in new_func     return ctx.invoke(f, obj, *args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 610, in invoke     return callback(*args, **kwargs)   File "/usr/src/kamclienv/kamcli/kamcli/commands/cmd_subscriber.py", line 235, in subscriber_show     ioutils_dbres_print(ctx, oformat, ostyle, res)   File "/usr/src/kamclienv/kamcli/kamcli/ioutils.py", line 70, in ioutils_dbres_print     gstring = tabulate(allrows, headers=res.keys(), tablefmt=ostyle)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/tabulate.py", line 1529, in tabulate     tabular_data, headers, showindex=showindex   File "/usr/src/kamclienv/venv/lib/python3.7/site-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 (venv) kamailio-BT:/usr/src/kamclienv/kamcli # kamcli -c /etc/kamcli/kamcli.ini.sqlite subscriber add test passwordtest Traceback (most recent call last):   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context     cursor, statement, parameters, context   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute     cursor.execute(statement, parameters) sqlite3.IntegrityError: UNIQUE constraint failed: subscriber.username, subscriber.domain The above exception was the direct cause of the following exception: Traceback (most recent call last):   File "/usr/src/kamclienv/venv/bin/kamcli", line 33, in <module>     sys.exit(load_entry_point('kamcli', 'console_scripts', 'kamcli')())   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 829, in __call__     return self.main(*args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 782, in main     rv = self.invoke(ctx)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 1259, in invoke     return _process_result(sub_ctx.command.invoke(sub_ctx))   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 1259, in invoke     return _process_result(sub_ctx.command.invoke(sub_ctx))   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 1066, in invoke     return ctx.invoke(self.callback, **ctx.params)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 610, in invoke     return callback(*args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/decorators.py", line 73, in new_func     return ctx.invoke(f, obj, *args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", line 610, in invoke     return callback(*args, **kwargs)   File "/usr/src/kamclienv/kamcli/kamcli/commands/cmd_subscriber.py", line 69, in subscriber_add     ha1b,   File "<string>", line 2, in execute   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py", line 390, in warned     return fn(*args, **kwargs)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 3037, in execute     return connection.execute(statement, *multiparams, **params)   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1190, in execute     future=False,   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1491, in _exec_driver_sql     distilled_parameters,   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1749, in _execute_context     e, statement, parameters, cursor, context   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1930, in _handle_dbapi_exception     sqlalchemy_exception, with_traceback=exc_info[2], from_=e   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 211, in raise_     raise exception   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context     cursor, statement, parameters, context   File "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute     cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: subscriber.username, subscriber.domain [SQL: insert into subscriber (username, domain, password, ha1, ha1b) values ('test', 'sip.test.dev <http://sip.test.dev>', 'passwordtest', '96a1efd95bcd6e8e573d07ebdacab623', '025f1dc363fa8c1ce1d7f2069d94ed0f')] (Background on this error at: http://sqlalche.me/e/14/gkpj <http://sqlalche.me/e/14/gkpj>) best regards On Wed, Apr 21, 2021 at 3:10 PM Daniel-Constantin Mierla &lt;miconda(a)gmail.com <mailto:miconda@gmail.com>> wrote: 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@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@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@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@kamailio-BT:~# kamcli -c > /etc/kamcli/kamcli.ini.pgsql subscriber show > +------+------------+----------+------------+-------+--------+ > | id   | username   | domain   | password   | ha1   | > ha1b   | > +======+============+==========+============+=======+========+ > +------+------------+----------+------------+-------+--------+ > root@kamailio-BT:~# kamcli -c > /etc/kamcli/kamcli.ini.pgsql subscriber add test password > root@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@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 <http://www.asipto.com> www.twitter.com/miconda <http://www.twitter.com/miconda> -- www.linkedin.com/in/miconda <http://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/ <https://www.asipto.com/sw/kamailio-advanced-training-online/> _______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org <mailto:sr-users@lists.kamailio.org> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users <https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users>