Re: [sqlalchemy] Selects, Outer Joins, and Auto-Correlation

[andrew cooke]

Thank-you!  I am sure that will be right - I was trying to find a
"from" in the docs, but missed the select_from().

Thanks again,
Andrew

On Mon, Nov 12, 2018 at 10:15:05AM -0500, Mike Bayer wrote:
> On Mon, Nov 12, 2018 at 10:07 AM Mike Bayer  wrote:
> >
> > On Mon, Nov 12, 2018 at 7:56 AM andrew cooke  wrote:
> > >
> > >
> > > I have some code that uses high-level ORM and it works fine, except that 
> > > one particular section is too slow.  So I was trying to replace that 
> > > section with a lower level query.  But I can't work out how to persuade 
> > > the Expression Language to stop auto-correlation.
> > >
> > > The query I am trying to generate is:
> > >
> > >  select statistic_journal.id,
> > > statistic_name.name,
> > > coalesce(statistic_journal_float.value,
> > >  statistic_journal_integer.value)
> > >from statistic_journal
> > >join statistic_name
> > >  on statistic_journal.statistic_name_id = statistic_name.id
> > >left outer join statistic_journal_float
> > >  on statistic_journal.id = statistic_journal_float.id
> > >left outer join statistic_journal_integer
> > >  on statistic_journal.id = statistic_journal_integer.id
> > >   where statistic_name.owner = -19043;
> > >
> >
> > this query is not using any correlation, it has no subqueries.
> >
> > > But the closest I can get (only aiming for the outer join part of the 
> > > query) is:
> > >
> > > Python 3.7.0 (default, Aug 20 2018, 18:32:46)
> > > [GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux
> > > Type "help", "copyright", "credits" or "license" for more information.
> > > >>> from sqlalchemy import inspect
> > > >>> from sqlalchemy.sql.functions import coalesce
> > > >>> from sqlalchemy.sql import select
> > > >>> from ch2.squeal.tables.statistic import StatisticJournal, 
> > > >>> StatisticName, StatisticJournalInteger, StatisticJournalFloat
> > > >>>
> > > >>> sj = inspect(StatisticJournal).local_table
> > > >>> sji = inspect(StatisticJournalInteger).local_table
> > > >>> sjf = inspect(StatisticJournalFloat).local_table
> > > >>>
> > > >>> print(select([sj.c.id,
> > > ...   coalesce(sjf.c.value, sji.c.value)])
> > > ... .correlate_except(sji, sjf)
> > > ... .outerjoin(sjf).outerjoin(sji))
> > > (SELECT statistic_journal.id AS id, 
> > > coalesce(statistic_journal_float.value, statistic_journal_integer.value) 
> > > AS coalesce_1
> > > FROM statistic_journal, statistic_journal_float, 
> > > statistic_journal_integer) LEFT OUTER JOIN statistic_journal_float ON id 
> > > = statistic_journal_float.id LEFT OUTER JOIN statistic_journal_integer ON 
> > > id = statistic_journal_integer.id
> 
> oh wait, the problem is that outerjoin() method you're using.  that
> creates a join to the SELECT as a whole and generates a subquery.
> That's not appropriate here.   here's how to join:
> 
> select([x, y, z]).select_from(sj.outerjoin(sjf).outerjoin(sji))
> 
> 
> see the tutorial at
> https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-joins .
> 
> 
> 
> > >
> > > I realise this is not complete or runnable, but I hope it illustrates the 
> > > problem: the initial FROM includes statistic_journal_float and 
> > > statistic_journal_integer.  I don't want those!  They should come later.  
> > > I was hoping that the correlate_except() would remove them, but it 
> > > doesn't seem to have any effect.
> >
> > can you remove the correlate_except?   this is a straightforward
> > query, however I can't be sure what the structure of "sjf" is, are
> > these all Table objects or are you mapping to selectables?   there's
> > no reason I can see which would generate that additional FROM clause.
> >
> >
> > >
> > > Also, I am not sure if this is how you mix ORM and expression language 
> > > (explicitly getting the tables via inspect).
> > >
> > > Is that clear?  What am I doing wrong?  This is SQLAlchemy 1.2.14 if it 
> > > makes any difference.
> > >
> > > Thanks,
> > > Andrew
> > >
> > > --
> > > SQLAlchemy -
> > > The Python SQL Toolkit and Object Relational Mapper

[sqlalchemy] Selects, Outer Joins, and Auto-Correlation

[andrew cooke]

I have some code that uses high-level ORM and it works fine, except that 
one particular section is too slow.  So I was trying to replace that 
section with a lower level query.  But I can't work out how to persuade the 
Expression Language to stop auto-correlation.

The query I am trying to generate is:
 
 select statistic_journal.id,
statistic_name.name,
coalesce(statistic_journal_float.value,
 statistic_journal_integer.value)
   from statistic_journal
   join statistic_name
 on statistic_journal.statistic_name_id = statistic_name.id
   left outer join statistic_journal_float
 on statistic_journal.id = statistic_journal_float.id
   left outer join statistic_journal_integer
 on statistic_journal.id = statistic_journal_integer.id
  where statistic_name.owner = -19043;

But the closest I can get (only aiming for the outer join part of the 
query) is:

Python 3.7.0 (default, Aug 20 2018, 18:32:46) 
[GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import inspect
>>> from sqlalchemy.sql.functions import coalesce
>>> from sqlalchemy.sql import select
>>> from ch2.squeal.tables.statistic import StatisticJournal, StatisticName, 
StatisticJournalInteger, StatisticJournalFloat
>>> 
>>> sj = inspect(StatisticJournal).local_table
>>> sji = inspect(StatisticJournalInteger).local_table
>>> sjf = inspect(StatisticJournalFloat).local_table
>>> 
>>> print(select([sj.c.id,
...   coalesce(sjf.c.value, sji.c.value)])
... .correlate_except(sji, sjf)
... .outerjoin(sjf).outerjoin(sji))
(SELECT statistic_journal.id AS id, coalesce(statistic_journal_float.value, 
statistic_journal_integer.value) AS coalesce_1 
FROM statistic_journal, statistic_journal_float, statistic_journal_integer) 
LEFT OUTER JOIN statistic_journal_float ON id = statistic_journal_float.id 
LEFT OUTER JOIN statistic_journal_integer ON id = statistic_journal_integer.
id

I realise this is not complete or runnable, but I hope it illustrates the 
problem: the initial FROM includes statistic_journal_float and 
statistic_journal_integer.  I don't want those!  They should come later.  I 
was hoping that the correlate_except() would remove them, but it doesn't 
seem to have any effect.

Also, I am not sure if this is how you mix ORM and expression language 
(explicitly getting the tables via inspect).

Is that clear?  What am I doing wrong?  This is SQLAlchemy 1.2.14 if it 
makes any difference.

Thanks,
Andrew

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Key error while creating link in graph

[andrew cooke]

Hi,

Could someone please explain what the following error means?  I assume I am 
expecting too much magic from SQLAlchemy, but I can't see why it doesn't 
just work.

I am trying to define a directed graph of related (music) artists.  So I 
have an Artist class (nodes) and a Link class (directed arcs).

The full code is available 
at 
https://github.com/andrewcooke/uykfg/blob/master/src/uykfg/music/db/network.py 
and 
https://github.com/andrewcooke/uykfg/blob/master/src/uykfg/music/db/catalogue.py
 
but I think the essential information is below:

class Link(TableBase):
__tablename__ = 'music_links'
src_id = Column(Integer, ForeignKey(Artist.id), primary_key=True, 
nullable=False)
src = relationship(Artist, backref='srcs', 
primaryjoin=src_id==Artist.id)
dst_id = Column(Integer, ForeignKey(Artist.id), primary_key=True, 
nullable=False)
dst = relationship(Artist, backref='dsts', 
primaryjoin=dst_id==Artist.id)

and then I ran this test:

alice = Artist(name='alice')
session.add(alice)
bob = Artist(name='bob')
session.add(bob)
bob.srcs.append(alice)
assert len(alice.dsts) == 1, alice.dsts

where the append gives the error:

  File 
/home/andrew/project/uykfg/git/env/lib/python3.2/site-packages/SQLAlchemy-0.8.0b2-py3.2.egg/sqlalchemy/orm/attributes.py,
 
line 1066, in emit_backref_from_collection_append_event
child_impl = child_state.manager[key].impl
KeyError: 'src'

Please - why?

Thanks for reading,
Andrew

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Re: Key error while creating link in graph

[andrew cooke]

Oh, staring at that some more - I guess the problem is that there is 
nothing that says that if alice links to bob then bob links from alice.  I 
should just defined one arc.  Sorry - I will fix my code.  Andrew

On Thursday, 7 February 2013 07:56:18 UTC-3, andrew cooke wrote:


 Hi,

 Could someone please explain what the following error means?  I assume I 
 am expecting too much magic from SQLAlchemy, but I can't see why it doesn't 
 just work.

 I am trying to define a directed graph of related (music) artists.  So I 
 have an Artist class (nodes) and a Link class (directed arcs).

 The full code is available at 
 https://github.com/andrewcooke/uykfg/blob/master/src/uykfg/music/db/network.pyand
  
 https://github.com/andrewcooke/uykfg/blob/master/src/uykfg/music/db/catalogue.pybut
  I think the essential information is below:

 class Link(TableBase):
 __tablename__ = 'music_links'
 src_id = Column(Integer, ForeignKey(Artist.id), primary_key=True, 
 nullable=False)
 src = relationship(Artist, backref='srcs', 
 primaryjoin=src_id==Artist.id)
 dst_id = Column(Integer, ForeignKey(Artist.id), primary_key=True, 
 nullable=False)
 dst = relationship(Artist, backref='dsts', 
 primaryjoin=dst_id==Artist.id)

 and then I ran this test:

 alice = Artist(name='alice')
 session.add(alice)
 bob = Artist(name='bob')
 session.add(bob)
 bob.srcs.append(alice)
 assert len(alice.dsts) == 1, alice.dsts

 where the append gives the error:

   File 
 /home/andrew/project/uykfg/git/env/lib/python3.2/site-packages/SQLAlchemy-0.8.0b2-py3.2.egg/sqlalchemy/orm/attributes.py,
  
 line 1066, in emit_backref_from_collection_append_event
 child_impl = child_state.manager[key].impl
 KeyError: 'src'

 Please - why?

 Thanks for reading,
 Andrew



-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Re: Key error while creating link in graph

[andrew cooke]

I'm an idiot.  Will delete post.  I need to be more explicit about the 
intermediate table.

On Thursday, 7 February 2013 07:56:18 UTC-3, andrew cooke wrote:


 Hi,

 Could someone please explain what the following error means?  I assume I 
 am expecting too much magic from SQLAlchemy, but I can't see why it doesn't 
 just work.

 I am trying to define a directed graph of related (music) artists.  So I 
 have an Artist class (nodes) and a Link class (directed arcs).

 The full code is available at 
 https://github.com/andrewcooke/uykfg/blob/master/src/uykfg/music/db/network.pyand
  
 https://github.com/andrewcooke/uykfg/blob/master/src/uykfg/music/db/catalogue.pybut
  I think the essential information is below:

 class Link(TableBase):
 __tablename__ = 'music_links'
 src_id = Column(Integer, ForeignKey(Artist.id), primary_key=True, 
 nullable=False)
 src = relationship(Artist, backref='srcs', 
 primaryjoin=src_id==Artist.id)
 dst_id = Column(Integer, ForeignKey(Artist.id), primary_key=True, 
 nullable=False)
 dst = relationship(Artist, backref='dsts', 
 primaryjoin=dst_id==Artist.id)

 and then I ran this test:

 alice = Artist(name='alice')
 session.add(alice)
 bob = Artist(name='bob')
 session.add(bob)
 bob.srcs.append(alice)
 assert len(alice.dsts) == 1, alice.dsts

 where the append gives the error:

   File 
 /home/andrew/project/uykfg/git/env/lib/python3.2/site-packages/SQLAlchemy-0.8.0b2-py3.2.egg/sqlalchemy/orm/attributes.py,
  
 line 1066, in emit_backref_from_collection_append_event
 child_impl = child_state.manager[key].impl
 KeyError: 'src'

 Please - why?

 Thanks for reading,
 Andrew



-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Polymorphic write - 'InstanceState' object has no attribute 'manager'

[andrew cooke]

Hi,

This trace is from some code that is loading a lot of objects (and
which usually does so with no problems).  I don't have more details
yet (it will be quite some work to find out exactly what data is
causing the error), but it does use joined table inheritance.  Does
anyone have any idea what the cause might be just from the trace?

SQLAlchemy 0.5.8 with Python 2.5 on Linux.

Thanks,
Andrew

  File /usr/local/home/andrewc/tngb3/repository-lib/src/kpi/load/
manage/statio\
ns.py, line 170, in _zero
loader.commit()
  File /usr/local/home/andrewc/tngb3/repository-lib/src/kpi/load/load/
loader.p\
y, line 169, in commit
self.__load_data(entity_map, time_series_map, metric_map)
  File /usr/local/home/andrewc/tngb3/repository-lib/src/kpi/load/load/
loader.p\
y, line 291, in __load_data
self.__session.flush()
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/sessi\
on.py, line 1354, in flush
self._flush(objects)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/sessi\
on.py, line 1432, in _flush
flush_context.execute()
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/unito\
fwork.py, line 261, in execute
UOWExecutor().execute(self, tasks)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/unito\
fwork.py, line 753, in execute
self.execute_save_steps(trans, task)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/unito\
fwork.py, line 768, in execute_save_steps
self.save_objects(trans, task)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/unito\
fwork.py, line 759, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/mappe\
r.py, line 1434, in _save_obj
if mapper._get_state_attr_by_column(state, col) is None and
len(primary_key\
)  i:
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/mappe\
r.py, line , in _get_state_attr_by_column
return self._get_col_to_prop(column).getattr(state, column)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/prope\
rties.py, line 99, in getattr
return state.get_impl(self.key).get(state, state.dict)
  File /usr/local/home/andrewc/SQLAlchemy-0.5.8/build/lib/sqlalchemy/
orm/state\
.py, line 92, in get_impl
return self.manager.get_impl(key)
AttributeError: 'InstanceState' object has no attribute 'manager'

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

[sqlalchemy] Re: Polymorphic write - 'InstanceState' object has no attribute 'manager'

[andrew cooke]

As far as I know, I'm doing nothing that complex.  I am creating a
pile of mapped objects in Python and then dumping them to the
database.  The most likely cause is that a field is None, or of the
incorrect type in some way, I would have guessed.

Andrew

On May 7, 9:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
 this looks like some kind of serialization issue.   are you deserializing 
 instances before mappers have been compiled ?    if you upgrade to 0.6, this 
 will raise an error immediately at the point at which it occurs.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

[sqlalchemy] Creating Tables From Selects

[andrew cooke]

(I thought this might be a FAQ, but I can't find it, and searching
isn't turning up anything either...).

Am I right n thinking it is not possible to do CREATE TABLE  AS
in SQLAlchemy?  In other words, creating a table from a select
statement?

I'm using 0.4 and Oracle.  If it is possible I would love to know how.

I understand insert from select is a TODO (found in a recent post).
So I guess my best bet is to just enter the entire SQL command to
create the table literally?!

(I am trying to duplicate some SQL that is currently run as a script;
I tried replacing the table with an embedded select, which works, but
is slower than expected.  So I need to do this both for efficiency on
one query and also because later queries are going to use the same
table, so I don't want to duplicate work).

Thanks,
Andrew

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

[sqlalchemy] Constructors not called?

[andrew cooke]

Hi,

Not sure if I'm misunderstanding what's happening, whether I have a
bug, or whether this is normal behaviour, but as far as I can tell
mapped objects that are returned from a query do not have their
constructor called.  Is that correct?

In a bit more detail - I have a mapping defined to class Metric, and a
query() returns a Metric instance, with the correct attributes, but if
in the constructor I have something like:

class Metric(object):
def __init__(self):
self.constructor_called = True

then the instance returned by query does not have the
constructor_called attribute (my actual code is more complex, but
that's the general idea).

Is this normal behaviour?  Is there any way to get the constructor to
be called?  I can work around this if not, but would liek to be sure I
am not ignoring an error of some kind.

I've looked through the documentation, but couldn't find anything
relevant.  Apologies if I've missed the obvious.  This is with version
0.4.7p1

Thanks,
Andrew

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Composite Columns in Subclass with Joined Table Inheritance

[andrew cooke]

Hi,

(Apologies for asking so many questions here - I really appreciate the
help).

I have been using joined table inheritance without any problems for a
while with a very simple hierarchy: a base class/table called
Measurement and various subclasses that contain a typed value column
(FloatMeasurement, IntegerMeasurement, etc).  In particular, I have
been doing a query on the base type (which has additional fields like
date) and all is sweet - I receive the correct subclass as expected.

Recently I added a new subclass that had a composite column - the
value is a ratio of two integers and the custom datatype is a
subclass of tuple.  This works fine when used directly - I can load
and save instances of RatioMeasurement with no problems.

However, if I query the base class and the query should return a
RatioMeasurement I now get an error.  What appears to be happening is
that the default ColumnLoader is failing at line 65 of strategies.py
(version 0.4.6) because the row for the initial query (made on the
base class) doesn't contain the columns required for the composite
column (since they are in the subclass table).

Adding .with_polymorphic('*') to the query makes no difference.  Nor
does adding polymorphic_fetch='deferred' to the base class mapping.

Does the above make sense?  What am I doing wrong this time?!

Thanks,
Andrew

PS The trace is:
[...]
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/query.py,
line 907, in first
ret = list(self[0:1])
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/query.py,
line 986, in iterate_instances
rows = [process[0](context, row) for row in fetch]
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/query.py,
line 1551, in main
extension=context.extension,
only_load_props=context.only_load_props,
refresh_instance=context.refresh_instance
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/mapper.py,
line 1368, in _instance
return mapper._instance(context, row, result=result,
polymorphic_from=self)
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/mapper.py,
line 1444, in _instance
self.populate_instance(context, instance, row,
only_load_props=only_load_props, instancekey=identitykey, isnew=isnew)
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/mapper.py,
line 1484, in populate_instance
(newpop, existingpop, post_proc) =
selectcontext.exec_with_path(self, prop.key,
prop.create_row_processor, selectcontext, self, row)
TypeError; 'NoneType' object is not iterable

As far as I can tell, the None is the result of the break at line 65
of strategies.py which is returned up the call stack until the
unpacking of the result.  I'm not completely sure about this, but the
break is the last thing I see executing with a debugger.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Composite Columns in Subclass with Joined Table Inheritance

[andrew cooke]

I'm sorry - ignore that, I am talking crap.

Adding with_polymorphic('*') *does* fix this - I was mislead because I
had the same problem in several places, so when I fixed one section of
the code another failed with a very similar stack trace.

Cheers,
Andrew
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Possible quoting bug, SQLite? If not, what wrong (please)?

[andrew cooke]

1 - changing isnull to foo worked.  thanks!

2 - i tried adding text('nulls last') in the order_by(...), but got an
error from sqlite.  i don't suppose there's something clever in
sqlalchemy that does this in a portable way is there?

3 - what do you use as a reference for SQL?  do you have the ansi
standard?  can you recommend a good book?  i think i need a
reference...

cheers and thanks again,
andrew


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Possible quoting bug, SQLite? If not, what wrong (please)?

[andrew cooke]

the case solution works too, which is what i'll go with.  thanks.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: in not implemented for queries?

[andrew cooke]

On May 21, 2:04 am, [EMAIL PROTECTED] wrote:
 what u want to accomplish?

Well, it's the equivalent of the SQL IN.

So i want to retrieve all Measurements which are associated with with
a Time Series in the given list.  The SQL equivalent would be
something like:

SELECT * from Measurement as M where M.time_series_id in (123, 456,
789)

except that I am using objects and have a list of TimeSeries
instances.

My question wasn't about how to use SQL, but whether the failure I was
seeing when trying to use in_ (which is mentioned very briefly in an
example in the documentation) is because that feature is not
supported, or because of an error in my code.

The example in the documentation is:
 session.query(Address).filter(
... Address.email_address.in_(['[EMAIL PROTECTED]',
'[EMAIL PROTECTED]'])

but that is using a literal field rather than a reference generated by
mapper(...properties={...relation...}) (as in my case with
time_series).  So my suspicion is that in_ is not implemented when the
attribute is generated in that way.

Andrew

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: in not implemented for queries?

[andrew cooke]

On May 21, 9:37 am, Michael Bayer [EMAIL PROTECTED] wrote:
 in_ is supported for column-based attributes.  If you are getting
 NotImplemented, that's beacuse time_series is referencing either a
 collection or a many-to-one object reference.  so SQL IN wouldn't
 work here.For the many-to-one case, IN could be implemented in
 SQLA easily if the referenced object had a single-column primary key,
 but for a composite primary key we dont yet support the (x, y, z) IN
 ((a,b,c), (d,e,f)) syntax (not sure of DB support for that either).

Excellent.  Thanks very much.  I am using a single column key, so it
sounds like I could also make the key explicit, so will try something
like:

.filter(Measurement.time_series.id.in_([getattr(ts, 'id') for ts in
time_series]).

I may give that a go at some point because it seems more natural to me
that the or approach (which I may not understand completely) (for
now it's working via an explicit accumulation in a loop over the time
series members).

Cheers,
Andrew

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] in not implemented for queries?

[andrew cooke]

Hi,

Just wanted to check: I am seeing a NotImplementedError (v 0.4.6 on
Linux w Python 2.5) when I try to query some objects with the filter
in.  Does that mean that the feature is not implemented, or is it
more likely an error in my code (eg somehow I'm calling a base class;
Address.email_address.in_ does appear in the ORM tutorial, which
makes me wonder whether it is supported)?

If this is unsupported, what's the best approach?  Just loop over the
various instances myself?

My code looks like:

query = session.query(Measurement)
query = query.filter(Measurement.time_series.in_(time_series))

where Measurement is a mapped class, and time_series is both a list of
TimeSeries instances (another mapped class) and a Measurement
attribute that is mapped via a relation.  If it's relevant -
Measurement is actually a base class for joined table inheritance (the
time series key col is in the base class table).

The trace looks like:
  File /home/andrew/projects/isti/kpi-pilot-2/repository-tool/src/
isti/reptool/core/actions/readonly.py, line 96, in _no_undo
Measurement.time_series.in_(time_series))
  File /usr/lib64/python2.5/site-packages/sqlalchemy/sql/
expression.py, line 1220, in in_
return self.operate(operators.in_op, other)
  File /usr/lib64/python2.5/site-packages/sqlalchemy/orm/
attributes.py, line 56, in operate
return op(self.comparator, *other, **kwargs)
  File /usr/lib64/python2.5/site-packages/sqlalchemy/sql/
operators.py, line 47, in in_op
return a.in_(*b)
  File /usr/lib64/python2.5/site-packages/sqlalchemy/sql/
expression.py, line 1220, in in_
return self.operate(operators.in_op, other)
  File /usr/lib64/python2.5/site-packages/sqlalchemy/sql/
expression.py, line 1181, in operate
raise NotImplementedError()
NotImplementedError

Finally, thanks for this library - it's an excellent piece of
software.  The first ORM/SQL lib that I've felt really works well.

Cheers,
Andrew

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---