python - 在 Python 中执行 Postgresql 查询

Question

我正在尝试使用 Python 运行以下 postgres sql 查询。

SELECT aws_commons.create_s3_uri('data-analytics-bucket02-dev','output','ap-southeast-1') AS s3_uri_1\gset

“SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM sample_table', :'s3_uri_1');”

下面是我的Python代码:

        print('PostgreSQL database version:')
        # cur.execute('SELECT version()')
        select_query = "SELECT aws_commons.create_s3_uri('data-analytics-bucket02-dev','output','ap-southeast-1') AS s3_uri_1 \gset"
        cur.execute(select_query)
        cur.execute("SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM sample_table', :'s3_uri_1');")

出现以下错误

Answer 1

正如 Adrian Klaver 所指出的，\gset 仅在 psql 中可用。

尝试这样的事情，而不是:

     cur.execute("""
       with get_uri as (
         select aws_commons.create_s3_uri(
                 'data-analytics-bucket02-dev',
                 'output',
                 'ap-southeast-1') AS s3_uri_1
       )
       select e.* 
         from get_uri g
              cross join lateral aws_s3.query_export_to_s3(
                                   'select * from sample_table', 
                                   g.s3_uri_1
                                 ) e
     """)

或者，您可以嵌套函数调用:

     cur.execute("""
       select *
         from aws_s3.query_export_to_s3(
                'select * from sample_table', 
                aws_commons.create_s3_uri(
                  'data-analytics-bucket02-dev',
                  'output',
                  'ap-southeast-1'
                )
              )  
     """)