sqlalchemy postgresql where int = string
Simply cast to a string:
db.session.query(Vehicle).filter(str(Car.id) == Vehicle.value)
if Car.id
is a local variable that is an 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 whitespace you may have to consider trimming, or instead casting the string value to an integer (and leave the integer column an integer).
applechief
Updated on June 05, 2022Comments
-
applechief almost 2 years
I have 0 experience with postgresql and am deploying an app written in python using sqlalchemy to a server with postgres.
For development, I used an sqlite server.
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
Vehicles
wheretype = 'car' AND value = 10
This is throwing an error:sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: integer = character varying
So I guess this is because
Car.id
is an int andVehicle.value
is a string..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?
currently it looks like that
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.