Блог

Я пытаюсь сохранить свой запрос select в CSV-файл с помощью python, но получаю сообщение об ошибке ниже. Я подключаюсь к серверу postgres и запускаю запрос выбора, а затем сохраняю результирующий набор в файл csv с именем resultsfile_yyyy/mm / dd.csv .

 Traceback (most recent call last):
  File "d:testscampaigns.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
  

ниже приведен мой код :

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

Удалите «)» в

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

ПОГАСИТЬ КАК (ВЫБЕРИТЕ LOAN_FK, SUM(CENTS_PRINCIPAL) КАК CENTS_PRINCIPAL
ИЗ TBL_LOANS_REPAY
ГДЕ (ВРЕМЯ СОБЫТИЯ МЕЖДУ (CURRENT_DATE — 21) И CURRENT_DATE — ИНТЕРВАЛ ‘1 СЕКУНДА’)
ГРУППА ПО LOAN_FK
—
ограничение тестового предложения 100
)
ВЫБЕРИТЕ (237000000000 SUBSCRIBER_FK) КАК MSISDN,
(SUM(LEND_AMOUNT) — SUM(ОБЪЕДИНИТЬ (CENTS_PRINCIPAL, 0)) 100) КАК DEBT_AMOUNT_PLUS_100
ИЗ REPAY КАК R
ПРАВИЛЬНО
ПРИСОЕДИНИТЕ LOANS КАК L ON (L.LOAN_ID = R.LOAN_FK)
ГРУППА ПО (237000000000 SUBSCRIBER_FK)
ПОРЯДОК ПО DEBT_AMOUNT_PLUS_100 desc
)##Удалить это
«»»

Замените ваш запрос на это

 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