[sqlalchemy] Deletion of referenced objects fails
Hi,
I have a problem using SQLAlchemy 0.4 when deleting referenced
objects in a PostgreSQL database and adding new ones within the same
transaction.
Originally, I found the problem with Elixir 0.4.0-pre (svn r216) and
SQLAlchemy 0.3.11 -
http://groups.google.com/group/sqlelixir/browse_thread/thread/b03a96e3ba9e61ea
Probably the problem can only be reproduced using a database which enforces
ForeignKey constraints (Postgresql does).
I don't know much about SQLAlchemy but I tried to create an SQLAlchemy script
which
reproduces the behavior (the original Elixir test case can be found on
http://pastebin.com/f3307e3c0 ). I hope the script exhibits really the same
problem as my Elixir script...
Example snippet (complete script http://pastebin.com/f6057bdbf ):
---
foo = session.query(User).filter_by(name='Foo Bar').one()
session.save(foo)
for address in foo.addresses:
foo.addresses.remove(address)
session.delete(address)
session.delete(foo)
foo = User()
session.save(foo)
foo.id = 1
foo_addr = Address()
session.save(foo_addr)
foo_addr.street = Picadelly Circus
foo.addresses.append(foo_addr)
transaction.commit()
---
This gives me the following traceback (complete output:
http://pastebin.com/f28f0e198 ,
original Elixir traceback http://pastebin.com/f5ae5c7c ):
---
Traceback (most recent call last):
File ./sqlalchemy_foreignkeys.py, line 88, in ?
transaction.commit()
...
File
/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py,
line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py,
line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) Aktualisieren oder
Löschen in Tabelle »Address« verletzt Fremdschlüssel-Constraint
»users_addresses__Address_Address_id_fkey« von Tabelle
»users_addresses__Address«
DETAIL: Auf Schlüssel (id)=(1) wird noch aus Tabelle
»users_addresses__Address« verwiesen.
'DELETE FROM Address WHERE Address.id = %(id)s' {'id': 1}
---
Sorry for the German exception message, I did not manage to get an English one
despite
switching the system locale to en_US. Here is a rough translation to English:
Update or deletion of table »Address« violates foreign key
constraint »users_addresses__Address_Address_id_fkey« of table
»users_addresses__Address«
DETAIL: Table »users_addresses__Address« still references key (id)=(1).
SQL trace:
---
BEGIN
SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name = %(users_name)s ORDER BY users.id LIMIT 2 OFFSET 0
{'users_name': 'Foo Bar'}
SELECT Address.id AS Address_id, Address.street AS Address_street
FROM Address, users_addresses__Address
WHERE %(param_1)s = users_addresses__Address.user_id AND
users_addresses__Address.Address_id = Address.id
{'param_1': 1}
UPDATE users SET name=%(name)s WHERE users.id = %(users_id)s
{'users_id': 1, 'name': None}
select nextval('Address_id_seq')
None
INSERT INTO Address (id, street) VALUES (%(id)s, %(street)s)
{'street': 'Picadelly Circus', 'id': 2L}
INSERT INTO users_addresses__Address (user_id, Address_id) VALUES
(%(user_id)s, %(Address_id)s)
{'Address_id': 2L, 'user_id': 1}
DELETE FROM Address WHERE Address.id = %(id)s
{'id': 1}
ROLLBACK
---
I think the problem is the order of the SQL deletion statements. The item in
»users_addresses__Address«
must be deleted before deleting the address.
Is this behavior by design? Do I abuse the SQLAlchemy api?
fs
--~--~-~--~~~---~--~~
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] unicode support for MSSQL
Hi Mike, Hi all, Our conversation yesterday night on IRC gave me ideas :) I just added ticket #839 to the trac and attached a patch that enables unicode conversion for MSSQL dialects. I tested it with pyodbc but it should work the same with the other dialects. Best regards, Florent Aide. --~--~-~--~~~---~--~~ 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: Looking for feedback on encapsulating SA logic (newbie)
Is this a turbogears app? or just your stand alone app?
On 10/28/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I just started experimenting with .4 last night, and I'm really
jazzed. The tutorials have been helpful, but I'm struggling to figure
out how to bridge the gap between the tutorial fragments and a real-
world application.
So far, I've got a little idea working, and I'm hoping to get some
feedback if I'm working in the right direction. I'm trying to
encapsulate my SA logic in its own module and work with these objects
in my programs.
Below is a very simplified example of what I'm trying to do. I could
be handling my sessions, metadata, and tables poorly/inefficiently,
and I'd love some feedback where it could be better.
One glaring problem is the handling of the session information. I
tried to put it into a __get_session() method of ModelHandler, but I
was having trouble getting it working when called from a subclass. (b/
c User has no method __get_session())
It also seems that the four lines under my classes (creating the
engine, metadata, mapping, etc.) could be put into the constructor of
my superclass, but I'm not sure how to refrerence it yet.
Thanks in advance - I'm really looking forward to diving deeper with
SA!
*** Models.py ***
from sqlalchemy import *
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker
class ModelHandler(object):
def save(self):
# This session stuff should probably be handled by a private
method
# but I'm having trouble getting it to work when save is
subclassed()
Session = sessionmaker(bind=db, autoflush=True,
transactional=True)
session = Session()
session.save(self)
session.commit()
print Debugging Statement: Saved User
class User(ModelHandler):
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
# There should be some tidy place to put these in my objects
db = create_engine('postgres://apache:@localhost:5432/test')
metadata = MetaData(db)
users_table = Table('users', metadata, autoload=True)
mapper(User, users_table)
from models import *
new_user = User('wendy', 'Wendy Williams', 'foobar')
new_user.save()
--
--
Vim auto completion for python
http://lucasmanual.com/mywiki/FrontPage#head-8ce19b13e89893059e126b719bebe4ee32fe103c
TurboGears from start to finish:
http://www.lucasmanual.com/mywiki/TurboGears
--~--~-~--~~~---~--~~
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: Separate version table
Thanks Arnar, that was interesting to read. I learned a lot with these
codes. Here i share current point of my progress. I managed to do
mapper extension and document - documentversion objects seems to work
ok, but im not so sure, if my solution was very elegant. So if anyone
sees this and wants to give further addvises, i'll be glad to hear. My
next step is to extend versioned documents from user point to the
organisation wide shared documents.
# Models
from string import join
from datetime import datetime
class User(object):
def __init__(self, name):
self.name = name
class Organisation(object):
def __init__(self, name):
self.name = name
class Language(object):
def __init__(self, alpha2, name):
self.alpha2 = alpha2
self.name = name
class Document(object):
def first(self):
return self.versions[0]
def latest(self):
return self.versions[len(self.versions)-1]
class DocumentVersion(object):
def __init__(self, name, content, language):
self.name = name
self.content = content
self.language = language
class OrganisationDocumentVersion(object):
def __init__(self, document_version, user):
self.document_version = document_version
self.updator = user
# Table definitions
from connections import engine
from sqlalchemy import ForeignKey, MetaData, Table, Column, String,
Integer, Unicode, DateTime, Date, Time, Boolean
from datetime import datetime
METADATA = MetaData()
USERS_TABLE = Table(users, METADATA,
Column(id, Integer, primary_key=True),
Column(name, Unicode(100), nullable=False),
)
ORGANISATIONS_TABLE = Table(organisations, METADATA,
Column(id, Integer, primary_key=True),
Column(name, Unicode(100), nullable=False),
)
LANGUAGES_TABLE = Table(languages, METADATA,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2), unique=True, nullable=False),
Column(name, Unicode(100), unique=True, nullable=False),
)
DOCUMENTS_TABLE = Table(documents, METADATA,
Column(id, Integer, primary_key=True),
Column(user_id, Integer, ForeignKey('users.id'),
nullable=False),
Column(status, Integer, default=1, nullable=False), # 0 =
deleted, 1 = active
Column(created, DateTime, default=datetime.now()),
)
DOCUMENT_VERSIONS_TABLE = Table(document_versions, METADATA,
Column(id, Integer, primary_key=True),
Column(document_id, Integer, ForeignKey('documents.id'),
nullable=False),
Column(language_id, Integer, ForeignKey('languages.id'),
nullable=False),
Column(name, Unicode(64), nullable=False),
Column(content, Unicode),
Column(version, Integer, default=1, nullable=False),
Column(updated, DateTime, default=datetime.now()),
Column(status, Integer, default=1, nullable=False), # 0 =
deleted, 1 = active
)
ORGANISATION_DOCUMENT_VERSIONS =
Table(organisation_document_versions, METADATA,
Column(organisation_id, Integer, ForeignKey('organisations.id'),
primary_key=True),
Column(document_version_id, Integer,
ForeignKey('document_versions.id'), primary_key=True),
Column(user_id, Integer, ForeignKey('users.id'),
nullable=False),
)
METADATA.create_all(engine)
# Mappers
from models import *
from tables import *
from sqlalchemy.orm import mapper, relation, MapperExtension,
EXT_PASS, EXT_STOP
from datetime import datetime
class VersionedDocumentMapperExtension(MapperExtension):
def before_update(self, mapper, connection, instance):
colvalues = dict([(key, getattr(instance, key)) for key in
instance.c.keys()])
del colvalues['id']
colvalues['version'] = instance.version + 1
colvalues['updated'] = datetime.now()
# create a new version insert
ins = DOCUMENT_VERSIONS_TABLE.insert(colvalues)
connection.execute(ins)
# get old values select, revert old values to current instance
# this prevents making an update for the current instance
sel =
DOCUMENT_VERSIONS_TABLE.select(DOCUMENT_VERSIONS_TABLE.c.id ==
instance.id)
oldvalues = connection.execute(sel).fetchone()
instance.name = oldvalues[name]
instance.content = oldvalues[content]
instance.language_id = oldvalues[language_id]
instance.status = oldvalues[status]
return EXT_STOP
def before_delete(self, mapper, connection, instance):
TODO! Hwo to halt actualy deleting the row?
upd = DOCUMENT_TABLE.update(DOCUMENT_TABLE.c.id ==
instance.document.id)
[sqlalchemy] Re: Looking for feedback on encapsulating SA logic (newbie)
On Oct 30, 2007, at 2:26 AM, Sanjay wrote: mapper(User, users_table) Is not using assign_mapper preferable? assign_mapper is an optional extension. its replaced in version 0.4 by the ScopedSession.mapper function, which is described in the docs. only use these extensions if you're looking for their specific functionality...they aren't needed to use sqlalchemy. --~--~-~--~~~---~--~~ 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: How to get list of relations
Hei Wes and Paul...
On 10/29/07, Wes Duff [EMAIL PROTECTED] wrote:
Hey whats going on.
I am a new sqlalchemist as well but lets see if this helps any.
This is how I am getting a list of all names that corrispond with my
document names class.
names = [ c.name for c in model.Document.select_by(param=param) ]
So I am just collecting all the names from my Document table. I added
select_by(param=param) if you want to find all the names that have to do
with a parameter.
names becomes a list
I hope this helps a little.
I think you misunderstood Paul's question (which I'm also waiting for
response)... :)
What Paul is asking is: given this mapping:
mapper(User, users_table)
mapper(Address, addresses_table, properties={
'user' : relation(User, backref='addresses', lazy=False)
})
We are able to know that Address class has a reference to User class
by introspecting its mapper's properties:
q = session.query(Address)
q.mapper.properties['user']
sqlalchemy.orm.properties.PropertyLoader object at 0x013094D0
However, if the relation is declared this way:
mapper(Address, addresses_table)
mapper(User, users_table, properties={
'addresses' : relation(Address, backref='user', lazy=False)
})
... our introspection gives us:
q = session.query(Address)
q.mapper.properties['user']
Traceback (most recent call last):
File pyshell#42, line 1, in -toplevel-
q.mapper.properties['user']
KeyError: 'user'
But Address actually has a user property... :(
If we need to introspect both Address-user and User-addresses, one
solution is to not use the backref ... :
mapper(User, users_table, properties={
'addresses' : relation(Address, lazy=False)})
mapper(Address, addresses_table, properties={
'user' : relation(User, lazy=False)})
q = session.query(User)
q.mapper.properties['addresses']
sqlalchemy.orm.properties.PropertyLoader object at 0x01313F70
q = session.query(Address)
q.mapper.properties['user']
sqlalchemy.orm.properties.PropertyLoader object at 0x013134F0
BTW, one practical use of this introspection can be found at:
http://trac.turbogears.org/ticket/1582
On Oct 29, 2007 11:21 AM, Paul Johnston [EMAIL PROTECTED] wrote:
Hi,
How do I get a list of the relations a mapper has? I've been using
mapper.properties, but have just realised this doesn't pick up backrefs.
/me too...:)
Any ideas? Thanks,
I also want to know that...
Cheers,
Roger
--~--~-~--~~~---~--~~
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] sort results by date
Hello, I am trying to make a select statement which involves date sorting. x1=model.User.select(model.User.c.ZIPCODE==zipcode ) now I get a list of 3 records x1[0],x1[1],x1[2] How can I sort x1 by x[0].c.DATE ?? ASC ? What would python code for that look like? or how could I change this to sort by DATE ASC? x1=model.User.select(model.User.c.ZIPCODE==zipcode ) Lucas --~--~-~--~~~---~--~~ 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: How to get list of relations
On Oct 30, 2007, at 10:16 AM, Roger Demetrescu wrote: If we need to introspect both Address-user and User-addresses, one solution is to not use the backref ... : use mapper.get_property(name) and mapper.iterate_properties(). I've considered removing properties as a public accessor since it serves no useful purpose. --~--~-~--~~~---~--~~ 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: Impossible to reopen a ticket in TG's trac system
Dammit... Sent it to the wrong ML.. :-/ Sorry about the noise... Roger On 10/30/07, Roger Demetrescu [EMAIL PROTECTED] wrote: Hei guys... I have just tried to reopen a ticket, but I am getting this error: Submission rejected as potential spam (Akismet says content is spam) I swear... I am not a spammer... :o) []s Roger --~--~-~--~~~---~--~~ 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: How to get list of relations
Mike,
use mapper.get_property(name) and mapper.iterate_properties(). I've
considered removing properties as a public accessor since it serves
no useful purpose.
This doesn't work for me - the following code outputs:
[Column('id', Integer(), primary_key=True, nullable=False)]
[Column('val', String(length=None,convert_unicode=False))]
I can do a test case without Elixir if needed, but I don't think that will
change the result.
from sqlalchemy import *
from elixir import *
__metadata__ = MetaData('mssql://./test')
class Paj(Entity):
val = Field(String)
class Bob(Entity):
paj = ManyToOne(Paj, primary_key=True, backref='bob')
silly = Field(Integer)
for a in Paj.mapper.iterate_properties:
print a.columns
Paul
--~--~-~--~~~---~--~~
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: How to get list of relations
Hi, use mapper.get_property(name) and mapper.iterate_properties (). I've considered removing properties as a public accessor since it serves no useful purpose. Ok, I found a hacky way that does what I need: [(n, getattr(obj, n)) for n in dir(obj) if isinstance(getattr(obj, n), sqlalchemy.orm.attributes.InstrumentedAttribute)] That'll do me for now. Paul --~--~-~--~~~---~--~~ 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: Deletion of referenced objects fails
On Oct 30, 2007, at 5:41 AM, Felix Schwarz wrote: foo = session.query(User).filter_by(name='Foo Bar').one() session.save(foo) for address in foo.addresses: foo.addresses.remove(address) session.delete(address) session.delete(foo) foo = User() session.save(foo) foo.id = 1 foo_addr = Address() session.save(foo_addr) foo_addr.street = Picadelly Circus foo.addresses.append(foo_addr) transaction.commit() specifically its the foo.id=1 thats causing it to fail. by setting it, you trigger a special rule in SQLAlchemy designed to deal with this, called a row switch, where it converts your DELETE and INSERT into a single UPDATE. apparently the cascade onto the related addresses collection is getting confused; ticket 841 is added. additionally, your session.save(foo) right below the query is also incorrect (though does not affect the test), ive added ticket 840 to ensure an exception is raised there. if you truly need to row switch, i.e. your second User needs to have the same id #1, issue a flush after the deletion of the previous user: for address in foo.addresses: foo.addresses.remove(address) session.delete(address) session.flush() that will remove the previous address rows from the database before getting into the newly added user. also the removal of the addresses there could be automated by just placing cascade='all,delete-orphan' on the User.addresses 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: Looking for feedback on encapsulating SA logic (newbie)
Is this a turbogears app? or just your stand alone app? It's a standalone (and non-web) app. --~--~-~--~~~---~--~~ 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: How to get list of relations
Hi, Ok, I found a hacky way that does what I need: [(n, getattr(obj, n)) for n in dir(obj) if isinstance(getattr(obj, n), sqlalchemy.orm.attributes.InstrumentedAttribute)] Ooops, not quite what I need. How do I go from a CollectionAttributeImpl to a mapper? Paul --~--~-~--~~~---~--~~ 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: Add arbitrary information to some classes
Rick Morrison wrote: On 10/26/07, *Paul Johnston* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Ticket #573 mentions adding a field for arbitrary application-specific information to tables. I now have a need for this, so I'm prepared to do the work to make it happen. The main consideration is the name of the field, with the ticket suggesting attributes. Personally I'd prefer info, but I'm fine with attributes. The other decision is what objects to add this to. I need it on column, and table seems sensible. Query, session, metadata have been mentioned, although I'd expect the requirement there is less common. So, I propose adding attributes to Table and Column. Any thoughts? I often use Session as a context placeholder, and have felt a bit uneasy about this as you never know when some new release is going to stake a claim on the name you've used. I know I'd feel better if there was a name that would be kept aside. 'attributes' (QueryContext) and 'properties' (Connection) are both in use currently. Of these I prefer 'properties', but both names have the serious downside of also being the names of key ORM components. The thesaurus turned up 'notes' as another option. How does that sound? Apache uses 'notes' for their request object and that api has always seemed very clear to me. -j --~--~-~--~~~---~--~~ 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: How to get list of relations
On Oct 30, 2007, at 11:25 AM, Paul Johnston wrote:
Mike,
use mapper.get_property(name) and mapper.iterate_properties (). I've
considered removing properties as a public accessor since it serves
no useful purpose.
This doesn't work for me - the following code outputs:
[Column('id', Integer(), primary_key=True, nullable=False)]
[Column('val', String(length=None,convert_unicode=False))]
I can do a test case without Elixir if needed, but I don't think
that will change the result.
from sqlalchemy import *
from elixir import *
__metadata__ = MetaData('mssql://./test')
class Paj(Entity):
val = Field(String)
class Bob(Entity):
paj = ManyToOne(Paj, primary_key=True, backref='bob')
silly = Field(Integer)
for a in Paj.mapper.iterate_properties:
print a.columns
what is it youre looking for ? those columns are associated with
ColumnProperty objects associated with your mapper. you want to
filter out and get just the PropertyLoaders, those correspond to
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: How to get list of relations
On 10/30/07, Paul Johnston [EMAIL PROTECTED] wrote:
Mike,
use mapper.get_property(name) and mapper.iterate_properties (). I've
considered removing properties as a public accessor since it serves
no useful purpose.
This doesn't work for me - the following code outputs:
[Column('id', Integer(), primary_key=True, nullable=False)]
[Column('val', String(length=None,convert_unicode=False))]
I can do a test case without Elixir if needed, but I don't think that will
change the result.
from sqlalchemy import *
from elixir import *
__metadata__ = MetaData('mssql://./test')
class Paj(Entity):
val = Field(String)
class Bob(Entity):
paj = ManyToOne(Paj, primary_key=True, backref='bob')
silly = Field(Integer)
You are missing a compile call before you can iterate properties.
try adding:
Paj.mapper.compile()
But maybe that should be done automatically in iterate_properties.
for a in Paj.mapper.iterate_properties:
print a.columns
--
Gaëtan de Menten
http://openhex.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: Add arbitrary information to some classes
personal opinion: I'm not wild about either 'attributes' or 'properties', (a) they seem too long, and (b) yes, they are too similar to generic ORM terms many many moons ago (pre Windows-1.0) I used an Ascii-GUI thing called C-scape (I think it's called vermont views now). anyway, most of its objects had a space for a pointer to arbitrary user data, and they consistently used something like udata for the name of the pointer. So I'm +1 on a short, non-generic and uniquely user-y kind of name like udata. I know it sounds ugly, but we're dealing with database and ORM terminology. Just about every generic name you can think of is bound to be confused with something database-oriented. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Add arbitrary information to some classes
Rick Morrison wrote: personal opinion: I'm not wild about either 'attributes' or 'properties', (a) they seem too long, and (b) yes, they are too similar to generic ORM terms many many moons ago (pre Windows-1.0 ) I used an Ascii-GUI thing called C-scape (I think it's called vermont views now). anyway, most of its objects had a space for a pointer to arbitrary user data, and they consistently used something like udata for the name of the pointer. So I'm +1 on a short, non-generic and uniquely user-y kind of name like udata. I know it sounds ugly, but we're dealing with database and ORM terminology. Just about every generic name you can think of is bound to be confused with something database-oriented. The core can (and does) use these buckets too, so I'm not sure about the user-y moniker. But if that were it, I'd only be +1 on a spelled out version like 'userdata' that can be grokked without consulting the docs. --~--~-~--~~~---~--~~ 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: Add arbitrary information to some classes
The core can (and does) use these buckets too, so I'm not sure about the user-y moniker. Hold it. I thought the whole point of this was to separate core usage from user usage? To create a safe-zone for library user's private data. But if that were it, I'd only be +1 on a spelled out version like 'userdata' that can be grokked without consulting the docs. Sure, agreed here. --~--~-~--~~~---~--~~ 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: post_processors error during 0.3.10 to 0.4 migration (returning different object type based on db data)
you could just be using one mapper for all the classes here. its
almost like you should monkeypatch class_mapper() and object_mapper()
and just be done with it.
of course the reason mappers are usually specific to a class is
because, every class would have completely different attributes and
relations. but it seems here that is not the case.
Well, the subclasses actually map to a select on the table while Thing
maps to the whole table. So the Class mapped over different sets even
though they had identical attributes and relations. But the primary
reason for the additional classes is to get additional methods/
functionality so I may be able to drop the added complexity of mapping
to selects. How do you reuse a mapper on additional classes?
class Foo(object):
pass
foo_mapper = mapper(...)
class Bar(Foo):
pass
then what?
foo_mapper.addClass(?) or something ??
so, you want people to say:
s = Server(model='apple')
then later, they ...upgrade ? by v0.1 - v0.2 you mean a new copy of
your framework ? or just hypothetical versions of the user's
application ?
then they say:
s = some_query.get_my_thing(criterion)
and they get back an AppleServer, which is some kind of improvement
over Server.
I guess I should explain further what I'm trying to build. My app is
a tool to help with managing clusters. This includes everything from
Datacenters, Racks, Servers, Switches, etc. I don't know ahead of
time the sorts of specific Things and functionality that might be
useful so I'm trying to make things as flexible, generic, and easy to
extend as possible. So at the core I suppose you could call this
Thing/Attributes abstraction the framework upon which I and others
would code drivers like Server, SunServer, AppleServer, Switch,
CiscoSwitch, CiscoRouter, Pool, Location, etc etc. All those together
with a command line interface to facilitate scripting is an app called
clusto. So, I'm a sysadmin and I'm using this tool. I just bought a
Load Balancer 5000. I immediately put it into my system as a plain
old LoadBalancer(manufacturer='Load Balancer', model='5000'). Later
on I decide that I'd like clusto to be able to add servers to my fancy
LoadBalancer5000 configuration. Nobody else has implemented the
functionality yet, so in true open source form, I dive in and do it
myself:
class LoadBalancer5000(LoadBalancer):
meta_attrs = [('manufacturer', 'Load Balancer'), ('model', '5000')]
def addServer(self, someserver):
# magic
done. No futzing with the database, no diving into obscure parts of
the code, nothing. I just plop that class into the right path and it
works. With a clever command line and scripting interface it may even
be useful.
would they ever get a Server back again ? if not, why does the
database need to change ? why not just map AppleServer to
server ?
Because there might also be a SunServer and a FooBarServer and an
AlphaServer.
also, arent you concerned about query overhead here ?
with all your objects being completely homogenized into a vertical
structure and all, that is.
Yeah, this tool isn't built for speed or high load. It's built for
flexibility and usefulness. If I want speed I'll figure out caching
and optimization later. Also, it is version 0.0001 so it's acting, in
part, as a proof of concept.
theres no straightforward way for me to
get a list of all the AppleServers, for example, since id have to
query all these different attributes just to identify those objects.
So, underneath the hood, to get all the AppleServers you'd do:
## pseudocode
for attr in SomeThingClass.all_meta_attrs:
# all_meta_attrs is a list of all the meta_attrs for that class going
up the inheritance chain, cls.mro()
thingquery += and_(Attribute.c.key==attr[0],
Attribute.c.value==attr[1])
select(and_(Thing.c.name==Attribute.c.name, thingquery))
#that should get all the Thing that can be managed by the given
class. Maybe not straightforward but not terribly complex either.
So, in my implementation, the metaclass mapped each Class to such a
select. I am mapping against different selectables, and so having
different mappers made sense. So if I did SA functions like:
AppleServer.select(and_(Attribute.c.key='numports',
Attribute.c.value='2'))
I'd only get AppleServers with ('numports', '2') and not any other
types of Things. At one point I got things working as I just
described, but I'm not sure if that was the case in my latest
iteration of the code.
I'm still soaking in these examples. I think what I really want is to
have mapper accept something like polymorphic_func and base_class. So
I would pass it my _setProperClass function and Thing. The mapper
will build against Thing and then run _setProperClass against the
instance. Yeah, I'm cheating, cause that's kind of basically what I'm
doing now. I'm just not sure how else to achieve the functionality
I'm looking for.
ah well making polymorphic_on optionally a callable
[sqlalchemy] Re: Add arbitrary information to some classes
Rick Morrison wrote: The core can (and does) use these buckets too, so I'm not sure about the user-y moniker. Hold it. I thought the whole point of this was to separate core usage from user usage? To create a safe-zone for library user's private data. Yes to the second but not exactly the first. The goal is a stable bucket for everyone's annotations- end-user, apps, 3rd party extensions, internal extensions, etc. Keeping annotations separate is just a matter of namespacing in the dict keys- the tuple-style SA currently uses and the dotted-string style the WSGI 'environ' uses both work well. --~--~-~--~~~---~--~~ 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: Deletion of referenced objects fails
On Oct 30, 2007, at 5:41 AM, Felix Schwarz wrote: for address in foo.addresses: foo.addresses.remove(address) session.delete(address) session.delete(foo) foo = User() session.save(foo) foo.id = 1 foo_addr = Address() session.save(foo_addr) foo_addr.street = Picadelly Circus foo.addresses.append(foo_addr) transaction.commit() -- - OK, both tickets are fixed in r3681. you can use the trunk if you like or it will be available in version 0.4.1. --~--~-~--~~~---~--~~ 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: Add arbitrary information to some classes
Ah sure, so it's to be a namespace for namespaces, a shared dict() parking lot. Got it. So then, how about aux etc other or maybe miscdata extra more additional supplemental auxiliary adjunct appendix surplus spare augment --~--~-~--~~~---~--~~ 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: post_processors error during 0.3.10 to 0.4 migration (returning different object type based on db data)
On Oct 30, 2007, at 1:35 PM, Ron wrote:
I guess I should explain further what I'm trying to build. My app is
a tool to help with managing clusters. This includes everything from
Datacenters, Racks, Servers, Switches, etc. I don't know ahead of
time the sorts of specific Things and functionality that might be
useful so I'm trying to make things as flexible, generic, and easy to
extend as possible. So at the core I suppose you could call this
Thing/Attributes abstraction the framework upon which I and others
would code drivers like Server, SunServer, AppleServer, Switch,
CiscoSwitch, CiscoRouter, Pool, Location, etc etc. All those together
with a command line interface to facilitate scripting is an app called
clusto. So, I'm a sysadmin and I'm using this tool. I just bought a
Load Balancer 5000. I immediately put it into my system as a plain
old LoadBalancer(manufacturer='Load Balancer', model='5000'). Later
on I decide that I'd like clusto to be able to add servers to my fancy
LoadBalancer5000 configuration. Nobody else has implemented the
functionality yet, so in true open source form, I dive in and do it
myself:
class LoadBalancer5000(LoadBalancer):
meta_attrs = [('manufacturer', 'Load Balancer'), ('model', '5000')]
def addServer(self, someserver):
# magic
done. No futzing with the database, no diving into obscure parts of
the code, nothing. I just plop that class into the right path and it
works. With a clever command line and scripting interface it may even
be useful.
If i were writing an app like that, id actualy have some kind of end-
user commands: create new type - LoadBalancer5000; convert all
LoadBalancer + model=5000 to LoadBalancer5000. i.e. i *would*
update the data, but id make it easy. because the database is much
more efficient if you use a single horizontal column to differentiate
types. if you have to dive into vertical attributes every time, that
greatly limits functionality. what if i wanted to get a report of
25,000 objects and their types really quickly ? would you rather
iterate through 25000 rows, or 25000 * total number of attributes,
apply complex rules on the client side to aggrgate the attribute rows
together and determine types, etc ? you're not really making the
best usage of the database in that case.
this is actually not a unique scenario at all. If you work with
search engines, often you have to configure a combination of
horizontal and vertical properties for documents which are
stored. the horizontal properties are those that can be searched
very quickly, whereas the vertical are those which require
secondary queries to retrieve (like the document's full list of
metatags).
theres no straightforward way for me to
get a list of all the AppleServers, for example, since id have to
query all these different attributes just to identify those objects.
So, underneath the hood, to get all the AppleServers you'd do:
## pseudocode
for attr in SomeThingClass.all_meta_attrs:
# all_meta_attrs is a list of all the meta_attrs for that class going
up the inheritance chain, cls.mro()
thingquery += and_(Attribute.c.key==attr[0],
Attribute.c.value==attr[1])
select(and_(Thing.c.name==Attribute.c.name, thingquery))
#that should get all the Thing that can be managed by the given
class. Maybe not straightforward but not terribly complex either.
So, in my implementation, the metaclass mapped each Class to such a
select. I am mapping against different selectables, and so having
different mappers made sense. So if I did SA functions like:
AppleServer.select(and_(Attribute.c.key='numports',
Attribute.c.value='2'))
I'd only get AppleServers with ('numports', '2') and not any other
types of Things. At one point I got things working as I just
described, but I'm not sure if that was the case in my latest
iteration of the code.
you can still have a bunch of selects that you just feed into a Thing
query. its not critical to have them mapped.
however, didnt you say that your class
attributes come from a different table ? in that case this is still
not going to work...if youre relying upon eager loading of related ,
multiple sets of rows, thats not available until well after the
polymorphic decisions have been made. the most that polymorhpic_func
could get is the first row with the Thing's primary key in it.
That's a good point. I suppose the function could use that primary
key to select stuff out of the Attributes table and then analyze those
to determine the proper class. But that seems like an unhappy hack.
Why isn't there a hook into a post-populate part of the mapping? Or
whatever the absolute very last step of making an instance happens to
be. Does such a thing exist and I just missed it?
no, we'd have to add a hook there too. every hook slows down
sqlalchemy's load time just a little bit more, not because of the
hook itself but because
[sqlalchemy] Re: SQLalchemy coding style
Hi all, thank you for your comments. I really expected to get comments from more people. But probably I'm concerned about something you don't have to. ;-) So, I'll wait. Best regards Andreas --~--~-~--~~~---~--~~ 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: SQLalchemy coding style
On 10/30/07, McA [EMAIL PROTECTED] wrote: Hi all, thank you for your comments. I really expected to get comments from more people. But probably I'm concerned about something you don't have to. ;-) So, I'll wait. it seems better to use: import sqlalchemy and in code do: sqlalchemy.select(...) if you start using import * from sqlalchemy then your code will get confusing really quick. For example you won't know which select() you talk about if you use pure sqlaclhemy or assign_mapper. So it is preferred that you use import sqlalchemy or import sqlalchemy as sa at all times. Lucas -- -- Vim auto completion for python http://lucasmanual.com/mywiki/FrontPage#head-8ce19b13e89893059e126b719bebe4ee32fe103c TurboGears from start to finish: http://www.lucasmanual.com/mywiki/TurboGears --~--~-~--~~~---~--~~ 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: SQLalchemy coding style
If you don't want to pollute the current namespace, then make an indirect
reference.
1) Make a module of your own, say db.py
2) In db.py:
...
from sqlalchemy import *
from sqlalchemy.orm import *
...
table1 = Table('footable', ...)
...
# other table defs, mappers, classes, etc.
3) In your non-polluted code:
...
import db# your indirect reference
...
...
qry = db.select([db.table1.c.col], db.table1.c.col2 == 'foo')
So now you have most of the convenience of all of the SqlAlchemy names all
loaded, but your namespace only contains the 'db' indirect reference. You
still need to worry about the 'db' namespace being polluted, but at least
it's only in that one module.
On 10/29/07, McA [EMAIL PROTECTED] wrote:
Hi all,
I'm intersted in using sqlalchemy and started to read the manuals.
I didn't find a hint for my question, so I'm asking here. I hope it's
not too annoying.
Most code examples in the documentation use something like this
from sqlalchemy. import
My question is: Is this the good/proper way to import the sqlalchemy
stuff.
I'm concerned about polluting the current namespace. I could assume
that
class names like 'Table' are too common to reserve them for the
sqlalchemy
classes.
What would you recommend? How are the gurus out there using
sqlalchemy?
Thanks in advance.
Best regards
Andreas Mock
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
