sqlalchemy postgresql where int = string
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12643646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlalchemy postgresql where int = string
提问by applechief
I have 0 experience with postgresql and am deploying an app written in python using sqlalchemy to a server with postgres.
我对 postgresql 的经验为 0，我正在将使用 sqlalchemy 用 python 编写的应用程序部署到带有 postgres 的服务器上。
For development, I used an sqlite server.
对于开发，我使用了一个 sqlite 服务器。
Things are going pretty smoothly, but I hit a bump I don't know how to resolve.
事情进展得很顺利，但我遇到了一个不知道如何解决的问题。
I have three tables that look like that
我有三张看起来像这样的桌子
class Car(db.Model):
id= db.Column(db.Integer, primary_key=True)
...
class Truck(db.Model):
id= db.Column(db.String(32), primary_key=True)
...
class Vehicles(db.Model):
id= db.Column(db.Integer, primary_key=True)
type= db.Column(db.String) #This is either 'car' or 'truck'
value= db.Column(db.String) #That's the car or truck id
...
I have a query that selects from Vehicleswhere type = 'car' AND value = 10This is throwing an error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: integer = character varying
我有一个查询，从Vehicles哪里选择type = 'car' AND value = 10这是抛出错误：
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: integer = character varying
So I guess this is because Car.idis an int and Vehicle.valueis a string..
所以我想这是因为 Car.id是一个 int 并且Vehicle.value是一个字符串..
How to write this query in sqlalchemy? Is there a way to write it and make it compatible with my sqlite dev environment and the pgsql production?
如何在 sqlalchemy 中编写此查询？有没有办法编写它并使其与我的 sqlite 开发环境和 pgsql 产品兼容？
currently it looks like that
目前看起来像这样
db.session.query(Vehicle).filter(Car.id == Vehicle.value)
db.session.query(Vehicle).filter(Car.id == Vehicle.value)
PS: The truck id has to be a string and the car id has to be an int. I don't have control over that.
PS：卡车 id 必须是字符串，汽车 id 必须是 int。我对此没有控制权。
回答by Martijn Pieters
Simply cast to a string:
简单地转换为字符串：
db.session.query(Vehicle).filter(str(Car.id) == Vehicle.value)
if Car.idis a local variable that is an int.
ifCar.id是一个 int 类型的局部变量。
If you need to use this in a join, have the database cast it to a string:
如果您需要在连接中使用它，请让数据库将其转换为字符串：
from sqlalchemy.sql.expression import cast
db.session.query(Vehicle).filter(cast(Car.id, sqlalchemy.String) == Vehicle.value)
If the string value in the other column contains digits and possibly whitespaceyou may have to consider trimming, or instead casting the string value to an integer (and leave the integer column an integer).
如果另一列中的字符串值包含数字和可能的空格，您可能必须考虑修剪，或者将字符串值转换为整数（并将整数列保留为整数）。
