Previous table column value in update query

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)

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.

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

That’s exactly what I do.

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

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()

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"

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.

1 Like

A post was split to a new topic: Use temporary table