如何调整PANDAS方法to_sql，以便我的数据使用正确的编写。小数？
我需要在 Oracle 表中填充天气数据。该表如下所示：
"IDGRID" NUMBER(12,0) NOT NULL,
"DAY" DATE CONSTRAINT NOT NULL,
"TEMPERATURE_MAX" NUMBER(3,1) NOT NULL,
"TEMPERATURE_MIN" NUMBER(3,1) NOT NULL,
"TEMPERATURE_AVG" NUMBER(3,1) NOT NULL,
"TEMPERATURE_DEW" NUMBER(3,1) NOT NULL,
"VAPOURPRESSURE" NUMBER(4,2) NOT NULL,
"WINDSPEED" NUMBER(5,1) NOT NULL,
"PRECIPITATION" NUMBER(4,1) NOT NULL,
"RADIATION" NUMBER(6,0) NOT NULL,
"SNOWDEPTH" NUMBER(6,0),
"SNOWWEQ" NUMBER(6,2),
"CLOUDCOVER" NUMBER(4,3) NOT NULL
我正在开发 python 代码来执行此任务。我的数据位于 Pandas 数据框 df2 中，所有列的名称与表字段的名称相同。为了安排正确的精度，我在调用 pandas 方法“to_sql”之前添加了此语句：
df2 = df2.round({"TEMPERATURE_MAX": 1, "TEMPERATURE_MIN": 1, "TEMPERATURE_AVG": 1,
"TEMPERATURE_DEW": 1, "VAPOURPRESSURE": 2, "WINDSPEED": 1, "PRECIPITATION": 1,
"RADIATION": 0, "SNOWDEPTH": 0, "SNOWWEQ": 2, "CLOUDCOVER": 3}
)
df2.to_sql(name='MY_WEATHER_TABLE', con=engine, chunksize=1000, if_exists='append')
供您参考：我使用 sqlalchemy 引擎连接到 Oracle 数据库。不幸的是，数据的舍入似乎没有达到预期的结果。我的问题是：我可以在 Python 代码中做什么来解决这个问题？
异常：(cx_Oracle.DatabaseError) ORA-01438: 值大于此列允许的指定精度 [SQL: INSERT INTO "WEATHER_ERA5_GRID_TEST" ("IDGRID", "VAPOURPRESSURE", "TEMPERATURE_AVG", "TEMPERATURE_DEW", "TEMPERATURE_MAX", "TEMPERATURE_MIN", "CLOUDCOVER", "SNOWWEQ", "SNOWDEPTH", "PRECIPITATION", "辐射”， “WINDSPEED”，“DAY”）值（：IDGRID，：VAPOURPRESSURE，：TEMPERATURE_AVG，：TEMPERATURE_DEW，：TEMPERATURE_MAX，：TEMPERATURE_MIN，：CLOUDCOVER，：SNOWWEQ，：SNOWDEPTH，：降水，：辐射，：WINDSPEED，：DAY）] [参数：[{'IDGRID'：904491077，'VAPOURPRESSURE'：11.729999542236328，'TEMPERATURE_AVG'：-17.899999618530273，'TEMPERATURE_DEW'：-20.700000762939453， 'TEMPERATURE_MAX'：-17.799999237060547，'TEMPERATURE_MIN'：-25.100000381469727，'云层'：0.9700000286102295，'SNOWWEQ'：8.399999618530273， '雪深'：55.0，'降水'：1.399999976158142，'辐射'：379.0，'风速'：4.599999904632568，'日'：datetime.date(2020, 1, 1)}等
There's an Oracle table which I need to fill with weather data. This is what the table looks like:
"IDGRID" NUMBER(12,0) NOT NULL,
"DAY" DATE CONSTRAINT NOT NULL,
"TEMPERATURE_MAX" NUMBER(3,1) NOT NULL,
"TEMPERATURE_MIN" NUMBER(3,1) NOT NULL,
"TEMPERATURE_AVG" NUMBER(3,1) NOT NULL,
"TEMPERATURE_DEW" NUMBER(3,1) NOT NULL,
"VAPOURPRESSURE" NUMBER(4,2) NOT NULL,
"WINDSPEED" NUMBER(5,1) NOT NULL,
"PRECIPITATION" NUMBER(4,1) NOT NULL,
"RADIATION" NUMBER(6,0) NOT NULL,
"SNOWDEPTH" NUMBER(6,0),
"SNOWWEQ" NUMBER(6,2),
"CLOUDCOVER" NUMBER(4,3) NOT NULL
I'm developing python code to carry out this task. I have the data in a Pandas dataframe df2, with all the columns having the same name as the table fields. In order to arrange for the right precision I added this statement, before I invoke the pandas method "to_sql":
df2 = df2.round({"TEMPERATURE_MAX": 1, "TEMPERATURE_MIN": 1, "TEMPERATURE_AVG": 1,
"TEMPERATURE_DEW": 1, "VAPOURPRESSURE": 2, "WINDSPEED": 1, "PRECIPITATION": 1,
"RADIATION": 0, "SNOWDEPTH": 0, "SNOWWEQ": 2, "CLOUDCOVER": 3}
)
df2.to_sql(name='MY_WEATHER_TABLE', con=engine, chunksize=1000, if_exists='append')
For your information: I use an sqlalchemy engine to connect to the Oracle database. Unfortunately, it seems that the rounding of the data is not having the expected result. My question is: what can I do in my Python code to overcome this problem?
Exception: (cx_Oracle.DatabaseError) ORA-01438: value larger than specified precision allowed for this column
[SQL: INSERT INTO "WEATHER_ERA5_GRID_TEST" ("IDGRID", "VAPOURPRESSURE", "TEMPERATURE_AVG", "TEMPERATURE_DEW", "TEMPERATURE_MAX", "TEMPERATURE_MIN", "CLOUDCOVER", "SNOWWEQ", "SNOWDEPTH", "PRECIPITATION", "RADIATION", "WINDSPEED", "DAY") VALUES (:IDGRID, :VAPOURPRESSURE, :TEMPERATURE_AVG, :TEMPERATURE_DEW, :TEMPERATURE_MAX, :TEMPERATURE_MIN, :CLOUDCOVER, :SNOWWEQ, :SNOWDEPTH, :PRECIPITATION, :RADIATION, :WINDSPEED, :DAY)]
[parameters: [{'IDGRID': 904491077, 'VAPOURPRESSURE': 11.729999542236328, 'TEMPERATURE_AVG': -17.899999618530273, 'TEMPERATURE_DEW': -20.700000762939453, 'TEMPERATURE_MAX': -17.799999237060547, 'TEMPERATURE_MIN': -25.100000381469727, 'CLOUDCOVER': 0.9700000286102295, 'SNOWWEQ': 8.399999618530273, 'SNOWDEPTH': 55.0, 'PRECIPITATION': 1.399999976158142, 'RADIATION': 379.0, 'WINDSPEED': 4.599999904632568, 'DAY': datetime.date(2020, 1, 1)}, etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我的计算蒸气压的代码似乎存在错误，导致值大于 99.99，并且这些值不适合定义为 NUMBER(4,2) 的列。 Oracle 的错误消息让我感到厌烦，因为其中使用了“精度”这个词。因此，INSERT 语句中使用的大量小数并没有真正导致问题。这意味着不需要将数据帧的任何列转换为十进制类型。解决办法是纠正计算中的错误。不管怎样，谢谢，克里斯托弗·琼斯！
It appeared that there was a mistake in my code for calculating the vapour pressure, leading to values greater than 99.99 and these did not fit in the column defined as NUMBER(4,2). The error message from Oracle put me off because of the word precision used in it. The great number of decimals used in the INSERT-statement did therefore not really cause the problem. It means that it was not necessary to convert any column of the dataframe to type decimal. The solution was to correct the mistake in the calculation. Thanks anyway, Christopher Jones!