[sqlalchemy] Re: Get default value
Em Sex, 2008-01-11 às 08:54 +0200, [EMAIL PROTECTED] escreveu:
either put the correct polymorphic_on=resource.c.poly, or remove it
alltogether,
it comes from the inherited base-mapper.
Exactly, works fine leaving poly on resource and mapping all
polymorphic_on=resource.c.poly
cookbook helps me to, but just this change make my model working
Thank you a lot, and thank's to all replies.
follow the correct mapping to anyone wants:
-code---8--code-
from sqlalchemy import create_engine, MetaData, Table, Column, types, ForeignKey
from sqlalchemy.orm import mapper, relation, backref, create_session
from sqlalchemy.sql.expression import outerjoin, join
from sqlalchemy import String, Unicode, Integer, DateTime, Numeric, Boolean,
UnicodeText
db = create_engine('sqlite:///sa.db')
metadata = MetaData()
metadata = MetaData(db)
metadata.bind = db
session = create_session(bind=db)
resource_table = Table('resource', metadata,
Column('id',Integer, primary_key=True),
Column('name', String(30)),
Column('poly', String(31), nullable=True)
)
person_table = Table('person', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
material_table = Table('material', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
employee_table = Table('employee', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
technical_table = Table('technical', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
class Resource(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return Resource id=%d ,name=%s % (self.id,self.name)
class Person(Resource):
def __repr__(self):
return Person id=%d ,name=%s % (self.id,self.name)
class Material(Resource):
def __repr__(self):
return Material id=%d ,name=%s % (self.id,self.name)
class Employee(Person):
def __repr__(self):
return Employee id=%d ,name=%s % (self.id,self.name)
class Technical(Person):
def __repr__(self):
return Technical id=%d ,name=%s % (self.id,self.name)
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource',
)
mapper(Person, person_table,
inherits=Resource, polymorphic_identity='person',
polymorphic_on= resource_table.c.poly,
)
mapper(Material, material_table,
inherit_condition= material_table.c.id == resource_table.c.id,
inherits=Resource, polymorphic_identity='material'
)
mapper(Employee, employee_table,
inherit_condition= employee_table.c.id == person_table.c.id,
inherits=Person, polymorphic_identity='employee',
)
mapper(Technical, technical_table,
inherit_condition= technical_table.c.id == person_table.c.id,
inherits=Person, polymorphic_identity='technical',
)
metadata.create_all(bind=db)
r = Resource('resource name')
p = Person('person name')
m = Material('material name')
e = Employee('employee name')
t = Technical('technical name')
session.save(r)
session.save(p)
session.save(m)
session.save(e)
session.save(t)
session.flush()
session.clear()
print LIST FROM RESOURCES #
for o in session.query(Resource).all():
print o, o.poly
print LIST FROM PERSONS #
for o in session.query(Person).all():
print o, o.poly
-code---8--code-
results are:
LIST FROM RESOURCES #
Resource id=1 ,name=resource name resource
Person id=2 ,name=person name person
Material id=3 ,name=material name material
Employee id=4 ,name=employee name employee
Technical id=5 ,name=technical name technical
LIST FROM PERSONS #
Person id=2 ,name=person name person
Employee id=4 ,name=employee name employee
Technical id=5 ,name=technical name technical
Thanks again,
Att
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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: filter() on inherited class doesn't point to the correct table
svilen wrote: On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? I was expecting that SA would know that from the polymorphic type flag. I have a catalog relation on media. When I do media.catalog, it doesn't just return a Catalog object, but really a CatalogChannel object (which is the whole point of polymorphic inheritance). And I thought it could figure out channel from that. But Mike said no. :) That's why he talked about having some extra API query methods: Media.query.join_to_subclass(CatalogChannel).join(channel).filter(Channel.c.id==playlist.id_channel).all() We could even join classes only directly (isn't this ORM after all?): Media.query.join([CatalogChannel, Channel]) your query above is missing the isinstance-filter specifying that u need catalogchannels and not just any catalogs. i'm not sure how this would be expressed in SA but it has to be explicit - and probably somewhere on level of tables. have u tried Media.query.join( [catalog, id, channel])... ??? Nope, that doesn't work, it's like doing ([catalog, catalog, channel]) Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: svilen wrote: Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id why u need such a query? that's exactly what (inheritance) join does, and automaticaly - just query( CatalogChannel).all() would give u the above query. I have hidden the full concept I'm working on and only focused my problem. Here's my full setup the query is involved with: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? Forget DB; think plain objects. u point to a base class but expect it always to have an attribute that belongs to one of children classes... which is implicitly saying that while pointing to base-class AND u need only those pointing to the child-class AND the attribute is whatever. (i.e. isinstance(x,CatChanel) and x.channel==... your query above is missing the isinstance-filter specifying that u need catalogchannels and not just any catalogs. i'm not sure how this would be expressed in SA but it has to be explicit - and probably somewhere on level of tables. have u tried Media.query.join( [catalog, id, channel])... ??? --~--~-~--~~~---~--~~ 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: doubly-linked list
Jonathan LaCour wrote:
I am attempting to model a doubly-linked list, as follows:
... seems to do the trick. I had tried using backref's earlier,
but it was failing because I was specifying a remote_side
keyword argument to the backref(), which was making it blow up
with cycle detection exceptions for some reason.
Oops, spoke too soon! Here is a test case which shows something
quite odd. I create some elements, link them together, and then
walk the relations forward and backward, printing out the results.
All seems fine. Then, I update the order of the linked list, and
print them out forward, and they work okay, but when I print things
out in reverse order, its all screwy.
Any ideas?
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///')
metadata = MetaData(engine)
Session = scoped_session(
sessionmaker(bind=engine, autoflush=True, transactional=True)
)
task_table = Table('task', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode),
Column('next_task_id', Integer, ForeignKey('task.id')),
Column('previous_task_id', Integer, ForeignKey('task.id'))
)
class Task(object):
def __init__(self, **kw):
for key, value in kw.items():
setattr(self, key, value)
def __repr__(self):
return 'Task :: %s' % self.name
Session.mapper(Task, task_table, properties={
'next_task' : relation(
Task,
primaryjoin=task_table.c.next_task_id==task_table.c.id,
uselist=False,
remote_side=task_table.c.id,
backref=backref(
'previous_task',
primaryjoin=task_table.c.previous_task_id==task_table.c.id,
uselist=False
)
),
})
if __name__ == '__main__':
metadata.create_all()
t1 = Task(name=u'Item One')
t2 = Task(name=u'Item Two')
t3 = Task(name=u'Item Three')
t4 = Task(name=u'Item Four')
t5 = Task(name=u'Item Five')
t6 = Task(name=u'Item Six')
t1.next_task = t2
t2.next_task = t3
t3.next_task = t4
t4.next_task = t5
t5.next_task = t6
Session.commit()
Session.clear()
print '-' * 80
task = Task.query.filter_by(name=u'Item One').one()
while task is not None:
print task
task = task.next_task
print '-' * 80
print '-' * 80
task = Task.query.filter_by(name=u'Item Six').one()
while task is not None:
print task
task = task.previous_task
print '-' * 80
Session.clear()
t1 = Task.query.filter_by(name=u'Item One').one()
t2 = Task.query.filter_by(name=u'Item Two').one()
t3 = Task.query.filter_by(name=u'Item Three').one()
t4 = Task.query.filter_by(name=u'Item Four').one()
t5 = Task.query.filter_by(name=u'Item Five').one()
t6 = Task.query.filter_by(name=u'Item Six').one()
t1.next_task = t5
t5.next_task = t2
t4.next_task = t6
Session.commit()
Session.clear()
print '-' * 80
task = Task.query.filter_by(name=u'Item One').one()
while task is not None:
print task
task = task.next_task
print '-' * 80
print '-' * 80
task = Task.query.filter_by(name=u'Item Six').one()
while task is not None:
print task
task = task.previous_task
print '-' * 80
--
Jonathan LaCour
http://cleverdevil.org
--~--~-~--~~~---~--~~
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: doubly-linked list
On Jan 11, 2008 7:57 PM, Jonathan LaCour [EMAIL PROTECTED] wrote:
Jonathan LaCour wrote:
I am attempting to model a doubly-linked list, as follows:
... seems to do the trick. I had tried using backref's earlier,
but it was failing because I was specifying a remote_side
keyword argument to the backref(), which was making it blow up
with cycle detection exceptions for some reason.
Oops, spoke too soon! Here is a test case which shows something
quite odd. I create some elements, link them together, and then
walk the relations forward and backward, printing out the results.
All seems fine. Then, I update the order of the linked list, and
print them out forward, and they work okay, but when I print things
out in reverse order, its all screwy.
Any ideas?
I believe you need either the only next_task_id or the only
previous_task_id, but not both, since one can be calculated from
another. Something like the following:
task_table = Table('task', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode),
Column('next_task_id', Integer, unique=True, ForeignKey('task.id')),
)
Session.mapper(Task, task_table, properties={
'next_task' : relation(
Task,
uselist=False,
remote_side=task_table.c.id,
backref=backref('previous_task', uselist=False),
),
})
--~--~-~--~~~---~--~~
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] doubly-linked list
All,
I am attempting to model a doubly-linked list, as follows:
task_table = Table('task', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode),
Column('next_task_id', Integer, ForeignKey('task.id')),
Column('previous_task_id', Integer, ForeignKey('task.id'))
)
class Task(object): pass
mapper(Task, task_table, properties={
'next_task' : relation(
Task,
primaryjoin=task_table.c.next_task_id==task_table.c.id,
uselist=False
),
'previous_task' : relation(
Task,
primaryjoin=task_table.c.previous_task_id==task_table.c.id,
uselist=False
)
})
Now, this works, but only in one direction. If I create a list
structure with a bunch of instances using `next_task`, then that
direction of the relation works fine, but the reverse side doesn't
seem to get managed automatically. Same in the other direction. Is
there a way to get SQLAlchemy to understand that these relations are
the inverse of one another, and manage both sides for me?
--
Jonathan LaCour
http://cleverdevil.org
--~--~-~--~~~---~--~~
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: filter() on inherited class doesn't point to the correct table
On Friday 11 January 2008 13:58:34 Alexandre Conrad wrote: Hi, playing with inheritance, I figured out that an inherited mapped class passed to filter doesn't point to the correct table. I have 2 classes, Catalog and CatalogChannel(Catalog). Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id why u need such a query? that's exactly what (inheritance) join does, and automaticaly - just query( CatalogChannel).all() would give u the above query. as of the relations, they are quite automatic BUT magic does not always work, so u have to explicitly specify some things manualy. Normaly, I would join([A, B]) the tables between each other. But if a channel relation only exists on the CatalogChannel class, join(channel) wouldn't work as SA looks at superclass Catalog. I thought it would naturally find the relationship by looking at the polymorphic type column from Catalog, but it doesn't. Mike suggested we would need to extend the API with a new method like join_to_subclass() or so... Even though, I still think SA should figure out which relation I'm looking at... i'm not sure i can follow u here... i do have tests about referencing to baseclas / subclasses, self or not, and they work ok. dbcook/tests/sa/ref_*.py for plain sa (160 combinations), and dbcook/tests/mapper/test_ABC_inh_ref_all.py (1 combinations) IMO u are missing some explicit argument --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Friday 11 January 2008 17:03:06 Alexandre Conrad wrote: svilen wrote: On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? I was expecting that SA would know that from the polymorphic type flag. I have a catalog relation on media. When I do media.catalog, it doesn't just return a Catalog object, but really a CatalogChannel object hey, polymorphic means ANY subtype, u could have 5 other CatalogRivers that have no .channel in them... how to guess which one? Find the one that has .channel? the root-most one or some of its children-klasses? (which is the whole point of polymorphic inheritance). And I thought it could figure out channel from that. But Mike said no. :) That's why he talked about having some extra API query methods: Media.query.join_to_subclass(CatalogChannel).join(channel).filter (Channel.c.id==playlist.id_channel).all() We could even join classes only directly (isn't this ORM after all?): Media.query.join([CatalogChannel, Channel]) this is completely different beast. it might be useful... although the whole idea of the join(list) is list of attribute-names and not klasses/tables - to have a.b.c.d.e.f, i.e. be specific and avoid thinking in diagram ways (klasA referencing klasB means nothing if it happens via 5 diff.attributes) when i needed similar thing, a) i moved the .channel into the root or b) changed media to reference CatChannel and not the base one. ciao svilen --~--~-~--~~~---~--~~ 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] filter() on inherited class doesn't point to the correct table
Hi, playing with inheritance, I figured out that an inherited mapped class passed to filter doesn't point to the correct table. I have 2 classes, Catalog and CatalogChannel(Catalog). Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id The generated query should be WHERE catalogs.id = catalog_channels.id. I can make this happend by explicitly using the table itself rather than the class: query.filter(Catalog.c.id==catalog_channel_table.c.id) WHERE catalogs.id = catalog_channels.id Should I open a ticket for that ? Normaly, I would join([A, B]) the tables between each other. But if a channel relation only exists on the CatalogChannel class, join(channel) wouldn't work as SA looks at superclass Catalog. I thought it would naturally find the relationship by looking at the polymorphic type column from Catalog, but it doesn't. Mike suggested we would need to extend the API with a new method like join_to_subclass() or so... Even though, I still think SA should figure out which relation I'm looking at... Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: doubly-linked list
Jonathan LaCour wrote:
I am attempting to model a doubly-linked list, as follows:
Replying to myself:
task_table = Table('task', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode),
Column('next_task_id', Integer, ForeignKey('task.id')),
Column('previous_task_id', Integer, ForeignKey('task.id'))
)
class Task(object):
def __init__(self, **kw):
for key, value in kw.items():
setattr(self, key, value)
def __repr__(self):
return 'Task :: %s' % self.name
mapper(Task, task_table, properties={
'next_task' : relation(
Task,
primaryjoin=task_table.c.next_task_id==task_table.c.id,
uselist=False,
remote_side=task_table.c.id,
backref=backref(
'previous_task',
primaryjoin=task_table.c.previous_task_id==task_table.c.id,
uselist=False
)
),
})
... seems to do the trick. I had tried using backref's earlier,
but it was failing because I was specifying a remote_side keyword
argument to the backref(), which was making it blow up with cycle
detection exceptions for some reason.
--
Jonathan LaCour
http://cleverdevil.org
--~--~-~--~~~---~--~~
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: Exclude Autogenerated Timestamp Column
On Jan 10, 2008, at 9:15 PM, deanH wrote:
Hello,
I am having a problem inserting an object into a MS SQL table that
contains a timestamp field (now) that is generated automatically -
sqlalchemy is defaulting this column to None and when it is generating
the SQL insert. Is there a way to configure the mapper so that it
ignores specific columns?
I looked at the related topic below, but that is resolved by using a
sqlalchemy construct specific to primary keys, and i have not seen one
that is designated for timestamps.
http://groups.google.com/group/sqlalchemy/browse_thread/thread/749c55a835b7458/51b38f4b08d31d6f?lnk=gstq=column+exclude#51b38f4b08d31d6f
I am new to sqlalchemy so I may be going about this the wrong way, but
my attempts at overriding with a reflected column were similarly
unsuccessful.
Column('now', MSTimeStamp, nullable=False)
Any thoughts on how to exclude columns from generated inserts?
assuming there is an MS-SQL-side default generator for the column, you
just need to tell your Table definition that the column is capable of
populating itself, via:
Column('now', MSTimeStamp, PassiveDefault(), nullable=False)
hope this helps
--~--~-~--~~~---~--~~
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: filter() on inherited class doesn't point to the correct table
On Jan 11, 2008, at 10:03 AM, Alexandre Conrad wrote: svilen wrote: On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? I was expecting that SA would know that from the polymorphic type flag. I have a catalog relation on media. When I do media.catalog, it doesn't just return a Catalog object, but really a CatalogChannel object (which is the whole point of polymorphic inheritance). And I thought it could figure out channel from that. But Mike said no. :) That's why he talked about having some extra API query methods: I dont see where the type element youre mentioning is present in this query. if Media points to Catalog, thats the end of the story - what is telling it about a CatalogChannel ? look at it this way. Suppose you have CatalogA(Catalog), CatalogB(Catalog), CatalogChannel(Catalog), CatalogQ(Catalog). all four of those classes have an attribute called channel. Catalog does not. Media references Catalog; therefore, the catalog attribute on Media can be any of: CatalogA, CatalogB, CatalogChannel, Catalog, or CatalogQ. Now I say: Media.query.join([catalog, channel]) Whats the join is has to produce ? what table does it join to ? catalog_channel, catalog_a, catalog_b, catalog_c, catalog_q ? or does it try to make a UNION out of joins for all of those (clearly we arent going to guess that deeply) ? --~--~-~--~~~---~--~~ 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: doubly-linked list
All of the crazy mappings today are blowing my mind, so I'll point you
to an old unit test with a doubly linked list:
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/test/orm/inheritance/poly_linked_list.py
the above uses just a single foreign key (but we can still traverse bi-
directionally of course). Im not sure what the double foreign key
approach here is for. wouldnt the previous task of one task be the
next task for another with just one FK relation ? in the
relational model, a single FK is automatically bidirectional, unlike a
reference in a programming language.
On Jan 11, 2008, at 11:57 AM, Jonathan LaCour wrote:
Jonathan LaCour wrote:
I am attempting to model a doubly-linked list, as follows:
... seems to do the trick. I had tried using backref's earlier,
but it was failing because I was specifying a remote_side
keyword argument to the backref(), which was making it blow up
with cycle detection exceptions for some reason.
Oops, spoke too soon! Here is a test case which shows something
quite odd. I create some elements, link them together, and then
walk the relations forward and backward, printing out the results.
All seems fine. Then, I update the order of the linked list, and
print them out forward, and they work okay, but when I print things
out in reverse order, its all screwy.
Any ideas?
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///')
metadata = MetaData(engine)
Session = scoped_session(
sessionmaker(bind=engine, autoflush=True, transactional=True)
)
task_table = Table('task', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode),
Column('next_task_id', Integer, ForeignKey('task.id')),
Column('previous_task_id', Integer, ForeignKey('task.id'))
)
class Task(object):
def __init__(self, **kw):
for key, value in kw.items():
setattr(self, key, value)
def __repr__(self):
return 'Task :: %s' % self.name
Session.mapper(Task, task_table, properties={
'next_task' : relation(
Task,
primaryjoin=task_table.c.next_task_id==task_table.c.id,
uselist=False,
remote_side=task_table.c.id,
backref=backref(
'previous_task',
primaryjoin=task_table.c.previous_task_id==task_table.c.id,
uselist=False
)
),
})
if __name__ == '__main__':
metadata.create_all()
t1 = Task(name=u'Item One')
t2 = Task(name=u'Item Two')
t3 = Task(name=u'Item Three')
t4 = Task(name=u'Item Four')
t5 = Task(name=u'Item Five')
t6 = Task(name=u'Item Six')
t1.next_task = t2
t2.next_task = t3
t3.next_task = t4
t4.next_task = t5
t5.next_task = t6
Session.commit()
Session.clear()
print '-' * 80
task = Task.query.filter_by(name=u'Item One').one()
while task is not None:
print task
task = task.next_task
print '-' * 80
print '-' * 80
task = Task.query.filter_by(name=u'Item Six').one()
while task is not None:
print task
task = task.previous_task
print '-' * 80
Session.clear()
t1 = Task.query.filter_by(name=u'Item One').one()
t2 = Task.query.filter_by(name=u'Item Two').one()
t3 = Task.query.filter_by(name=u'Item Three').one()
t4 = Task.query.filter_by(name=u'Item Four').one()
t5 = Task.query.filter_by(name=u'Item Five').one()
t6 = Task.query.filter_by(name=u'Item Six').one()
t1.next_task = t5
t5.next_task = t2
t4.next_task = t6
Session.commit()
Session.clear()
print '-' * 80
task = Task.query.filter_by(name=u'Item One').one()
while task is not None:
print task
task = task.next_task
print '-' * 80
print '-' * 80
task = Task.query.filter_by(name=u'Item Six').one()
while task is not None:
print task
task = task.previous_task
print '-' * 80
--
Jonathan LaCour
http://cleverdevil.org
--~--~-~--~~~---~--~~
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: filter() on inherited class doesn't point to the correct table
svilen wrote: Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id why u need such a query? that's exactly what (inheritance) join does, and automaticaly - just query( CatalogChannel).all() would give u the above query. I have hidden the full concept I'm working on and only focused my problem. Here's my full setup the query is involved with: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. as of the relations, they are quite automatic BUT magic does not always work, so u have to explicitly specify some things manualy. So right now, I'm building that awfully long query to explicitly tell it to look at CatalogChannel: Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==CatalogChannel.c.id).filter(CatalogChannel.c.id_channel==c.playlist.id_channel).all() # Pheeww... But even this doesn't work well. The part where .filter(Catalog.c.id==CatalogChannel.c.id) wrongly generates: catalogs.id = catalogs.id So I need to use the table itself: .filter(Catalog.c.id==catalog_channel_table.c.id) correctly generates: catalogs.id = catalog_channels.id This works. Regards, -- Alexandre CONRAD - TLV FRANCE Research Development --~--~-~--~~~---~--~~ 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: doubly-linked list
Michael Bayer wrote: All of the crazy mappings today are blowing my mind, so I'll point you to an old unit test with a doubly linked list: Yeah, trust me, it was blowing my mind as well, so I elected not to go this direction anyway. You're also correct that there isn't _really_ a need to maintain the previous anyway, since it can be implied from the next a heck of a lot easier. The simplest solution is usually the best, and this one definitely wasn't very simple! On to other ideas... -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ 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: Is there a way to replace object in DB?
On Jan 11, 2008, at 12:30 PM, Denis S. Otkidach wrote: # Another program. We have to insure that object with id=1 exists in DB and has # certain properties. obj2 = ModelObject(1, u'title2') session.merge(obj2) session.commit() what that looks like to me is that you're attempting to query the database for object ID #1 using merge(). when you merge(), its going to treat the object similarly to how it does using session.save_or_update(). that is, it looks for an _instance_key attribute to determine if the object represents a transient or persisted instance. So you could hack the way youre doing it like: obj2 = ModelObject(1, u'title2') obj2._instance_key = session.identity_key(instance=obj2) session.merge(obj2) session.commit() we have yet to define a completely public API for the above operation, i.e. treat this object as though its persistent. im not sure yet how we could define one that has a straightforward use case which wouldn't add confusion. Anyway, the legit way to go is this (and this is what the above merge() is doing anyway): obj2 = session.query(ModelObject).get(1) if not obj2: obj2 = ModelObject(1, u'title2') session.save(obj2) else: obj2.title= u'title2' session.commit() --~--~-~--~~~---~--~~ 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: Is there a way to replace object in DB?
On Dec 28, 2007 6:25 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Dec 28, 2007, at 5:50 AM, Denis S. Otkidach wrote:
Sure, I can get an object from DB and copy data from new one. But
there is a lot of object types, so have to invent yet another meta
description for it (while it already exists in sqlalchemy). And
requirements changes often, so I have to change scheme in 2 places.
This is not good and error prone. Why I have to invent new description
when there is already one from sqlalchemy mapping? Can't I use it for
my purpose? Something like merge(objFromDB, newObj) will solve the
problem.
session.merge() does copy the attributes of one object into another.
theres some bugs with dont_load that have been fixed in trunk so try
out the trunk if you have problems.
This doesn't work: I have the same IntegrityError or FlushError
depending on whether original object exists in the session (line
session.clear() is commented in the code below). What I do wrong?
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
obj1 = ModelObject(1, u'title1')
session.save(obj1)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj2 = ModelObject(1, u'title2')
session.merge(obj2)
session.commit()
---8---
--~--~-~--~~~---~--~~
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: filter() on inherited class doesn't point to the correct table
I think I understand what you trying to do
in fact polymorphic objects are load correctly in my test, I think it is
an approach to your case.
follow the code I used before to ask about polymorphic inheritance, note
to the Catalog class, this class have a resource list (catalog_id on
Resource), and I am inserting different types of resources, than when
select,
each resource is loaded correctly with their type
---code--8--code-
from sqlalchemy import create_engine, MetaData, Table, Column, types,
ForeignKey
from sqlalchemy.orm import mapper, relation, backref, create_session
from sqlalchemy.sql.expression import outerjoin, join
from sqlalchemy import String, Unicode, Integer, DateTime, Numeric,
Boolean, UnicodeText
db = create_engine('sqlite:///:memory:')
metadata = MetaData()
metadata = MetaData(db)
metadata.bind = db
session = create_session(bind=db)
resource_table = Table('resource', metadata,
Column('id',Integer, primary_key=True),
Column('name', String(30)),
Column('catalog_id', Integer, ForeignKey('catalog.id')),
Column('poly', String(31), nullable=True)
)
person_table = Table('person', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
material_table = Table('material', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
employee_table = Table('employee', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
technical_table = Table('technical', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
catalog_table = Table('catalog', metadata,
Column('id',Integer, primary_key=True),
)
catalog_resources = Table('catalog_resources', metadata,
Column('id', Integer, primary_key=True),
Column('resource_id',Integer, ForeignKey('resource.id')),
)
class Resource(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return Resource id=%d ,name=%s % (self.id,self.name)
class Person(Resource):
def __repr__(self):
return Person id=%d ,name=%s % (self.id,self.name)
class Material(Resource):
def __repr__(self):
return Material id=%d ,name=%s % (self.id,self.name)
class Employee(Person):
def __repr__(self):
return Employee id=%d ,name=%s % (self.id,self.name)
class Technical(Person):
def __repr__(self):
return Technical id=%d ,name=%s % (self.id,self.name)
class Catalog(object):
def __repr__(self):
return catalog id=%d resources=%s %
(self.id,str([str(r)+',' for r in self.resources]))
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource',
)
mapper(Person, person_table,
inherits=Resource, polymorphic_identity='person',
polymorphic_on= resource_table.c.poly,
)
mapper(Material, material_table,
inherit_condition= material_table.c.id == resource_table.c.id,
inherits=Resource, polymorphic_identity='material'
)
mapper(Employee, employee_table,
inherit_condition= employee_table.c.id == person_table.c.id,
inherits=Person, polymorphic_identity='employee',
)
mapper(Technical, technical_table,
inherit_condition= technical_table.c.id == person_table.c.id,
inherits=Person, polymorphic_identity='technical',
)
mapper(Catalog, catalog_table,
properties={
'resources':relation(Resource)
})
metadata.create_all(bind=db)
r = Resource('resource name')
p = Person('person name')
m = Material('material name')
e = Employee('employee name')
t = Technical('technical name')
x = Catalog()
x.resources = [p,m,e,t]
session.save(r)
session.save(p)
session.save(m)
session.save(e)
session.save(t)
session.save(x)
session.flush()
session.clear()
print LIST FROM RESOURCES #
for o in session.query(Resource).all():
print o, o.poly
print LIST FROM PERSONS #
for o in session.query(Person).all():
print o, o.poly
print Catalog #
y= session.query(Catalog).one()
print y
---code--8--code-
I Hope it helps you
Att
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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: Exclude Autogenerated Timestamp Column
Thanks for the responses guys. The PassiveDefault() parameter did
exactly what I wanted it to do - which was to exclude that column from
the generated insert query so that MS SQL could handle those on it's
own.
... now to figure out why I am getting an unsubscriptable object type
error from the operation:
---
...
File c:\python24\lib\site-packages\sqlalchemy-0.3.11-py2.4.egg
\sqlalchemy\orm\mapper.py, line 1255, in _postfetch
self.set_attr_by_column(obj, c, row[c])
TypeError: unsubscriptable object
---
the comments for the _postfetch method indicate that it is checking to
see if 'PassiveDefaults' were fired off on the insert. It looks the
row[c] operation is breaking as the row object doesn't support []
subscripting... could be a bug?
On Jan 11, 9:11 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jan 10, 2008, at 9:15 PM, deanH wrote:
Hello,
I am having a problem inserting an object into a MS SQL table that
contains a timestamp field (now) that is generated automatically -
sqlalchemy is defaulting this column to None and when it is generating
the SQL insert. Is there a way to configure the mapper so that it
ignores specific columns?
I looked at the related topic below, but that is resolved by using a
sqlalchemy construct specific to primary keys, and i have not seen one
that is designated for timestamps.
http://groups.google.com/group/sqlalchemy/browse_thread/thread/749c55...
I am new to sqlalchemy so I may be going about this the wrong way, but
my attempts at overriding with a reflected column were similarly
unsuccessful.
Column('now', MSTimeStamp, nullable=False)
Any thoughts on how to exclude columns from generated inserts?
assuming there is an MS-SQL-side default generator for the column, you
just need to tell your Table definition that the column is capable of
populating itself, via:
Column('now', MSTimeStamp, PassiveDefault(), nullable=False)
hope this helps
--~--~-~--~~~---~--~~
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: Exclude Autogenerated Timestamp Column
On Jan 11, 2008, at 4:37 PM, Dean Halford wrote: Thanks for the responses guys. The PassiveDefault() parameter did exactly what I wanted it to do - which was to exclude that column from the generated insert query so that MS SQL could handle those on it's own. ... now to figure out why I am getting an unsubscriptable object type error from the operation: --- ... File c:\python24\lib\site-packages\sqlalchemy-0.3.11-py2.4.egg \sqlalchemy\orm\mapper.py, line 1255, in _postfetch self.set_attr_by_column(obj, c, row[c]) TypeError: unsubscriptable object --- the comments for the _postfetch method indicate that it is checking to see if 'PassiveDefaults' were fired off on the insert. It looks the row[c] operation is breaking as the row object doesn't support [] subscripting... could be a bug? rows are subscriptable so that means its getting None back. so its a bug that its not handling that more gracefully...but also, should be getting a row back.you should see in your SQL logs that a SELECT is being issued right after a series of INSERT/UPDATE statements for that table - if the SELECT queries for a primary key of NULL that may mean that the default-id-generation scheme in use is not working (either not genning an ID or not telling the result about it correctly)i know on MS-SQL the default ID generation schemes are quite complex. we'll see what Rick says but its possible things would work a whole lot better if you were using sqlalchemy 0.4. --~--~-~--~~~---~--~~ 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: Exclude Autogenerated Timestamp Column
MSSQL ID generation is limited to integer PKs of the IDENTITY type, and they work fine in 0.4 series. That wiki page should be updated. It's most likely a case of the Table not knowing that the PK should be an auto-increment type. Are you defining the table via an SA Table() definition, or trying to autoload the definition via table reflection? On Jan 11, 2008 5:23 PM, Dean Halford [EMAIL PROTECTED] wrote: thanks micheal - the only reason we went with 3.11 was the following statement on the wiki: Currently (Aug 2007) the 0.4 branch has a number of problems with MS- SQL. http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MS-SQL I checked the logs and it does have to do with the MS-SQL ID generation, so I'll readup on that. 2008-01-11 14:19:31,292 INFO sqlalchemy.engine.base.Engine.0x..30 SELECT shot.[skuId], shot.number, shot.name, shot.description, shot. [teamCategory], shot.comments, shot.props, shot.time, shot.talent, shot.source, shot.id, shot.now, shot.[rowNumber] FROM shot WHERE shot.id IS NULL - cheers On Jan 11, 1:46 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 11, 2008, at 4:37 PM, Dean Halford wrote: Thanks for the responses guys. The PassiveDefault() parameter did exactly what I wanted it to do - which was to exclude that column from the generated insert query so that MS SQL could handle those on it's own. ... now to figure out why I am getting an unsubscriptable object type error from the operation: --- ... File c:\python24\lib\site-packages\sqlalchemy-0.3.11-py2.4.egg \sqlalchemy\orm\mapper.py, line 1255, in _postfetch self.set_attr_by_column(obj, c, row[c]) TypeError: unsubscriptable object --- the comments for the _postfetch method indicate that it is checking to see if 'PassiveDefaults' were fired off on the insert. It looks the row[c] operation is breaking as the row object doesn't support [] subscripting... could be a bug? rows are subscriptable so that means its getting None back. so its a bug that its not handling that more gracefully...but also, should be getting a row back.you should see in your SQL logs that a SELECT is being issued right after a series of INSERT/UPDATE statements for that table - if the SELECT queries for a primary key of NULL that may mean that the default-id-generation scheme in use is not working (either not genning an ID or not telling the result about it correctly)i know on MS-SQL the default ID generation schemes are quite complex. we'll see what Rick says but its possible things would work a whole lot better if you were using sqlalchemy 0.4. --~--~-~--~~~---~--~~ 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: Exclude Autogenerated Timestamp Column
thanks micheal - the only reason we went with 3.11 was the following statement on the wiki: Currently (Aug 2007) the 0.4 branch has a number of problems with MS- SQL. http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MS-SQL I checked the logs and it does have to do with the MS-SQL ID generation, so I'll readup on that. 2008-01-11 14:19:31,292 INFO sqlalchemy.engine.base.Engine.0x..30 SELECT shot.[skuId], shot.number, shot.name, shot.description, shot. [teamCategory], shot.comments, shot.props, shot.time, shot.talent, shot.source, shot.id, shot.now, shot.[rowNumber] FROM shot WHERE shot.id IS NULL - cheers On Jan 11, 1:46 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 11, 2008, at 4:37 PM, Dean Halford wrote: Thanks for the responses guys. The PassiveDefault() parameter did exactly what I wanted it to do - which was to exclude that column from the generated insert query so that MS SQL could handle those on it's own. ... now to figure out why I am getting an unsubscriptable object type error from the operation: --- ... File c:\python24\lib\site-packages\sqlalchemy-0.3.11-py2.4.egg \sqlalchemy\orm\mapper.py, line 1255, in _postfetch self.set_attr_by_column(obj, c, row[c]) TypeError: unsubscriptable object --- the comments for the _postfetch method indicate that it is checking to see if 'PassiveDefaults' were fired off on the insert. It looks the row[c] operation is breaking as the row object doesn't support [] subscripting... could be a bug? rows are subscriptable so that means its getting None back. so its a bug that its not handling that more gracefully...but also, should be getting a row back.you should see in your SQL logs that a SELECT is being issued right after a series of INSERT/UPDATE statements for that table - if the SELECT queries for a primary key of NULL that may mean that the default-id-generation scheme in use is not working (either not genning an ID or not telling the result about it correctly)i know on MS-SQL the default ID generation schemes are quite complex. we'll see what Rick says but its possible things would work a whole lot better if you were using sqlalchemy 0.4. --~--~-~--~~~---~--~~ 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: Exclude Autogenerated Timestamp Column
its updated. Dean, try to get on 0.4 ! On Jan 11, 2008, at 5:45 PM, Rick Morrison wrote: MSSQL ID generation is limited to integer PKs of the IDENTITY type, and they work fine in 0.4 series. That wiki page should be updated. It's most likely a case of the Table not knowing that the PK should be an auto-increment type. Are you defining the table via an SA Table() definition, or trying to autoload the definition via table reflection? On Jan 11, 2008 5:23 PM, Dean Halford [EMAIL PROTECTED] wrote: thanks micheal - the only reason we went with 3.11 was the following statement on the wiki: Currently (Aug 2007) the 0.4 branch has a number of problems with MS- SQL. http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MS-SQL I checked the logs and it does have to do with the MS-SQL ID generation, so I'll readup on that. 2008-01-11 14:19:31,292 INFO sqlalchemy.engine.base.Engine.0x..30 SELECT shot.[skuId], shot.number, shot.name, shot.description, shot. [teamCategory], shot.comments , shot.props, shot.time, shot.talent, shot.source, shot.id, shot.now, shot.[rowNumber] FROM shot WHERE shot.id IS NULL - cheers On Jan 11, 1:46 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 11, 2008, at 4:37 PM, Dean Halford wrote: Thanks for the responses guys. The PassiveDefault() parameter did exactly what I wanted it to do - which was to exclude that column from the generated insert query so that MS SQL could handle those on it's own. ... now to figure out why I am getting an unsubscriptable object type error from the operation: --- ... File c:\python24\lib\site-packages\sqlalchemy-0.3.11-py2.4.egg \sqlalchemy\orm\mapper.py, line 1255, in _postfetch self.set_attr_by_column(obj, c, row[c]) TypeError: unsubscriptable object --- the comments for the _postfetch method indicate that it is checking to see if 'PassiveDefaults' were fired off on the insert. It looks the row[c] operation is breaking as the row object doesn't support [] subscripting... could be a bug? rows are subscriptable so that means its getting None back. so its a bug that its not handling that more gracefully...but also, should be getting a row back.you should see in your SQL logs that a SELECT is being issued right after a series of INSERT/UPDATE statements for that table - if the SELECT queries for a primary key of NULL that may mean that the default-id-generation scheme in use is not working (either not genning an ID or not telling the result about it correctly)i know on MS-SQL the default ID generation schemes are quite complex. we'll see what Rick says but its possible things would work a whole lot better if you were using sqlalchemy 0.4. --~--~-~--~~~---~--~~ 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: MySQL encoding
What character set is the db-api driver using? Try: engine.connect().connection.character_set_name() If it's not utf8, you can configure the driver by adding 'charset=utf8' to your database url. I add charset='utf-8' to 'create_engine' function, but before send data(from query) to mako i need decode string from UTF-8 ... How can i do that automatically(decoding) ? --~--~-~--~~~---~--~~ 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: MySQL encoding
phasma wrote: What character set is the db-api driver using? Try: engine.connect().connection.character_set_name() If it's not utf8, you can configure the driver by adding 'charset=utf8' to your database url. I add charset='utf-8' to 'create_engine' function, but before send data(from query) to mako i need decode string from UTF-8 ... How can i do that automatically(decoding) ? Use the Unicode column type in your table declarations for these columns, or for global behavior you can add convert_unicode=True to your create_engine() arguments (not the url). With this driver you can also get some increased efficiency by having it do the Unicode translation. If all you've added to the engine URL is 'charset' it should already be in Unicode mode- try adding 'use_unicode=1' to the url as well if you're not getting Unicode strings from queries. Full info: http://www.sqlalchemy.org/docs/04/sqlalchemy_databases_mysql.html Cheers, Jason --~--~-~--~~~---~--~~ 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: Exclude Autogenerated Timestamp Column
That's unfortunate because our database is built around MS GUIDs and not integer PKs, but good to know. I am just using pythoncom.CreateGuid() to generate the object ids before insert and that is working great. thanks for all the help On Jan 11, 2:45 pm, Rick Morrison [EMAIL PROTECTED] wrote: MSSQL ID generation is limited to integer PKs of the IDENTITY type, and they work fine in 0.4 series. That wiki page should be updated. It's most likely a case of the Table not knowing that the PK should be an auto-increment type. Are you defining the table via an SA Table() definition, or trying to autoload the definition via table reflection? On Jan 11, 2008 5:23 PM, Dean Halford [EMAIL PROTECTED] wrote: thanks micheal - the only reason we went with 3.11 was the following statement on the wiki: Currently (Aug 2007) the 0.4 branch has a number of problems with MS- SQL. http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MS-SQL I checked the logs and it does have to do with the MS-SQL ID generation, so I'll readup on that. 2008-01-11 14:19:31,292 INFO sqlalchemy.engine.base.Engine.0x..30 SELECT shot.[skuId], shot.number, shot.name, shot.description, shot. [teamCategory], shot.comments, shot.props, shot.time, shot.talent, shot.source, shot.id, shot.now, shot.[rowNumber] FROM shot WHERE shot.id IS NULL - cheers On Jan 11, 1:46 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 11, 2008, at 4:37 PM, Dean Halford wrote: Thanks for the responses guys. The PassiveDefault() parameter did exactly what I wanted it to do - which was to exclude that column from the generated insert query so that MS SQL could handle those on it's own. ... now to figure out why I am getting an unsubscriptable object type error from the operation: --- ... File c:\python24\lib\site-packages\sqlalchemy-0.3.11-py2.4.egg \sqlalchemy\orm\mapper.py, line 1255, in _postfetch self.set_attr_by_column(obj, c, row[c]) TypeError: unsubscriptable object --- the comments for the _postfetch method indicate that it is checking to see if 'PassiveDefaults' were fired off on the insert. It looks the row[c] operation is breaking as the row object doesn't support [] subscripting... could be a bug? rows are subscriptable so that means its getting None back. so its a bug that its not handling that more gracefully...but also, should be getting a row back.you should see in your SQL logs that a SELECT is being issued right after a series of INSERT/UPDATE statements for that table - if the SELECT queries for a primary key of NULL that may mean that the default-id-generation scheme in use is not working (either not genning an ID or not telling the result about it correctly)i know on MS-SQL the default ID generation schemes are quite complex. we'll see what Rick says but its possible things would work a whole lot better if you were using sqlalchemy 0.4. --~--~-~--~~~---~--~~ 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: MySQL encoding
On 12 янв, 04:07, jason kirtland [EMAIL PROTECTED] wrote: phasma wrote: What character set is the db-api driver using? Try: engine.connect().connection.character_set_name() If it's not utf8, you can configure the driver by adding 'charset=utf8' to your database url. I add charset='utf-8' to 'create_engine' function, but before send data(from query) to mako i need decode string from UTF-8 ... How can i do that automatically(decoding) ? Use the Unicode column type in your table declarations for these columns, or for global behavior you can add convert_unicode=True to your create_engine() arguments (not the url). With this driver you can also get some increased efficiency by having it do the Unicode translation. If all you've added to the engine URL is 'charset' it should already be in Unicode mode- try adding 'use_unicode=1' to the url as well if you're not getting Unicode strings from queries. Full info: http://www.sqlalchemy.org/docs/04/sqlalchemy_databases_mysql.html Cheers, Jason Big thanks ) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Exclude Autogenerated Timestamp Column
My experience with GUID PKs is that they almost always cause more troubles than they purport to solve, and 99% of the time a plain Integer PK will work just fine instead. The two rare exceptions are with multi-database synchronization (and even there integer PKs can work fine with an additional 'source' discriminator column) and humungo databases where overflowing a bigint col is a real fear. If the only role it's going to play is a plain old surrogate key, they're almost always a bad idea. Anyway, you're not the first to ask about them. Trouble is that MSSQL doesn't make it easy to get the new GUID PK after it's been inserted. If you know the magic words to get MSSQL to cough up an auto-inserted GUID PK, then it would be fairly straightforward to add support for them. Rick --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
