[sqlalchemy] Re: associative table with extra field
That made difference, i think i got it working. I made a little
example for study purposes. From test part you see i can fetch
meaningful information with Country, CountryLanguage and Language
objects. Country has many CountryLanguages (having Language object and
is_primary field), CountryLanguage has only one Country but Language
has many countries. Thank you Barry and Michael.
PS.
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association
might have an update time due to this. I couldnt get association table
work without primary key field definitions, but on tutorial, its not
mentioned.
##
# Tables
##
metadata = MetaData()
countries_table = Table(countries, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(alpha3, String(3)),
Column(name, Unicode(100)),
)
countries_languages = Table(countries_languages, metadata,
# either separate primary key or both link fields as primary_keys
#Column(id, Integer, primary_key=True),
Column(country_id, Integer, ForeignKey('countries.id'),
primary_key=True),
Column(language_id, Integer, ForeignKey('languages.id'),
primary_key=True),
Column(is_primary, Boolean, default=False), # only one can be
default at time
)
languages_table = Table(languages, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(name, Unicode(100)),
)
metadata.create_all(engine)
##
# Models
##
class Country(object):
def __init__(self, alpha2, alpha3, name):
self.alpha2 = alpha2
self.alpha3 = alpha3
self.name = name
def __repr__(self):
return %s(%s, %s, %s) % (self.__class__, self.name,
self.alpha2, self.alpha3)
class Language(object):
def __init__(self, alpha2, name):
self.alpha2 = alpha2
self.name = name
def __repr__(self):
return %s(%s, %s) % (self.__class__, self.name,
self.alpha2)
class CountryLanguage(object):
def __init__(self, language, is_primary = False):
self.language = language
self.is_primary = is_primary
def __repr__(self):
return %s(%s, %s) % (self.__class__, self.language,
self.is_primary)
##
# Mappers
##
mapper(Country, countries_table, properties={
'languages':relation(CountryLanguage, backref='country'), # many
to many
}
)
mapper(CountryLanguage, countries_languages, properties={
'language':relation(Language, backref=countries)
}
)
mapper(Language, languages_table)
##
# Fixtures
##
language_fi = Language('fi', 'Finnish')
language_sv = Language('sv', 'Swedish')
language_es = Language('es', 'Spain')
language_en = Language('en', 'English')
country_fi = Country('fi', 'fin', 'Finland')
country_se = Country('se', 'swe', 'Sweden')
clanguage_fi = CountryLanguage(language_fi, True) # making this mother
language
clanguage_sv = CountryLanguage(language_sv)
country_fi.languages.append(clanguage_fi)
country_fi.languages.append(clanguage_sv)
# more straight form
country_se.languages.append(CountryLanguage(language_sv, True))
# db_sess = Session()
db_sess.save(country_fi)
db_sess.save(country_se)
db_sess.commit()
##
# Test
##
language = db_sess.query(Language).filter_by(name=Finnish).first()
country = db_sess.query(Country).filter_by(alpha2=se).first()
print language
print country
# get all Country and CountryLanguage
countries = db_sess.query(Country)
for country in countries:
for language in country.languages:
print language
# get all Language and Country
languages = db_sess.query(Language)
for language in languages:
for country in language.countries:
print country
# get all CountryLanguage and Country
clanguages = db_sess.query(CountryLanguage)
for clanguage in clanguages:
print clanguage.language
print clanguage.country
--~--~-~--~~~---~--~~
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] Separate version table
Next design problem for me is version table. I have Document model
with DocumentVersion model, but i dont know how to:
- get the latest version of document
- set creator and updator, automatic behavior for this
- update version number
- fetch thru Document(s) and DocumentVersion(s)
Lets see the code sample i have prepared (raw sketch):
##
# Tables
##
users_table = Table(users, metadata,
Column(id, Integer, primary_key=True),
)
languages_table = Table(languages, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(name, Unicode(100)),
)
documents_table = Table(documents, metadata,
Column(id, Integer, primary_key=True),
# how to get latest version
# Column(latest_version, Integer,
ForeignKey('document_versions.version'), default=1),
Column(created, DateTime, default=datetime.now),
# how to set creator and updator?
Column(creator, Integer, ForeignKey('users.id'),
default=uid_users_id),
)
document_versions_table = Table(document_versions, metadata,
Column(id, Integer, primary_key=True),
Column(document_id, Integer, ForeignKey('documents.id')),
Column(language_id, Integer, ForeignKey('language.id')),
Column(name, Unicode(64)),
Column(content, Unicode),
# how to update DocumentVersion.version ???
Column(version, Integer, default=1,
onupdate=DocumentVersion.version+1),
Column(updated, DateTime, default=datetime.now,
onupdate=datetime.now),
# how to set creator and updator?
Column(updator, Integer, ForeignKey('users.id'),
onupdate=uid_users_id),
)
##
# Models
##
class BaseObject(object):
def __init__(self):
self._repr_ = []
def __repr__(self):
from string import join
str = ', '.join('%s' % (self.__dict__[v]) for v in
self._repr_)
return %s(%s) % (self.__class__, str)
class NameObject(BaseObject):
def __init__(self, name):
BaseObject.__init__(self)
self.name = name
def __repr__(self):
self._repr_ = [name]
return BaseObject.__repr__(self)
class User(BaseObject):
def __init__(self):
BaseObject.__init__(self)
class Language(NameObject):
def __init__(self, alpha2, name):
NameObject.__init__(self, name)
self.alpha2 = alpha2
def __repr__(self):
self._repr_ = [alpha2, name]
return BaseObject.__repr__(self)
class Document(BaseObject):
def __init__(self):
BaseObject.__init__(self)
def __repr__(self):
self._repr_ = [created, creator]
return BaseObject.__repr__(self)
class DocumentVersion(NameObject):
def __init__(self, name, content = , language = Null):
NameObject.__init__(self, name)
self.document = Document()
self.content = content
self.language = language
def __repr__(self):
self._repr_ = [name, document, version]
return BaseObject.__repr__(self)
class UserDocument(object):
pass
class OrganisationDocument(object):
pass
##
# Mappers
##
mapper(User, users_table)
mapper(Document, documents_table, properties={
'user':relation(User, backref=documentversions),
}
)
mapper(DocumentVersion, document_versions_table, properties={
'document':relation(Document, backref=documentversions),
'language':relation(Language, backref=documentversions),
'user':relation(User, backref=documentversions),
}
)
mapper(Language, languages_table)
mapper(UserDocument, user_documents, properties={
'document':relation(DocumentVersion, backref=users),
}
)
mapper(OrganisationDocument, organisation_documents, properties={
'document':relation(DocumentVersion, backref=organisations),
}
)
##
# Fixtures
##
language_fi = Language('fi', 'Finnish')
language_en = Language('en', 'English')
dv = DocumentVersion(My first document, some content...,
language_en)
db_sess.save(dv)
# accidental english
dv = DocumentVersion(Toinen dokumentti, hiukan tavaraa...,
language_en)
db_sess.save(dv)
##
# Tests and examples
##
dv = db_sess.query(DocumentVersion).filter_by(name=Toinen
dokumentti).first()
dv.language = language_fi
dv.save()
print dv # should have version 2
# how to fetch thru Document(s) and their DocumentVersion(s)
# how to set up UserDocument(s)
--~--~-~--~~~---~--~~
You
[sqlalchemy] Re: Separate version table
Hi there,
On 10/28/07, mmstud [EMAIL PROTECTED] wrote:
Next design problem for me is version table. I have Document model
with DocumentVersion model, but i dont know how to:
- get the latest version of document
- set creator and updator, automatic behavior for this
- update version number
- fetch thru Document(s) and DocumentVersion(s)
I didn't read your code thoroughly, but I have a model with some
similarities. Perhaps it will provide some insight. Basically, I'm
dealing with Pages and PageVersions. PageVersions refers to it's
parent Page, but Page also keeps the version number of the latest
version.
Arnar
# encoding: utf-8
import os
from datetime import datetime
from sqlalchemy import *
from softproof import utils
from softproof.json import jsonify_saobject
__meta__ = metadata
def constructor(fun):
def decorated(self, *args, **kw):
assert hasattr(self, 'c')
for key,value in kw.items():
if hasattr(self.c, key):
setattr(self, key, value)
del kw[key]
fun(self, *args, **kw)
return decorated
jobs = Table(jobs, __meta__,
Column(jobno, Unicode(15), primary_key=True),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(deleted, Boolean, nullable=False, default=False))
class Job(object):
@constructor
def __init__(self, jobno=None):
if jobno:
self.jobno = jobno
def sortedpages(self):
listcopy = self.pages[:]
listcopy.sort(key=Page.sort_key)
return listcopy
def get_page_by_name(self, pagename):
Finnur síðu með nafnið pagename og skilar henni. Skilar
None ef engin síða hefur
viðkomandi nafn.
Ef pagename er _firstpage_ er skilað viðeigandi síðu (t.d.
kápu ef hún er til)
if len(self.pages) == 0:
return None
if '_firstpage_' == pagename:
for p in self.pages:
if 'KAP' in p.pagename:
return p
return self.pages[0]
for p in self.pages:
if p.pagename == pagename:
return p
return None
def create_page(self, pagename, *args, **kwargs):
p = Page(job=self, pagename=pagename, *args, **kwargs)
return p
def get_path(self):
if self.jobno.startswith('P'):
pg1, pg2, pg3 = self.jobno.split('.')
return os.path.join(pg1, pg1+'.'+pg2, self.jobno)
else:
return os.path.join(self.jobno[:-3]+'000',
self.jobno[:-2]+'00', self.jobno)
mapper(Job, jobs)
pageversions = Table(pageversions, __meta__,
Column(jobno, Unicode(15),
ForeignKey(pages.jobno), primary_key=True),
Column(pagename, Unicode(30),
ForeignKey(pages.pagename), primary_key=True),
Column(version, Integer, primary_key=True, default=1),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(md5sum, String(32)),
Column(width, Integer, nullable=False, default=0),
Column(height, Integer, nullable=False, default=0),
ForeignKeyConstraint([jobno,
pagename],[pages.jobno, pages.pagename]))
class PageVersion(object):
@constructor
def __init__(self, page=None, version=None):
if page:
self.page = page
if version:
self.version = version
@property
def filename(self):
if self.version == 1:
return self.page.pagename + '.jpg'
else:
return %s.v%02d.jpg % (self.page.pagename, self.version)
mapper(PageVersion, pageversions)
PageStates = utils.Enum('new', 'approved', 'rejected')
pages = Table(pages, __meta__,
Column(jobno, Unicode(15), ForeignKey(jobs.jobno),
primary_key=True),
Column(pagename, Unicode(30), primary_key=True),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(deleted, Boolean, nullable=False, default=False),
Column(current_version, Integer),
Column(status, PageStates, nullable=False,
default=PageStates.new))
class Page(object):
@constructor
def __init__(self, job=None, pagename=None):
if job:
self.job = job
if pagename:
self.pagename = pagename
self.currentversion = PageVersion(self, 1)
self.status = PageStates.new
def add_version(self):
self.currentversion = PageVersion(self, self.currentversion.version+1)
self.status = PageStates.new
comment = self.add_comment()
comment.closeable = False
comment.content = u'Ný útgáfa rippuð'
return self.currentversion
def get_version(self, versionno):
return self.versions[versionno-1]
def _get_status(self):
return self._status
def _set_status(self, newstatus):
if self._status is
[sqlalchemy] 0.3 to 0.4 migration problem
hi all. I've been using sqlalchemy in one of may projects. I
followed the steps in http://www.sqlalchemy.org/trac/wiki/WhatsNewIn04,
but the I got this backtrace:
Traceback (most recent call last):
File ./kress.py, line 664, in ?
main(sys.argv)
File ./kress.py, line 658, in main
mainWindow= Kress (app)
File ./kress.py, line 92, in __init__
self.fromDatabase ()
File ./kress.py, line 109, in fromDatabase
for post in self.model.posts (self.index, self.showPosts, self.activeFeeds,
self.filteringTags, self.textFilter):
File
/home/mdione/src/projects/kreissy/src/branches/multi-feed-tag/src/kress/model/kressdata.py,
line 96, in posts
p= q.all ()
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 571, in
all
return list(self)
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 615, in
__iter__
context = self._compile_context()
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 864, in
_compile_context
statement.append_order_by(*sql_util.ClauseAdapter(s3).copy_and_process(order_by))
File /usr/lib/python2.4/site-packages/sqlalchemy/sql/util.py, line 232, in
copy_and_process
self.process_list(list_)
File /usr/lib/python2.4/site-packages/sqlalchemy/sql/util.py, line 243, in
process_list
list_[i] = self.traverse(list_[i], clone=True)
File /usr/lib/python2.4/site-packages/sqlalchemy/sql/visitors.py, line 56,
in traverse
for c in t.get_children(**self.__traverse_options__):
File /usr/lib/python2.4/site-packages/sqlalchemy/sql/expression.py, line
1858, in get_children
return self.bindparams.values()
AttributeError: 'list' object has no attribute 'values'
the relevant code near that Query.all() is:
def posts (self, index=0, count=None, feeds=None, tags=None, search=None):
# apply filters
constraint= Post.c.state!='deleted'
# this two chunks just adds constraints by or'ing feed and tag names.
if len (feeds)0:
# or'ing feeds
# by cases
feedName= feeds[0]
if len (feeds)==1:
constraint= constraint (Feed.c.name==feedName)
self.query.join_to ('feed')
elif len (feeds)1:
temp= (Feed.c.name==feedName) self.query.join_to ('feed')
for feedName in feeds[1:]:
temp= temp | (Feed.c.name==feedName) self.query.join_to
('feed')
constraint= constraint temp
if len (tags)0:
tagName= tags[0]
if len (tags)==1:
constraint= constraint (Tag.c.name==tagName)
self.query.join_to ('tags')
elif len (tags)1:
temp= (Tag.c.name==tagName) self.query.join_to ('tags')
for tagName in tags[1:]:
temp= temp | (Tag.c.name==tagName) self.query.join_to
('tags')
constraint= constraint temp
if search is not None:
constraint= constraint (Post.c.title.like ('%'+search+'%'))
q= self.query.offset (index)
if count is not None:
q= q.limit (count)
q= q.order_by (sqlalchemy.desc ('date'))
q= q.filter (constraint)
print self.query.compile () ---
p= q.all ()
return p
that print hightlighted there gives:
SELECT
tag_1.id AS tag_1_id,
tag_1.name AS tag_1_name,
post.id AS post_id,
post.guid AS post_guid,
post.feed_id AS post_feed_id,
post.title AS post_title,
post.content AS post_content,
post.date AS post_date,
post.state AS post_state
FROM
post
LEFT OUTER JOIN post_tag AS post_tag_2 ON
post.id = post_tag_2.post_id
LEFT OUTER JOIN tag AS tag_1 ON
tag_1.id = post_tag_2.tag_id
ORDER BY
post.oid, post_tag_2.oid
I will try to minimize the example, but I wanted you opinion in the
meanwhile.
--~--~-~--~~~---~--~~
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] Looking for feedback on encapsulating SA logic (newbie)
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()
--~--~-~--~~~---~--~~
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: 0.3 to 0.4 migration problem
On Oct 28, 2007, at 12:23 PM, Marcos Dione wrote:
q= self.query.offset (index)
if count is not None:
q= q.limit (count)
q= q.order_by (sqlalchemy.desc ('date'))
q= q.filter (constraint)
print self.query.compile () ---
p= q.all ()
return p
this is a small bug fixed in r3678, for a workaround dont use literal
strings for the order_by() expression above; it has to do with
translation of text() fields (such as 'date' above).
--~--~-~--~~~---~--~~
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
Next design problem for me is version table. I have Document model with DocumentVersion model, but i dont know how to: - get the latest version of document - set creator and updator, automatic behavior for this - update version number - fetch thru Document(s) and DocumentVersion(s) just to warn you, if u're trying to have a versioned document, i.e. document with history of changes/versions, and track them in time, that's a rather complicated thing. see bitemporal mixin recipe in dbcook: https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/misc/timed2/ if u don't realy care about the history, but only need the last one, that might be easier, YMMV. automatic setup of fields in an object, e.g. in your case creator/modifier of document, might be done at several places/times: - object's constructor - just before saving the object to DB - mapperEextension.befor_insert and friends - maybe other places to hook between these two but u'll need a context-like state to keep track of the current user (or time or whatever). or, u can do it by hand somewhere at proper place within your workflow, around saving the object. Beware that either way it must be done in a way that does not change/affect objects which have not been really modified - else all objects will be always saved/updated, over and over. --~--~-~--~~~---~--~~ 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] post_processors error during 0.3.10 to 0.4 migration
I've been trying to migrate my code to to 0.4 and I'm getting stuck on
this error. I haven't been able to narrow down what property of my
schema or code triggers this, but I thought I'd ask the group in case
there was an easy answer.
Here Thing is a class that is mapped to a table with a single column.
It has a relation to an attribute table with (thing_id, key, value)
columns. I have a subclass of Thing called Server, that instead of
mapping directly to the table maps to a select on the thing table
where the thing has certain attributes from the attribute table. If I
create a Server then add attributes to it then flush the data I get no
errors. But if I try to query for a Server to which I tried to add
attributes I get the attached error. Adding attributes straight to
Things or querying for Servers that I didn't add attributes to does
not produce the error.
Not sure if any of that was clear, but it's a start. Any ideas?
-Ron
return Thing.query.filter(Thing.c.name == name).one()
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.4.0-
py2.4.egg/sqlalchemy/orm/query.py, line 605, in one
ret = list(self[0:2])
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.4.0-
py2.4.egg/sqlalchemy/orm/query.py, line 619, in __iter__
return self._execute_and_instances(context)
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.4.0-
py2.4.egg/sqlalchemy/orm/query.py, line 624, in
_execute_and_instances
return iter(self.instances(result, querycontext=querycontext))
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.4.0-
py2.4.egg/sqlalchemy/orm/query.py, line 685, in instances
context.attributes.get(('populating_mapper', instance),
object_mapper(instance))._post_instance(context, instance)
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.4.0-
py2.4.egg/sqlalchemy/orm/mapper.py, line 1534, in _post_instance
post_processors = selectcontext.attributes[('post_processors',
self, None)]
KeyError: ('post_processors', sqlalchemy.orm.mapper.Mapper object at
0xb78a18cc, None)
--~--~-~--~~~---~--~~
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: Mapper bug involving select with labeled foreign key target and clause default?
Thanks for the response Mike... comments below. On Oct 28, 12:52 am, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 27, 2007, at 8:33 PM, Brian Beck wrote: without trying it yet the first thing that seems wrong is that your select has no join condition between a_table and b_table (nor are you using a join(), which would figure it out for you based on foreign keys, so your foreign key doesn't impact the equation much here on the select side). the select will return the cartesian product between a and b which is definitely not what you want. Doesn't matter -- same thing happens with or without the join condition. (The cartesian product was intentional.) the actual error seems that the b_id column is tripping off a refresh of the instance's row, but when it issues your select(), its still not getting back what it wants. since the mapper seems confused by the primary key of the select (which is likely because you have a_id represented twice), you might want to look into setting up your 'the_id_of_a' property at the mapper level as a synonym() or column_property() attribute. I dont see what good it does embedded into that select(). or, try explicitly setting the mapper's primary_key attribute to the desired columns from your select(). Pretend the Select is more complicated and needs to reference both IDs (which may have come from subqueries) -- in this case any mapper features (like synonym) don't help, the Select needs to exist first! Isn't it an error that b_id trips off a refresh? The correct values are inserted and should be available in last_inserted_ids for the mapper to populate instance with... --~--~-~--~~~---~--~~ 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 there were some good ideas to try. Btw. what does the first def
constructor(fun)?
On 28 loka, 18:00, Arnar Birgisson [EMAIL PROTECTED] wrote:
Hi there,
On 10/28/07, mmstud [EMAIL PROTECTED] wrote:
Next design problem for me is version table. I have Document model
with DocumentVersion model, but i dont know how to:
- get the latest version of document
- set creator and updator, automatic behavior for this
- update version number
- fetch thru Document(s) and DocumentVersion(s)
I didn't read your code thoroughly, but I have a model with some
similarities. Perhaps it will provide some insight. Basically, I'm
dealing with Pages and PageVersions. PageVersions refers to it's
parent Page, but Page also keeps the version number of the latest
version.
Arnar
# encoding: utf-8
import os
from datetime import datetime
from sqlalchemy import *
from softproof import utils
from softproof.json import jsonify_saobject
__meta__ = metadata
def constructor(fun):
def decorated(self, *args, **kw):
assert hasattr(self, 'c')
for key,value in kw.items():
if hasattr(self.c, key):
setattr(self, key, value)
del kw[key]
fun(self, *args, **kw)
return decorated
jobs = Table(jobs, __meta__,
Column(jobno, Unicode(15), primary_key=True),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(deleted, Boolean, nullable=False, default=False))
class Job(object):
@constructor
def __init__(self, jobno=None):
if jobno:
self.jobno = jobno
def sortedpages(self):
listcopy = self.pages[:]
listcopy.sort(key=Page.sort_key)
return listcopy
def get_page_by_name(self, pagename):
Finnur síðu með nafnið pagename og skilar henni. Skilar
None ef engin síða hefur
viðkomandi nafn.
Ef pagename er _firstpage_ er skilað viðeigandi síðu (t.d.
kápu ef hún er til)
if len(self.pages) == 0:
return None
if '_firstpage_' == pagename:
for p in self.pages:
if 'KAP' in p.pagename:
return p
return self.pages[0]
for p in self.pages:
if p.pagename == pagename:
return p
return None
def create_page(self, pagename, *args, **kwargs):
p = Page(job=self, pagename=pagename, *args, **kwargs)
return p
def get_path(self):
if self.jobno.startswith('P'):
pg1, pg2, pg3 = self.jobno.split('.')
return os.path.join(pg1, pg1+'.'+pg2, self.jobno)
else:
return os.path.join(self.jobno[:-3]+'000',
self.jobno[:-2]+'00', self.jobno)
mapper(Job, jobs)
pageversions = Table(pageversions, __meta__,
Column(jobno, Unicode(15),
ForeignKey(pages.jobno), primary_key=True),
Column(pagename, Unicode(30),
ForeignKey(pages.pagename), primary_key=True),
Column(version, Integer, primary_key=True, default=1),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(md5sum, String(32)),
Column(width, Integer, nullable=False, default=0),
Column(height, Integer, nullable=False, default=0),
ForeignKeyConstraint([jobno,
pagename],[pages.jobno, pages.pagename]))
class PageVersion(object):
@constructor
def __init__(self, page=None, version=None):
if page:
self.page = page
if version:
self.version = version
@property
def filename(self):
if self.version == 1:
return self.page.pagename + '.jpg'
else:
return %s.v%02d.jpg % (self.page.pagename, self.version)
mapper(PageVersion, pageversions)
PageStates = utils.Enum('new', 'approved', 'rejected')
pages = Table(pages, __meta__,
Column(jobno, Unicode(15), ForeignKey(jobs.jobno),
primary_key=True),
Column(pagename, Unicode(30), primary_key=True),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(deleted, Boolean, nullable=False, default=False),
Column(current_version, Integer),
Column(status, PageStates, nullable=False,
default=PageStates.new))
class Page(object):
@constructor
def __init__(self, job=None, pagename=None):
if job:
self.job = job
if pagename:
self.pagename = pagename
self.currentversion = PageVersion(self, 1)
self.status = PageStates.new
def add_version(self):
self.currentversion = PageVersion(self, self.currentversion.version+1)
self.status = PageStates.new
comment = self.add_comment()
comment.closeable = False
[sqlalchemy] Re: post_processors error during 0.3.10 to 0.4 migration
On Oct 28, 2007, at 3:39 PM, Ron wrote: I've been trying to migrate my code to to 0.4 and I'm getting stuck on this error. I haven't been able to narrow down what property of my schema or code triggers this, but I thought I'd ask the group in case there was an easy answer. Here Thing is a class that is mapped to a table with a single column. It has a relation to an attribute table with (thing_id, key, value) columns. I have a subclass of Thing called Server, that instead of mapping directly to the table maps to a select on the thing table where the thing has certain attributes from the attribute table. If I create a Server then add attributes to it then flush the data I get no errors. But if I try to query for a Server to which I tried to add attributes I get the attached error. Adding attributes straight to Things or querying for Servers that I didn't add attributes to does not produce the error. Not sure if any of that was clear, but it's a start. Any ideas? youd have to attach your full table setup and mappings to have any idea how this error is occuring. id probably classify this as a bug since if your mapping has something SA can't handle, it should be raising a specific error at compile time instaed of randomly failing at query time. --~--~-~--~~~---~--~~ 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: Mapper bug involving select with labeled foreign key target and clause default?
On Oct 28, 2007, at 4:32 PM, Brian Beck wrote:
Pretend the Select is more complicated and needs to reference both IDs
(which may have come from subqueries) -- in this case any mapper
features (like synonym) don't help, the Select needs to exist first!
the select can reference whatever columns it wishes in its WHERE
clause, ORDER BY clause, whereever, *without* them being in the
columns clause...your columns clause need not reference any columns
whatsoever and it can still locate the correct rows. so you should
be putting only unique columns in the columns clause of your
SELECT.or, if youd like to tell your mapper about both columns
being the same, set up the mapping like this:
ab_mapper = mapper(AB, s, properties={
'id':[s.c.a_id, s.c.the_id_of_a]
})
then the class has a single 'id' attribute mapped to both columns.
Isn't it an error that b_id trips off a refresh? The correct values
are inserted and should be available in last_inserted_ids for the
mapper to populate instance with...
this is a slight issue in that its seeing the inline SQL as a
postfetch trigger when its not, so that is fixed in r3679.
--~--~-~--~~~---~--~~
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
On 10/28/07, mmstud [EMAIL PROTECTED] wrote: Thanks there were some good ideas to try. Btw. what does the first def constructor(fun)? It is a decorator I use on mapped classes constructors. It allows me to give keyword arguments to constructors with initial values for any field in class.c (i.e. any mapped columns). cheers, Arnar --~--~-~--~~~---~--~~ 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)
Ok, I've figured out the problem but not really sure what the proper
solution is.
Basically, I have Thing objects that can have attributes associated
with them. I have other classes that are subclasses of the Thing
object. These classes can provide more specific functionality based
on the type of Thing it is. Since Thing and it's subclasses all share
the same table, I need a way to get the correct class based on what
type of Thing it is. I do this by examining the Attributes associated
with a thing. The different subclasses of Thing match different
attributes. In 0.3 I did this by called an instance._setProperClass()
function in the populate_instance method of a MapperExtension. This
seems to make 0.4 angry. If I call the same _setProperClass() after I
get the object normally everything seems to work fine.
I've attached a simplified version of what I do in my code to
illustrate the problem.
What I did was kind of a hack in 0.3 so I'm not that surprised that it
doesn't work in 0.4, but I'm not sure how else to achieve the
functionality I'm looking for. Is there a better way to allow for
sqlalchemy to return objects of different types based on the data they
happen to contain?
-Ron
#!/usr/bin/env python
from sqlalchemy import *
from sqlalchemy.ext.sessioncontext import SessionContext
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.orm import * #Mapper, MapperExtension
from sqlalchemy.orm.mapper import Mapper
#from clusto.sqlalchemyhelpers import ClustoMapperExtension
import sys
# session context
METADATA = MetaData()
SESSION = scoped_session(sessionmaker(autoflush=True,
transactional=True))
THING_TABLE = Table('things', METADATA,
Column('name', String(128), primary_key=True),
#Column('thingtype', String(128)),
mysql_engine='InnoDB'
)
ATTR_TABLE = Table('thing_attrs', METADATA,
Column('attr_id', Integer, primary_key=True),
Column('thing_name', String(128),
ForeignKey('things.name',
ondelete=CASCADE,
onupdate=CASCADE)),
Column('key', String(1024)),
Column('value', String),
mysql_engine='InnoDB'
)
class CustomMapperExtension(MapperExtension):
def populate_instance(self, mapper, selectcontext, row, instance,
**flags):
Mapper.populate_instance(mapper, selectcontext, instance, row,
**flags)
## Causes problems if run here!
instance._setProperClass()
return EXT_CONTINUE
class Attribute(object):
Attribute class holds key/value pair backed by DB
def __init__(self, key, value, thing_name=None):
self.key = key
self.value = value
if thing_name:
self.thing_name = thing_name
def __repr__(self):
return thingname: %s, keyname: %s, value: %s %
(self.thing_name,
self.key,
self.value)
def delete(self):
SESSION.delete(self)
SESSION.mapper(Attribute, ATTR_TABLE)
DRIVERLIST = {}
class Thing(object):
Anything
someattrs = (('klass', 'server'),)
def __init__(self, name, *args, **kwargs):
self.name = name
for attr in self.someattrs:
self.addAttr(*attr)
def _setProperClass(self):
Set the class for the proper object to the best suited driver
if self.hasAttr('klass'):
klass = self.getAttr('klass')
self.__class__ = DRIVERLIST[klass]
def getAttr(self, key, justone=True):
returns the first value of a given key.
if justone is False then return all values for the given key.
attrlist = filter(lambda x: x.key == key, self._attrs)
if not attrlist:
raise KeyError(key)
return justone and attrlist[0].value or [a.value for a in
attrlist]
def hasAttr(self, key, value=None):
if value:
attrlist = filter(lambda x: x.key == key and x.value ==
value, self._attrs)
else:
attrlist = filter(lambda x: x.key == key, self._attrs)
return attrlist and True or False
def addAttr(self, key, value):
Add an attribute (key/value pair) to this Thing.
Attribute keys can have multiple values.
self._attrs.append(Attribute(key, value))
SESSION.mapper(Thing, THING_TABLE,
properties={'_attrs' : relation(Attribute, lazy=False,
cascade='all, delete-
orphan',),
},
extension=CustomMapperExtension())
DRIVERLIST['thing'] = Thing
class Server(Thing):
someattrs = (('klass', 'server'),)
pass
DRIVERLIST['server'] = Server
[sqlalchemy] Re: post_processors error during 0.3.10 to 0.4 migration (returning different object type based on db data)
On Oct 28, 2007, at 6:58 PM, Ron wrote: Ok, I've figured out the problem but not really sure what the proper solution is. Basically, I have Thing objects that can have attributes associated with them. I have other classes that are subclasses of the Thing object. These classes can provide more specific functionality based on the type of Thing it is. Since Thing and it's subclasses all share the same table, I need a way to get the correct class based on what type of Thing it is. I do this by examining the Attributes associated with a thing. The different subclasses of Thing match different attributes. In 0.3 I did this by called an instance._setProperClass() function in the populate_instance method of a MapperExtension. This seems to make 0.4 angry. If I call the same _setProperClass() after I get the object normally everything seems to work fine. I've attached a simplified version of what I do in my code to illustrate the problem. What I did was kind of a hack in 0.3 so I'm not that surprised that it doesn't work in 0.4, but I'm not sure how else to achieve the functionality I'm looking for. Is there a better way to allow for sqlalchemy to return objects of different types based on the data they happen to contain? OK, there was an original intended way for this to happen if via MapperExtension, youd do it in create_instance() - just return whatever type of object you want. however, from looking at the error youre getting, this is actually not going to fix the problem here. the class of object determines which mapper is used to populate its attributes. the populate step and the new-in-0.4 post-populate step are not communicating here because the official mapper for your instance changes midway (its based on class). while I can make the post-populate step ignore the mis-communication and just not fire off, or i can change how those two steps communicate, the fact remains that the *wrong* mapper populates your class (including in your 0.3 version)...so im not sure if its the right approach to support wrongish behavior like that. the official way to have the class and populating mapper selected based on attributes is using polymorphic inheritance. if your classes are all in an inheritance hierarchy, and theres a single attribute that can determine which is the right class, you can use that out of the box. it seems like your model could conform to that since its a single type attribute determining the class. I'd suggest trying to work with that model (single table inheritance). Otherwise ill probably have to add another MapperExtension hook to support this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: post_processors error during 0.3.10 to 0.4 migration (returning different object type based on db data)
So, the code I posted is a much simplified version of what I'm trying
to accomplish only used to illustrate the error I was getting. What I
actually want to do is select the appropriate class based on any
number of Attributes a Thing might have. I have a metaclass that is
applied to Thing and all it's subclasses. This metaclass does the
actual call to mapper, creates the select query to map against for the
various subclasses, and builds a DRIVERLIST dictionary with data that
can be used by setProperClass. In other words, the type of a given
Thing is determined by it's attributes at runtime, not when the Thing
is created.
I didn't run into any functional problems doing it this way in 0.3 so
I'm not sure what you mean by wrong mapper (I used assign_mapper if
that makes any difference). The reason I did the setProperClass at
the end of the populate_instance function is because I wanted to make
use of the attrs that the mapper would populate. That seemed like the
easiest way to accomplish the goal at the time.
I've read the Mapping Class Inheritance Hierarchies section in the
documentation and it looks like they won't quite do what I'm trying to
accomplish. Maybe if I explained my app architecture a little more
you could clarify the solution a bit (sorry, this ended up being more
verbose than I intended):
I have a datastore that consists of 3 tables.
1. Thing table (just a primary-key name column)
2. Attr table (key/value columns with an id and foreign key to Thing
table)
3. Thing-to-Thing relation table (Things can be 'connected' to each
other)
The idea for that schema is to maximize the flexibility of what one
can store. In this vein I created a Thing class. This class has many
methods for managing attributes, connections between Things,
searching, matching, clever __init__, __str__, __eq__, etc. The
design is such that subclasses of Thing only need to set class
variables to achieve certain functionality. For example, there is a
meta_attrs list that will pre-fill the attributes for an object and
there is also a required_attrs var that will let you define required
arguments to init. My goal was to make sublcasses or 'drivers' as
simple as possible.
To expand on the Server example from my testcode, say I had this
class:
class Server(Thing):
meta_attrs = [('type', 'server')]
def ssh(self):
# start an ssh session to this server
somemagic()
People use that class and do things like:
someserver.addAttr('manufacturer', 'sun')
adding lots of data to the db. Then later someone decides that sun
servers have some special functionality that should be exposed, say cd
ejection. They create a new class:
class SunServer(Server):
meta_attrs = [('manufacturer', 'sun')]
def ejectCD(self):
# eject the cd
It should be that easy. Now, some things I didn't mention earlier.
The all meta_attrs of all parent classes also get applied to new
objects. So any s=SunServer() will have both ('type', 'server') and
('manufacturer', 'sun') attributes. Also, now that I have this new
SunServer class any time I select something from the database that
matches all its meta_attrs it should return a SunServer object where
it used to return a regular Server object.
ex.
t1 = Thing.query.filter(Thing.c.name == 'someOldSunServer')
isinstance(t1, SunServer) == True
This is without updating the database, or making any other change
aside from adding that new SunServer class. So, where does sqlalchemy
fit into all this? People developing drivers shouldn't ever have to
know about SA (they, of course, could make use of it if they want).
The Thing class has a __metaclass__ that takes care of all the SA
magic so every subclass of Thing is taken care of. However, I'm not
sure how to get the polymorfic stuff in the SA mapper to match against
arbitrary attributes of classes (specifically those named in the
meta_attrs). Should I not rely on SA to return any specific type at
all (just always return Thing) and make my own query/select functions
that call _setProperClass on their own?
I'm trying to take advantage of as much of the SA magic as possible,
but I'm unsure when I am going beyond its scope and some of the more
advanced topics are not quite documented enough for me to fully
understand how to use them.
Thanks for the help,
-Ron
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
