How to update to null a table column with python-sql

18 views
Skip to first unread message

Raimon Esteve

unread,
Jan 31, 2018, 8:15:06 AM1/31/18
to tryto...@googlegroups.com
hie,

I try with python-sql to update a column to "null".

Somebody know how to update to null a column?

Example:

            query = user.update(
                    columns=[user.main_company],
                    values=[None], <- I like here to update with null
                    where=user.main_company != Null)

Thanks

Cédric Krier

unread,
Jan 31, 2018, 9:20:08 AM1/31/18
to tryto...@googlegroups.com
As you do not explain what is not working, we can not help you.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

Raimon Esteve

unread,
Jan 31, 2018, 9:55:10 AM1/31/18
to tryto...@googlegroups.com
2018-01-31 15:19 GMT+01:00 Cédric Krier <cedric...@b2ck.com>:
On 2018-01-31 13:50, Raimon Esteve wrote:
> I try with python-sql to update a column to "null".
>
> Somebody know how to update to null a column?
>
> Example:
>
>             query = user.update(
>                     columns=[user.main_company],
>                     values=[None], <- I like here to update with null
>                     where=user.main_company != Null)

As you do not explain what is not working, we can not help you.

Examples and errors with python-sql 0.9:

1- values is [None]

            query = user.update(
                    columns=[user.main_company],
                    values=[None],
                    where=user.main_company != Null) 

Query is:

('UPDATE "res_user" SET "main_company" = %s WHERE ("res_user"."main_company" IS NOT NULL)', (None,))

Get error:

  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 193, in __iter__
    yield str(self)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 766, in __str__
    where = ' WHERE ' + str(self.where)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/operators.py", line 224, in __str__
    return '(%s IS NOT NULL)' % self.left
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 1300, in __str__
    alias = self._from.alias
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 250, in alias
    return AliasManager.get(self)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 155, in get
    if getattr(cls.local, 'alias', None) is None:
RuntimeError: maximum recursion depth exceeded in cmp

2- values is [Null]

            query = user.update(
                    columns=[user.main_company],
                    values=[Null],
                    where=user.main_company != Null)

Query is:

('UPDATE "res_user" SET "main_company" = %s WHERE ("res_user"."main_company" IS NOT NULL)', (None,))

Get error:

  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 193, in __iter__
    yield str(self)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 766, in __str__
    where = ' WHERE ' + str(self.where)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/operators.py", line 224, in __str__
    return '(%s IS NOT NULL)' % self.left
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 1300, in __str__
    alias = self._from.alias
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 250, in alias
    return AliasManager.get(self)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 155, in get
    if getattr(cls.local, 'alias', None) is None:
RuntimeError: maximum recursion depth exceeded in cmp

3- values is an empty list: []

            query = user.update(
                    columns=[user.main_company],
                    values=[],
                    where=user.main_company != Null)

Query is:

('UPDATE "res_user" SET  WHERE ("res_user"."main_company" IS NOT NULL)', ())

Get error:

Traceback (most recent call last):
  File "./trytond/bin/trytond-admin", line 21, in <module>
    admin.run(options)
  File "/home/resteve/virtualenv/nan40/trytond/trytond/admin.py", line 48, in run
    Pool(db_name).init(update=options.update, lang=lang)
  File "/home/resteve/virtualenv/nan40/trytond/trytond/pool.py", line 155, in init
    lang=lang)
  File "/home/resteve/virtualenv/nan40/trytond/trytond/modules/__init__.py", line 429, in load_modules
    _load_modules()
  File "/home/resteve/virtualenv/nan40/trytond/trytond/modules/__init__.py", line 398, in _load_modules
    load_module_graph(graph, pool, update, lang)
  File "/home/resteve/virtualenv/nan40/trytond/trytond/modules/__init__.py", line 237, in load_module_graph
    cls.__register__(module)
  File "/home/resteve/virtualenv/nan40/trytond/trytond/modules/party_company/user.py", line 51, in __register__
    cursor.execute(*query)
psycopg2.ProgrammingError: syntax error at or near "WHERE"
LINE 1: UPDATE "res_user" SET  WHERE ("res_user"."main_company" IS N...

Annex:

- I don't found an example a tryton modules or at  tests in python-sql to set null a column.

Thanks

Raimon Esteve

unread,
Feb 1, 2018, 5:30:06 AM2/1/18
to tryto...@googlegroups.com
Hie,

2018-01-31 15:40 GMT+01:00 Raimon Esteve <raimon...@gmail.com>:


2018-01-31 15:19 GMT+01:00 Cédric Krier <cedric...@b2ck.com>:
On 2018-01-31 13:50, Raimon Esteve wrote:
> I try with python-sql to update a column to "null".
>
> Somebody know how to update to null a column?
>
> Example:
>
>             query = user.update(
>                     columns=[user.main_company],
>                     values=[None], <- I like here to update with null
>                     where=user.main_company != Null)

As you do not explain what is not working, we can not help you.

Examples and errors with python-sql 0.9:

1- values is [None]

            query = user.update(
                    columns=[user.main_company],
                    values=[None],
                    where=user.main_company != Null) 

Query is:

('UPDATE "res_user" SET "main_company" = %s WHERE ("res_user"."main_company" IS NOT NULL)', (None,))

I'ts correct and working me.

Get error:

  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 193, in __iter__
    yield str(self)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 766, in __str__
    where = ' WHERE ' + str(self.where)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/operators.py", line 224, in __str__
    return '(%s IS NOT NULL)' % self.left
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 1300, in __str__
    alias = self._from.alias
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 250, in alias
    return AliasManager.get(self)
  File "/home/resteve/.virtualenvs/nan40/local/lib/python2.7/site-packages/sql/__init__.py", line 155, in get
    if getattr(cls.local, 'alias', None) is None:
RuntimeError: maximum recursion depth exceeded in cmp

The problem was that I was __register__ in another class :/ Change __register__() in other class work for me.



--
Si us plau, NO adjunti arxius a les seves respostes. Li preguem que integri el text al cos del missatge. Pot respondre usant NetEtiquete que li ajudarà a seguir la conversa. http://es.wikipedia.org/wiki/Netiquette

Por favor, NO adjunte archivos a sus respuestas. Le rogamos que integre el texto en el cuerpo del mensaje. Puede responder usando NetEtiquete que le ayudará a seguir la conversación.http://es.wikipedia.org/wiki/Netiquette

Please, DO NOT send attachment files with your answers, just copy and paste only the text you need to send into the body of your mails. Repply using NetEtiquete. http://en.wikipedia.org/wiki/Netiquette
Reply all
Reply to author
Forward
0 new messages