Hi,
I am using sqlalchemy in flask, essentially through flask-sqlalchemy, and
my goal is to generate and execute queries on the fly. I am pretty new to
sqlalchemy ( a great challenge and a great work) I am using a model
generated by automap.
The entities are automap classes
I managed to produce a select expression like this one:
>>> expr
<sqlalchemy.sql.selectable.Select at 0x7fa067022b10; Select object>
>>> print expr
SELECT devis."N_Devis" AS "N\xb0Devis", devis."Date" AS "Date", devis."Code
Client" AS "Code Client",
(SELECT client."Nom" FROM client WHERE client."Code" =
devis."Code Client") AS "Nom Client",
devis."Nom Contact" AS "Nom Contact"
FROM devis
Note the embbed query (I formatted manually the print result here for easy
reading).
This expression works perfectly when submitted directly to the RDBMS (mysql)
the probleme come when i generate the correspondant query
Query=db.session.query(expr)
>>> Query
<sqlalchemy.orm.query.Query object at 0x7fa066fe1390>
>>> print Query
SELECT"N\xb0Devis" AS "N\xb0Devis", "Date" AS "Date", "Code Client" AS
"Code Client", "Nom Client" AS "Nom Client", "Nom Contact" AS "Nom Contact"
FROM (SELECT devis."N_Devis" AS "N\xb0Devis", devis."Date" AS "Date",
devis."Code Client" AS "Code Client", (SELECT client."Nom"
FROM client
WHERE client."Code" = devis."Code Client") AS "Nom Client", devis."Nom
Contact" AS "Nom Contact"
FROM devis)
The entire inital query was embedded as a subquery in the FROM clause
then
>>> Query.count()
Traceback (most recent call last):
........
..........
File
"/home/mb/envjam/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 442, in do_execute
cursor.execute(statement, parameters)
File
"/home/mb/envjam/local/lib/python2.7/site-packages/MySQLdb/cursors.py",
line 205, in execute
self.errorhandler(self, exc, value)
File
"/home/mb/envjam/local/lib/python2.7/site-packages/MySQLdb/connections.py",
line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (1248, 'Every
derived table must have its own alias')
So, maybe i'm missing something , or maybe it's the flask-sqlalchemy
environment, my db object being an SQLAlchemy class of flask-sqlalchemy:
>>> db
<SQLAlchemy
engine='mysql://root:xxxxxxxxxxxxxxxx@localhost/biogal?charset=utf8'>
I know I coud add an alias to avoid the error, but doing so I end in other
issues with my soft.
My question is: how to tell the query object to keep the initial expression
inchanged ?
Thank you for your help
Regards
--
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 post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
