Hi all,
I'm working on a REST API which is built using Flask-SQLAlchemy and
Connexion. I'm fairly new to SQLAlchemy, but it's been brilliant so far
:) This API uses SQLAlchemy 1.3.16, and connects to an Oracle Database (12c
12.1.0.1.0 64bit).
<https://stackoverflow.com/posts/70341129/timeline>
I'm having an issue generating the correct SQL from a SQLAlchemy query. I
would really appreciate any help. The troublesome function is shown below.
def similar_matches(input_descriptor, threshold, lim=None, offset):
query = db.select([tbl, text(":q_descriptors as query_descriptors,
comparison(descriptors, :q_descriptors) as
similarity")bindparams(q_descriptor=input_descriptor).\
where( text("comparison(descriptors, :q_descriptors) >=
q_threshold").bindparams(q_descriptor=input_descriptor, q_threshold =
threshold)
res = query.execute().fetchall()
if len(res)=0 return '', 204
return MatchLimaScheme(many = True).dump(res)
This SQLAlchemy code takes two inputs (descriptor and threshold), and
searches through each descriptor in an Oracle database, calculating a
similarity measure between the queried descriptor and each stored
descriptor. All rows where similarity score >= threshold are returned in a
JSON.
The above code works fine, but returns all results - whereas I want to also
be able to include a .offset() and a .limit() (for lazy loading). The code
above generates SQL along these lines:
SELECT ID, last_modified, descriptors, :q_descriptors as query_descriptors,
comparison(descriptors, :q_descriptors) as similarity' FROM tbl WHERE
compare(descriptors, :q_descriptors) >= :q_threshold
which works well. However, when I add .limit() or .offset() on the end of
my query i.e.
query = db.select([tbl, text(":q_descriptors as query_descriptors,
comparison(descriptors, :q_descriptors) as similarity"
).bindparams(q_descriptor=input_descriptor).where(
text("comparison(descriptors, :q_descriptors) >= :q_threshold")
.bindparams(q_descriptor=input_descriptor,q_threshold =
threshold).limit(limit)
The SQL generated changes to be along these lines:
SELECT ID, last_modified, descriptors FROM (SELECT tbl.ID as ID,
tbl.last_modified as last_modified, tbl.descriptors as descriptors,
:q_descriptors as query_descriptors, comparison(descriptors,
:q_descriptors) as similarity) FROM tbl WHERE compare(descriptors,
:q_descriptors) >= :q_threshold WHERE ROWNUM <= :q_limit
As a raw SQL query this is fine, but I'm no longer including the
query_descriptors and similarity metrics in my SELECT clause. Thus I get a
columnNotFoundError. How do I adjust the .select() function above so that
my SQL looks more like:
SELECT ID, last_modified, descriptors, query_descriptors, similarity FROM
(SELECT tbl.ID as ID, tbl.last_modified as last_modified, tbl.descriptors
as descriptors,:q_descriptors as query_descriptors comparison(descriptors,
:q_descriptors) as similarity' FROM tbl WHERE compare(descriptors,
:q_descriptors) >= :q_threshold WHERE ROWNUM <= :q_limit OFFSET :q_offset
Basically, I'm looking to explicitly tell SQLAlchemy that I want to SELECT
tbl AND query_descriptors AND similarity.
I've also been informed that it's bad practice to not include a .order_by()
in these queries, but I don't think that is what's causing the issue here.
It is on my to do list though.
Please let me know if I need to provide more information.
Thanks for any help,
Dan
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/adf3ee50-d198-4193-bcfb-e866cad62e52n%40googlegroups.com.
