时间戳

首页 > 解决方案 > 在 python 上将 select psql 语句存储到 csv 文件时出错

python - 在 python 上将 select psql 语句存储到 csv 文件时出错

问题描述

我正在尝试使用 python 将我的选择查询保存到 csv 文件中，但我遇到了错误。我正在连接到 postgres 服务器，并运行选择查询，然后将结果集存储到名为 resultsfile_yyyy/mm/dd.csv 的 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

下面是我的代码：

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

标签： pythoncsv

解决方案

删除“）”在

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 子句限制 100 ) SELECT (23700000000000+ , (SUM(LEND_AMOUNT) - SUM(COALESCE(CENTS_PRINCIPAL, 0)) + 100) 作为 DEBT_AMOUNT_PLUS_100 从 RIGHT JOIN 贷款中偿还作为 LON (L.LOAN_ID = R.LOAN_FK) GROUP BY (237000000000 + SUBSCRIBER_FK) ORDER BY DEBT_AMOUNT desc )##删除这个 """

推荐阅读