python EOF中的SQL

Question

我正在使用已经写入的SQL DB2代码将数据拉入我的Python程序（在Jupyter笔记本中） - 我只是将它粘贴到三重引号字符串中并打开一个JDBC连接使用Jaydebe图书馆。这工作得很好，但对于更复杂的拉我不断得到一个“EOF在多行字符串”错误。我已经证实，当我将它粘贴到WinSQL中时，SQL运行正常，所以我不认为这是一个缺少括号或者沿着这些线的问题。我将粘贴代码块，然后是错误。

curs = conn.cursor() 
curs.execute("""/* Determine Final Payment Date for Closed Claims */ 

declare global temporary table session.cause_close_dt (
    hcode varchar(25), 
    claim_number varchar(15), 
    spec_cov char(2), 
    cause_of_loss char(2), 
    final_pmt_dt date) 
with replace partitioning key (hcode) using hashing on commit preserve rows; 

insert into session.cause_close_dt (
    hcode, 
    claim_number, 
    spec_cov, 
    cause_of_loss, 
    final_pmt_dt) 

select distinct 
    hcode, 
    state||pui||division||trim(claim_number) as claim_number, 
    spec_cov, 
    cause_of_loss, 
    max(date_of_final_payment) as final_pmt_dt 
from 
    v23.fire_los_v 
where 
    state in('05','55','75') 
    and hcode in('022###') 
    and payment_status in('1','A') 
    and date_of_final_payment is not null 
    and year(date_of_final_payment) > 2004 
group by 
    hcode, 
    state||pui||division||trim(claim_number), 
    spec_cov, 
    cause_of_loss 
order by 
    1,2,3,4 

/* Summarize Loss Data by Closed Date */ 
select 
    state, 
    hcode, 
    cause_close_yr, 
    cause_close_qtr, 
    kind_of_loss, 
    sum(paid_cause_cnt_ncat) as ncpd_cause_cnt, 
    sum(paid_amt_tot_ncat) as ncpd_amt 
from (
    select 
    u92.statename_u(a.state) as state, 
    case 
     when a.hcode in('022###') then 'PIM' 
     else '' 
    end as hcode, 
    a.year, 
    a.state||a.pui||a.division||trim(a.claim_number) as claim_number, 
    a.kol_sum as kind_of_loss, 
    a.cause_of_loss, 
    a.spec_cov, 
    b.final_pmt_dt, 
    year(b.final_pmt_dt) as cause_close_yr, 
    quarter(b.final_pmt_dt) as cause_close_qtr, 
    paid_cause_cnt_ncat, 
    paid_amt_tot_ncat 
    from 
    v23.fire_los_v a 
    left outer join session.cause_close_dt b on 
     a.hcode = b.hcode 
     and a.state||a.pui||a.division||trim(a.claim_number) = b.claim_number 
     and a.cause_of_loss = b.cause_of_loss 
     and a.spec_cov = b.spec_cov 
    where 
    a.state in('05','55','75') 
    and a.hcode in('022###')) 
where 
    cause_close_yr > 2004 
group by 
    state, 
    hcode, 
    cause_close_yr, 
    cause_close_qtr, 
    kind_of_loss 
order by 1,2,5,3,4; 
""") 
test = curs.fetchall() 

我得到的错误如下：

ERROR: An unexpected error occurred while tokenizing input 
The following traceback may be corrupted or invalid 
The error message is: ('EOF in multi-line string', (1, 0)) 


com.ibm.db2.jcc.am.SqlSyntaxErrorExceptionPyRaisableTraceback (most recent call last) 
<ipython-input-32-90277cac8343> in <module>() 
    85 kind_of_loss 
    86 order by 1,2,5,3,4; 
---> 87 """) 
    88 test = curs.fetchall() 

/opt/anaconda/4.1.1/lib/python2.7/site-packages/jaydebeapi/__init__.pyc in execute(self, operation, parameters) 
    495    parameters =() 
    496   self._close_last() 
--> 497   self._prep = self._connection.jconn.prepareStatement(operation) 
    498   self._set_stmt_parms(self._prep, parameters) 
    499   try: 

com.ibm.db2.jcc.am.SqlSyntaxErrorExceptionPyRaisable: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=; 

insert into session.cause_clos;commit preserve rows;<distribute_by>, DRIVER=4.15.122 

错误总是指向与三重引号的最后一行。我不确定SQL的间隔是否会抛出异常。我已经尝试了一些东西，如更改间距和删除分号，但似乎没有任何工作。

Answer 1

该问题试图在一个execute（）语句中运行带有多个选择的SQL语句。这是我的最终代码的样子：

curs = conn.cursor() 
curs.execute("""declare global temporary table session.cause_close_dt (
    hcode varchar(25), 
    claim_number varchar(15), 
    spec_cov char(2), 
    cause_of_loss char(2), 
    final_pmt_dt date) 
with replace partitioning key (hcode) using hashing on commit preserve rows""") 

curs.execute("""insert into session.cause_close_dt (
    hcode, 
    claim_number, 
    spec_cov, 
    cause_of_loss, 
    final_pmt_dt) 

select distinct 
    hcode, 
    state||pui||division||trim(claim_number) as claim_number, 
    spec_cov, 
    cause_of_loss, 
    max(date_of_final_payment) as final_pmt_dt 
from 
    v23.fire_los_v 
where 
    state in('05','55','75') 
    and hcode in('022###') 
    and payment_status in('1','A') 
    and date_of_final_payment is not null 
    and year(date_of_final_payment) > 2004 
group by 
    hcode, 
    state||pui||division||trim(claim_number), 
    spec_cov, 
    cause_of_loss 
order by 
    1,2,3,4""") 

curs.execute(""" 
select 
    state, 
    hcode, 
    cause_close_yr, 
    cause_close_qtr, 
    kind_of_loss, 
    sum(paid_cause_cnt_ncat) as ncpd_cause_cnt, 
    sum(paid_amt_tot_ncat) as ncpd_amt 
from (
    select 
    u92.statename_u(a.state) as state, 
    case 
     when a.hcode in('022###') then 'PIM' 
     else '' 
    end as hcode, 
    a.year, 
    a.state||a.pui||a.division||trim(a.claim_number) as claim_number, 
    a.kol_sum as kind_of_loss, 
    a.cause_of_loss, 
    a.spec_cov, 
    b.final_pmt_dt, 
    year(b.final_pmt_dt) as cause_close_yr, 
    quarter(b.final_pmt_dt) as cause_close_qtr, 
    paid_cause_cnt_ncat, 
    paid_amt_tot_ncat 
    from 
    v23.fire_los_v a 
    left outer join session.cause_close_dt b on 
     a.hcode = b.hcode 
     and a.state||a.pui||a.division||trim(a.claim_number) = b.claim_number 
     and a.cause_of_loss = b.cause_of_loss 
     and a.spec_cov = b.spec_cov 
    where 
    a.state in('05','55','75') 
    and a.hcode in('022###')) 
where 
    cause_close_yr > 2004 
group by 
    state, 
    hcode, 
    cause_close_yr, 
    cause_close_qtr, 
    kind_of_loss 
order by 1,2,5,3,4 
""") 
test = curs.fetchall()