[sqlalchemy] Re: column renaming and adding
Thanks Michael! That's a good thing to know!
On Thu, Sep 25, 2008 at 3:51 AM, Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 24, 2008, at 2:34 PM, Wim Verhavert wrote:
How is this possible? I thought that by saying:
properties={'achternaam':entity_tabel.c.naam}
you actually rename the column, but it seems it creates another
attribute which would lead to unexpected results in my case.
normally this is the case but since your Persoon object inherits from
Entity, the naam attribute is inherited from Entity and is still
present.The case of a subclass trying to move the column which
is named differently on the base class has not been addressed as of
yet (which basically means, it doesn't work). It would also be a
little tricky to make it work within SA since if you set different
data on Persoon.naam and Persoon.achternaam, that would be kind of
ambiguous. There's no real way for Persoon to not have naam at all
since its a subclass of Entity and from a Python point of view will
inherit all 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] column renaming and adding
I have a setup like this:
class Entity(object): pass
class Persoon(Entity): pass
entity_tabel = Table('contact', metadata,
Column('contactid', Integer, primary_key=True),
Column('voornaam', String(75)),
Column('naam', String(75)),
Column('organisatietypeid', Integer))
entity_mapper = mapper(self.Entity, entity_tabel,
polymorphic_on=entity_tabel.c.organisatietypeid,
polymorphic_identity=None)
persoon_mapper = mapper(self.Persoon,
inherits=entity_mapper,
polymorphic_identity=None,
properties={
'achternaam':entity_tabel.c.naam})
Now, I want to add a 'Persoon':
p = Persoon()
p.voornaam = 'firstname'
p.achternaam = 'surname'
mySession.add(p)
mySession.commit()
But this leaves my 'naam' field to 'NULL'. Only if I do:
p = Persoon()
p.voornaam = 'firstname'
p.naam = 'surname'
mySession.add(p)
mySession.commit()
Things work as they should.
How is this possible? I thought that by saying:
properties={'achternaam':entity_tabel.c.naam}
you actually rename the column, but it seems it creates another
attribute which would lead to unexpected results in my case.
Anybody more info about 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: default values for columns in select mappers
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
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
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
[sqlalchemy] default values for columns in select mappers
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
Thanks! That's indeed the stuff I was looking for!
On Wed, Sep 3, 2008 at 9:23 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 3, 2008, at 2:59 PM, [EMAIL PROTECTED] wrote:
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)
it is quite common and the pattern you describe is single table
inheritance. You can map straight to the table and the type
column will be taken care of for you. You can configure subtypes
corresponding to each value for type and Query for just that
subclass (or for all classes).
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_inheritance_single
the feature is available in 0.4 and 0.5 but has some improvements to
its behavior in the 0.5 series.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
