Executing Postgresql Query in Python

Question

Im trying to run the below postgres sql queries using Python.

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');"

The below is my Python Code:

        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');")

Getting the below error

user_13808319 · Answer

As noted by Adrian Klaver, \gset is available only in psql.

Try something like this, instead:

     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
     """)

Or, you can just nest the function call:

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

Executing Postgresql Query in Python

1 Answers