使用pandas数据帧更新红移失败，字符串索引超出范围

Question

我试图用psycopg2和psycopg2.extras更新一个红移表，但是失败了，出现以下错误。有人能帮忙解决这个错误吗？

{
  "errorMessage": "string index out of range",
  "errorType": "IndexError",
  "stackTrace": [
    "  File \"/var/task/lambda_function.py\", line 110, in lambda_handler\n    psycopg2.extras.execute_values (cursor, update_query, row, template=None, page_size=2000)\n",
    "  File \"/opt/python/lib/python3.8/site-packages/psycopg2/extras.py\", line 1289, in execute_values\n    parts.append(cur.mogrify(template, args))\n"
  ]
}

我有一个23列的数据帧，我正在尝试在AWS Lambda中更新如下。与数据库的连接成功，但更新失败：

import psycopg2
import psycopg2.extras

df_pandas ## dataframe with 23 columns and 28 rows
connection = psycopg2.connect(host='casuc', dbname='skhcbiw',
                                             user='cksbci', password='****', port=0000)
                cursor = connection.cursor()
                #UPDATE: INSTEAD OF ITERTUPLES, I HAVE REPLACED IT with ITERROWS WHICH HAS GIVEN A DIFFERENT ERROR FROM THE ONE ABOVE. ERROR specified below the CODE
                for _, row in df_pandas.iterrows():
                    row = str(tuple(row)) #create a tuple that is a string
                    row = row[1:len(row)-1] #remove the beginning & ending ()
                    print(row)
                
                    update_query = """UPDATE table AS t 
                                      SET column1 = e.column1, column2 = e.column2, column3 = e.column3, 
                                          ......................................................
                                          ......................................................
                                          column22 = e.column22, column23 = e.column23
                                      FROM (VALUES %s) AS e('column1', 'column2', 'column3',
                                                             .......................................
                                                             .......................................
                                                             .......................................
                                                             .......................................
                                                             'column21', 'column22', 'column23') 
                                      WHERE e.column23 = t.column23;"""
                    psycopg2.extras.execute_values (cursor, update_query, row, template=None, page_size=2000)

新错误

{
  "errorMessage": "syntax error at or near \")\"\nLINE 9: ...','1','7','6','5','1','''',',',' ','0','.','0',',',' ','0'))\n                                                                      ^\n",
  "errorType": "SyntaxError",
  "stackTrace": [
    "  File \"/var/task/lambda_function.py\", line 118, in lambda_handler\n    psycopg2.extras.execute_values(cursor, update_query, (row, ), template=None, page_size=2000)\n",
    "  File \"/opt/python/lib/python3.8/site-packages/psycopg2/extras.py\", line 1292, in execute_values\n    cur.execute(b''.join(parts))\n"
  ]
}

我的输入行如下

'Zone 99', 'J005', 'Accepted', 'BIWUDBI', 'MNO101', '90.00H50 IUHIUH   YY 55RR', '878767', 0, 'Knoidci', 'A99', 0.0, 0, '2192238', '2020-12-31', 0.0, 0.0, 0.0, 0, 0, 0, '50017651', 0.0, 0

我看到数据“50017651”的每个值都被传递为“5”、“0”、“0”……。我不知道是什么原因？

我从StackOverflow推荐了这2个URL来解决我的问题，但运气不好。

1. psycopg2：使用元组元组中的值更新表中的多行
2. 如何使用熊猫数据框更新后记表列？

谢谢Ganesh

Answer 1

上面的代码不起作用，因此我决定使用链接中提到的方法：如何使用熊猫数据帧更新Postgres表列？

方法是创建一个TEMP TABLE并将数据转储/插入到该表中，然后更新我想使用此TEMP TABLE的表。一旦连接提交，AWS Redshift中的TEMP TABLE将自动删除。我们不需要在TEMP TABLE代码中指定“ON COMMIT DROP”。

import psycopg2

conn = psycopg2.connect("dbname='db' user='user' host='localhost' password='test'")
cur = conn.cursor()

rows = zip(df.id, df.z)
cur.execute("""CREATE TEMP TABLE codelist(id INTEGER, z INTEGER) ON COMMIT DROP""")
cur.executemany("""INSERT INTO codelist (id, z) VALUES(%s, %s)""", rows)

cur.execute("""
    UPDATE table_name
    SET z = codelist.z
    FROM codelist
    WHERE codelist.id = vehicle.id;
    """)

cur.rowcount
conn.commit()
cur.close()
conn.close()

问候加内什·巴特