RE: [sqlalchemy] update existing row
vitsin wrote:
hi,
can't figure out why raw SQL works fine, but update() is not working:
1.working raw SQL:
self.session.execute(update public.my_table set
status='L',updated_at=now() where my_name='%s' % (self.my_name))
2.non working update() from Alchemy:
s = aliased(MyTable)
query = self.session.query(s).filter(s.my_name==self.my_name)
sts = self.session.execute(query).fetchone()
sts.update(values={'status':'L'})
sts.update(values={s.status:'L'})
File /usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py,
line 2097, in _key_fallback
Could not locate column in row for column '%s' % key)
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'update'
But Column s.status exists ...
appreciate any help,
--vs
In your example, 'sts' represents a single row from the database. These
objects don't have an 'update' method, which is why you are getting that
error. It thinks you are trying to access a column called 'update'
instead.
You appear to be using the SQL Expression language (ie. MyTable is
created using sqlalchemy.Table). You can create an 'update' statement
using MyTable.update(). Examples are at:
http://www.sqlalchemy.org/docs/core/tutorial.html#inserts-and-updates
(You should be able to substitute conn.execute() with session.execute())
However, you might be interested in using the ORM part of SQLAlchemy:
http://www.sqlalchemy.org/docs/orm/tutorial.html
Your usage would then look something like this (assuming MyMappedClass
is the class mapped to MyTable):
s = MyMappedClass
query = self.session.query(s).filter(s.my_name == self.my_name)
sts = query.first()
sts.status = 'L'
self.session.flush()
Hope that helps,
Simon
--
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] Updating user interface after rollback (using after_rollback SessionExtension?)
Hello world, I am tearing my hair about my SQLAlchemy usage in a GUI application, especially to get it error tolerant. Usually the GUI filters user inputs before they are thrown at SQLAlchemy to store it into the database. If that fails, however, it can happen that data is thrown at the database that leads to a rollback because of e.g. violated foreign key constraints. If that happens, the session rolls back (fine) but the GUI still shows the state that I tried to write into the database. Being MVC, I would need all database object to fire a changed event so they are pulled fresh from the database. I tried using the after_commit extension (SQLAlchemy 0.6.8) to do this. Paraphrased, this works like this: def after_rollback(self, session): for instance in session: instance.signal_changed() This works fine if the transaction being rolled back is the toplevel transaction. However, if that is a nested transaction, this destroys my session state (as the listeners try to load from the database): InvalidRequestError: This Session's transaction has been rolled back by a nested rollback() call. To begin a new transaction, issue Session.rollback() first. So I would need the callback only after the rollback of the toplevel session occurred. I tried looking at the _is_transaction_boundary property of session.transaction: def after_rollback(self, session): if session.transaction._is_transaction_boundary: for instance in session: instance.signal_changed() This stops the exceptions, but it also never comes to signal_changed if subtransactions are rolled back. It looks like I only get a single event for the innermost transaction?! What would be a sane way to implement this? I also looked at the MapperExtension in the hope that there is a callback whenever an instance is expired, but does not seem to be such a thing. Hints welcome, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Relationship spanning on multiple tables
I'm trying to get some relationships spanning on multiple tables (4 or 5). While I got the `4 tables` one working on first attempt (I was surpized I could...) I can't get the `5 tables` one to work while the code is almost the same. Moreover with the first relationship if I add adding `viewonly=True` initialization fails. I just need these to get (not even set) some values with convenience of SA attributes. I published a working example on pastebin: http://pastebin.com/RsZ6GCRq I hope someone can sort out this thing, thank you. Greetings neurino -- 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] Building hierarchy tree in reverse
Hi. I have a problem and am not sure where to begin. I need to construct a hierarchy tree, something like adjacency_list but in reverse. More precisely, I need entire branch but only the branch containing given node ID. In practice, I need this for a product category tree menu which shows items in the currently selected branch only (where for example I don't need children of bba, or ba, or A because node ID is not in their branches): A B ba bb bba bbb bbc - this node id is given at first bbca bbcb bbcd bc bd C D Basically, the way I see it, I need to: 1. Find node by ID 2. Find node's children 3. Find node's siblings 4. Node's parent becomes node, repeat from step 3 as long as there's a parent The table is constructed with parent_id foreign key reference to itself, and I can fetch entire tree at level X using joinedload_all as given in this example: http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py I have an idea how to do it manually but I was wondering if there is a feature of SQLAlchemy I could use. I'd google for any similar problems or implementations since I don't think this is an uncommon problem, but I'm not sure what to look for. Thanks! -- .oO V Oo. -- 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] Are data really in database using session.comit
Daer all I have a following problem : Session = sessionmaker(bind=engine) session = Session() for item in items: item1 = session.query(item) if len(item1)==0: session.add(item1) session.commit() The session is valid for a loop and I write items in a database during each iteration. Can it happen that if an item is identical to the previous one, the session.commit() will not make it to write the previous item in the database before the query of the next iteration is executed and the next session.commit() will fail because of sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint. How can I make sure that the previous item is already in the database before the query in the next iteration is carried out? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Updating user interface after rollback (using after_rollback SessionExtension?)
On Aug 1, 2011, at 6:25 AM, Torsten Landschoff wrote: Hello world, I am tearing my hair about my SQLAlchemy usage in a GUI application, especially to get it error tolerant. Usually the GUI filters user inputs before they are thrown at SQLAlchemy to store it into the database. If that fails, however, it can happen that data is thrown at the database that leads to a rollback because of e.g. violated foreign key constraints. If that happens, the session rolls back (fine) but the GUI still shows the state that I tried to write into the database. Being MVC, I would need all database object to fire a changed event so they are pulled fresh from the database. The Session's default behavior is to expire fully the state present after a rollback occurs. The next access of any attribute will guaranteed pull fresh from the DB in a new transaction. I'm assuming you know this, and that there is something additional here you're looking for, like an event to the GUI to actually re-access the attributes, or something. I tried using the after_commit extension (SQLAlchemy 0.6.8) to do this. Paraphrased, this works like this: def after_rollback(self, session): for instance in session: instance.signal_changed() This works fine if the transaction being rolled back is the toplevel transaction. However, if that is a nested transaction, this destroys my session state (as the listeners try to load from the database): InvalidRequestError: This Session's transaction has been rolled back by a nested rollback() call. To begin a new transaction, issue Session.rollback() first. A surprise for me.Yes the after_rollback() seems to only fire after an actual database rollback. This is a bug in that there is no way to catch soft rollbacks. I can't change the event, as it is integral in transactional extensions like that supplied for Zope; a new one will have to be added and the docs for after_rollback() will need to be updated. Ticket #2241 is added for this and is completed. You can now use the 0.7 tip and use the after_soft_rollback() event in conjunction with the is_active flag: @event.listens_for(Session, after_soft_rollback) def do_something(session, previous_transaction): if session.is_active: session.execute(select * from some_table) http://www.sqlalchemy.org/trac/ticket/2241 http://hg.sqlalchemy.org/sqlalchemy/rev/94d54a7e4d49 -- 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.
Re: [sqlalchemy] Are data really in database using session.comit
On Aug 1, 2011, at 11:07 AM, Eduardo wrote: Daer all I have a following problem : Session = sessionmaker(bind=engine) session = Session() for item in items: item1 = session.query(item) if len(item1)==0: session.add(item1) session.commit() The session is valid for a loop and I write items in a database during each iteration. Can it happen that if an item is identical to the previous one, the session.commit() will not make it to write the previous item in the database before the query of the next iteration is executed and the next session.commit() will fail because of sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint. How can I make sure that the previous item is already in the database before the query in the next iteration is carried out? I'd get the data in order before going in, and most likely do not commit() on every insert, this is wasteful. Assuming the primary key of item is item.id: # uniquify on id items_by_id = dict((item.id, item) for item in items) for item in items_by_id.values(): # merge each. item = session.merge(item) session.commit() If the above approach is emitting too many SELECTs for your taste, an additional optimization would be to pre-load the existing items, such as: # get the ids into a dict items_by_id = dict((item.id, item) for item in items) # load whatever is present first in one SELECT, keep them in memory # inside of existing_items existing_items = session.query(Item).filter(Item.id.in_(items_by_id)).all() # then do the loop + merge() -- 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] Simple one-to-one translation with hybrid_attribute
Hello everyone,
Trying to use hybrid_attribute to provide friendly names for integers
representing object states. Storage and retrieval works fine, but I
can't get filtering working. I want the translation to happen on the
Python side prior to filling in the query parameters, but
hybrid_attribute is thinking the DB should do it. Example at the
bottom.
I don't really understand how to write the @state.expression the way I
want things to happen.
Thanks,
Ross
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
Session = orm.scoped_session(orm.sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
statei2a = {
0 : captured,
1 : registered,
2 : prepared,
}
statea2i = dict((v, k) for k, v in statei2a.iteritems())
class Device(Base):
__tablename__ = device
id = sa.Column(sa.Integer, primary_key=True)
statenum = sa.Column(sa.Integer, nullable=False, default=0)
def __init__(self, state):
self.state = state
@hybrid_property
def state(self):
return statei2a[self.statenum]
@state.setter
def state(self, state):
self.statenum = statea2i[state]
@state.expression
def state(self):
return statea2i[self.statenum]
Base.metadata.create_all(engine)
d1 = Device(captured)
d2 = Device(registered)
d3 = Device(prepared)
Session.add_all([d1, d2, d3])
Session.commit()
q = Session.query(Device)
q.filter_by(state=captured)
q.filter(Device.state 0)
signature.asc
Description: This is a digitally signed message part
Re: [sqlalchemy] Relationship spanning on multiple tables
'transm_limit': relationship(SurfaceRes, single_parent=True, #uselist=False, #primaryjoin=and_( #user_stratigraphies.c.id_prov==provinces.c.id, #provinces.c.id_cz==transm_limits.c.id_cz, #user_stratigraphies.c.id_str==stratigraphies.c.id, #stratigraphies.c.id_tec==tech_elements_classes.c.id, #tech_elements_classes.c.id_tu==transm_limits.c.id_tu, #), this fails because you aren't joining to the table to which SurfaceRes is mapped, surface_res. On Aug 1, 2011, at 7:02 AM, neurino wrote: I'm trying to get some relationships spanning on multiple tables (4 or 5). While I got the `4 tables` one working on first attempt (I was surpized I could...) I can't get the `5 tables` one to work while the code is almost the same. Moreover with the first relationship if I add adding `viewonly=True` initialization fails. I just need these to get (not even set) some values with convenience of SA attributes. I published a working example on pastebin: http://pastebin.com/RsZ6GCRq I hope someone can sort out this thing, thank you. Greetings neurino -- 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. -- 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.
Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute
On Aug 1, 2011, at 5:08 PM, Ross Vandegrift wrote:
Hello everyone,
Trying to use hybrid_attribute to provide friendly names for integers
representing object states. Storage and retrieval works fine, but I
can't get filtering working. I want the translation to happen on the
Python side prior to filling in the query parameters, but
hybrid_attribute is thinking the DB should do it. Example at the
bottom.
I don't really understand how to write the @state.expression the way I
want things to happen.
statei2a = {
0 : captured,
1 : registered,
2 : prepared,
}
statea2i = dict((v, k) for k, v in statei2a.iteritems())
class Device(Base):
__tablename__ = device
id = sa.Column(sa.Integer, primary_key=True)
statenum = sa.Column(sa.Integer, nullable=False, default=0)
def __init__(self, state):
self.state = state
@hybrid_property
def state(self):
return statei2a[self.statenum]
@state.setter
def state(self, state):
self.statenum = statea2i[state]
@state.expression
def state(self):
return statea2i[self.statenum]
You're looking to convert from int-string using a mapping in a SQL expression,
so I think you'd need to write @state.expression as a CASE statement.
from sqlalchemy import case
@state.expression
def state(self):
return case(self.statenum, statei2a)
--
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.
Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute
On Mon, 2011-08-01 at 17:22 -0400, Michael Bayer wrote: You're looking to convert from int-string using a mapping in a SQL expression, so I think you'd need to write @state.expression as a CASE statement. from sqlalchemy import case @state.expression def state(self): return case(self.statenum, statei2a) Exactly what I needed, works like a charm - thank you so much! For the archives - the args are flipped above. Should be case(statei2a, self.statenum). Thanks, Ross signature.asc Description: This is a digitally signed message part
[sqlalchemy] Integrity error when using association_proxy - one of the foreign keys is missing
Hi,
I'm trying to convert a 'simple' many-to-many relationship to an
association object in order to allow the relationship to have
attributes. I've followed the code in examples/association/
proxied_association.py fairly closely (or so I thought) but it isn't
working for me.
As an example, let's say I am trying to create a relationship between
an RSS feed and the pages from the feed but I want a feed_date on the
relationship. Currently, my code look something like this:
class Page (Base):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
posts = relationship(Post, cascade=all, delete-orphan,
backref='pages')
feeds = association_proxy(feeds, feed)
class Post(Base)
__tablename__ = 'pages_feeds'
page_id = Column(Integer, ForeignKey('pages.id'),
primary_key=True)
feed_id = Column(Integer, ForeignKey('feeds.id'),
primary_key=True)
feed = relationship(Feed, lazy='joined')
I haven't changed the Feed class at all.
When I do something along the lines of:
page = Page()
feed = Feed()
page.feeds.append(feed)
the query that is issued is as follows:
IntegrityError: (IntegrityError) pages_feeds.feed_id may not be NULL
u'INSERT INTO pages_feeds (page_id) VALUES (?)' (1,)
So, clearly I have missed out the part of the config that explains
that adds in the second foreign key. Could anybody point me to where
please?
Thanks,
Ben
--
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.
Re: [sqlalchemy] Integrity error when using association_proxy - one of the foreign keys is missing
On Aug 1, 2011, at 8:34 PM, somewhatofftheway wrote:
Hi,
I'm trying to convert a 'simple' many-to-many relationship to an
association object in order to allow the relationship to have
attributes. I've followed the code in examples/association/
proxied_association.py fairly closely (or so I thought) but it isn't
working for me.
As an example, let's say I am trying to create a relationship between
an RSS feed and the pages from the feed but I want a feed_date on the
relationship. Currently, my code look something like this:
class Page (Base):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
posts = relationship(Post, cascade=all, delete-orphan,
backref='pages')
feeds = association_proxy(feeds, feed)
class Post(Base)
__tablename__ = 'pages_feeds'
page_id = Column(Integer, ForeignKey('pages.id'),
primary_key=True)
feed_id = Column(Integer, ForeignKey('feeds.id'),
primary_key=True)
feed = relationship(Feed, lazy='joined')
I haven't changed the Feed class at all.
When I do something along the lines of:
page = Page()
feed = Feed()
page.feeds.append(feed)
the query that is issued is as follows:
IntegrityError: (IntegrityError) pages_feeds.feed_id may not be NULL
u'INSERT INTO pages_feeds (page_id) VALUES (?)' (1,)
So, clearly I have missed out the part of the config that explains
that adds in the second foreign key. Could anybody point me to where
please?
you need a constructor and/or creator that generates Post() with the feed
attached to it, like def __init__(self, feed): self.feed = feed, then the assoc
proxy with creator=Post.
--
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: Building hierarchy tree in reverse
You could look for recursive CTE (Common Table Expressions), if your database engine supports such queries. See e.g. http://www.postgresql.org/docs/8.4/static/queries-with.html for PostgreSQL. That allows arbitrary-depth queries, as opposed to join chains that have to assume a fixed depth. You could probably apply two recursive queries, one downward and one upward from the given node, to avoid querying the whole tree. SQLAlchemy has no support for CTEs directly, though of course you can construct the query manually and execute and fetch results through SQLAlchemy. You *can* get some support for recursive queries under SQLAlchemy in https://github.com/marplatense/sqla_hierarchy/ but be warned, that project is ... youthful :) Regards, - Gulli -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/g7-7S4mBC3wJ. 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.
