On 9/15/15 4:52 PM, Mourad Ben Cheikh wrote:
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)
you wouldn't want to do this. If you have a select() object and just
want the rows back, you'd call:
result = db.session.execute(expr)
If OTOH you have some objects to get back, you'd want to say:
q = db.session.query(MyClass).from_statement(my_select)
>>> 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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
