sqlalchemy postgresql where int = string

Question

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 where type = '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 and Vehicle.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.

Answer 1

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).