I guess since, I learn it now, EXCEPT is not supported by MySQL...
I guess I'll have to change my query at all...
On Feb 22, 12:57 pm, neurino <neur...@gmail.com> wrote:
> I have now problems with except_ in MySQL: the code that worked flawlessly
> in sqlite now causes an error, seems right after EXCEPT in query:
>
> ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version for
> the right syntax to use near 'SELECT sensors.id_cu AS sensors_id_cu,
> sensors.id_meas AS sensors_id_meas, senso' at line 3") 'SELECT
> anon_1.sensors_id_cu AS anon_1_sensors_id_cu, anon_1.sensors_id_meas AS
> anon_1_sensors_id_meas, anon_1.sensors_id_elab AS anon_1_sensors_id_elab,
> anon_1.sensors_name AS anon_1_sensors_name, anon_1.sensors_desc AS
> anon_1_sensors_desc \nFROM (SELECT sensors.id_cu AS sensors_id_cu,
> sensors.id_meas AS sensors_id_meas, sensors.id_elab AS sensors_id_elab,
> sensors.name AS sensors_name, sensors.`desc` AS sensors_desc \nFROM sensors
> EXCEPT SELECT sensors.id_cu AS sensors_id_cu, sensors.id_meas AS
> sensors_id_meas, sensors.id_elab AS sensors_id_elab, sensors.name AS
> sensors_name, sensors.`desc` AS sensors_desc \nFROM sensors, view_opts
> \nWHERE sensors.id_cu = view_opts.id_cu AND sensors.id_meas =
> view_opts.id_meas AND view_opts.id_view = %s AND view_opts.id IS NOT NULL
> ORDER BY sensors.id_cu, sensors.id_meas) AS anon_1 ORDER BY
> anon_1.sensors_id_cu, anon_1.sensors_id_meas' (1L,)
>
> I'm using:
> * sqlalchemy 0.6.6
> * MySQLdb 1.2.3
> * MySQL Ver 14.14 Distrib 5.1.41,
>
> Thanks for your support
>
> 2011/1/13 neurino <neur...@gmail.com>
>
>
>
>
>
>
>
> > Thanks Michael,
>
> > just for following readers I precise the ORDER BY clause causing the
> > OperationalError is the one coming *before* the EXCEPT so I had to
> > add .order_by(None) to the first query, now it looks like:
>
> > Session.query(model.Sensor) \
> > .order_by(None) \
> > .except_(
> > Session.query(model.Sensor) \
> > .filter(model.Sensor.id_cu==model.ViewOpt.id_cu) \
> > .filter(model.Sensor.id_meas==model.ViewOpt.id_meas) \
> > .filter(model.ViewOpt.id_view==1)
> > )
>
> > and works perfectly, thanks again!
>
> > Cheers
> > neurino
>
> > On Jan 12, 5:28 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > > On Jan 12, 2011, at 11:20 AM, neurino wrote:
>
> > > > Well as I wrote ordering involves everything, also forms creation with
> > > > formalchemy (make a select where all sensors are ordered that way etc)
> > > > anyway I understand your point of view.
>
> > > >> quickest is a "where sensor id not in (query)", as a simple WHERE
> > clause
>
> > > > Problem comes when Sensor primary key is composite (id_cu +
> > > > id_meas)...
>
> > > > The good 'ol python comes in handy anyway:
>
> > > >>>> all = Session.query(model.Sensor).all()
> > > >>>> selected = Session.query(model.Sensor).filter(
> > > > ... model.Sensor.id_cu==model.ViewOpt.id_cu).filter(
> > > > ... model.Sensor.id_meas==model.ViewOpt.id_meas).filter(
> > > > ... model.ViewOpt.id_view==1).all()
> > > >>>> diff = [sens for sens in all if sens not in selected]
> > > >>>> len(all), len(selected), len(diff)
> > > > (154, 6, 148)
>
> > > > We're talking of working on max total 200/300 sensors.
>
> > > > The OR way did not filter anything (maybe I made somwthing wrong).
>
> > > Oh you know what, I completely forgot the best solution. It *is*
> > documented on query.order_by() though which is an argument for....checking!
> > pass None to query.order_by(). That disables all order_by's for that
> > query. So go back to your except_() and use except_(q.order_by(None)).
>
> > > > Greetings
>
> > > > On Jan 12, 4:04 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > > >> On Jan 12, 2011, at 8:46 AM, neurino wrote:
>
> > > >>> I need always the same order_by in all app and it could be subject of
> > > >>> modification and / or integration in the near future so which better
> > > >>> place than mapper to define it once instead of any time I do a query?
>
> > > >> It sounds like the ordering here is for the purposes of view logic so
> > I'd have view logic that is factored down to receive Query objects that
> > return Sensor rows, the view logic then applies the .order_by() to the
> > Query. I.e. in a web app I use a Paginator object of some kind that does
> > this, given a Query. This is probably a reason I don't like "order_by" to
> > be within mapper(), it doesn't define persistence, rather a view.
>
> > > >>> Anyway do you think there are alternate paths to get `all sensors but
> > > >>> already choosen` which are "order_by" compatible?
>
> > > >> quickest is a "where sensor id not in (query)", as a simple WHERE
> > clause, or use OR, "query sensor where sensor.cu != cu OR sensor.meas !=
> > meas OR sensor.view != view". "Except" is not as widely used and I think
> > its not even supported by all backends, even though it is a nice logical set
> > operator, its got annoying quirks like this one.
>
> > > >>> Thanks for your support
>
> > > >>> On Jan 12, 2:38 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > > >>>> On Jan 12, 2011, at 7:28 AM, neurino wrote:
>
> > > >>>>> I have this model:
>
> > > >>>>> ``I organize views with many view_options each one showing a
> > sensor.
> > > >>>>> A sensor can appear just once per view.``
>
> > > >>>>> sensors = Table('sensors', metadata,
> > > >>>>> Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
> > > >>>>> primary_key=True,
> > > >>>>> autoincrement=False),
> > > >>>>> Column('id_meas', Integer, primary_key=True,
> > autoincrement=False),
> > > >>>>> Column('id_elab', Integer, nullable=False),
> > > >>>>> Column('name', Unicode(40), nullable=False),
> > > >>>>> Column('desc', Unicode(80), nullable=True),
> > > >>>>> )
>
> > > >>>>> ctrl_units = Table('ctrl_units', metadata,
> > > >>>>> Column('id', Integer, primary_key=True, autoincrement=False),
> > > >>>>> Column('name', Unicode(40), nullable=False)
> > > >>>>> )
>
> > > >>>>> views = Table('views', metadata,
> > > >>>>> Column('id', Integer, primary_key=True),
> > > >>>>> Column('name', Unicode(40), nullable=False),
> > > >>>>> Column('desc', Unicode(80), nullable=True),
> > > >>>>> )
>
> > > >>>>> view_opts = Table('view_opts', metadata,
> > > >>>>> Column('id', Integer, primary_key=True),
> > > >>>>> Column('id_view', Integer, ForeignKey('views.id'),
> > > >>>>> nullable=False),
> > > >>>>> Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
> > > >>>>> nullable=False),
> > > >>>>> Column('id_meas', Integer, nullable=False),
> > > >>>>> Column('ord', Integer, nullable=False),
> > > >>>>> ForeignKeyConstraint(('id_cu', 'id_meas'),
> > > >>>>> ('sensors.id_cu', 'sensors.id_meas')),
> > > >>>>> #sensor can appear just once per view
> > > >>>>> UniqueConstraint('id_view', 'id_cu', 'id_meas'),
> > > >>>>> )
>
> > > >>>>> Now I let the user add view_options letting him select the sensor.
> > > >>>>> I'd like to show him only the sensors not already selected in other
> > > >>>>> options of the same parent view so I tried to use except_ this way:
>
> > > >>>>> q = Session.query(model.Sensor) \
> > > >>>>> .except_(
> > > >>>>> Session.query(model.Sensor) \
> > > >>>>> .filter(model.Sensor.id_cu==model.ViewOpt.id_cu) \
> > > >>>>> .filter(model.Sensor.id_meas==model.ViewOpt.id_meas)
> > \
> > > >>>>> .filter(model.ViewOpt.id_view==1)
> > > >>>>> )
>
> > > >>>>> Sensor mapping has a order_by:
>
> > > >>>>> orm.mapper(Sensor, sensors,
> > > >>>>> order_by=[sensors.c.id_cu,
> > > >>>>> sensors.c.id_meas
> > > >>>>> ])
>
> > > >>>>> I get this SQL and this error, probably due to mapping order_by in
> > > >>>>> Sensor:
>
> > > >>>>> (OperationalError) ORDER BY clause should come after EXCEPT not
> > > >>>>> before
> > > >>>>> u'SELECT anon_1.sensors_id_cu AS anon_1_sensors_id_cu,
> > > >>>>> anon_1.sensors_id_meas AS anon_1_sensors_id_meas,
> > > >>>>> anon_1.sensors_id_elab AS anon_1_sensors_id_elab,
> > anon_1.sensors_name
> > > >>>>> AS anon_1_sensors_name, anon_1.sensors_desc AS anon_1_sensors_desc
> > > >>>>> FROM (SELECT sensors.id_cu AS sensors_id_cu, sensors.id_meas AS
> > > >>>>> sensors_id_meas, sensors.id_elab AS sensors_id_elab, sensors.nameAS
> > > >>>>> sensors_name, sensors."desc" AS sensors_desc
> > > >>>>> FROM sensors ORDER BY sensors.id_cu, sensors.id_meas EXCEPT SELECT
> > > >>>>> sensors.id_cu AS sensors_id_cu, sensors.id_meas AS sensors_id_meas,
> > > >>>>> sensors.id_elab AS sensors_id_elab, sensors.name AS sensors_name,
> > > >>>>> sensors."desc" AS sensors_desc
> > > >>>>> FROM sensors, view_opts
> > > >>>>> WHERE sensors.id_cu = view_opts.id_cu AND sensors.id_meas =
> > > >>>>> view_opts.id_meas AND view_opts.id_view = ? ORDER BY sensors.id_cu,
> > > >>>>> sensors.id_meas) AS anon_1 ORDER BY anon_1.sensors_id_cu,
> > > >>>>> anon_1.sensors_id_meas'
>
> > > >>>>> is this supposed to be a bug?
>
> > > >>>>> Any alternative solution (and maybe simpler :) ) to get what I
> > need?
>
> > > >>>> I suppose its a bug, though I'm not a huge fan of "order_by" on
> > mapper though, so my recommendation would be to not rely upon that. A
> > solution in SQLA would be if some flag were passed through to not render
> > built-in order bys. I've added 2022 targeted for 0.7.xx for that.
>
> > > >>> --
> > > >>> You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > > >>> To post to this group, send email to sqlalchemy@googlegroups.com.
> > > >>> To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > > >>> For more options, visit this group athttp://
> > groups.google.com/group/sqlalchemy?hl=en.
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > > > To post to this group, send email to sqlalchemy@googlegroups.com.
> > > > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > > > For more options, visit this group athttp://
> > groups.google.com/group/sqlalchemy?hl=en.
>
> > --
> > You received this message because you
>
> ...
>
> read more »
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
