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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/adf3ee50-d198-4193-bcfb-e866cad62e52n%40googlegroups.com.