How to do calculation with postgres in python?

I try to do a math operation with Postgres in python to update the balance record at the users table but it failed, I tried the query manually on the terminal console and I successful. And I got a different result when I put the query in python code

CREATE TABLE users (      uid VARCHAR (16) PRIMARY KEY,      name VARCHAR (100),      email VARCHAR (100),      saldo VARCHAR (15),      password VARCHAR (100),      api_key VARCHAR (100),      callback_url VARCHAR (50),      regis_date TIMESTAMP);  

Here my example code :

#-*- coding: utf-8 -*-   import psycopg2  from datetime import datetime    def addSaldo(uid, saldo):      connection = None        try:          connection = psycopg2.connect(user = "postgres",                                  password = "-------",                                  host = "postgres",                                  port = "5432",                                  database = "testDB")           cursor = connection.cursor()           sql_update_query = "UPDATE users SET saldo = saldo::int + %s where uid = %s"         cursor.execute(sql_update_query, (saldo, uid) )         connection.commit()         cursor.close()       except (Exception, psycopg2.Error) as error:         print(str(error))       finally:         if connection is not None:             connection.close()  

The record before call the function

     uid |     name     |        email        | saldo | password  |    api_key    |   callback_url   |     regis_date        -----+--------------+---------------------+-------+-----------+---------------+------------------+---------------------   123 | memberlau | yeaah@gmail.com | 100    | ---- | apikeytest123 | http://127.0.0.1 | 2021-08-08 09:09:09  

The record after call the function

     uid |     name     |        email        | saldo | password  |    api_key    |   callback_url   |     regis_date        -----+--------------+---------------------+-------+-----------+---------------+------------------+---------------------   123 | memberlau | yeaah@gmail.com | 10    | ---- | apikeytest123 | http://127.0.0.1 | 2021-08-08 09:09:09  

I call the function updateSaldo('123', '10'), the result not from calculation but it replace all the value with '10'. Anyone can give me advice?

https://stackoverflow.com/questions/66540232/how-to-do-calculation-with-postgres-in-python March 09, 2021 at 11:07AM