python - 来自 python 的数据显示在一列而不是 ppostgresqll 上的两列

Question

表格正在显示，但所有数据都显示在 HY 下。我希望第 19 行显示在 HY 下，第 20 行显示在 AY 下。事实上，正在创建 AY 列，但所有行都显示 [null]

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import csv
from itertools import count
path = r'C:\Users\sammy\Downloads\E0.csv'
with open(path, "r") as csvfile:
    readCSV = csv.reader(csvfile, delimiter=",")
    firstline = 1
    con = None
    con = psycopg2.connect("host='localhost' dbname='football' user='postgres' password='XXX'")   
    cur = con.cursor()
    cur.execute("DROP TABLE testtest1234")
    cur.execute("CREATE TABLE testtest1234 (HY INTEGER, AY INTEGER)")

try:
    for row in readCSV:
        if firstline:
            firstline=0
            continue 
        new_data = row[19]
        newer_data = row[20]
        print(new_data)
        print(newer_data)
        cur.execute("INSERT INTO testtest1234 values ("+new_data+"), ("+newer_data+")")
except psycopg2.DatabaseError as e:
    if con:
        con.rollback() 
        print ("Error %s % e", e)
        sys.exit(1) 
finally:
    if con:
        con.commit()
        con.close()

print(" ".join(row))
out=open("new_data.csv", "w")
output = csv.writer(out)

for row in new_data:
    output.writerow(row)

out.close()

Answer 1

在我看来，您形成查询的方式不对，下面是如何让它发挥作用的方法。

创建要插入表中的数据元组:

data = (new_data, newer_data)

然后创建查询语句为:

query =  "INSERT INTO items (HY, AY) VALUES (%s, %s);"

然后您可以执行查询:

cursor = conn.cursor()
cursor.execute(query, data)

然后提交:

conn.commit()

您在此处编辑的代码:

for row in readCSV:
    if firstline:
        firstline=0
        continue
    new_data = row[19]
    newer_data = row[20]
    data = (new_data, newer_data)
    query = "INSERT INTO testtest1234 (HY, AY) VALUES (%s, %s);"
    print(new_data)
    print(newer_data)
    cursor = con.cursor()
    cursor.execute(query, data)