英文:
psycopg2.errors.UndefinedColumn when trying to insert data into postgres database
问题
我有一个带有来自CSV文件的数据的pandas数据框架。
我想将这些数据导入到我的PostgreSQL数据库。
import pandas as pd
import psycopg2
# 导入CSV文件，创建数据框
data = pd.read_csv('my_csv.csv', delimiter=';')
df = pd.DataFrame(data)
# 准备数据（重命名/缩短列标题）
columnsFromCSV = list(df.columns)
for i in columnsFromCSV:
columnName = i.rsplit(None, 2)[0]
df.rename(columns={i: columnName}, inplace=True)
df.columns = df.columns.str.lower()
# 连接到数据库
conn = psycopg2.connect(
database='database', user='postgres', password='admin', host='127.0.0.1', port='5432'
)
cursor = conn.cursor()
# 将数据框插入数据库
for i in df.columns[1:]:
cursor.execute('INSERT INTO counter (counterid) VALUES ({0})'.format(i))
for j in range(365):
cursor.execute('INSERT INTO measurements (counterid) VALUES({0})'.format(i))
conn.commit()
conn.close()
首先，我想在counter表中存储每个计数器一次，然后我想在measurements表中存储一年的每日测量数据，包括counterid，每日测量数据将在稍后插入。
这是我收到的错误消息：
Traceback (most recent call last):
File "c:\EnergyCounter\EnergyCounter\backend\CSV_read_script.py", line 25, in <module>
cursor.execute('INSERT INTO counter (counterid) VALUES ({0})'.format(i))
psycopg2.errors.UndefinedColumn: ERROR: Column "counter1" does not exist
LINE 1: INSERT INTO counter (counterid) VALUES (counter1)
我尝试过小写，引用表名，使用"%s"而不是".format"。
英文:
I have a pandas data framework which has data from a csv file.
I want to import that data into my postgres database.
import pandas as pd
import psycopg2
# Import CSV, create Data Frame
data = pd.read_csv('my_csv.csv', delimiter=';')
df = pd.DataFrame(data)
# Prepare Data (Rename / Shorten Column-Headers)
columnsFromCSV = list(df.columns)
for i in columnsFromCSV:
columnName = i.rsplit(None, 2)[0]
df.rename(columns={i : columnName}, inplace=True)
df.columns = df.columns.str.lower()
# Connect to Database
conn = psycopg2.connect(
database='database', user='postgres', password='admin', host='127.0.0.1', port= '5432'
)
cursor = conn.cursor()
# Insert Data Frame into Database
for i in df.columns[1:]:
cursor.execute('INSERT INTO counter (counterid) VALUES ({0})'.format(i))
for j in range(365):
cursor.execute('INSERT INTO measurements (counterid) VALUES({0})'.format(i))
conn.commit()
conn.close()
First I want to store every counter once in the counter table and after that I want to store the daily measurements of one year in the measurements table together with the counterid, the daily measurements will be inserted at a later time.
Thats the error message I'm getting:
Traceback (most recent call last):
File "c:\EnergyCounter\EnergyCounter\backend\CSV_read_script.py", line 25, in <module>
cursor.execute('INSERT INTO counter (counterid) VALUES ({0})'.format(i))
psycopg2.errors.UndefinedColumn: ERROR: Column »counter1« does not exist
LINE 1: INSERT INTO counter (counterid) VALUES (counter1)
Tried lower case, quoting tablenames, using "%s" instead of ".format".
答案1
得分: 0
答案是使用命名参数：https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries
现在的代码看起来是这样的，它可以正常工作：
# 将数据框插入数据库
for i in df.columns[1:]:
cursor.execute('''INSERT INTO counter (counterid) VALUES (%(columnName)s)''', {'columnName': i})
for j in range(365):
cursor.execute('''INSERT INTO measurements (counterid) VALUES(%(columnName)s)''', {'columnName': i})
conn.commit()
conn.close()
英文:
The answer is to use named arguments: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries
The code now looks like this and it works:
# Insert Data Frame into Database
for i in df.columns[1:]:
cursor.execute('''INSERT INTO counter (counterid) VALUES (%(columnName)s)''', {'columnName': i})
for j in range(365):
cursor.execute('''INSERT INTO measurements (counterid) VALUES(%(columnName)s)''', {'columnName': i})
conn.commit()
conn.close()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论