Python从数据库中读取数据并重写到另一个表中

Question

def store_locations(location):
    db= psycopg2.connect(database=DATABASE, user=USER, password=PASSWD, host=HOST, port=PORT)
    cursor = db.cursor()
    insert_query = "INSERT INTO account_locations2(location) VALUES (%s) ON CONFLICT (location) DO UPDATE SET location=EXCLUDED.location"
    cursor.execute(insert_query, (location))
    db.commit()
    cursor.close()
    db.close()
    return


def read_locations():
    db= psycopg2.connect(database=DATABASE, user=USER, password=PASSWD, host=HOST, port=PORT)
    cursor = db.cursor()
    cursor.execute("SELECT * FROM public_accounts")
    rows = cursor.fetchall()
    for row in rows:
        print(row[3])
        store_locations(row[3])
    db.commit()
    cursor.close()
    db.close()

read_locations()

我尝试使用 Python 从我的数据库中读取表的特定列。我刚打印的时候就成功了，显示了我想要的内容。但是当我试图将它恢复到另一个表时，它给了我一个错误:

IndexError                                Traceback (most recent call last)
<ipython-input-38-7012639773d6> in <module>()
 23     db.close()
 24 
---> 25 read_locations()

<ipython-input-38-7012639773d6> in read_locations()
 18         location=row[3]
 19         print(location)
---> 20         store_locations(location)
 21     db.commit()
 22     cursor.close()

<ipython-input-38-7012639773d6> in store_locations(location)
  3     cursor = db.cursor()
  4     insert_query = "INSERT INTO account_locations2(location) VALUES (%s) ON CONFLICT (location) DO UPDATE SET location=EXCLUDED.location"
----> 5     cursor.execute(insert_query, (location))
  6     db.commit()
  7     cursor.close()

IndexError: string index out of range

Answer 1

cursor.execute() 中的第二个参数应该是一个元组:

cursor.execute(insert_query, (location,))

根据 the documentation:

• For positional variables binding, the second argument must always be a sequence, even if it contains a single variable (remember that Python requires a comma to create a single element tuple)

请注意但是您可以像这样在单个查询中执行相同的操作:

INSERT INTO account_locations2(location) 
SELECT location FROM public_accounts
ON CONFLICT (location) DO NOTHING
-- when there is a conflict on location
-- then this makes no sense:
-- UPDATE SET location = EXCLUDED.location
-- as both are the same