Execute psql command from python script

Question

In what way can I execute a psql command from a Python script? My code inserts and updates data from a PostgreSQL DB (using psycopg2 and cursor method). It works fine when I execute a query like

cursor.execute("UPDATE Segment set idcurve = %s where id = %s ", (id_curve, id_segment,))

But in the psql case, when I pass commands to cursor.execute (see edits to similar question) I get SyntaxError: syntax error at or near "\". Moreover, the command fails in console.sql DataGRIP (it executes for all the rows anyway...)

Does this command only work in the shell (do I have to use os.system)? as cursor is not able to interpret it?

EDIT trying with subprocess:

import subprocess

sql = """SELECT uuid IS NULL AS is_uuid FROM dpoint WHERE uuid = '5547f4b7-00b3-4aac-8ceb-c9ca163a0214';
\gset 
\if :is_uuid 
    INSERT INTO DPoint (uuid) VALUES ('5547f4b7-00b3-4aac-8ceb-c9ca163a0214');
    WITH ins1 AS (INSERT INTO Point (latitude, longitude, srid) 
          VALUES (64.44, 28.77, 4326) RETURNING id AS id_point)
    INSERT INTO SPoint (idPoint, uuiddpt) VALUES ((SELECT id_point FROM ins1), '5547f4b7-00b3-4aac-8ceb-c9ca163a0214');
\endif
"""
subprocess.check_call(['psql -h localhost -d dbase -U myuser -W --command={}'.format(sql)], env={'PGPASSWORD': 'mypass'})

Throws OSError: [Errno 36] File name too long

EDIT2

subprocess.check_call(['psql', '-q', '-U', 'myuser', '-h', 'localhost', '-c', '{}'.format(sql), 'dbase'], env={'PGPASSWORD': 'mypass'})

When I split up all the arguments the query is trying to execute -> it fails with syntax error at or near "\". -E (as Lu M suggests) does not help. It works in the case where I save my query to .sql file however I would like to execute it interactively.

EDIT3

According to psql tutorial in chapter --command=command there are two options available to mix SQL and psql meta-commands. In what way is it possible to wrap it in a subprocess? I tried both, but undesired extra backslashes appear and it is not recognized as one command:

subprocess.check_call(['psql', '-q', '-U', 'myuser', '-h', 'localhost',
                   '-c', '{}'.format(sql),
                   '-c', '{}'.format('\gset'),
                   '-c', '{}'.format('\if :is_uuid '),
                   '-c', '{}'.format(sql2),
                   '-c', '{}'.format('\endif'), 'dbase'],
                  env={'PGPASSWORD': 'mypass'})

fails with unrecognized value ":is_uuid" for "\if expression": Boolean expected

The commands starting with ` are, as you already guessed, psql` commands and are not interpreted by the DBMS itself but by the psql program. — sticky bit
– sticky bit, Commented Dec 24, 2019 at 9:15
@stickybit is this answer stackoverflow.com/a/19930714/8129993 is still valid and up-to-date? — Jane
– Jane, Commented Dec 24, 2019 at 9:39
Well, why don't you just try it and see if it works for you? — sticky bit
– sticky bit, Commented Dec 24, 2019 at 9:54
You could theoretically rewrite this to use regular sql, but I'm not sure what it's supposed to do. The first query would either return nothing or false. — Jeremy
– Jeremy, Commented Dec 24, 2019 at 11:35

chluebi · Accepted Answer · 2019-12-24 10:03:29Z

2

It seems like you are trying to run a Meta-Command through psycopg2 similar to to this question. Psycopg2 is unable to process Meta-Commands and that's why it's throwing this Syntax error.

What may work for your case is the following from this question:

It's really an important information that the command line psql -E will echo SQL queries used to implement \d and other backslash commands (whenever you use one of them in the psql prompt) as @piro has written in comment. This way you get what you want very easily.

Edit: Otherwise you will have to use subprocess, as comments have already pointed out.

answered Dec 24, 2019 at 10:03

chluebi

1,8291 gold badge10 silver badges22 bronze badges

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

6 Comments

Jane Over a year ago

Thank you for the answer! I am trying your both ideas (see my edits), but no success(

chluebi Over a year ago

I’m currently busy, but if the problem persists, I‘ll see if I can find a solution for running meta commands in a subprocess. Maybe try storing the whole command in a txt file and reading that?

Jane Over a year ago

I tried to save my command to .sql file. It works, but I would like to execute it interactively

chluebi Over a year ago

It‘s just a hack for now, but you could theoretically always generate the file, then call it via subprocess.

Jane Over a year ago

Serialization/deserialization is a high-load operation, isn't it? There are so many objects I have to process...Moreover I have tested with hard-coded values for uuid, etc. in fact they are parametrized from script (what way is to generate the queries? According to initd.org/psycopg/docs/… the correct way is to execute sql with passing variables)

|

Collectives™ on Stack Overflow

Execute psql command from python script

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related