读取多个excel文件并将其插入到PosgreSQL数据库中的表中

Question

我将读取位于PC中特定文件夹中的多个excel文件，这些文件的结构相同，然后将它们插入PostgreSQL中的DB表中。我正在读取excel文件，但是，在cur.execute节中出现了一个错误

Excel文件示例：

还有我的剧本：

import pandas as pd
import glob
import psycopg2
import numpy
from psycopg2.extensions import register_adapter, AsIs

def addapt_numpy_float64(numpy_float64):
    return AsIs(numpy_float64)
def addapt_numpy_int64(numpy_int64):
    return AsIs(numpy_int64)
register_adapter(numpy.float64, addapt_numpy_float64)
register_adapter(numpy.int64, addapt_numpy_int64)

def connect_db(frame):
    # database connection

    print("DB opened successfully")
    sqlpart(conn, frame)


def sqlpart(conn,frame):
    cur = conn.cursor()

    sql="""INSERT INTO actuals (timeslot,unit_id,actual) 
            VALUES(%s,%s,%s) 
            ON CONFLICT (timeslot,unit_id,actual)
            DO UPDATE SET  timeslot=%s, unit_id=%s,actual=%s"""

    conn.commit()
    values=[]
    da=[]
    for x in range(len(frame)):
        da=frame[0][x],  frame[1][x],  frame[2][x]
        values.append(da)
        print(values)
    cur.execute(sql, values)
    print("everything is done")

def patika():

    path = r'mypath'
    all_files = glob.glob(path + "/*.xlsx")

    li = []

    for filename in all_files:
        df = pd.read_excel(filename, index_col=None,header=None)
        li.append(df)

    frame = pd.concat(li, axis=0, ignore_index=True)
    #print(frame)--->> [(Timestamp('2020-03-01 00:00:00'), 200, -1000),....] values that comes from excel like this

    connect_db(frame)


patika()

输出：

cur.execute(sql, values)
TypeError: not all arguments converted during string formatting

Answer 1

您的查询有六个需要绑定的参数。正如您在注释中所验证的，绑定元组只包含三个元素：

sql="""INSERT INTO actuals (timeslot,unit_id,actual) 
            VALUES(%s,%s,%s) 
            ON CONFLICT (timeslot,unit_id,actual)
            DO UPDATE SET  timeslot=%s, unit_id=%s,actual=%s"""

请将您的insert更新为：

sql="""INSERT INTO actuals (timeslot,unit_id,actual) 
            VALUES(%s,%s,%s) 
            ON CONFLICT (timeslot,unit_id,actual)
            DO NOTHING"""

如果您的表在(timeslot, unit_id)上是唯一的，那么您的insert将是：

sql="""INSERT INTO actuals (timeslot,unit_id,actual) 
            VALUES(%s,%s,%s) 
            ON CONFLICT (timeslot,unit_id)
            DO UPDATE SET actual = EXCLUDED.actual"""

更新 仔细阅读后，您的代码中存在多个问题：

def sqlpart(conn,frame):
    cur = conn.cursor()

    sql="""INSERT INTO actuals (timeslot,unit_id,actual) 
            VALUES(%s,%s,%s) 
            ON CONFLICT (timeslot,unit_id,actual)
            DO UPDATE SET  timeslot=%s, unit_id=%s,actual=%s"""

    conn.commit()
    values=[]
    da=[]
    for x in range(len(frame)):
        da=frame[0][x],  frame[1][x],  frame[2][x]
        values.append(da)
        print(values)
    cur.execute(sql, values)
    print("everything is done")

首先，您的conn.commit()在执行insert之后没有被执行

其次，您正试图将list个tuple对象传递给insert执行

请尝试以下方式：

def sqlpart(conn,frame):
    cur = conn.cursor()

    sql="""INSERT INTO actuals (timeslot,unit_id,actual) 
            VALUES(%s,%s,%s) 
            ON CONFLICT (timeslot,unit_id,actual)
            DO NOTHING"""

    #values=[]
    #da=[]
    for x in range(len(frame)):
        da=frame[0][x],  frame[1][x],  frame[2][x]
        #values.append(da)
        #print(values)
        cur.execute(sql, da)
    conn.commit()
    print("everything is done")