Run postgresql functions in python and gets error

Question

I would like to run below postgresql function queries in python but I keep getting error message says

cursor = connection.cursor()
cursor.execute("SELECT 
ST_Distance_sphere(st_makepoint(32.836956,39.925018)
,st_makepoint(28.990174,41.036857))")
df = cursor.fetchall()
df

InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

How can I fix this? Thank you.

I've seen this error several times although always through an IDE (pgAdmin being one) but not Python. Typically I've found this results from a previous transaction which has not been committed nor rolled back. Don't know if this is your issue, but it might be worth looking into. — Belayer
– Belayer, Commented Jul 19, 2019 at 17:16

Alexander Santos · Accepted Answer · 2019-07-19 17:33:49Z

1

According to psycopg docs, you probably have an error in your command (SQL).

There was a problem in the previous command to the database, which resulted in an error. The database will not recover automatically from this condition: you must run a rollback() before sending new commands to the session (if this seems too harsh, remember that PostgreSQL supports nested transactions using the SAVEPOINT command).

I highly recommend using try/except/finally clause in your database connections. Or use with statement.

Here is an example from http://www.postgresqltutorial.com/postgresql-python/transaction/:

conn = psycopg2.connect(dsn)

# transaction 1
with conn:
    with conn.cursor() as cur:
        cur.execute(sql)

# transaction 2
with conn:
    with conn.cursor() as cur:
        cur.execute(sql)
conn.close()

answered Jul 19, 2019 at 17:33

Alexander Santos

1,73916 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run postgresql functions in python and gets error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related