SQLAlchemy NOT exists on subselect?

2024/4/14 19:22:57

I'm trying to replicate this raw sql into proper sqlalchemy implementation but after a lot of tries I can't find a proper way to do it:

SELECT *
FROM images i
WHERE NOT EXISTS (SELECT image_idFROM events eWHERE e.image_id=i.id AND e.chat_id=:chat_id)
ORDER BY random()
LIMIT 1

Closest I got is:

session.query(Image).filter(and_(Event.image_id == Image.id, Event.chat_id == chat_id)).order_by(func.random()).limit(1)

But I cant seem to find how to put the NOT EXISTS clause.

Can anyone lend a helping hand?

Thanks!

Answer

You're querying the FROM images table, but the WHERE clause is a subquery, not e.image_id=i.id AND e.chat_id=:chat_id (these filters are for events instead). So, the correct query is of the form

session.query(Image).filter(subquery).order_by(func.random()).limit(1)

The way to form an EXISTS subquery is with the .exists() method, so to get NOT EXISTS just use the ~ operator:

subquery = ~session.query(Event).filter(Event.image_id == Image.id, Event.chat_id == chat_id).exists()

Note that the emitted query is not identical to your original (e.g. it uses EXISTS (SELECT 1 ...)), but it's functionally the same.

https://en.xdnf.cn/q/72862.html

Related Q&A

What is the correct way to obtain explanations for predictions using Shap?

Im new to using shap, so Im still trying to get my head around it. Basically, I have a simple sklearn.ensemble.RandomForestClassifier fit using model.fit(X_train,y_train), and so on. After training, Id…

value error when using numpy.savetxt

I want to save each numpy array (A,B, and C) as column in a text file, delimited by space:import numpy as npA = np.array([5,7,8912,44])B = np.array([5.7,7.45,8912.43,44.99])C = np.array([15.7,17.45,189…

How do I retrieve key from value in django choices field?

The sample code is below:REFUND_STATUS = ((S, SUCCESS),(F, FAIL) ) refund_status = models.CharField(max_length=3, choices=REFUND_STATUS)I know in the model I can retrieve the SUCCESS with method get_re…

Errno13, Permission denied when trying to read file

I have created a small python script. With that I am trying to read a txt file but my access is denied resolving to an no.13 error, here is my code:import time import osdestPath = C:\Users\PC\Desktop\N…

How to write large JSON data?

I have been trying to write large amount (>800mb) of data to JSON file; I did some fair amount of trial and error to get this code:def write_to_cube(data):with open(test.json) as file1:temp_data = j…

Using absolute_import and handling relative module name conflicts in python [duplicate]

This question already has answers here:How can I import from the standard library, when my project has a module with the same name? (How can I control where Python looks for modules?)(7 answers)Close…

Setting results of torch.gather(...) calls

I have a 2D pytorch tensor of shape n by m. I want to index the second dimension using a list of indices (which could be done with torch.gather) then then also set new values to the result of the index…

Why does PyCharm use double backslash to indicate escaping?

For instance, I write a normal string and another "abnormal" string like this:Now I debug it, finding that in the debug tool, the "abnormal" string will be shown like this:Heres the…

Execute Shell Script from Python with multiple pipes

I want to execute the following Shell Command in a python script:dom=myserver cat /etc/xen/$myserver.cfg | grep limited | cut -d= -f2 | tr -d \"I have this:dom = myserverlimit = subprocess.cal…

Python: Is there a way to split a string of numbers into every 3rd number?

For example, if I have a string a=123456789876567543 could i have a list like...123 456 789 876 567 543