How to convert a postgresql not exists to sqlalchemy(1.x)?

Question

I'm trying to convert an sql query into an sqlalchemy one and I'm having trouble with translating a where not exists(select...) part.

Here's the base query that I wish to convert to sqlalchemy:

select sd.address,
       sd.colour_code,
       s.id as deactivated_id,
       s.type,
       'http://site/groups/' || c.group_id || '/user/' || c.subject_id as URL
from sensors s
   join contracts c on s.contract_id=c.id
   join sensordevices sd on sd.id=s.device_id
where s.begin_time != null and 
      s.end_time != null and 
      c.end_date != and 
      not exists (select * from sensors sb
   where sb.begin_time != null and --b properly entered
         sb.end_time == null and -- b active
         sb.contract_id==s.contract_id and
         s.type==sb.type and
         s.end_time<=sb.begin_time)
;

This is where I left the code(erroneous code) after my last tries:

s = aliased(SensorInstance)
sb = aliased(SensorInstance)
sd = aliased(SensorDevice)
c = aliased(Contract)

info = db.session.query(sd.address,sd.colour_code,s.id,s.role,c.group_id,c.resident_id)\
            .filter(s.contract_id==c.id)\
            .filter(s.device_id==sd.id)\
            .filter(s.begin_time != None)\
            .filter(s.end_time != None)\
            .filter(c.end_date != None)\
            .filter(sd.device_type == device_type)\
            .filter(sd.address == device_address)\
            .filter(not_(and_(sb.begin_time != None,
                                         sb.end_time == None,
                                         sb.role==s.role,
                                         sb.contract_id==s.contract_id,
                                         s.end_time<=sb.begin_time).exists())).first()

Which produces a 'Comparator' object has no attribute 'exists' which is a reasonable error.

Another thing that I have tried is this (with the same aliases as above):

info = db.session.query(sd.address,sd.colour_code,s.id,s.role,c.group_id,c.resident_id)\
            .filter(s.contract_id==c.id)\
            .filter(s.device_id==sd.id)\
            .filter(s.begin_time != None)\
            .filter(s.end_time != None)\
            .filter(c.end_date != None)\
            .filter(sd.device_type == device_type)\
            .filter(sd.address == device_address)\
            .filter(~exists().where(and_(sb.begin_time != None,
                                         sb.end_time == None,
                                         sb.role==s.role,
                                         sb.contract_id==s.contract_id,
                                         s.end_time<=sb.begin_time).exists())).first()

Which produces a sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) SELECT * with no tables specified is not valid LINE 3: ...sordevices_1.address = 4278262373 AND NOT (EXISTS (SELECT * error. (This I tried to do based on the solution provided here

Does anyone have any pointers or a solution on how to fix this?

score 0 · Answer 1 · answered May 19 '22 at 17:10

Without being able to test because I dont have access to data I believe the second approach can work but as the error says you have a malformed subquery. You are making a select without specifying the table (or object in this cas of orm) where the subquery operates on.

Basically I think you mees to make a subquery of the form:

subquery = ~db.session.query(sd.address,sd.colour_code,s.id,s.role,c.group_id,c.resident_id)\
.filter(sb.begin_time is not None,
                                         sb.end_time is None,
                                         sb.role==s.role,
                                         sb.contract_id==s.contract_id,
                                         s.end_time<=sb.begin_time).exists()

For more details see here this thread

How to convert a postgresql not exists to sqlalchemy(1.x)?

1 Answers1