SQLAlchemy + Postgres:“您可能需要添加显式类型转换";合并时 [英] SQLAlchemy + Postgres: "You might need to add explicit type casts" on merge
问题描述
我有一个使用 SQLAlchemy 的应用程序,当我使用 SQLite 时它运行良好.然而,当我转向 PostgreSQL 时,我发现自己甚至无法进行合并.这是我用于无法合并项目的表的模型:
I have an app that uses SQLAlchemy, and it worked fine when I used SQLite. However, when I moved to PostgreSQL, I found myself unable to even do the merge. Here's the model that I use for the table I am unable to merge items with:
class Item(db.Model):
__tablename__ = "prices"
defindex = db.Column(db.Integer, primary_key=True)
quality = db.Column(db.Integer, primary_key=True)
craftable = db.Column(db.Boolean, primary_key=True)
tradeable = db.Column(db.Boolean, primary_key=True)
item_metadata = db.Column(db.String(70), primary_key=True)
name = db.Column(db.String(70), primary_key=True)
currency = db.Column(db.String(70))
price = db.Column(db.Float)
price_high = db.Column(db.Float)
这是我尝试合并的方式(假设所有变量都分配了正确的值):
And here is how I am trying to do the merge (assume all variables do have proper values assigned):
new_item = Item(defindex=int(defindex), quality=int(quality), craftable=bool(is_craft),
tradeable=bool(is_trade), item_metadata=int(priceindex), currency=str(currency),
price=float(price), price_high=float(price), name=str(name))
但是在这个调用中,我收到一个错误:
But on this call, I get an error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: character varying = integer
LINE 3: ... prices.tradeable = true AND prices.item_metadata = 0 AND pr...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
[SQL: 'SELECT prices.defindex AS prices_defindex, prices.quality AS prices_quality, prices.craftable AS prices_craftable, prices.tradeable AS prices_tradeable, prices.item_metadata AS prices_item_metadata, prices.name AS prices_name, prices.currency AS prices_currency, prices.price AS prices_price, prices.price_high AS prices_price_high \nFROM prices \nWHERE prices.defindex = %(param_1)s AND prices.quality = %(param_2)s AND prices.craftable = %(param_3)s AND prices.tradeable = %(param_4)s AND prices.item_metadata = %(param_5)s AND prices.name = %(param_6)s'] [parameters: {'param_1': 1067, 'param_3': True, 'param_6': 'Grandmaster', 'param_5': 0, 'param_4': True, 'param_2': 1}]
我尝试在创建 new_item
时添加所有类型转换,但没有用,错误仍然存在.我该怎么做才能修复它?
I tried adding all the typecasts in creation of new_item
, but it was of no use, the error persisted. What should I do to fix it?
推荐答案
Column item_metadata
定义为字符串,所以必须是字符串.使用 SQLite 它可以工作,因为 SQLite 使用动态类型.它不强制执行数据类型约束.任何类型的数据(通常)都可以插入到任何列中.
Column item_metadata
defined as a string, so it must be a string. With SQLite it worked because SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column.
这篇关于SQLAlchemy + Postgres:“您可能需要添加显式类型转换";合并时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!