时间戳

首页 > 解决方案 > 从 python 脚本执行 psql 命令

python - 从 python 脚本执行 psql 命令

问题描述

我可以通过什么方式从 Python 脚本执行 psql 命令？我的代码从 PostgreSQL 数据库插入和更新数据（使用 psycopg2 和游标方法）。当我执行类似的查询时它工作正常

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

但是在 psql 的情况下，当我将命令传递给cursor.execute（参见对类似问题的编辑）时，我得到SyntaxError: syntax error at or near "\". 此外，该命令在 console.sql DataGRIP 中失败（无论如何它都会对所有行执行......）

此命令是否仅在 shell 中有效（我必须使用 os.system）吗？至于cursor是不是能够解释呢？

编辑 尝试使用子流程：

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'})

投掷OSError: [Errno 36] File name too long

编辑2

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

当我拆分查询尝试执行的所有参数时->它失败并显示syntax error at or near "\". -E（正如 Lu M 建议的那样）没有帮助。它适用于我将查询保存到 .sql 文件但我想以交互方式执行它的情况。

编辑3

根据--command=command 一章中的psql 教程，有两个选项可用于混合 SQL 和 psql 元命令。以什么方式可以将其包装在子流程中？我都尝试了，但出现了不需要的额外反斜杠，并且它不被识别为一个命令：

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'})

失败了unrecognized value ":is_uuid" for "\if expression": Boolean expected

标签： pythonsqlpostgresqlsubprocesspsql

解决方案

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.

推荐阅读