[sqlalchemy] Re: Multiple Inheritance

[az]

i went for polymorphic asociation on my multiple inheritances / 
multiple aspects. it gives even more freedom than what strict 
inheritance needs.

the examples around ruby-on-rails are for one2many/many2one: 
http://wiki.rubyonrails.org/rails/pages/UnderstandingPolymorphicAssociations
sqlalchemy/examples/poly_assoc/

i have a many2many version, 
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/usage/polymassoc.py

the idea: the assoc.table points to the value and to all the possible 
owners (via foreign keys), with only one of the owners set-up for 
each record. this can probably be extended to point to multiple types 
of values too.

ciao
svil


On Thursday 04 September 2008 06:25:53 Michael Bayer wrote:
 On Sep 3, 2008, at 8:47 PM, Sam Magister wrote:
  I was wondering if it is possible to set up joined table
  inheritance so that a subclass inherits from more than one base
  table. To extend the example given in the documentation, we would
  have a base class 'Employee' and a base class 'Citizen' such that
  an 'Engineer' would inherit from both Employee and Citizen
  classes and have independent 'citizen_id' and 'employee_id'. One
  could imagine other classes that only inherit from either
  employee or citizen.
 
  employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('employee_type', String(30), nullable=False)
  )
 
  citizens = Table('citizens', metadata,
Column('citizen_id', Integer, primary_key=True),
Column('citizen_type', String(30), nullable=False)
  )
 
  An engineer who is both an employee and a citizen would have am
  employee_id and a citizen_id:
 
  engineers = Table('engineers', metadata,
Column('id', Integer, primary_key=True)
Column('employee_id', Integer,
  ForeignKey('employees.employee_id')),
Column('citizen_id', Integer,
  ForeignKey('citizens.citizen_id')), Column('engineer_info',
  String(50)),
  )

 This pattern doesnt entirely make sense - the citizen_type and
 employee_type columns seem superfluous and redundant against each
 other, since we really can't load Engineer rows without querying
 all three tables.  In that sense it takes on all the limitations of
 concrete table inheritance, which doesnt use a type column at the
 table level.

 Also, a key aspect of SQLA's polymorphic loading capability is that
 a mapper is aware of all of its possible subtypes.   If multiple
 inheritance is part of that, the geometry of what are all my
 subtypes? becomes a more chaotic.  We'd have to join to every
 table in the whole hierarchy to identify the type.   To be fair I
 think this is a behavior that Hibernate supports but they only
 support it for single inheritance (and they also boast of how
 difficult it was to implement).SQLA's usual notion of primary
 key with respect to joined table inheritance wouldn't work here
 either (engineer's PK is either (x, y) or (x, y, z), employee and
 citizen are just (x)), suggesting again a more concrete notion -
 you need to select from the subclass table in order to locate the
 object, and the primary key itself does not provide enough
 information to select the appropriate subclass table.

 The standard patterns for multiple inheritance in SQL are listed
 at
 http://www.agiledata.org/essays/mappingObjects.html#MappingMultiple
Inheritance .  There you'll find examples of concrete, single, and
 joined table multiple inheritance.

 You can certainly map to any of the hierarchies indicated in that
 article, but you wouldn't be able to take advantage of SQLA's
 polymorphic capabilities, which are designed to only handle
 single inheritance.   You'd really want to make your
 Engineer(Employee, Citizen) class and just map it to
 engineers.join(citizens).join(employees).   That would get your
 schema going, just without SQLA having any awareness of the
 inheritance portion of it, and is extremely similar to a plain
 concrete setup, which is pretty much all you'd get anyway without
 the ability to load polymorphically.

  For my application, this pattern is important (the above example
  is only an example of the pattern, I'm not really modeling
  employees and citizens) and I was wondering if anyone had any
  suggestions as to how to go about implementing this
  functionality, which I'm planning on doing.

 if you mean implementing within SQLAlchemy itself such that its
 core notion of inheritance is modified to support multiple base
 classes spread across multiple tables, this would be an enormously
 difficult feature to implement.For polymorphic loading, at the
 very least SQLA would need to lose its dependency on
 discriminator columns and learn to just look for row presence in
 a table as evidence of belonging to a certain type (that alone is
 not necessarily a bad thing, as Hibernate does this too).

 It would also need to learn to create joins to other tables
 corresponding to horizontal and vertical relationships, and be able
 to guess the type of a row 

[sqlalchemy] Re: Accessing Views with SqlAlchemy

[az]

put a class ABC(object): pass
and see what it gets filled with?

On Thursday 04 September 2008 00:10:44 Mike wrote:
 On Sep 3, 3:45 pm, Michael Bayer [EMAIL PROTECTED] wrote:
  On Sep 3, 2008, at 4:14 PM, Mike wrote:
   Replying to my own message seems kind of schizo, but I found
   one solution. I reflected the view into a Table() object and
   then used the select method along with and_ and not_. I
   would prefer to use the session object, but this works.
  
   I'm currently converting the following SQL:
  
   SELECT LNAME, FNAME, NETNAME
   FROM MyView
   WHERE (DEPT = '%s') AND (NETNAME  '')
  
   I then access it like this:
  
   code
  
   engine = create_engine('mssql://user:[EMAIL PROTECTED]/DB')
   meta = MetaData(engine)
   emp_tbl = Table('MyView', meta, autoload=True)
   s = select([emp_tbl.c.LNAME,
          emp_tbl.c.FNAME,
          emp_tbl.c.NETNAME],
         and_(emp_tbl.c.HOME_DEPT==deptNum,
   not_(emp_tbl.c.NETNAME==))) res = engine.execute(s)
   row = res.fetchall()
  
   /code
  
   Is there a less messy way to accomplish this? (Yes, I am a SQL
   newb!)
 
  im surprised the autoload works for a view.  If you have that,
  then   you should be able to just make a mapper() to that Table
  as usual - you might need to specify primary_key to your mapper
  and/or Table (e.g. Table('name', meta, Column('id', Integer,
  primary_key=True), autoload=True)) .  You just can't issue any
  changes to the object (unless the view is writeable).

 I guess my issue is getting my mind around how to create the class
 object to map to. I didn't create this view or the tables that it
 manipulates, so I'm not sure what attributes / properties to give
 the class. I guess I can get the column names that are returned in
 SQL Server Enterprise Manager and see if those work for the class
 attributes.

--~--~-~--~~~---~--~~
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: default values for columns in select mappers

[az]

see (single) table inheritance and the rest, 
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_inheritance

On Wednesday 03 September 2008 21:59:28 [EMAIL PROTECTED] wrote:
 Hi all,

 I just started playing with SQLAlchemy today (after several years
 of plain SQL experience) and I must say I'm impressed. I'm reading
 my way through the docs now, but there is one thing I can't seem to
 find. Let me briefly explain the situation.

 I was given the task of rewriting a database which is in use for
 many years now. And since many applications depend on its current
 structure I can only make small changes at the time. My plan is to
 rewrite all the attached applications but this time abstracting the
 app's logic from the data-structure itself. I think that SQLAlchemy
 will allow me to achieve this task by building a library of POPO's
 and some mappers to the data-structure. In that way I can rework
 the database and only have to adapt the mappers to keep my app's
 running. So I started that and immediately stumbled upon a 'common'
 situation which I don't now how to solve in SQLA. So here goes:

 I have 1 table (mytable) which is structured somewhat like this:
 id = int (primary key)
 name = varchar()
 type = int

 Now all rows with a type, say 1 'constitute' a MyObject. And rows
 with type say 2 are MyOtherObject instances, and so on. So in my
 applications I want to create a class like this:

 class MyObject(object):
 def __init__(self, name):
 self.name = name

 Then I need to map this to the database. So I write a mapper like
 this:
 myobject_table = select([mytable], mytable.c.type ==
 1).alias('somealias') (not sure if this is entirely correct. I'm
 writing this post at home and don't have access to my code at the
 office. But this is not the point so...)
 mapper(MyObject, myobject_table)

 So far all ok, but now when I insert new instances of type
 MyObject, the type column is not filled with value 1. The instance
 is inserted ok except for this 'hidden' column. I don't want to add
 this column to my MyObject class since I foresee that the structure
 of my DB will change and then there will be no more value for the
 type column. The column 'type' belongs to the internals of my
 data-structure and shouldn't be visible in my app's. In the new
 structure there will be a table just for MyObject instances.

 Does any guru out there knows how to solve this rather 'common'
 problem?

 Many thanks for reading this post!

 --
 Wim

 


--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter

[Michael Brickenstein]

and here is the new traceback ;-)

Traceback (most recent call last):
  File foo.py, line 38, in module
DBSession.flush()
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/scoping.py,
line 106, in do
return getattr(self.registry(), name)(*args, **kwargs)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/session.py,
line 1409, in flush
flush_context.execute()
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
unitofwork.py, line 265, in execute
UOWExecutor().execute(self, tasks)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
unitofwork.py, line 753, in execute
self.execute_save_steps(trans, task)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
unitofwork.py, line 771, in execute_save_steps
self.execute_dependencies(trans, task, True)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
unitofwork.py, line 783, in execute_dependencies
self.execute_dependency(trans, dep, True)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
unitofwork.py, line 765, in execute_dependency
dep.execute(trans, isdelete)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
unitofwork.py, line 722, in execute
self.processor.process_dependencies(self.targettask, [elem.state
for elem in self.targettask.polymorphic_todelete_elements], trans,
delete=True)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
dependency.py, line 181, in process_dependencies
self._synchronize(state, child, None, True, uowcommit)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/
dependency.py, line 251, in _synchronize
sync.clear(dest, self.mapper, self.prop.synchronize_pairs)
  File /Users/michael/programming/rumdemo3/lib/python2.5/site-
packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/sync.py, line
28, in clear
raise AssertionError(Dependency rule tried to blank-out primary
key column '%s' on instance '%s' % (r, mapperutil.state_str(dest)))
AssertionError: Dependency rule tried to blank-out primary key column
'project_programming_language.programming_language_id' on instance
'[EMAIL PROTECTED]'

--~--~-~--~~~---~--~~
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: default values for columns in select mappers

[Wim Verhavert]

Thanks for the quick answers. But I'm left with some side-effect I'm a
little bit struggling with: in order for this to work myObject and
myOtherObject need to inherit some base class let's say 'entity'. Now
the ones who created the database clearly didn't had much experience
with databases (damn MS Access for making databases that accessible!)
because they simply put several unrelated objects into one table. The
objects share some properties, for example 'name', but they also have
other properties specific for the object (so column 'x' only has
relevance for type 1 and column 'y' only for type 2 and so on). Don't
tell me this is wrong, I know and I want to correct this, but I simply
can't at this stage since to many apps out there depend on this
structure. So actually I want myObject and myOtherObject to inherit
only from 'object'. Can this be done?

On Thu, Sep 4, 2008 at 9:01 AM,  [EMAIL PROTECTED] wrote:

 see (single) table inheritance and the rest,
 http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_inheritance

 On Wednesday 03 September 2008 21:59:28 [EMAIL PROTECTED] wrote:
 Hi all,

 I just started playing with SQLAlchemy today (after several years
 of plain SQL experience) and I must say I'm impressed. I'm reading
 my way through the docs now, but there is one thing I can't seem to
 find. Let me briefly explain the situation.

 I was given the task of rewriting a database which is in use for
 many years now. And since many applications depend on its current
 structure I can only make small changes at the time. My plan is to
 rewrite all the attached applications but this time abstracting the
 app's logic from the data-structure itself. I think that SQLAlchemy
 will allow me to achieve this task by building a library of POPO's
 and some mappers to the data-structure. In that way I can rework
 the database and only have to adapt the mappers to keep my app's
 running. So I started that and immediately stumbled upon a 'common'
 situation which I don't now how to solve in SQLA. So here goes:

 I have 1 table (mytable) which is structured somewhat like this:
 id = int (primary key)
 name = varchar()
 type = int

 Now all rows with a type, say 1 'constitute' a MyObject. And rows
 with type say 2 are MyOtherObject instances, and so on. So in my
 applications I want to create a class like this:

 class MyObject(object):
 def __init__(self, name):
 self.name = name

 Then I need to map this to the database. So I write a mapper like
 this:
 myobject_table = select([mytable], mytable.c.type ==
 1).alias('somealias') (not sure if this is entirely correct. I'm
 writing this post at home and don't have access to my code at the
 office. But this is not the point so...)
 mapper(MyObject, myobject_table)

 So far all ok, but now when I insert new instances of type
 MyObject, the type column is not filled with value 1. The instance
 is inserted ok except for this 'hidden' column. I don't want to add
 this column to my MyObject class since I foresee that the structure
 of my DB will change and then there will be no more value for the
 type column. The column 'type' belongs to the internals of my
 data-structure and shouldn't be visible in my app's. In the new
 structure there will be a table just for MyObject instances.

 Does any guru out there knows how to solve this rather 'common'
 problem?

 Many thanks for reading this post!

 --
 Wim




 


--~--~-~--~~~---~--~~
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: default values for columns in select mappers

[az]

AFAIK for the single inh. your object hierarchy makes no difference - 
it all goes in one table, regardless if it is one class of whole tree 
of not-realy-related-ones. what is the python side of things is up to 
you. why is that entity base class bothering you? declare it just 
inheriting object without attributes, but dont use it..
or maybe i dont understand what u want.. wait for other replies.

On Thursday 04 September 2008 11:22:08 Wim Verhavert wrote:
 Thanks for the quick answers. But I'm left with some side-effect
 I'm a little bit struggling with: in order for this to work
 myObject and myOtherObject need to inherit some base class let's
 say 'entity'. Now the ones who created the database clearly didn't
 had much experience with databases (damn MS Access for making
 databases that accessible!) because they simply put several
 unrelated objects into one table. The objects share some
 properties, for example 'name', but they also have other properties
 specific for the object (so column 'x' only has relevance for type
 1 and column 'y' only for type 2 and so on). Don't tell me this is
 wrong, I know and I want to correct this, but I simply can't at
 this stage since to many apps out there depend on this structure.
 So actually I want myObject and myOtherObject to inherit only from
 'object'. Can this be done?

 On Thu, Sep 4, 2008 at 9:01 AM,  [EMAIL PROTECTED] wrote:
  see (single) table inheritance and the rest,
  http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_map
 per_inheritance
 
  On Wednesday 03 September 2008 21:59:28 [EMAIL PROTECTED] 
wrote:
  Hi all,
 
  I just started playing with SQLAlchemy today (after several
  years of plain SQL experience) and I must say I'm impressed. I'm
  reading my way through the docs now, but there is one thing I
  can't seem to find. Let me briefly explain the situation.
 
  I was given the task of rewriting a database which is in use for
  many years now. And since many applications depend on its
  current structure I can only make small changes at the time. My
  plan is to rewrite all the attached applications but this time
  abstracting the app's logic from the data-structure itself. I
  think that SQLAlchemy will allow me to achieve this task by
  building a library of POPO's and some mappers to the
  data-structure. In that way I can rework the database and only
  have to adapt the mappers to keep my app's running. So I started
  that and immediately stumbled upon a 'common' situation which I
  don't now how to solve in SQLA. So here goes:
 
  I have 1 table (mytable) which is structured somewhat like this:
  id = int (primary key)
  name = varchar()
  type = int
 
  Now all rows with a type, say 1 'constitute' a MyObject. And
  rows with type say 2 are MyOtherObject instances, and so on. So
  in my applications I want to create a class like this:
 
  class MyObject(object):
  def __init__(self, name):
  self.name = name
 
  Then I need to map this to the database. So I write a mapper
  like this:
  myobject_table = select([mytable], mytable.c.type ==
  1).alias('somealias') (not sure if this is entirely correct. I'm
  writing this post at home and don't have access to my code at
  the office. But this is not the point so...)
  mapper(MyObject, myobject_table)
 
  So far all ok, but now when I insert new instances of type
  MyObject, the type column is not filled with value 1. The
  instance is inserted ok except for this 'hidden' column. I don't
  want to add this column to my MyObject class since I foresee
  that the structure of my DB will change and then there will be
  no more value for the type column. The column 'type' belongs to
  the internals of my data-structure and shouldn't be visible in
  my app's. In the new structure there will be a table just for
  MyObject instances.
 
  Does any guru out there knows how to solve this rather 'common'
  problem?
 
  Many thanks for reading this post!
 
  --
  Wim

 


--~--~-~--~~~---~--~~
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: default values for columns in select mappers

[Wim Verhavert]

Well let me be more concrete, I'll show you part of the mess I'm in:

We have persons, called contacts, and departments. For some crazy
reason the previous designers put them all in one table (called
'contacts' can you believe this?!). Now they share almost nothing but
a name. There all kinds of columns (like address and such) but only
relate to persons while there are other columns that only relate to
departments. Now I want to clearly separate the two, but by inheriting
them from 'entity' I somehow relate the two, as such this is actually
a minor consern, what is bothering me is that departments have a 'head
of department' which is a person of course. So the guys created a
'relation' table which maps contacts with other contacts by using a
type indicator. So for example contactid 100 (which is actually a
department because its typeid in the contacts table says so) is
related to contact 235 (which is a person and thus the head of the
department). So in the 'relations' table we can find something like:

contactid  |  relation  |  contactid
---
100  |  1   |  235

Since relation 1 means 'head of ... we can derive from this that
contact 235 is head of department 100 (which is also a contact).

I don't know for you guys, but this is a terrible design.
So what I was looking after was to do something like this in my python code:

class Person(object):
def __init__(self, name):
self.name = name

class Department(object):
def __init__(self, name, head):
self.name = name
self.head = head  # an instance of a person

Is there a way I can setup the mappers of SQLA to do this. I would
understand if it can't, because this is a terrible design of course,
but I'm sure you all have seen some terrible things in your career...

Many thanks!

--
Wim

On Thu, Sep 4, 2008 at 11:04 AM,  [EMAIL PROTECTED] wrote:

 AFAIK for the single inh. your object hierarchy makes no difference -
 it all goes in one table, regardless if it is one class of whole tree
 of not-realy-related-ones. what is the python side of things is up to
 you. why is that entity base class bothering you? declare it just
 inheriting object without attributes, but dont use it..
 or maybe i dont understand what u want.. wait for other replies.

 On Thursday 04 September 2008 11:22:08 Wim Verhavert wrote:
 Thanks for the quick answers. But I'm left with some side-effect
 I'm a little bit struggling with: in order for this to work
 myObject and myOtherObject need to inherit some base class let's
 say 'entity'. Now the ones who created the database clearly didn't
 had much experience with databases (damn MS Access for making
 databases that accessible!) because they simply put several
 unrelated objects into one table. The objects share some
 properties, for example 'name', but they also have other properties
 specific for the object (so column 'x' only has relevance for type
 1 and column 'y' only for type 2 and so on). Don't tell me this is
 wrong, I know and I want to correct this, but I simply can't at
 this stage since to many apps out there depend on this structure.
 So actually I want myObject and myOtherObject to inherit only from
 'object'. Can this be done?

 On Thu, Sep 4, 2008 at 9:01 AM,  [EMAIL PROTECTED] wrote:
  see (single) table inheritance and the rest,
  http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_map
 per_inheritance
 
  On Wednesday 03 September 2008 21:59:28 [EMAIL PROTECTED]
 wrote:
  Hi all,
 
  I just started playing with SQLAlchemy today (after several
  years of plain SQL experience) and I must say I'm impressed. I'm
  reading my way through the docs now, but there is one thing I
  can't seem to find. Let me briefly explain the situation.
 
  I was given the task of rewriting a database which is in use for
  many years now. And since many applications depend on its
  current structure I can only make small changes at the time. My
  plan is to rewrite all the attached applications but this time
  abstracting the app's logic from the data-structure itself. I
  think that SQLAlchemy will allow me to achieve this task by
  building a library of POPO's and some mappers to the
  data-structure. In that way I can rework the database and only
  have to adapt the mappers to keep my app's running. So I started
  that and immediately stumbled upon a 'common' situation which I
  don't now how to solve in SQLA. So here goes:
 
  I have 1 table (mytable) which is structured somewhat like this:
  id = int (primary key)
  name = varchar()
  type = int
 
  Now all rows with a type, say 1 'constitute' a MyObject. And
  rows with type say 2 are MyOtherObject instances, and so on. So
  in my applications I want to create a class like this:
 
  class MyObject(object):
  def __init__(self, name):
  self.name = name
 
  Then I need to map this to the database. So I write a mapper
  like this:
  myobject_table = select([mytable], 

[sqlalchemy] Re: default values for columns in select mappers

[az]

thats still not much of a mess, at least u have 5 tables and not 500.

see, i've never used single table inh, and i'm not sql fan at all - 
thats why i made dbcook.sf.net - but maybe it looks like:

entity_table = Table('contacts', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('dept_data', String(50)),
Column('person_info', String(50)),
Column('type', whatevertypeitis, nullable=False)
) #i guess this can be autoloaded too

rela_table = Table('relatable', metadata,
Column('left_id', Integer, ForeignKey('contacs.id')),
Column('right_id', Integer, ForeignKey('contacts.id')),
Column('type', Integer, )
 )

class Entity( object):pass
class Dept( Entity): ...
class Person( Entity): ...
class Rela( object): pass

entity_mapper = mapper( Entity, entity_table, 
polymorphic_on= entity_table.c.type,
polymorphic_identity= typeidofany )
dept_mapper = mapper( Dept, inherits= entity_mapper,
  polymorphic_identity= typeidofdepts )
person_mapper = mapper( Person, inherits= entity_mapper,
  polymorphic_identity= typeidofppl )
so far so good. u can check if this reads things properly.

now for your m2m relation... 
if u want the items split by rela.type in different properties, i.e. 
type=1 is always head, type=3 is always somethingelse, then u can 
probably go with implict mapping via secondary table and explicit 
secondary join that spells the id2id link + the type==.. 
i'm not sure how that spells in plain SA, something like:

dept_mapper.add_property( 'head', relation(
Person, secondary_table=rela_table, 
   secondary_join = and_( 
 contacttbl.c.id == rela_table.c.left, 
 contacttbl.c.id == rela_table.c.right, 
 rela_table.c.type == 1 )
 ) )
this will leave u with all other rela.type unused/invisible - unless u 
make other similar props.

otherwise u may need explicit mapping to get the relation.type... 
(assoc.object), i leave that to your exercise.

plz do not expect the above to be THE solution, u may have to fix 
mistakes or tweak or even abandon ... read docs on inheritance, 
relations (many2many), and related.

svil

On Thursday 04 September 2008 11:57:35 Wim Verhavert wrote:
 Well let me be more concrete, I'll show you part of the mess I'm
 in:

 We have persons, called contacts, and departments. For some crazy
 reason the previous designers put them all in one table (called
 'contacts' can you believe this?!). Now they share almost nothing
 but a name. There all kinds of columns (like address and such) but
 only relate to persons while there are other columns that only
 relate to departments. Now I want to clearly separate the two, but
 by inheriting them from 'entity' I somehow relate the two, as such
 this is actually a minor consern, what is bothering me is that
 departments have a 'head of department' which is a person of
 course. So the guys created a 'relation' table which maps contacts
 with other contacts by using a type indicator. So for example
 contactid 100 (which is actually a department because its typeid in
 the contacts table says so) is related to contact 235 (which is a
 person and thus the head of the department). So in the 'relations'
 table we can find something like:

 contactid  |  relation  |  contactid
 ---
 100  |  1   |  235

 Since relation 1 means 'head of ... we can derive from this that
 contact 235 is head of department 100 (which is also a contact).

 I don't know for you guys, but this is a terrible design.
 So what I was looking after was to do something like this in my
 python code:

 class Person(object):
 def __init__(self, name):
 self.name = name

 class Department(object):
 def __init__(self, name, head):
 self.name = name
 self.head = head  # an instance of a person

 Is there a way I can setup the mappers of SQLA to do this. I would
 understand if it can't, because this is a terrible design of
 course, but I'm sure you all have seen some terrible things in your
 career...

 Many thanks!

 --
 Wim

 On Thu, Sep 4, 2008 at 11:04 AM,  [EMAIL PROTECTED] wrote:
  AFAIK for the single inh. your object hierarchy makes no
  difference - it all goes in one table, regardless if it is one
  class of whole tree of not-realy-related-ones. what is the python
  side of things is up to you. why is that entity base class
  bothering you? declare it just inheriting object without
  attributes, but dont use it..
  or maybe i dont understand what u want.. wait for other replies.
 
  On Thursday 04 September 2008 11:22:08 Wim Verhavert wrote:
  Thanks for the quick answers. But I'm left with some side-effect
  I'm a little bit struggling with: in order for this to work
  myObject and myOtherObject need to inherit some base class let's
  say 'entity'. Now the ones who created the database clearly
  didn't had much experience with databases (damn MS Access for
  making databases that 

[sqlalchemy] Re: default values for columns in select mappers

[Wim Verhavert]

Thanks for the response. I will read into the docs a little bit more
and let you know what I came up with...

On Thu, Sep 4, 2008 at 11:32 AM,  [EMAIL PROTECTED] wrote:

 thats still not much of a mess, at least u have 5 tables and not 500.

 see, i've never used single table inh, and i'm not sql fan at all -
 thats why i made dbcook.sf.net - but maybe it looks like:

 entity_table = Table('contacts', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('dept_data', String(50)),
Column('person_info', String(50)),
Column('type', whatevertypeitis, nullable=False)
 ) #i guess this can be autoloaded too

 rela_table = Table('relatable', metadata,
Column('left_id', Integer, ForeignKey('contacs.id')),
Column('right_id', Integer, ForeignKey('contacts.id')),
Column('type', Integer, )
  )

 class Entity( object):pass
 class Dept( Entity): ...
 class Person( Entity): ...
 class Rela( object): pass

 entity_mapper = mapper( Entity, entity_table,
polymorphic_on= entity_table.c.type,
polymorphic_identity= typeidofany )
 dept_mapper = mapper( Dept, inherits= entity_mapper,
  polymorphic_identity= typeidofdepts )
 person_mapper = mapper( Person, inherits= entity_mapper,
  polymorphic_identity= typeidofppl )
 so far so good. u can check if this reads things properly.

 now for your m2m relation...
 if u want the items split by rela.type in different properties, i.e.
 type=1 is always head, type=3 is always somethingelse, then u can
 probably go with implict mapping via secondary table and explicit
 secondary join that spells the id2id link + the type==..
 i'm not sure how that spells in plain SA, something like:

 dept_mapper.add_property( 'head', relation(
Person, secondary_table=rela_table,
   secondary_join = and_(
 contacttbl.c.id == rela_table.c.left,
 contacttbl.c.id == rela_table.c.right,
 rela_table.c.type == 1 )
  ) )
 this will leave u with all other rela.type unused/invisible - unless u
 make other similar props.

 otherwise u may need explicit mapping to get the relation.type...
 (assoc.object), i leave that to your exercise.

 plz do not expect the above to be THE solution, u may have to fix
 mistakes or tweak or even abandon ... read docs on inheritance,
 relations (many2many), and related.

 svil

 On Thursday 04 September 2008 11:57:35 Wim Verhavert wrote:
 Well let me be more concrete, I'll show you part of the mess I'm
 in:

 We have persons, called contacts, and departments. For some crazy
 reason the previous designers put them all in one table (called
 'contacts' can you believe this?!). Now they share almost nothing
 but a name. There all kinds of columns (like address and such) but
 only relate to persons while there are other columns that only
 relate to departments. Now I want to clearly separate the two, but
 by inheriting them from 'entity' I somehow relate the two, as such
 this is actually a minor consern, what is bothering me is that
 departments have a 'head of department' which is a person of
 course. So the guys created a 'relation' table which maps contacts
 with other contacts by using a type indicator. So for example
 contactid 100 (which is actually a department because its typeid in
 the contacts table says so) is related to contact 235 (which is a
 person and thus the head of the department). So in the 'relations'
 table we can find something like:

 contactid  |  relation  |  contactid
 ---
 100  |  1   |  235

 Since relation 1 means 'head of ... we can derive from this that
 contact 235 is head of department 100 (which is also a contact).

 I don't know for you guys, but this is a terrible design.
 So what I was looking after was to do something like this in my
 python code:

 class Person(object):
 def __init__(self, name):
 self.name = name

 class Department(object):
 def __init__(self, name, head):
 self.name = name
 self.head = head  # an instance of a person

 Is there a way I can setup the mappers of SQLA to do this. I would
 understand if it can't, because this is a terrible design of
 course, but I'm sure you all have seen some terrible things in your
 career...

 Many thanks!

 --
 Wim

 On Thu, Sep 4, 2008 at 11:04 AM,  [EMAIL PROTECTED] wrote:
  AFAIK for the single inh. your object hierarchy makes no
  difference - it all goes in one table, regardless if it is one
  class of whole tree of not-realy-related-ones. what is the python
  side of things is up to you. why is that entity base class
  bothering you? declare it just inheriting object without
  attributes, but dont use it..
  or maybe i dont understand what u want.. wait for other replies.
 
  On Thursday 04 September 2008 11:22:08 Wim Verhavert wrote:
  Thanks for the quick answers. But I'm left with some side-effect
  I'm a little bit struggling with: in order for this to work
  myObject and myOtherObject need to 

[sqlalchemy] Comparing tuples

[Christoph Zwerschke]

Is there a simple way of comparing tuples in the SQLAlchemy query
language, like (User.last_name, User.first_name)  ('Joe', 'Doe')?
SQL-wise it is possible (at least with PostgreSQL), but you cannot write
this as a filter expression.

-- Christoph

--~--~-~--~~~---~--~~
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: Multiple Inheritance

[Michael Bayer]

On Sep 3, 2008, at 11:25 PM, Michael Bayer wrote:


 This pattern doesnt entirely make sense - the citizen_type and
 employee_type columns seem superfluous and redundant against each
 other, since we really can't load Engineer rows without querying all
 three tables.  In that sense it takes on all the limitations of
 concrete table inheritance, which doesnt use a type column at the
 table level.


after a night's sleep, let me backtrack a bit.having  
discriminiator columns in all superclass tables would probably still  
be effective in the way we use discriminiator columns right now.   the  
mapper would basically have to use one or the other in the case where  
more than one is available (like, querying subclasses of Engineer).

You would want to share the primary key column across all three tables  
though (i.e. foreign key in the subclass table, like the link I  
mentioned) so that the primary key takes on the same form no matter  
what class you're looking at - that helps inheritance a great deal  
since its one of the assumptions SQLA makes.

The change to SA's internals would still be pretty heavy and its hard  
to say what kinds of roadblocks would appear when developing such a  
feature.

--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter

[Michael Bayer]

On Sep 4, 2008, at 4:15 AM, Michael Brickenstein wrote:

 AssertionError: Dependency rule tried to blank-out primary key column
 'project_programming_language.programming_language_id' on instance
 '[EMAIL PROTECTED]'


the project_programming_language table's primary key is  
programming_language_id, and this column is a foreign key to  
programming_language's primary key column.  You can't delete a row  
from programming_language without also deleting the row from  
project_programming_language.   Set cascade='all, delete-orphan' on  
the project_languages relation.


--~--~-~--~~~---~--~~
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: sql templating for those sql lovers (or just people who work with dirty legacy enterprise data)

[Michael Bayer]

On Sep 4, 2008, at 1:39 AM, gniquil wrote:

 documented. The sqlalchemy expression language can't really do very
 well here either (at least too proprietary in some ways...sort of like
 working with pylons versus working with a CMS like Plone, which both
 can be used to create a blog, but one gives more control, and the
 knowledge is more readily out there).

well the statement above can pretty easily be represented with a SQLA  
select()but im sensing some personal preference stuff here which  
is OK.

 Anyway, to keep this short, can anyone find a way to somehow integrate
 such a templating idea into sqlalchemy while leveraging the convention
 already established in ansi-sql and some python voodoo? (Of course,
 the existing stuff is great for creating transactional type of
 backend, like a blog or wiki, which then again, really occupies 10% of
 my time, and hopefully (or not) others as well.)

it would pretty much be  
connection.execute(my_sql_template.render()) .  or perhaps using  
text() if you'd like some of SQLA's bind param/result typing  
capabilities.The SQL expression tutorial has info on select(),  
text(), and execute().

--~--~-~--~~~---~--~~
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: Comparing tuples

[Christoph Zwerschke]

 Is there a simple way of comparing tuples in the SQLAlchemy query
 language, like (User.last_name, User.first_name)  ('Joe', 'Doe')?
 SQL-wise it is possible (at least with PostgreSQL), but you cannot write
 this as a filter expression.

Tried the following, but it does not work:

print session.query(User).filter(
text((id, name)  (%(fist_name)s, %(last_name)s),
bindparams=[
bindparam('first_name', 'Joe'),
bindparam('last_name', 'Doe')]
)).all()

What's wrong with that?

-- Christoph

--~--~-~--~~~---~--~~
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: using the native c implementation of ordereddict

[az]

On Thursday 04 September 2008 15:42:56 Michael Bayer wrote:
 On Sep 4, 2008, at 12:40 AM, gniquil wrote:
  Hi All,
 
  I am doing some work with xmlrpc. One thing I realize is that
  whenever I pass dict(row) through xmlrpc, I get an key-ordered
  struct. But this isn't what i really want. What I want is ordered
  by insertion or the original list order. This led me to look at
  the util.ordereddict implementation, which is pure python, which
  is slow. I looked around and found this:
 
  http://www.xs4all.nl/~anthon/Python/ordereddict/
 
  which is a c-implementation. At the bottom of the page, there are
  performance tests. It's much faster. I've got some pretty
  gigantic tables to pass around, which i think this would really
  help. Hopefully this could somehow find itself into next official
  python. But before that, we can use this or we can just
  incorporate it somehow in sqlalchemy...as a suggestion.

 the problem with saying utility class X is slow, therefore use Y
 is that you haven't evaluated if the slowness of X is really
 impacting the performance of SQLAlchemy overall in a negative way. 
  I think if you ran some profiling results you'd see that
 OrderedDict calls make up a miniscule portion of time spent for
 doing all operations, so an external dependency is not necessarily
 worth it in this case (though it may be).  I have some vague
 recollection that our own ODict does some things the native one
 does not but I'd have to dig back into the code to remember what
 they were.   If our own ODict could be swappable with ordereddict,
 we could at least try to import it then fall back to our own (this
 is what it would look like if ordereddict were introduced into
 python core anyway).

i used to set sqlalchemy.util.Set to be sqlalchemy.util.OrderedSet and 
that worked well... 
if all those basic things (dict, set, odict, oset, etc) are always 
routed via sqlachemy.util, then one can replace them with whatever 
fancy. One main usage is that testing cases would be more repeatable, 
because flush plans and other hash-relating things will be same, if 
all sets are ordered and all dicts are ordered.
this is not going to impact anything speedwise, it only means changing 
several hundred places where {} or set() is used, and keeping some 
discipline of not introducing those in future code.

someone may tell me about a way to directly hack pythons notion of {} 
with something mine... would be good but is going to impact speed of 
*any* code, not just SA.

mike, sorry for repeating myself again on the theme :-)
i can prepare The patch as long as u decide to keep such protocol...

svil

--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter

[Michael Brickenstein]

Dear Michael!
Thanks, I got it and understand the difference now.
Thank you very much for your help and your
time.
Michael
Am 04.09.2008 um 14:35 schrieb Michael Bayer:



 On Sep 4, 2008, at 4:15 AM, Michael Brickenstein wrote:

 AssertionError: Dependency rule tried to blank-out primary key column
 'project_programming_language.programming_language_id' on instance
 '[EMAIL PROTECTED]'


 the project_programming_language table's primary key is
 programming_language_id, and this column is a foreign key to
 programming_language's primary key column.  You can't delete a row
 from programming_language without also deleting the row from
 project_programming_language.   Set cascade='all, delete-orphan' on
 the project_languages relation.


 

--~--~-~--~~~---~--~~
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: Comparing tuples

[Christoph Zwerschke]

This does not work either:

print session.query(User).filter(
text((last_name, first_name)  (%(last_name)s, %(first_name)s),
)).params(first_name='Joe', last_name='Doe').all()

Running out of ideas...

-- Christoph

--~--~-~--~~~---~--~~
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: Comparing tuples

[Michael Bayer]

just use the plain string and not text(), and use :paramname as the  
bind param format.  example is here: 
http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_querying_using


On Sep 4, 2008, at 9:25 AM, Christoph Zwerschke wrote:


 This does not work either:

 print session.query(User).filter(
text((last_name, first_name)  (%(last_name)s, %(first_name)s),
)).params(first_name='Joe', last_name='Doe').all()

 Running out of ideas...

 -- Christoph

 


--~--~-~--~~~---~--~~
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: using the native c implementation of ordereddict

[jason kirtland]

Michael Bayer wrote:
 
 On Sep 4, 2008, at 12:40 AM, gniquil wrote:
 
 Hi All,

 I am doing some work with xmlrpc. One thing I realize is that whenever
 I pass dict(row) through xmlrpc, I get an key-ordered struct. But this
 isn't what i really want. What I want is ordered by insertion or the
 original list order. This led me to look at the util.ordereddict
 implementation, which is pure python, which is slow. I looked around
 and found this:

 http://www.xs4all.nl/~anthon/Python/ordereddict/

 which is a c-implementation. At the bottom of the page, there are
 performance tests. It's much faster. I've got some pretty gigantic
 tables to pass around, which i think this would really help. Hopefully
 this could somehow find itself into next official python. But before
 that, we can use this or we can just incorporate it somehow in
 sqlalchemy...as a suggestion.

 
 the problem with saying utility class X is slow, therefore use Y is  
 that you haven't evaluated if the slowness of X is really impacting  
 the performance of SQLAlchemy overall in a negative way.   I think if  
 you ran some profiling results you'd see that OrderedDict calls make  
 up a miniscule portion of time spent for doing all operations, so an  
 external dependency is not necessarily worth it in this case (though  
 it may be).  I have some vague recollection that our own ODict does  
 some things the native one does not but I'd have to dig back into the  
 code to remember what they were.   If our own ODict could be swappable  
 with ordereddict, we could at least try to import it then fall back to  
 our own (this is what it would look like if ordereddict were  
 introduced into python core anyway).

fwiw i spiked this out a while back (just before 0.4.0, maybe), and 
swapping in a native ordered dict was a very marginal speed improvement, 
and most of it was in metadata setup rather than runtime speed.

as svil said, it's easy to try this out by monkeypatching in alternate 
implementations and then hitting the various profiling and speed tests 
in the test suite.

--~--~-~--~~~---~--~~
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: Comparing tuples

[Christoph Zwerschke]

Michael Bayer schrieb:
 just use the plain string and not text(), and use :paramname as the  
 bind param format.  example is here: 
 http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_querying_using

Excellent, that works:

print session.query(User).filter(
(last_name, first_name)  (:last_name, :first_name),
).params(first_name='Joe', last_name='Doe').all()

I had looked into the 0.4 docs only...

-- Christoph

--~--~-~--~~~---~--~~
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: using the native c implementation of ordereddict

[az]

On Thursday 04 September 2008 17:51:56 Michael Bayer wrote:
 On Sep 4, 2008, at 9:03 AM, [EMAIL PROTECTED] wrote:
  i used to set sqlalchemy.util.Set to be
  sqlalchemy.util.OrderedSet and that worked well...
  if all those basic things (dict, set, odict, oset, etc) are
  always routed via sqlachemy.util, then one can replace them with
  whatever fancy. One main usage is that testing cases would be
  more repeatable, because flush plans and other hash-relating
  things will be same, if all sets are ordered and all dicts are
  ordered.
  this is not going to impact anything speedwise, it only means
  changing several hundred places where {} or set() is used, and
  keeping some discipline of not introducing those in future code.
 
  someone may tell me about a way to directly hack pythons notion
  of {} with something mine... would be good but is going to impact
  speed of *any* code, not just SA.
 
  mike, sorry for repeating myself again on the theme :-)
  i can prepare The patch as long as u decide to keep such
  protocol...

 i believe we already have a layer in the test/ suite which can
 globally replace imports with something specific, and it is being
 used.  It's Jason's thing but you can dig into the source to see
 how it works.

nooo, u got me wrong. i'd like all the {} dict() set() usage 
all-over-sa to be routed via util.dict and util.set (which default to 
the builtins), so then one could easily replace them with whatever 
s/he wants. i guess one could hack the python builtins/module or 
globals() to replace the dict() and set() globally, but i dont think 
{} is affected; still, such hack will affect any other code, and not 
only SA.

Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52)
 dict  
type 'dict'
 globals()
{'__builtins__': module '__builtin__' 
(built-in), '__name__': '__main__', '__doc__': None}
 locals()
{'__builtins__': module '__builtin__' 
(built-in), '__name__': '__main__', '__doc__': None}
 __builtins__
module '__builtin__' (built-in)
 __builtins__.dict
type 'dict'

 class mydict( dict): pass
... 
 __builtins__.dict = mydict
 dict
class '__main__.mydict'
 type({})
type 'dict'
 

--~--~-~--~~~---~--~~
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: csv engine?

[Michael Bayer]

On Sep 3, 2008, at 4:25 PM, Lukasz Szybalski wrote:


 Hello,
 I was wondering if there are any plans to have a csv engine for
 sqlalchemy. I would like to see support for csv. There are some cases
 where csv is the best way to convert data to and from especially when
 they require cleaning. What I would like to see is a sqlalchemy
 wrapping over csv module and providing file definition and some of the
 sql functionality into csv world. This would be really helpful when
 moving things over from one system to another.

 Ideas?

my first impression is odbc driver for excel files ?   SQLA is really  
oriented around DBAPI so that would be the starting point.



--~--~-~--~~~---~--~~
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: Multiple Inheritance

[Sam Magister]

Michael,

Thanks for the thoughtful replies. I'm going to explore the options
you raised here. I'll post back with any insights I come to.

Best,

Sam
--~--~-~--~~~---~--~~
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: Multiple Inheritance

[Sam Magister]

On Sep 3, 8:25 pm, Michael Bayer [EMAIL PROTECTED] wrote:

 You can certainly map to any of the hierarchies indicated in that  
 article, but you wouldn't be able to take advantage of SQLA's  
 polymorphic capabilities, which are designed to only handle single  
 inheritance.   You'd really want to make your Engineer(Employee,  
 Citizen) class and just map it to  
 engineers.join(citizens).join(employees).   That would get your schema  
 going, just without SQLA having any awareness of the inheritance  
 portion of it, and is extremely similar to a plain concrete setup,  
 which is pretty much all you'd get anyway without the ability to load  
 polymorphically.


Michael, what would the mapper function look like if it were to map
Engineer(Employee, Citizen) to
engineers.join(citizens).join(employees). What argument of the mapper
would that join condition be in? I think concrete inheritance might be
the way to go about things, at the cost of the nice polymorphic
loading features.

Thanks,

Sam
--~--~-~--~~~---~--~~
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: Multiple Inheritance

[Michael Bayer]

sorry for the rant.  my second response is closer to the mark.


On Sep 4, 2008, at 2:01 PM, Sam Magister wrote:


 Michael,

 Thanks for the thoughtful replies. I'm going to explore the options
 you raised here. I'll post back with any insights I come to.

 Best,

 Sam
 


--~--~-~--~~~---~--~~
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: Multiple Inheritance

[Michael Bayer]

On Sep 4, 2008, at 2:12 PM, Sam Magister wrote:

 Michael, what would the mapper function look like if it were to map
 Engineer(Employee, Citizen) to
 engineers.join(citizens).join(employees). What argument of the mapper
 would that join condition be in? I think concrete inheritance might be
 the way to go about things, at the cost of the nice polymorphic
 loading features.

that would just be the ordinary table argument.   The join  
conditions are within the join() calls themselves.  mapper(Engineer,  
engineers.join(citizens,...).join(employees, ...)) .   I dont think  
you can even say concrete=True here unless there were an inherits  
argument, in which case you'd have to just pick a superclass out of  
the two it would be better to not use the inherits argument at all  
though (pretty sure SQLA won't complain).

--~--~-~--~~~---~--~~
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] Translating a complex SQL Statement to SqlAlchemy

[Mike]

Hi,

I am trying to translate the following SQL into SqlAlchemy session
syntax:

sql = ''' SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv),
SUM(vac), SUM(ct), SUM(conv), SUM(misc)
FROM tbl_TimeEntries
WHERE dateworked  = '%s' AND dateworked = '%s' AND empid
= %s
''' % (start_date, end_date, emp_id)

I found the apply_sum query method, but this seems to be able to
only be applied to one column at a time. I haven't found a way to use
equality operators (= or =) yet. According to the SqlAchemy book by
Copeland, I should be able to use the equality operators with table
methods and the bitwise  operator. I prefer using sessions since
they seem easier to clean up in wxPython, but I'm pretty flexible.

Any hints are welcome. Thanks!

Mike
--~--~-~--~~~---~--~~
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: Translating a complex SQL Statement to SqlAlchemy

[Michael Bayer]

On Sep 4, 2008, at 3:14 PM, Mike wrote:


 Hi,

 I am trying to translate the following SQL into SqlAlchemy session
 syntax:

 sql = ''' SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv),
 SUM(vac), SUM(ct), SUM(conv), SUM(misc)
FROM tbl_TimeEntries
WHERE dateworked  = '%s' AND dateworked = '%s' AND empid
 = %s
''' % (start_date, end_date, emp_id)

 I found the apply_sum query method, but this seems to be able to
 only be applied to one column at a time. I haven't found a way to use
 equality operators (= or =) yet. According to the SqlAchemy book by
 Copeland, I should be able to use the equality operators with table
 methods and the bitwise  operator. I prefer using sessions since
 they seem easier to clean up in wxPython, but I'm pretty flexible.

 Any hints are welcome. Thanks!

apply_sum() is totally out; I'd recommend not using it.

The SUM constructs are expressed using func.sum().

The Copeland book only covers SQLA 0.4 through about midway; in that  
version, a query such as the above is usually not issued via the ORM  
and would instead be via select() construct:

select([func.sum(table.c.reg),  
func.sum(table.c.ot), ...]).where(criterion)

recent versions of 0.4 do support values(), which is probably not  
mentioned in the book since its recent:

session.query(TimeEntry).values(func.sum(TimeEntry.reg),  
func.sum(TimeEntry.ot), ...)

in 0.5, query() can also handle column expressions at the start:

session.query(func.sum(TimeEntry.reg), func.sum(TimeEntry.ot), ...)

The WHERE criterion can be AND ed together using the bitwise   
operator (though you need to watch your parenthesis), or the and_()  
function.   The ORM and expression tutorials on the site have plenty  
of examples on this.








--~--~-~--~~~---~--~~
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: Translating a complex SQL Statement to SqlAlchemy

[Mike]

Hi,

On Sep 4, 2:32 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Sep 4, 2008, at 3:14 PM, Mike wrote:





  Hi,

  I am trying to translate the following SQL into SqlAlchemy session
  syntax:

  sql = ''' SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv),
  SUM(vac), SUM(ct), SUM(conv), SUM(misc)
             FROM tbl_TimeEntries
             WHERE dateworked  = '%s' AND dateworked = '%s' AND empid
  = %s
             ''' % (start_date, end_date, emp_id)

  I found the apply_sum query method, but this seems to be able to
  only be applied to one column at a time. I haven't found a way to use
  equality operators (= or =) yet. According to the SqlAchemy book by
  Copeland, I should be able to use the equality operators with table
  methods and the bitwise  operator. I prefer using sessions since
  they seem easier to clean up in wxPython, but I'm pretty flexible.

  Any hints are welcome. Thanks!

 apply_sum() is totally out; I'd recommend not using it.

 The SUM constructs are expressed using func.sum().

 The Copeland book only covers SQLA 0.4 through about midway; in that  
 version, a query such as the above is usually not issued via the ORM  
 and would instead be via select() construct:

 select([func.sum(table.c.reg),  
 func.sum(table.c.ot), ...]).where(criterion)

 recent versions of 0.4 do support values(), which is probably not  
 mentioned in the book since its recent:

 session.query(TimeEntry).values(func.sum(TimeEntry.reg),  
 func.sum(TimeEntry.ot), ...)

 in 0.5, query() can also handle column expressions at the start:

 session.query(func.sum(TimeEntry.reg), func.sum(TimeEntry.ot), ...)

 The WHERE criterion can be AND ed together using the bitwise   
 operator (though you need to watch your parenthesis), or the and_()  
 function.   The ORM and expression tutorials on the site have plenty  
 of examples on this.

Thanks for the advice. I had found lots of examples of and_ in the
SQL Expression Tutorial, but that seems to deal exclusively with
table queries. The ORM tutorial has 2 instances of and_ under the
filter operators section, but I missed those before I posted.

Anyway, I think you've answered my question. Now I'll just need to
take a whack at implementing the details. Thanks again!

Mike
--~--~-~--~~~---~--~~
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] bug? order_by + get(some_id) results in InvalidRequestError

[Jon]

I'm using 0.4.6 but I thought I'd give 0.5b3 a try.

An existing (working) query failed with:

Query.__no_criterion() being called on a Query with existing
criterion.

I tracked that down to using order_by when building the query.
An example is below:

q = dbsess.query(Obj)
q = q.order_by(Obj.name)
instance = q.get(some_id)

Why does this happen and is it a bug?

--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

On Sep 4, 2008, at 4:17 PM, Jon wrote:


 I'm using 0.4.6 but I thought I'd give 0.5b3 a try.

 An existing (working) query failed with:

 Query.__no_criterion() being called on a Query with existing
 criterion.

 I tracked that down to using order_by when building the query.
 An example is below:

 q = dbsess.query(Obj)
 q = q.order_by(Obj.name)
 instance = q.get(some_id)

 Why does this happen and is it a bug?

it doesn't make sense to call order_by() before calling get().   SQLA  
raises an error instead of silently ignoring criterion which can't be  
applied.




--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

On Sep 4, 2008, at 4:32 PM, Michael Bayer wrote:



 On Sep 4, 2008, at 4:17 PM, Jon wrote:


 I'm using 0.4.6 but I thought I'd give 0.5b3 a try.

 An existing (working) query failed with:

 Query.__no_criterion() being called on a Query with existing
 criterion.

 I tracked that down to using order_by when building the query.
 An example is below:

 q = dbsess.query(Obj)
 q = q.order_by(Obj.name)
 instance = q.get(some_id)

 Why does this happen and is it a bug?

 it doesn't make sense to call order_by() before calling get().   SQLA
 raises an error instead of silently ignoring criterion which can't be
 applied.


I made a correction in r5084 such that the message is now:

sqlalchemy.exc.InvalidRequestError: Query.get() being called on a  
Query with existing criterion.



--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Jon]

On Sep 4, 3:32 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Sep 4, 2008, at 4:17 PM, Jon wrote:





  I'm using 0.4.6 but I thought I'd give 0.5b3 a try.

  An existing (working) query failed with:

  Query.__no_criterion() being called on a Query with existing
  criterion.

  I tracked that down to using order_by when building the query.
  An example is below:

  q = dbsess.query(Obj)
  q = q.order_by(Obj.name)
  instance = q.get(some_id)

  Why does this happen and is it a bug?

 it doesn't make sense to call order_by() before calling get().   SQLA  
 raises an error instead of silently ignoring criterion which can't be  
 applied.

I'll note that if I use something like this in the ORM mapper
definition:

  order_by=meta.tables['some_table'].c.some_column,

get(pkey) continues to work *and* ORDER BY is used in the SQL.
While ORDER BY doesn't make sense when acquiring just one item, it
doesn't hurt either, and it's one small thing that people converting
from 0.4 would need to know. Is there any reason why compatibility
cannot be retained?


--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Jon]

On Sep 4, 4:33 pm, Jon [EMAIL PROTECTED] wrote:
 On Sep 4, 3:32 pm, Michael Bayer [EMAIL PROTECTED] wrote:



  On Sep 4, 2008, at 4:17 PM, Jon wrote:

   I'm using 0.4.6 but I thought I'd give 0.5b3 a try.

   An existing (working) query failed with:

   Query.__no_criterion() being called on a Query with existing
   criterion.

   I tracked that down to using order_by when building the query.
   An example is below:

   q = dbsess.query(Obj)
   q = q.order_by(Obj.name)
   instance = q.get(some_id)

   Why does this happen and is it a bug?

I'll note that I find building a single query for potentially several
uses quite handy, whether I'm doing a get() or an additional filter/
filter_by. By prebuilding as much of the query as possible I reduce
the overall complexity of my applications, in some cases considerably.
I've found that I can *usually* get around this issue by making use of
the order_by in the ORM layer but that doesn't always help, in
particular when I'm not using get(x).

Often my queries look like this:
q = dbsess.query(Obj)
q = q.join('some_relation')
q = q.order_by(OtherObj.c.columnX)
q = q.options(eagerload('some_other_relation'))
q = q.options(eagerload('some_third_relation'))

and then later, often in another function and depending on the input:

instance = q.get(pkey)
or
instances = q.all()
or even
instances = q.filter(criteria).all()

Being able to make use of a single base query makes my job much easier
- in all cases I know ahead of time the relations and ordering I want,
but in almost every case the function(s) which get the query object
passed to them *don't* know this stuff - if I have to avoid building
my queries this way I'll either have to build multiple sets of queries
(one for get and one for everything else) or move the knowledge
necessary to do so around quite a bit more.

I'm hoping that by outlining my use case, and the fact that query_by
doesn't really change the result of a get either way (hey, if I want
to make an inefficient query...) that I can persuade you to allow the
use of query_by and get(X).


--~--~-~--~~~---~--~~
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] mssql unicode weirdness: empty nvarchar string is selected as u

[desmaj]

I'm setting up my first project that will use SA exclusively for
database access and I've run into some behavior that seems odd to me.

If I insert u into a column that is defined as nvarchar then when I
select that column, I receive u . So I'm receiving a unicode string
containing one space where I would expect to find an empty unicode
string. Does this ring any bells for anyone?

I'm using:
SQLAlchemy 0.5.0beta3
pyodbc 2.0.58
FreeTDS 0.82

Here's a test case:

import
unittest
import
pyodbc
import sqlalchemy as
sa

class
TestMSSQLConnections(unittest.TestCase):

def
setUp(self):

self.host
=
self.port
=
self.host_and_port = %s:%s % (self.host,
self.port)
self.database
=
self.user
=
self.password
=

engine_url = mssql://%s:[EMAIL PROTECTED]/%s %
\
 (self.user,
self.password,
  self.host_and_port,
self.database)
self.sa_engine =
sa.create_engine(engine_url)
self.unicode_test_table = sa.Table(unicode_test_table,
sa.MetaData(),
 
sa.Column(unicode_test_column,
 
sa.Unicode(60)))

 
self.unicode_test_table.drop(bind=self.sa_engine)
 
self.unicode_test_table.create(bind=self.sa_engine)

def
test_sqlalchemy_over_pyodbc(self):
assert
isinstance(self.sa_engine.dialect,
  sa.databases.mssql.MSSQLDialect_pyodbc),
\
 
self.sa_engine.dialect

 
self.sa_engine.execute(self.unicode_test_table.insert(),
 
unicode_test_column=u)
rows =
list(self.sa_engine.execute(self.unicode_test_table.select()))
assert u == rows[0].unicode_test_column,
\
 
repr(rows[0].unicode_test_column)

unittest.main()
--~--~-~--~~~---~--~~
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: mssql unicode weirdness: empty nvarchar string is selected as u

[desmaj]

Almost forgot: I'm working against SQL Server 2000.
--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

On Sep 4, 2008, at 5:33 PM, Jon wrote:


 I'll note that if I use something like this in the ORM mapper
 definition:

  order_by=meta.tables['some_table'].c.some_column,

 get(pkey) continues to work *and* ORDER BY is used in the SQL.
 While ORDER BY doesn't make sense when acquiring just one item, it
 doesn't hurt either, and it's one small thing that people converting
 from 0.4 would need to know. Is there any reason why compatibility
 cannot be retained?

get() should not be using the order_by specified in mapper().  If it  
is, that's a bug.In 0.5, since the Query is becoming much more  
comprehensive and richly featured than it used to be, it's important  
that it does not allow operations which make no sense to proceed. 
   

--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Jon]

On Sep 4, 5:31 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Sep 4, 2008, at 5:33 PM, Jon wrote:



  I'll note that if I use something like this in the ORM mapper
  definition:

       order_by=meta.tables['some_table'].c.some_column,

  get(pkey) continues to work *and* ORDER BY is used in the SQL.
  While ORDER BY doesn't make sense when acquiring just one item, it
  doesn't hurt either, and it's one small thing that people converting
  from 0.4 would need to know. Is there any reason why compatibility
  cannot be retained?

 get() should not be using the order_by specified in mapper().  If it  
 is, that's a bug.    In 0.5, since the Query is becoming much more  
 comprehensive and richly featured than it used to be, it's important  
 that it does not allow operations which make no sense to proceed.

It is.

I'd still like to request that order by be allowed - there is a big
difference between makes no sense and is an error. In this case,
sqlalchemy is making an error out of sometime that doesn't need to be
- the SQL is perfectly valid and doesn't impact the result whatsoever,
negatively or positively (in the case of get()). Ordering isn't
exactly a /condition/ of the query as it is manipulation of the result
- it's not filtering the results or anything of that nature.


--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

On Sep 4, 2008, at 5:55 PM, Jon wrote:


 I'll note that I find building a single query for potentially several
 uses quite handy, whether I'm doing a get() or an additional filter/
 filter_by. By prebuilding as much of the query as possible I reduce
 the overall complexity of my applications, in some cases considerably.
 I've found that I can *usually* get around this issue by making use of
 the order_by in the ORM layer but that doesn't always help, in
 particular when I'm not using get(x).

 Often my queries look like this:
 q = dbsess.query(Obj)
 q = q.join('some_relation')
 q = q.order_by(OtherObj.c.columnX)
 q = q.options(eagerload('some_other_relation'))
 q = q.options(eagerload('some_third_relation'))

 and then later, often in another function and depending on the input:

 instance = q.get(pkey)
 or
 instances = q.all()
 or even
 instances = q.filter(criteria).all()

 Being able to make use of a single base query makes my job much easier
 - in all cases I know ahead of time the relations and ordering I want,
 but in almost every case the function(s) which get the query object
 passed to them *don't* know this stuff - if I have to avoid building
 my queries this way I'll either have to build multiple sets of queries
 (one for get and one for everything else) or move the knowledge
 necessary to do so around quite a bit more.

 I'm hoping that by outlining my use case, and the fact that query_by
 doesn't really change the result of a get either way (hey, if I want
 to make an inefficient query...) that I can persuade you to allow the
 use of query_by and get(X).

order_by() in particular easily leads to ambiguous situations for  
which we have had users report as bugs, because SQLA was making an  
arbitrary choice which disagreed with what those users felt it should  
do.  In particular, an operation such as:

   query.filter(...).limit(2).order_by(criterion)

vs.

   query.filter(..).order_by(criterion).limit(2)

Some users feel that both queries should issue:   SELECT * FROM table  
WHERE criterion ORDER BY criterion LIMIT 2

Whereas other users feel that the second query only should issue:
SELECT * FROM (SELECT * FROM table WHERE criterion LIMIT 2) ORDER BY  
criterion

Because of scenarios like that, it makes more sense that the Query  
would not be a dumb accumulator of criteria, and instead would raise  
an error when being asked to do something ambiguous or nonsensical. 
So we have taken lots of steps to prevent unstructured usage of Query,  
which should lead to clearer application code.

In your specific case,  I don't see what's so hard about creating a  
Query which has *only* those aspects which make sense both to a get()  
as well as a join()/order_by() combination (in this case the eagerload  
options), and passing that to further functions.The backwards  
behavior you're looking for, i.e. that a bunch of state set up on  
Query would essentially be arbitrarily ignored, suggests that your  
application might be easier to understand if you rearranged it to not  
rely upon that behavior.









--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

On Sep 4, 2008, at 6:38 PM, Jon wrote:

 I'd still like to request that order by be allowed - there is a big
 difference between makes no sense and is an error.

At the moment I'm pretty convinced that allowing makes no sense to  
pass through silently is poor behavior. 
   

--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

as usual, since this one might turn out to be pretty controversial, I  
welcome the list to comment on this one.   The order_by().get() idea  
does fall in the category of nonsensical as opposed to ambiguous ,  
perhaps thats the distinction we'd want to go on.


--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Jon]

On Sep 4, 5:54 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 as usual, since this one might turn out to be pretty controversial, I  
 welcome the list to comment on this one.   The order_by().get() idea  
 does fall in the category of nonsensical as opposed to ambiguous ,  
 perhaps thats the distinction we'd want to go on.

What other changes (that you can recall off-hand) fall into these two
distinctions?

--~--~-~--~~~---~--~~
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] Inserts using reflection...

[Sam]

I woke up today and decided it was time to switch one of my simpler
programs from sqlobject to sqlalchemy.

I'm using reflection.   After some googling I was able to find a way
to insert:
mp = mphones.insert().execute(word=word, mphone=phone)

The above works okay.

But I'd rather be able to do something like this:
mp = mphones(word=word, mphone=phone)
sess.add(mp)

Unfortunately I couldn't make that work.

Here's the setup code I'm using:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker

engine = create_engine(postgres://postgres:[redacted]/mydb)
meta = MetaData()
meta.bind = engine

Session = sessionmaker(bind=engine)
sess = Session()

mphones = Table('mphones', meta, autoload=True)

This is sqlalchemy 0.5 beta 3.

--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Michael Bayer]

On Sep 4, 2008, at 7:19 PM, Jon wrote:


 In your specific case,  I don't see what's so hard about creating a
 Query which has *only* those aspects which make sense both to a get()
 as well as a join()/order_by() combination (in this case the  
 eagerload
 options), and passing that to further functions.The backwards
 behavior you're looking for, i.e. that a bunch of state set up on
 Query would essentially be arbitrarily ignored, suggests that your
 application might be easier to understand if you rearranged it to not
 rely upon that behavior.

 In this case it means a doubling of the number of queries I already
 have, and those queries are indexed by name. Since the queries
 (sometimes fairly complex) would be almost exactly the same it would
 actually make things much harder to understand. Currently, the only
 special case I have is whether to use get(X) or filter/filter_by +
 all().

im curious, since a Query is pretty much tied to a Session, how is  
that working in your application ?  Is it using just a single long- 
running session ?


 However, I'd much rather discuss things from a different
 standpoint. Let me rephrase the question - what is /wrong/ (not /
 nonsensical/) about combining order_by with get(X)? The only
 difference in the SQL generated is, in fact, the ORDER BY which
 shouldn't matter.

get() actually has an explicit line that erases the order_by even if  
one is present.  0.4 should be doing that but i havent checked lately  
if thats covered.   in 0.5 the mapper-level order_by() happens  
differently so I know why it might not be working (suggesting its not  
covered either).

 I guess I'm following the philosophy of: if it doesn't hurt, and it
 makes some things easier or clearer, then it's fine. This seems to no
 different than bash suddenly proclaiming that cat FILE | grep ...
 won't work any more because the cat is gratuitous.

I'd like to hear what other folks have to say since 0.4's query had  
this attitude - you could have filter(), order_by(), whatever you want  
set up, and get() would just ignore all that and do its thing.   There  
was general agreement that this was too open ended.  In this case I  
dont see order_by() as different from the others; theyre all things  
that get() just ignores (or complains about).

 One might argue that get(X) itself is superfluous because, hey,
 applications might as well just use filter/filter_by and check for  1
 return value themselves.

get() does have a different behavioral contract since it can locate  
directly from the Session if present.


--~--~-~--~~~---~--~~
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: Inserts using reflection...

[Michael Bayer]

On Sep 4, 2008, at 7:16 PM, Sam wrote:


 I woke up today and decided it was time to switch one of my simpler
 programs from sqlobject to sqlalchemy.

 I'm using reflection.   After some googling I was able to find a way
 to insert:
 mp = mphones.insert().execute(word=word, mphone=phone)

 The above works okay.

 But I'd rather be able to do something like this:
 mp = mphones(word=word, mphone=phone)
 sess.add(mp)

 Unfortunately I couldn't make that work.

 Here's the setup code I'm using:
 from sqlalchemy import *
 from sqlalchemy.orm import sessionmaker

 engine = create_engine(postgres://postgres:[redacted]/mydb)
 meta = MetaData()
 meta.bind = engine

 Session = sessionmaker(bind=engine)
 sess = Session()

 mphones = Table('mphones', meta, autoload=True)

Table isnt going to cut it alone; you'd have to use mappers.  Read  
through the ORM tutorial and you'll get a sense of how to do that.   
I'd recommend using declarative_base() to set things up which is what  
the tutorial uses most of the way.

--~--~-~--~~~---~--~~
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] When the database is to support sql 2005 ?

[guge]

When the database is to support sql 2005

--~--~-~--~~~---~--~~
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: bug? order_by + get(some_id) results in InvalidRequestError

[Jon]

On Sep 4, 7:01 pm, Michael Bayer [EMAIL PROTECTED] wrote:
  In this case it means a doubling of the number of queries I already
  have, and those queries are indexed by name. Since the queries
  (sometimes fairly complex) would be almost exactly the same it would
  actually make things much harder to understand. Currently, the only
  special case I have is whether to use get(X) or filter/filter_by +
  all().

 im curious, since a Query is pretty much tied to a Session, how is  
 that working in your application ?  Is it using just a single long-
 running session ?

I create and destroy (close, actually) a session for each request.
Essentially, at the beginning of a request the base query for each
type of object is built and then passed around quite a bit. From a
certain perspective, it's entirely serial as the only state is that
which is passed around rather than stored somewhere.

  However, I'd much rather discuss things from a different
  standpoint. Let me rephrase the question - what is /wrong/ (not /
  nonsensical/) about combining order_by with get(X)? The only
  difference in the SQL generated is, in fact, the ORDER BY which
  shouldn't matter.

 get() actually has an explicit line that erases the order_by even if  
 one is present.  0.4 should be doing that but i havent checked lately  
 if thats covered.   in 0.5 the mapper-level order_by() happens  
 differently so I know why it might not be working (suggesting its not  
 covered either).

When /not/ using get:

0.4.6 and 0.5b3 are both passing the order by /specified at the ORM
layer/ through. I'm talking about when defining the mapper I specify
an order_by, *not* when I'm building the query.

When using get:
0.4.6 appears to strip the order_by (when specified by the query) on
get, although the order_by specified in the mapper remains.
0.5b3 grumps, of course.

 I'd like to hear what other folks have to say since 0.4's query had  
 this attitude - you could have filter(), order_by(), whatever you want  
 set up, and get() would just ignore all that and do its thing.   There  
 was general agreement that this was too open ended.  In this case I  
 dont see order_by() as different from the others; theyre all things  
 that get() just ignores (or complains about).

For filter and such, yes, I can see that as very confusing - I've
specified conditions which should determine the final result but they
don't. On the other hand, order_by (and probably some others) are such
that they do /not/ alter the final result.

  One might argue that get(X) itself is superfluous because, hey,
  applications might as well just use filter/filter_by and check for  1
  return value themselves.

 get() does have a different behavioral contract since it can locate  
 directly from the Session if present.

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

[sqlalchemy] Re: Inserts using reflection...

[Sam]

Michael...

I've read the tutorial, and I think I understand it.

But maybe I don't understand reflection.  I thought that I could use
it to avoid defining anything.  I'd like to have objects basically
spring into life knowing exactly what their row names are, without
having to type anything.

Am I misunderstanding reflection?  Can it not automatically figure out
the row names for me?  If it can, is there an example of this
somewhere?

Or should I just sit down and type out all my classes with rownames?

Thanks


On Sep 4, 5:02 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Sep 4, 2008, at 7:16 PM, Sam wrote:





  I woke up today and decided it was time to switch one of my simpler
  programs from sqlobject to sqlalchemy.

  I'm using reflection.   After some googling I was able to find a way
  to insert:
  mp = mphones.insert().execute(word=word, mphone=phone)

  The above works okay.

  But I'd rather be able to do something like this:
  mp = mphones(word=word, mphone=phone)
  sess.add(mp)

  Unfortunately I couldn't make that work.

  Here's the setup code I'm using:
  from sqlalchemy import *
  from sqlalchemy.orm import sessionmaker

  engine = create_engine(postgres://postgres:[redacted]/mydb)
  meta = MetaData()
  meta.bind = engine

  Session = sessionmaker(bind=engine)
  sess = Session()

  mphones = Table('mphones', meta, autoload=True)

 Table isnt going to cut it alone; you'd have to use mappers.  Read  
 through the ORM tutorial and you'll get a sense of how to do that.  
 I'd recommend using declarative_base() to set things up which is what  
 the tutorial uses most of the way.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

{IT-Reks} 2 REKS FOR SR JAVA DEVELOPER WITH MARKET DATA EXP MUST AT NY

[Recruiter]

*PLEASE LET ME KNOW IF YOU HAVE ANY JAVA DEVELOPERS WITH MARKET DATA EXP AT
NY*

LOCATION : NY
DURATION : 1 YEAR

*1) **We are looking for a senior Java, J2EE developer to join business
focused Prime Brokerage Technology team*.
*Role is to do development, building the trade capture application, will
also be involved in some merger projects. Manager really likes people who
come from software development background- strong multi-threading.*
*7-10 years of experience (if sharp will consider w/less yrs of exp). *
**Key MUST HAVE SKILLS: Core Java, SQL -strong, Multithreading, Spring and
Hibernate (the spring is more important than hibernate b/c they are using
spring instead of J2EE) Java, Spring, Hibernate, OOP, AOP, Design Patterns
*Capital markets understanding is a big plus .*
Jboss Rules, OSWorkflow, GWT and ExtJs is a plus.


*2) Role is to do development, building the trade capture application, will
also be involved in some merger projects. Manager really likes people who
come from software development background- strong multi-threading.*

*Capital markets understanding is a big plus .*

*Key MUST HAVE SKILLS: Core Java, SQL -strong, Multithreading, Spring and
Hibernate (the spring is more important than hibernate b/c they are using
spristatement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) can't adapt


The query it displays works fine direct on database
thanks in advance
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---