使用psycopg2将数据导入postgres表时出错 [英] Value Error while importing data into postgres table using psycopg2
问题描述
我有一个元组如下
data =({'气象台名称':'变电站','风' :'6公里/小时','晴雨表':'1010.3hPa','湿度':'42%','温度':'34.5 C','place_id':'001D0A00B36E',' 05-10 09:48:58'})
我试图推动上面的值元组到postgres表使用以下代码:
try:
con = psycopg2.connect(dbname = WeatherForecast'user ='postgres'host ='localhost'password ='****')
cur = con.cursor()
cur.executemany(INSERT INTO weather_data(temperature,humidity,wind,barometer,updated_on,place_id)VALUES(%(temperature)f,%(humidity)f,%(wind)f,% )f,%(date)s,%(place_id)d),final_weather_data)
ver = cur.fetchone()
print(ver)
除了psycopg2 .DatabaseError为e:
print('Error {}'。format(e))
sys.exit(1)
finally:
if con:
con.close()
数据库中每个字段的数据类型如下所示:
id serial NOT NULL,
温度双精度NOT NULL,
湿度双精度NOT NULL,
风双精度NOT NULL,
气压计双精度NOT NULL,
updated_on时区与时区NOT NULL,
place_id整数NOT NULL,
当我运行代码使用psycopg2将数据推送到postgres表中时,它会引发错误ValueError:不受支持的格式字符'f'
/ p>
我希望问题出在格式化。使用Python3.4
看看文档:
变量占位符必须始终为
%s
,即使不同的占位符(如%d
用于整数或>> ;> cur.execute(INSERT INTO numbers VALUES(%d),(42,))#WRONG
>>> cur.execute(INSERT INTO numbers VALUES(%s),(42,))#correct
虽然,您的SQL查询包含所有类型的占位符:
INSERT INTO weather_data (温度,湿度,风,晴雨表,updated_on,place_id)
值(%(温度)f,%(湿度)f,%(风)f,%(晴雨表)f,%(日期) (place_id)d)
I have a tuple as below
data = ({'weather station name': 'Substation', 'wind': '6 km/h', 'barometer': '1010.3hPa', 'humidity': '42%', 'temperature': '34.5 C', 'place_id': '001D0A00B36E', 'date': '2016-05-10 09:48:58'})
I am trying to push the values from the above tuple to the postgres table using the code below:
try:
con = psycopg2.connect("dbname='WeatherForecast' user='postgres' host='localhost' password='****'")
cur = con.cursor()
cur.executemany("""INSERT INTO weather_data(temperature,humidity,wind,barometer,updated_on,place_id) VALUES (%(temperature)f, %(humidity)f, %(wind)f, %(barometer)f, %(date)s, %(place_id)d)""", final_weather_data)
ver = cur.fetchone()
print(ver)
except psycopg2.DatabaseError as e:
print('Error {}'.format(e))
sys.exit(1)
finally:
if con:
con.close()
Where datatype of each field in the DB is as below: id serial NOT NULL,
temperature double precision NOT NULL,
humidity double precision NOT NULL,
wind double precision NOT NULL,
barometer double precision NOT NULL,
updated_on timestamp with time zone NOT NULL,
place_id integer NOT NULL,
When i run the code to push the data into postgres table using psycopg2, it is raising an error "ValueError: unsupported format character 'f'"
I hope the issue is in formatting. Am using Python3.4
Have a look at the documentation:
The variables placeholder must always be a
%s
, even if a different placeholder (such as a%d
for integers or%f
for floats) may look more appropriate:>>> cur.execute("INSERT INTO numbers VALUES (%d)", (42,)) # WRONG >>> cur.execute("INSERT INTO numbers VALUES (%s)", (42,)) # correct
While, your SQL query contains all type of placeholders:
"""INSERT INTO weather_data(temperature,humidity,wind,barometer,updated_on,place_id)
VALUES (%(temperature)f, %(humidity)f, %(wind)f, %(barometer)f, %(date)s, %(place_id)d)"""
这篇关于使用psycopg2将数据导入postgres表时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!