Error when storing a select psql statement to csv file on python

Question

I am trying a to save my select query into a csv file using python but i am getting below error. I am connecting to a postgres server , and running a selecting query then storing the result set to a csv file called resultsfile_yyyy/mm/dd.csv .

Traceback (most recent call last):
  File "d:\tests\campaigns.py", line 37, in <module>
    cur.copy_expert(outputquery, f)
psycopg2.errors.SyntaxError: syntax error at or near ")"
LINE 24: ) TO STDOUT WITH CSV HEADER

below is my code :

import sys
from datetime import datetime
#set up psycopg2 environment
import psycopg2
#driving_distance module
#note the lack of trailing semi-colon in the query string, as per the Postgres documentation
query = """
    WITH LOANS AS (SELECT SUBSCRIBER_FK, LOAN_ID, (CENTS_LOANED - CENTS_SERVICEQ) AS LEND_AMOUNT

          FROM   TBL_LOANS
          WHERE  (LOAN_TIME BETWEEN (CURRENT_DATE - 21) AND CURRENT_DATE - INTERVAL '1 SECOND')
          --test clause
          limit 100
         ),
REPAY AS (SELECT LOAN_FK, SUM(CENTS_PRINCIPAL) AS CENTS_PRINCIPAL
          FROM   TBL_LOANS_REPAY
          WHERE  (EVENT_TIME BETWEEN (CURRENT_DATE - 21) AND CURRENT_DATE - INTERVAL '1 SECOND')
          GROUP BY LOAN_FK
          --test clause
          limit 100
         )
SELECT (237000000000 + SUBSCRIBER_FK) AS MSISDN,
(SUM(LEND_AMOUNT) - SUM(COALESCE(CENTS_PRINCIPAL, 0)) + 100) AS DEBT_AMOUNT_PLUS_100
FROM REPAY AS R
RIGHT
JOIN LOANS AS L ON (L.LOAN_ID = R.LOAN_FK)
GROUP BY (237000000000 + SUBSCRIBER_FK)
ORDER BY DEBT_AMOUNT_PLUS_100 desc 
    )
"""
#make connection between python and postgresql
conn = psycopg2.connect(host="10.66.54.200", user="mdsa_superuser", password="mdsa_superuser", dbname="mdh_cm", port="37821" )
cur = conn.cursor()
outputquery = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(query)
datetoday=datetime.today().strftime('%Y-%m-%d')
with open('resultsfile_'+datetoday+'.csv', 'w') as f:
    cur.copy_expert(outputquery, f)

conn.close()

Dennis Muturia · Accepted Answer · 2020-10-30 07:35:01Z

1

Remove the ")" in

query = """
WITH LOANS AS (SELECT SUBSCRIBER_FK, LOAN_ID, (CENTS_LOANED - CENTS_SERVICEQ) AS LEND_AMOUNT

      FROM   TBL_LOANS
      WHERE  (LOAN_TIME BETWEEN (CURRENT_DATE - 21) AND CURRENT_DATE - INTERVAL '1 SECOND')
      --test clause
      limit 100
     ),

REPAY AS (SELECT LOAN_FK, SUM(CENTS_PRINCIPAL) AS CENTS_PRINCIPAL FROM TBL_LOANS_REPAY WHERE (EVENT_TIME BETWEEN (CURRENT_DATE - 21) AND CURRENT_DATE - INTERVAL '1 SECOND') GROUP BY LOAN_FK --test clause limit 100 ) SELECT (237000000000 + SUBSCRIBER_FK) AS MSISDN, (SUM(LEND_AMOUNT) - SUM(COALESCE(CENTS_PRINCIPAL, 0)) + 100) AS DEBT_AMOUNT_PLUS_100 FROM REPAY AS R RIGHT JOIN LOANS AS L ON (L.LOAN_ID = R.LOAN_FK) GROUP BY (237000000000 + SUBSCRIBER_FK) ORDER BY DEBT_AMOUNT_PLUS_100 desc )##Remove this """

answered Oct 30, 2020 at 7:35

Dennis Muturia

263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

c0p13 · Accepted Answer · 2020-10-30 07:25:06Z

Replace your query with this

WITH LOANS AS (SELECT SUBSCRIBER_FK, LOAN_ID, (CENTS_LOANED - CENTS_SERVICEQ) AS LEND_AMOUNT FROM   TBL_LOANS WHERE  (LOAN_TIME BETWEEN (CURRENT_DATE - 21) AND CURRENT_DATE - INTERVAL '1 SECOND')
          --test clause
          limit 100
         ),
REPAY AS (SELECT LOAN_FK, SUM(CENTS_PRINCIPAL) AS CENTS_PRINCIPAL
          FROM   TBL_LOANS_REPAY
          WHERE  (EVENT_TIME BETWEEN (CURRENT_DATE - 21) AND CURRENT_DATE - INTERVAL '1 SECOND')
          GROUP BY LOAN_FK
          --test clause
          limit 100
         )
SELECT (237000000000 + SUBSCRIBER_FK) AS MSISDN,
(SUM(LEND_AMOUNT) - SUM(COALESCE(CENTS_PRINCIPAL, 0)) + 100) AS DEBT_AMOUNT_PLUS_100
FROM REPAY AS R
RIGHT JOIN LOANS AS L ON (L.LOAN_ID = R.LOAN_FK)
GROUP BY 1
ORDER BY 2 desc

Collectives™ on Stack Overflow

Error when storing a select psql statement to csv file on python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related