PyGreSQL以交互方式运行SQL

Question

我有一个GreenPlum数据库，并且我正在尝试创建几个外部表。

我使用PyGreSQL包与数据库进行交互。

然而，我无法摆脱\i (或者\dt，\du，\dv)。

db.query("\i h1/ext/eng/ext_eng" + i + ".sql")

pg.ProgrammingError: ERROR: syntax error at or near "\"

我尝试过"\\i"、" '\i' "和其他组合。

Answer 1

我不确定这些别名是否可以被普通客户端使用。

您可以通过以下方法避免\i：首先使用Python读取文件h1/ext/eng/ext_eng.sql，然后使用db.query()将生成的SQL文本流式传输到PyGreSQL。

至于\dt、\du和\dv -可以通过在psql提示符下运行\set ECHO_HIDDEN来查看SQL等效项，然后发出每个命令来查看真正的定义。我已经将结果包含在下面。

\dt：

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' WHEN 'p' THEN 'parquet' WHEN 'f' THEN 'foreign' END as "Storage"

FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND c.relstorage IN ('h', 'a', 'c', 'p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

\du：

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
ORDER BY 1;

\dv：

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' WHEN 'p' THEN 'parquet' WHEN 'f' THEN 'foreign' END as "Storage"

FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
AND c.relstorage IN ('v','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

这并不理想，但您可以用这些代码等效项替换\d引用。