[sqlalchemy] Re: M:N self-reference
Something exactly like that. Thanks much.
On Nov 8, 11:28 pm, Mike Conley mconl...@gmail.com wrote:
Something like this? The association table is declared in the relationships,
but never referenced when creating or accessing objects.
class Assoc(Base):
__tablename__ = 'assoc'
parent = Column(Integer, ForeignKey('m_to_n.id'), primary_key=True)
child = Column(Integer, ForeignKey('m_to_n.id'), primary_key=True)
class MToN(Base):
__tablename__ = 'm_to_n'
id = Column(Integer, primary_key=True)
name = Column(String)
children = relation('MToN', secondary=Assoc.__table__,
primaryjoin='MToN.id==Assoc.parent',
secondaryjoin='MToN.id==Assoc.child',
backref=backref('parents')
)
def __repr__(self):
return M:N %s %s % (self.id, self.name)
metadata.create_all()
compile_mappers()
p1 = MToN(name='P1')
p2 = MToN(name='P2')
p3 = MToN(name='P3')
c1 = MToN(name='C1')
c1a = MToN(name='C1A')
c2 = MToN(name='C2')
c3 = MToN(name='C3')
p1.children.append(c1)
p1.children.append(c1a)
c1.children.append(c2)
p2.children.append(c1)
c3.parents.append(p1)
c3.parents.append(p3)
session.add_all([p1, p2, p3])
session.commit()
engine.echo=False
qry_p = session.query(MToN).filter(MToN.name.like('P%'))
for p in qry_p:
print '=='
print p
for ch1 in p.children:
print ' ', ch1
for ch2 in ch1.children:
print ' ',ch2
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] what time sqlalchemy 0.6 out?
RT, i'm using it now, i want it's out when i product is online. -- my.unix-center.net/~WeiZhicheng --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] insertion of an object with a user defined field value
Hi,
I have the following 2 declarative objects to represent a book and its
pages:
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(32))
class Page(Base):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
book_id = Column(Integer, ForeignKey('books.id'), index=True)
content = Column(String(1000))
book = relation(Book, backref='pages', order_by=id)
def __init__(self, page_no, content, book_id=None):
self.id = page_no
self.content = content
if book_id is not None: self.book_id = book_id
I would like to read and insert info for books in a library (i.e. by
calling session.add(Book(My Book)) a vast number of times), keep
their database assigned ids in a dictionary and then insert info for
pages of these books (i.e. by calling session.add(Page(1, Hello my
book!, 1)) without requiring to fetch book object from database and
adding the page to it. However, the book_id field in pages table is
inserted as NULL instead of the value assigned in the constructor. Is
there a way to achieve this behavior? The aim of such kind of approach
is to avoid keeping book objects in the memory or retrieving those
from database during in the initial creation of the database.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insertion of an object with a user defined field value
The id is generate by the database engine, not SQLAlchemy, so session.add()
does nothing to push your object to the database and generate the id. You
need to execute session.flush() after session.add() to write the book to the
database and generate the id. After the flush() operation, the book id is
available to save in your dictionary.
something like this:
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
Without the flush(), the id will be NULL.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insertion of an object with a user defined field value
Thanks for the quick response. However, the problem I face is not
being able to access the id assigned by database but not being able to
modify the corresponding field in Page instance. To be more clear:
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
for i, content in enumerate(('Once upon a time there was a little
fellow called Tom.', 'His surname was Sawyer.')):
page = Page(i, content, bk_ids['Tom Sawyer'])
session.add(page)
session.commit()
When I check the database, book_id field in pages table is not
modified as it supposed (or at least I suppose) to be.
On Nov 9, 3:12 pm, Mike Conley mconl...@gmail.com wrote:
The id is generate by the database engine, not SQLAlchemy, so session.add()
does nothing to push your object to the database and generate the id. You
need to execute session.flush() after session.add() to write the book to the
database and generate the id. After the flush() operation, the book id is
available to save in your dictionary.
something like this:
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
Without the flush(), the id will be NULL.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insertion of an object with a user defined field value
Using your class definitions, it seems to work. What is different?
Base.metadata.bind=create_engine('sqlite:///')
Base.metadata.create_all()
session=sessionmaker()()
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
print bk_ids
for i, content in enumerate((
'Once upon a time there was a little fellow called Tom.',
'His surname was Sawyer.')):
page = Page(i, content, bk_ids['Tom Sawyer'])
session.add(page)
session.commit()
for page in session.query(Page):
print 'page:',page.id,' book:',page.book_id
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: little trouble remote_side when local col points to same col in parent
Actually I hadn't realized that the problem only occurred on eagerloading.
Would it make sense to be able to do an alias at the table level? In
other words:
task_parent=aliased(task_table)
mapper(Task,task_table, properties={
'Children' : relation(Task, backref=backref('Parent',
primaryjoin=(and_(task_parent.c.asset==task_table.c.parent_asset,
task_parent.c.name==task_table.c.name)),
remote_side=[task_parent.c.asset,
task_parent.c.name]),
order_by=task_table.c.asset,cascade='all',
lazy=True)
})
Michael Bayer wrote:
Theres a test case like this which had a behavioral change as of
0.5.5, but looking at that, eager loading doesn't come into the picture.
for that particular test, we add foreign_keys=[task.c.parent_asset] to
the many to one side, and foreign_keys=[None] to the one-to-many
side. You might need that here just in general.
But for eagerloading I actually don't think we have a solution for
that right now. the task_1 comes into the ON clause by way of
clause adaption, which has a list of columns that it wants to
adapt. So task.name is either in or not in the list.
I'm thinking of a completely bizarre hack which would be to add a
Column to the table with the same name as name, but a different key,
then setting up primaryjoin using that. But I don't know if that
would do it.
Otherwise you might just take the easy route and say:
t2 = aliased(Task)
s.query(Task).join(t2, and_(Task.parent_asset==t2.asset,
Task.name==t2.name)).options(contains_eager(parent , alias=t2))
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insertion of an object with a user defined field value
It does indeed! It is my bad, it works as expected, the problem was
due to not flushing properly before storing the values in the
dictionary.
Sorry for the inconvenience and thank you for the support.
On Nov 9, 4:46 pm, Mike Conley mconl...@gmail.com wrote:
Using your class definitions, it seems to work. What is different?
Base.metadata.bind=create_engine('sqlite:///')
Base.metadata.create_all()
session=sessionmaker()()
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
print bk_ids
for i, content in enumerate((
'Once upon a time there was a little fellow called Tom.',
'His surname was Sawyer.')):
page = Page(i, content, bk_ids['Tom Sawyer'])
session.add(page)
session.commit()
for page in session.query(Page):
print 'page:',page.id,' book:',page.book_id
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] using having
how can i use having? example: targets = meta.Session.query(ObjetoCusto.objeto_custo_id, ObjetoCusto.descricao).\ join(TipoObjeto).\ join(EtapaObjeto).\ filter(EtapaObjeto.ano_id == c.year).\ distinct().having(func.count(ObjetoCusto.mes_id) == 12).order_by(ObjetoCusto.descricao).all() i need to return where count(mes_id) = 12 --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: little trouble remote_side when local col points to same col in parent
On Nov 9, 2009, at 1:09 PM, David Gardner wrote:
Actually I hadn't realized that the problem only occurred on
eagerloading.
Would it make sense to be able to do an alias at the table level? In
other words:
task_parent=aliased(task_table)
mapper(Task,task_table, properties={
'Children' : relation(Task, backref=backref('Parent',
primaryjoin=(and_(task_parent.c.asset==task_table.c.parent_asset,
task_parent.c.name==task_table.c.name)),
remote_side=[task_parent.c.asset,
task_parent.c.name]),
order_by=task_table.c.asset,cascade='all',
lazy=True)
})
that wouldn't work for a variety of reasons. aliased() doesn't work
at that level. you'd at least want to use a table alias, i.e.
task_table.alias() - but then you'd need to map the class twice, and
then you're dealing with something totally different.
there's a way to make this work which I'll eventually get to in ticket
1612.
Michael Bayer wrote:
Theres a test case like this which had a behavioral change as of
0.5.5, but looking at that, eager loading doesn't come into the
picture.
for that particular test, we add foreign_keys=[task.c.parent_asset]
to
the many to one side, and foreign_keys=[None] to the one-to-many
side. You might need that here just in general.
But for eagerloading I actually don't think we have a solution for
that right now. the task_1 comes into the ON clause by way of
clause adaption, which has a list of columns that it wants to
adapt. So task.name is either in or not in the list.
I'm thinking of a completely bizarre hack which would be to add a
Column to the table with the same name as name, but a different
key,
then setting up primaryjoin using that. But I don't know if that
would do it.
Otherwise you might just take the easy route and say:
t2 = aliased(Task)
s.query(Task).join(t2, and_(Task.parent_asset==t2.asset,
Task.name==t2.name)).options(contains_eager(parent , alias=t2))
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: little trouble remote_side when local col points to same col in parent
Thanks for looking into this, just as an fyi this isn't effecting any production code for me, I still have the option of implementing parent/children using an integer id's column. Michael Bayer wrote: at that level. you'd at least want to use a table alias, i.e. task_table.alias() - but then you'd need to map the class twice, and then you're dealing with something totally different. there's a way to make this work which I'll eventually get to in ticket 1612. -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what time sqlalchemy 0.6 out?
we are looking at feburary at the latest, hopefully. On Nov 9, 2009, at 5:14 AM, 诚子 wrote: RT, i'm using it now, i want it's out when i product is online. -- my.unix-center.net/~WeiZhicheng --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Server / Client-gtk app
On Nov 8, 2009, at 5:06 PM, M3nt0r3 wrote:
Hi , i am sorry for my english, i am trying ti make a Server/Client
app. Until now client-gtk mapped the db and spoked directly with the
db ( sqlite and postgresql atm) . I wrote a small wsgi server based
on werkzeug, and it basically make the same thing of the client-gtk
but in pure web style. If received a request it render html with
template engine and response the html-code.
Now with client-gtk i am trying to use something like:
def getRecord(self,id=None):
Restituisce un record ( necessita di un ID )
if id:
params = {}
params[dao_class] = self.DaoModule.__module__
params[dao_name] = self.DaoModule.__name__
params[filter] = id
params[method] = getRecord
paramss = urllib.urlencode(params)
f = urllib.urlopen(http://localhost:8080/gtk;, paramss)
dati_uncompress = zlib.decompress(f.read())
dati= loads(dati_uncompress)
return dati
It is for get.
Server side this is the function to handler:
def gtk_(req, static=None, SUB=None, subdomain=None):
values = req.form.to_dict()
dao_name = values[dao_name]
dao_class = values[dao_class]
method = values[method]
filtri = values[filter]
exec from %s import %s %(dao_class.replace(promogest, core),
dao_name)
if method ==select or method ==getRecord:
exec d = %s().%s(filtri=%s) %(dao_name,method, filtri)
dumped = dumps(d)
if method==nuovo:
exec d = %s %(dao_name)
dumped = dumps(d)
compressed = zlib.compress(dumped)
return Response(compressed)
exec create something like: User().getRecord(filter=22)
For example for user table:
class User(Dao):
User class provides to make a Users dao
def __init__(self, req=None,arg=None):
Dao.__init__(self, entity=self)
std_mapper = mapper(User,user, properties={
per_giu :relation(PersonaGiuridica_, backref='cliente_'),
}, order_by=user.c.username)
It works until i need to work with a new istance:
i don't know how to have an istance in the client , to build there or
have it from a response from the server.
if I understand correctly aren't you looking to pass over just the
components of the User as a string, and have the exec do a User
(*args) on the server side ? That would be consistent with your
approach of strings sent from the client being interpreted as model/
ORM code on the server.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
