pandas:如何将数据帧导入postgresql数据库

Question

df1        
Matricule       Date Hours
0  10 2022-01-06 0
1  10 2022-01-07 0
2  10 2022-01-26 0
3  14 2022-01-03 0
4  14 2022-01-04 9
5  14 2022-01-05 7
6  14 2022-01-06 6
7  14 2022-01-07 5
8  14 2022-01-10 7
9  14 2022-01-11 6
10 14 2022-01-12 0
11 14 2022-01-13 9
12 14 2022-01-14 6
13 14 2022-01-17 0
14 14 2022-01-18 8
15 14 2022-01-19 8
16 14 2022-01-20 8
17 14 2022-01-21 5
18 14 2022-01-24 9
19 14 2022-01-25 3
20 14 2022-01-26 12
21 14 2022-01-27 10
22 14 2022-01-28 0
df2
Matricule FirstName Lastname Departement Sexe
10        Ben       aflick   Support     M
14        Angela    Dolberg  Support     F

这是我的两个数据帧，我想把它们加载到postgresql中，但数据库中的表总是空的，我想知道如何将它们链接到数据库，尽管postgres中的表与数据帧的名称不同这是postgresql表：表

我试过这个：

def execute_values(conn, df, table):

tuples = [tuple(x) for x in df.to_numpy()]

cols = ','.join(list(df.columns))
# SQL query to execute
query = "INSERT INTO %s(%s) VALUES %%s" % (table, cols)
cursor = conn.cursor()
try:
extras.execute_values(cursor, query, tuples)
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error: %s" % error)
conn.rollback()
cursor.close()
return 1
print("the dataframe is inserted")
cursor.close()


conn = psycopg2.connect(
database="test", user='postgres', password='pass', host='localhost', port='5432'
)

df2 = pd.read_csv('C:/Users/hp/Desktop/test.csv',sep=";",encoding='latin-1')

execute_values(conn, df2, "emp")

Answer 1

在我看来，try/except块的第一行不正确：

extras.execute_values(cursor, query, tuples)

我希望看到cursor.execute()或cursor.execute_batch()，而不是对您已经使用的函数的调用。