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.

Martijn Pieters · Accepted Answer · 2018-01-30 11:15:58Z

47

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

edited Jan 30, 2018 at 11:15

answered Sep 28, 2012 at 16:26

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

@chaft: If Truck.id holds a string that represents an integer - i.e. only digits (and possibly leading / trailing white space) and can be cast to integer - then you are better off casting Truck.id::int than the other way round (Car.id::varchar). Also note that yout get different results if your strings contain leading / trailing white space, because that's trimmed in a cast to integer, but makes a difference when comparing varchar or text.

Collectives™ on Stack Overflow

sqlalchemy postgresql where int = string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related