英文:
psycopg2.errors.UndefinedColumn when trying to insert data into postgres database
问题
我有一个带有来自CSV文件的数据的pandas数据框架。
我想将这些数据导入到我的PostgreSQL数据库。
import pandas as pdimport 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 existLINE 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 pdimport psycopg2# Import CSV, create Data Framedata = 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 Databaseconn = psycopg2.connect(database='database', user='postgres', password='admin', host='127.0.0.1', port= '5432')cursor = conn.cursor()# Insert Data Frame into Databasefor 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 existLINE 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 Databasefor 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()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论