"Injecting" Dictionary Data from foreign database into SQLAlchemy Object

Question

I am prototyping a machinelearning model to generate "sorting data" for objects. The objects are stored in a mysql db which are accessed through an SQLAlchemy abstraction layer. Now i would like to filter and sort this data through the data i pull from the second server (where the ML is running).

So far I have managed to filter the data by adding the id's pulled from the ML-server to a IN Filter statement:

.filter(Job.id.in_(list(sum(ai_match, ()))))

whereas ai_match is a tuple of the id's pulled from the ML Server. This works fine but I would like to sort the list by the similarityalso in the ML server. From an SQL perspective this would just be an inner join over the two databases:

`SELECT * FROM jobs 
INNER JOIN mlserver.matches ON mlserver.matches.job_id = jobs.id`

however the ML Server is not a SQL Alchemy Object and i do not want to create one (as the project is rather massive and very complex and i don't have the ability to go deep into the whole project structures myself).

Which leads me to the question: How can I "Inject" data into an existin SQL Alchemy Object so to acheive the desired JOIN on a dictionary which would be of the format [(job_id, similarity),]

Thanks for your help!

abigperson · Accepted Answer · 2016-12-19 15:41:48Z

1

If you can't create a SQLAlchemy Object the simplest option might be to sort the results after the query with the similarity information stored in a dict:

# get sqlalchemy results
results = Job.query.filter(Job.id.in_(list(sum(ai_match, ())))).all()

# dict of id/value you want to sort on
ai_similarity = {x.id: x.value for x in ai_similarity_source}

# imagine you'd want to use try/except on KeyError here
results.sort(key=lambda x: ai_similarity[x.id], reverse=True)

answered Dec 19, 2016 at 15:41

abigperson

5,3823 gold badges25 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

"Injecting" Dictionary Data from foreign database into SQLAlchemy Object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related