将sql表放入python时出错。怎么了？

Question

我想知道这个问题是怎么回事。我不能把它放到一个大熊猫的数据框里。错误描述为“IndexError:元组索引超出范围”

  i = 3
  query = ("SELECT COL1, COL2"
  "FROM whatever "
  " where SUBSTR(DATE,1,{}) = '201901' ").format(i)

   df = pd.read_sql(query,con)

问题在于格式。我需要将“I”放在查询中以获取不同的日期，然后将它们分组到一个数据帧中。你知道吗

Answer 1

问题是（在编辑之前查看原始问题中的查询），在format（）方法调用中有两个字符串替换，但只有一个值。如果要在查询中的两个{}中插入i，应该将它们更改为{0}。另外，给你一个提示，省去一些麻烦，多行字符串使用''''。你知道吗

for i in [1,2,3,4]:
    query = ('''SELECT FORMALIZADO, AMORTIZADO, PDT_AMORTIZACION, LSCRV, SUM(DESCUBIERTO) AS T_FORMALIZADO, SUM(CAPITAL_PENDIENTE) AS T_PDT_AMORTIZACION, COUNT(*) as CASOS "
             FROM
             (SELECT
                CASE WHEN DESCUBIERTO <= 1000 THEN 1000
                WHEN DESCUBIERTO >= 1001 AND DESCUBIERTO <=2000 THEN 2000 
                WHEN DESCUBIERTO >=2001 AND DESCUBIERTO <=3000 THEN 3000 
                WHEN DESCUBIERTO >= 3001 AND DESCUBIERTO <= 4000 THEN 4000
                WHEN DESCUBIERTO >= 4001 AND DESCUBIERTO <=5000 THEN 5000 
                WHEN DESCUBIERTO >=5001 AND DESCUBIERTO <=6000 THEN 6000 
                WHEN DESCUBIERTO >= 6001 AND DESCUBIERTO <= 7000 THEN 7000
                WHEN DESCUBIERTO >= 7001 AND DESCUBIERTO <=8000 THEN 8000 
                WHEN DESCUBIERTO >=8001 AND DESCUBIERTO <=9000 THEN 9000 
                WHEN DESCUBIERTO >= 9001 AND DESCUBIERTO <= 10000 THEN 10000 
                WHEN DESCUBIERTO >= 10001 AND DESCUBIERTO <=11000 THEN 11000 
                WHEN DESCUBIERTO >=11001 AND DESCUBIERTO <=12000 THEN 12000 
                WHEN DESCUBIERTO >= 12001 AND DESCUBIERTO <= 13000 THEN 13000 
                WHEN DESCUBIERTO >= 13001 AND DESCUBIERTO <= 14000 THEN 14000 
                WHEN DESCUBIERTO >= 14001 AND DESCUBIERTO <=15000 THEN 15000 
                WHEN DESCUBIERTO >= 15001 THEN 15001 
                ELSE NULL END AS FORMALIZADO, 
                CASE WHEN CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0) <= 0.25 THEN 'Q1' 
                WHEN CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0) >= 0.26 AND CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)  <= 0.50 THEN 'Q2' 
                WHEN CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0) >= 0.51 AND CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)  <= 0.75 THEN 'Q3' 
                WHEN CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0) >= 0.76 AND CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0) <= 1 THEN 'Q4' 
                ELSE NULL END AS AMORTIZADO, 
                CASE WHEN (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)) <= 0.25 THEN 'Q1' 
                WHEN (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)) >= 0.26 AND (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0))  <= 0.50 THEN 'Q2'
                WHEN (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)) >= 0.51 AND (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0))  <= 0.75 THEN 'Q3'
                WHEN (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)) >= 0.76 AND (1-CAPITAL_PENDIENTE / NULLIF(DESCUBIERTO,0)) <= 1 THEN 'Q4'
                ELSE NULL END AS PDT_AMORTIZACION,
                L.LSCRV, DESCUBIERTO, CAPITAL_PENDIENTE
                FROM ON5H C LEFT JOIN CRV L ON SUBSTR(L.CLIENTE, 1,12) = C.CLIENTE
                WHERE SUBSTR(C.FCH_FINANCIACION,1,6) = TO_CHAR(ADD_MONTHS(SYSDATE,{0}),'YYYYMM') AND NOMBRE_PRODUCTO = 'YM' AND TO_CHAR(C.FECHA_BASE, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE,{0}),'YYYYMM') AND POSIC_ACTUAL = '010')
             GROUP BY LSCRV, FORMALIZADO, PDT_AMORTIZACION, AMORTIZADO
             ORDER BY LSCRV''').format(i)

Answer 2

据我所知，你有两个问题，第一个是打字错误： 您忘记在连接之后添加空格，因此它找不到任何列

i = 3
query = ("SELECT COL1, COL2" # Here you forgot to add a space, the interpreter will read it as COL2FROM
"FROM whatever "
"where SUBSTR(DATE,1,{}) = '201901' ").format(i)

df = pd.read_sql(query,con)

第二件事，您使用SUBSTR所做的操作将不会返回任何内容，因为它只返回3个字符，您可以将其与6进行比较，因此将i更改为6或使其成为动态的。 另外，在对结果表执行任何操作之前，我建议您使用len(df)函数获取它的长度，因此它应该如下所示：

i = 6
query = ("SELECT COL1, COL2 "
"FROM whatever "
"where SUBSTR(DATE,1,{}) = '201901' ").format(i)

df = pd.read_sql(query,con)
if len(df):
    print(df[0]) # Prints the first item
else
    print("No items were found")