Re: [sqlalchemy] empty a many-many table
On 2012-01-05 1:24 PM, Michael Bayer wrote:
On Jan 5, 2012, at 9:57 AM, Michael Hipp wrote:
Working from the many-many example in the tutorial [1], it has an association
table like this:
post_keywords = Table('post_keywords', Base.metadata,
Column('post_id', Integer, ForeignKey('posts.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id'))
)
session.query(BlogPost).delete()
if you use ON DELETE CASCADE on the foreign keys referred to by post_keywords,
then those rows will delete automatically when saying query(BlogPost).delete().
Thanks, Michael. Setting ondelete='cascade' works well.
Is there a call for Table() objects that is equivalent to the
sess.query(Base).delete() way of deleting everything?
I have some possible occasions to do a brute force cleanup (e.g. unit
testing) but I'd prefer not having to revert to SQL.
Thanks,
Michael
--
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] and_(condition1,condition2) or condition1 and condition2
On Jan 6, 2012, at 12:16 AM, Manav Goel wrote: Hello I noticed that in filter method I can use either and_(condition1,condition2) or condition1 and condition2 But I searched and did not find any mention about second way anywhere. I want to know if both options are equal or there some catch in using second option. hoping you mean and not and. There's a docstring for and_() and variants here: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=and_#sqlalchemy.sql.expression.and_ Regards, Manav Goel -- 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.
[sqlalchemy] Clas ... does not have a mapped column named [moving to 0.7.4]
I'm updating my code to work with 0.7.4; and I have a class that is
derived from two tables.I'm looking at
http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables but I'm not having any luck. When I try to operate on the database the first time it dies with an exception ending in
-
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to
initialize - can't proceed with initialization of other mappers.
Original exception was: Class class
'coils.foundation.alchemy.task.TaskAction' does not have a mapped
column named 'job_id
Which I guess I just don't understand. Any pointers would be appreciated
from base import Base, KVC, metadata
from sqlalchemyimport *
import sqlalchemy.orm as orm
history_table = Table( 'job_history', metadata,
Column('job_history_id', Integer,
Sequence('key_generator'), primary_key=True),
Column('job_id', Integer),
Column('actor_id', Integer,
ForeignKey(Contact.object_id), nullable=False),
Column('action', String),
Column('action_date', UTCDateTime),
Column('job_status', String),
Column('db_status', String) )
info_table = Table( 'job_history_info', metadata,
Column('job_history_info_id', Integer,
Sequence('key_generator'), primary_key=True),
Column('comment', String),
Column('job_history_id', Integer,
ForeignKey('job_history.job_history_id')),
Column('db_status', String) )
history_and_info = join(history_table, info_table)
class TaskAction(Base, KVC):
An OpenGroupare Task History Info entry
__table__ = history_and_info
db_status = orm.column_property(history_table.c.db_status,
info_table.c.db_status)
object_id =
orm.column_property(history_table.c.job_history_id,
info_table.c.job_history_id)
task_id = history_table.c.job_id
task_status = history_table.c.job_status
action_date = history_table.c.action_date
actor_id = history_table.c.actor_id
comment = info_table.c.comment
Full Exception
Traceback (most recent call last):
File stdin, line 1, in module
File
/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py, line 969, in
query
return self._query_cls(entities, self, **kwargs)
File
/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py, line 107, in
__init__
self._set_entities(entities)
File
/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py, line 116, in
_set_entities
self._setup_aliasizers(self._entities)
File
/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py, line 131, in
_setup_aliasizers
_entity_info(entity)
File
/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/util.py, line 550, in
_entity_info
mapperlib.configure_mappers()
File
/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py, line 2858, in
configure_mappers
raise e
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to
initialize - can't proceed with initialization of other mappers.
Original exception was: Class class
'coils.foundation.alchemy.task.TaskAction' does not have a mapped
column named 'job_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.
Re: [sqlalchemy] Clas ... does not have a mapped column named [moving to 0.7.4]
On Jan 6, 2012, at 2:23 PM, Adam Tauno Williams wrote:
I'm updating my code to work with 0.7.4; and I have a class that is derived
from two tables.I'm looking at
http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables
but I'm not having any luck. When I try to operate on the database the
first time it dies with an exception ending in -
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize
- can't proceed with initialization of other mappers. Original exception
was: Class class 'coils.foundation.alchemy.task.TaskAction' does not have a
mapped column named 'job_id
Which I guess I just don't understand. Any pointers would be appreciated
Sorry, this is not enough information. You haven't given me KVC or Contact so
I cannot reproduce. Here is a test case with as much info as you have here,
runs fine (below). If you can attach a fully reproducing .py script with all
requisite areas we can identify at what point you're referring to job_id from
the TaskAction table, which isn't really specified here (note TaskAction
doesn't have job_id since you've redefined it as task_id).
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
metadata = Base.metadata
from sqlalchemyimport *
import sqlalchemy.orm as orm
class Contact(Base):
__tablename__ = 'contact'
object_id = Column(Integer, primary_key=True)
history_table = Table( 'job_history', metadata,
Column('job_history_id', Integer,
Sequence('key_generator'), primary_key=True),
Column('job_id', Integer),
Column('actor_id', Integer,
ForeignKey(Contact.object_id), nullable=False),
Column('action', String),
Column('action_date', DateTime),
Column('job_status', String),
Column('db_status', String) )
info_table = Table( 'job_history_info', metadata,
Column('job_history_info_id', Integer,
Sequence('key_generator'), primary_key=True),
Column('comment', String),
Column('job_history_id', Integer,
ForeignKey('job_history.job_history_id')),
Column('db_status', String) )
history_and_info = join(history_table, info_table)
class TaskAction(Base):
An OpenGroupare Task History Info entry
__table__ = history_and_info
db_status = orm.column_property(history_table.c.db_status,
info_table.c.db_status)
object_id = orm.column_property(history_table.c.job_history_id,
info_table.c.job_history_id)
task_id = history_table.c.job_id
task_status = history_table.c.job_status
action_date = history_table.c.action_date
actor_id = history_table.c.actor_id
comment = info_table.c.comment
configure_mappers()
s = Session()
print s.query(TaskAction)
--
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] cascade delete in relationship and session.execute(table.update())
I really appreciate your help! It works great without any problem.
I tried session.refresh(storeobject) and I was wondering why it didn't
work, now I learned the difference from your method.
Again, thank you very much, and have a great weekend.
On Tue, Jan 3, 2012 at 10:56 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jan 2, 2012, at 4:06 PM, Wubin wrote:
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message.
This would indicate that PolymorphicClass is mapped to a table as well.
A DELETE or UPDATE statement, in standard SQL, doesn't support more than
one table being affected at the same time (only MySQL has an extended
syntax that supports this but it's not supported by the ORM). There's
also a syntax that supports only one table being updated, but multiple
tables in the FROM clause which on Postgresql is UPDATE..FROM, and
SQLAlchemy now supports that too, but again the ORM doesn't yet have
support for that to be integrated.
So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine.
OK
But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted?
yeah just expire the collection:
session.expire(storeobject, ['name_of_products_collection'])
(2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Right now you can only pass in the base class, I took a look since we do
support UPDATE..FROM for supporting DBs, the controversial part here is
that an UPDATE against the child table which then refers to the base table
would need WHERE criterion to join the two together, which introduces
tricky decisionmaking. But one possibility is to just leave that up to
the user in this case.
I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this
possibility.
--
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.
[sqlalchemy] New to sqlalchemy multiple files one to one and one to many - circular relationship issues
I have an address class that i would like to use in several locations for
instance vendors need addresses and customers need addresses.
I have my model split between 2 files globals and ar. A customer can have a
default bill to address, default ship to address, and multiple address to
choose from for ship to's beyond the default. I have the bill_to and
ship_to default working fine but for the life of me can't figure out how to
create the locations reference. I know how to do it if i put it on the
address class but then i have python import issues obviously. I could put
them in the same file but then i lose the versalitity of having the same
kind of address setup for vendors (defaults and multiples locations also).
How can I define locations that would be a list of addresses on the
customer class.
Hope this makes sense. TIA
AR model ..
from erp.model.globals import Address
class Customer(DeclarativeBase):
__tablename__ = 'customers'
customer_id = Column(Integer, primary_key=True)
customer_name = Column(Unicode(100))
discount = Column(Float)
#bill_to_id = Column(Integer, ForeignKey('addresses.address_id'))
#bill_to =
relation(Address,primaryjoin=bill_to_id==Address.address_id,uselist=False)
ship_to_id = Column(Integer, ForeignKey('addresses.address_id'))
ship_to =
relation(Address,primaryjoin=ship_to_id==Address.address_id,uselist=False)
globals ...
rom erp.model import DeclarativeBase, metadata, DBSession
class Address(DeclarativeBase):
__tablename__ = 'addresses'
address_id = Column(Integer,primary_key=True)
name = Column(Unicode(100))
address_one = Column(Unicode(100))
address_two = Column(Unicode(100))
address_three = Column(Unicode(100))
city = Column(Unicode(100))
state = Column(Unicode(100))
zip_code = Column(Unicode(100))
phone = Column(Unicode(100))
fax = Column(Unicode(100))
contact = Column(Unicode(100))
--
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/-/0osPdVWRxgwJ.
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.
