python - 数据库上的浮点数舍入
问题描述
我有以下df:
df
Process Commodity Direction ratio ratio-min
0 Coal plant Coal In 1.00 NaN
1 Coal plant Elec Out 0.40 NaN
2 Coal plant CO2 Out 0.30 NaN
3 Lignite plant Lignite In 1.00 NaN
4 Lignite plant Elec Out 0.40 NaN
5 Lignite plant CO2 Out 0.40 NaN
6 Gas plant Gas In 1.00 NaN
7 Gas plant Elec Out 0.60 NaN
8 Gas plant CO2 Out 0.20 NaN
9 Biomass plant Biomass In 1.00 NaN
10 Biomass plant Elec Out 0.35 NaN
11 Biomass plant CO2 Out 0.00 NaN
12 Wind park Wind In 1.00 NaN
13 Wind park Elec Out 1.00 NaN
14 Hydro plant Hydro In 1.00 NaN
15 Hydro plant Elec Out 1.00 NaN
16 Photovoltaics Solar In 1.00 NaN
17 Photovoltaics Elec Out 1.00 NaN
如您所见ratio
,值是浮点数。
我正在尝试使用 sqlalchemy 将此数据帧发送到数据库。
我在这里设置表格:
import sqlalchemy as sa
table = sa.Table(table_name,
metadata,
sa.Column('index', sa.Integer, primary_key=True, autoincrement=True, nullable=False),
sa.Column('Process', sa.VARCHAR(50)),
sa.Column('Commodity', sa.VARCHAR(50)),
sa.Column('Direction', sa.VARCHAR(50)),
sa.Column('ratio', sa.Float(10)),
sa.Column('ratio-min', sa.Float(10)),
schema=schema_name)
然后我通过以下方式将表格发送到数据库:df.to_sql(table_name, engine, schema=schema_name, if_exists='replace')
问题是当我检查数据库时,所有的值ratio
都以某种方式四舍五入。以下是我从数据库中得到的(它也是数据库中相同的舍入值)
Process Commodity Direction ratio ratio-min
0 Coal plant Coal In 1.0 None
1 Coal plant Elec Out 0.0 None
2 Coal plant CO2 Out 0.0 None
3 Lignite plant Lignite In 1.0 None
4 Lignite plant Elec Out 0.0 None
5 Lignite plant CO2 Out 0.0 None
6 Gas plant Gas In 1.0 None
7 Gas plant Elec Out 1.0 None
8 Gas plant CO2 Out 0.0 None
9 Biomass plant Biomass In 1.0 None
10 Biomass plant Elec Out 0.0 None
11 Biomass plant CO2 Out 0.0 None
12 Wind park Wind In 1.0 None
13 Wind park Elec Out 1.0 None
14 Hydro plant Hydro In 1.0 None
15 Hydro plant Elec Out 1.0 None
16 Photovoltaics Solar In 1.0 None
17 Photovoltaics Elec Out 1.0 None
我将如何防止to_sql
四舍五入我的ratio
价值观?
解决方案
您可以通过指定列的数据类型来解决此问题,如下所示:
from sqlalchemy.types import Float
然后加
dtype={'ratio': Float()}
的论点df.to_sql
:
df.to_sql(table_name, engine, schema=schema_name, if_exists='replace', dtype={'ratio': Float()})
参数dtype
是一个字典,它以列名作为键,将数据类型作为值。这应该可以防止您的ratio
值被四舍五入。
推荐阅读
- ionic-framework - 使用 android (Ionic 3) 中的 ImagePicker 插件重定向以在其他图像文件夹中查看
- python - 使用 marshmallow-mongoengine 加载数据时如何包含其他未知字段?
- scala - 无法从生成的原型中获取 parseFrom 作为可用方法
- javascript - 有条件地在 create-react-app 中导入资产
- c# - AuditIgnore 部分来自正文
- c# - 使用 api-key 限制 Swagger .net 核心中的某些端点
- r - 如何按名称对 DNAStringSet 对象进行子集化?
- javascript - 折射代码给出错误代码 500,而原始代码工作正常
- python - 从python中的字符串中提取特定模式
- sql - Group by - 选择子句中的非分组表达式