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, Dec 24 '19 at 09:15
@stickybit is this answer https://stackoverflow.com/a/19930714/8129993 is still valid and up-to-date? — Jane, Dec 24 '19 at 09:39
Well, why don't you just try it and see if it works for you? — sticky bit, Dec 24 '19 at 09: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, Dec 24 '19 at 11:35
@Jeremy I tried using clean SQL for this case (see link in my question), I am not able to do it like that( Maybe you have an idea for regular SQL? — Jane, Dec 24 '19 at 11:55
If you try with a sql query more litte and simple like select * from.... work? — GiovaniSalazar, Dec 24 '19 at 12:22
@GiovaniSalazar, what do you mean `select * from...`? from where? — Jane, Dec 24 '19 at 12:28
`'-c', '{}'.format(sql)` works if sql variable contains only simple SQL, without `\gset` etc.. This is what you mean? I found here https://www.postgresql.org/docs/current/app-psql.html in chapter --command=command an example of using mix SQL and psql meta-commands. But still don't know how to wrap it to subprocess — Jane, Dec 24 '19 at 12:35
yes..ok ,..I will try to replicate but also check this check my reply of another question https://stackoverflow.com/questions/59462803/how-can-i-get-the-output-of-a-python-subprocess-command-that-contains-a-pipe/59462840#59462840 ... in variable cmd add your full command psql — GiovaniSalazar, Dec 24 '19 at 12:41

score 2 · Answer 1 · answered Dec 24 '19 at 10:03

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 '19 at 10:03

chluebi

1,809
1
8
21

Thank you for the answer! I am trying your both ideas (see my edits), but no success( – Jane Dec 24 '19 at 10:49
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? – chluebi Dec 24 '19 at 11:38
I tried to save my command to .sql file. It works, but I would like to execute it interactively – Jane Dec 24 '19 at 11:40
It‘s just a hack for now, but you could theoretically always generate the file, then call it via subprocess. – chluebi Dec 24 '19 at 11:44
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 http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters the correct way is to execute sql with passing variables) – Jane Dec 24 '19 at 14:23
Having decided to use python, you should use python. Translating the "psql" script into python (over psycopg2) should not be hard. It will easier than applying bandaid over bandaid over bandaid. – jjanes Dec 24 '19 at 16:38

Execute psql command from python script

1 Answers1