Previous table column value in update query

[bell (Victor)]

I need to run a query like this:

t.update([t.code], [t.code + 1])

In response, I receive a message:

psycopg2.errors.SyntaxError: syntax error at or near "%"
UPDATE "t" AS "a" SET "code" = ("a"."code" + %s)

[nicoe (Nicolas Évrard)]

You have to use Literal when using a literal value. So your code should be t.update([t.code], [t.code + Literal(1)]).

It can be imported from sql.

[nicoe (Nicolas Évrard)]

You should also send all the arguments to cursor.execute, it’s usually done by calling it like that: cursor.execute(*my_sql_expression).

[bell (Victor)]

That’s exactly what I do.

[bell (Victor)]

It does not help. The error message is the same. The problem is that instead of "a"."code", you need "code".

[nicoe (Nicolas Évrard)]

There must be something else in your query then.

Just to be sure I tested the following and it worked:

conn = psycopg2.connect('dbname=add_child')
cursor = conn.cursor()

table = sql.Table('line')
query = table.update([table.parent], [table.parent + 1])
cursor.execute(*query)
conn.rollback()

[bell (Victor)]

OK. It works.
But I tried to work with temporary table.
The following code doesn’t work:

    t = Table('t')
    t2 = Table('some_table')

    query1 = t2.select()
    query2 = t.update([t.code], [t.code + 1])

    query = "CREATE TEMPORARY TABLE t AS %s; %s" % (query1, query2)

    cursor = Transaction().connection.cursor()
    cursor.execute(query)

But the following code works:

    query2 = "UPDATE t SET code = code + 1"

[ced (Cédric Krier)]

@bell

The following code doesn’t work:

Of course it is missing the parameters of both queries.

You should execute:

cursor.execute(query, query1.params + query2.params)

But generally python-sql does not support DDL but only DML because the first is not really standardized.