Question 236450 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – sqlalchemy with postgres connection: .execute GRANT and DELETE not affecting database

Passing "SELECT"-based SQL queries works. But passing "DELETE"- or "GRANT"-based SQL executed in python without error but makes no change to my database (python 3.8, sqlalchemy 2.0.10). These "DELETE" and "GRANT" queries worked in Python 3.6, sqlalchemy 1.3.23. I could be missing something, but I don’t see anything in the sqlalchemy docs/change notes to indicate that this "DELETE" or "GRANT" functionality should be lost.

Here is an example that might shed light on where I’m going wrong. After initializing the engine (where I use ” to indicate some arbitrary input):

from sqlalchemy import create_engine
from sqlalchemy import text    
db_name = '<insert_db_name>'
port = ':<insert_port>/'
host = '<insert_host_endpoint>'
username = '<insert_username>'
password = '<insert_password>'
engine = create_engine('postgresql_psycopg2://'+username+':'+
                            password+'@'+host+port+db_name)

I am able read and write through connection.execute or even pandas. Something like the following works just fine (reading with pandas, writing with pandas, reading with .execute):

import pandas as pd
query_1 = "SELECT * from '<schema>'.'<table_name>'"
df = pd.read_sql(sql_query, engine)
df.to_sql(name = '<new_table_name>', con = engine, schema = <'schema'>)
with engine.connect() as con:
    con.execute(text("SELECT * from schema.'<new_table_name>'))

Passing GRANT- or DELETE-based SQL through the .execute runs in Python without errors, but there are no changes to the database–as if the Python script never ran. The following examples run in Python without error but I get no effect in the database:

query_2 = "GRANT SELECT ON TABLE '<schema>'.'<table_name>' TO <'some_user'>"
query_3 = "DELETE FROM '<schema>'.'<table_name>' WHERE <some condition>"
with engine.connect() as con:
    con.execute(text(query_2))
    con.execute(text(query_3))

Again, this same code worked in Python 3.6/sqlalchemy 1.3.23 (removing the sqlalchemy "text()" as it wasn’t necessary in older versions as described in sqlalchemy change notes).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue had nothing to do with the "SELECT", "DELETE", or "GRANT" SQL calls, rather (as posted in the comments) this was a failure to auto-commit (it does turn out to be related to stackoverflow.com/a/76947751/2144390). One solution to this is to add the parameter isolation_level = 'AUTOCOMMIT' when initiating the engine.

    Continuing the example above, this would be:

    engine = create_engine('postgresql_psycopg2://'+username+':'+
                            password+'@'+host+port+db_name, isolation_level = 'AUTOCOMMIT')

    See https://docs.sqlalchemy.org/en/20/core/connections.html#dbapi-autocommit


  2. 0

    You can add turn on AUTOCOMMIT when initializing the engine or you can explicitly commit the changes to your database by modifying your code to include the COMMIT command.

    query_2 = "GRANT SELECT ON TABLE '<schema>'.'<table_name>' TO <'some_user'>"
query_3 = "DELETE FROM '<schema>'.'<table_name>' WHERE <some condition>"
with engine.connect() as con:
    con.execute(text(query_2))
    con.execute(text(query_3))
    con.execute(text("COMMIT"))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search