I try to run queries in my python script, and in my console, I see that they execute ok, but when I see my Postgre DataBase and the changes don't appear

   _DATABASE = 'DB'
   _USERNAME = 'XXXXXXX'
   _PASSWORD = 'XXXXXXX'
   _DB_PORT = '5432'
   _HOST = '127.0.0.1'
   conection_DB = None
   cursor = None

try:
    connection_DB = psycopg2.connect(
        host=_HOST,
        dbname=_DATABASE,
        user=_USERNAME,
        password=_PASSWORD,
        port=_DB_PORT)
    cursor = connection_DB.cursor()

    query1 = 'UPDATE samples SET sample_code=%s, sample_type=%s WHERE sampleid=%s'
    query2 = 'INSERT INTO samples(sample_code, sample_type) VALUES(%s, %s)'
    query3 = 'DELETE FROM samples WHERE sample_internal_id=%s'
    query4 = 'SELECT * FROM samples'

    #SELECT QUERY 1
    cursor.execute(query4)
    for record in cursor.fetchall():
        print(record)

    #INSERT QUERY
    values2 = ('rrr-rrr', 'SampleType1')
    cursor.execute(query2, values2)

    #UPDATE QUERY
    values1 = ('jjj-jjj', 'SampleTyp2', 5)
    cursor.execute(query1, values1)

    #SELECT QUERY 2
    cursor.execute(query4)
    for record in cursor.fetchall():
        print(record)

except Exception as error:
    print('Error in DataBase communication') 

In console, I see that my "SELECT QUERY 2" shows that the other queries worked and were saved in my database, but from PgAdmin I see that isn't true.

CodePudding user response：

I think you missed the "commit" function. Because you only execute the query without committing the query to the DB.

You can add the commit after the last query execute

...
# Committing all query transaction
connection_DB.commit()

# Don't forget to close the connection
connection_DB..close()
...

References: here

CodePudding user response：

SOLVED!

use connection.commit() to save changes in database.