[sqlalchemy] Question on the precedence of insert and delete in session flush
Hi,
I am using SQLAlchemy 0.6.4 with postgres db. I have two tables - users and
addresses tables with addresses table having a foreign key constraint
referencing the users table. Each address record is identified by a unique
constraint key 'email_address'.
In my test case, each user instance have a collection of addresses. For each
user instance, I want to delete every address instance in the addresses
collection that the user instance has and then add new address instances
(they may have the same unique key that the previously deleted address
instance had). The problem I am having now is that at the end of the flush
call, unique constraint error for 'email_address' from addresses table is
thrown even though delete operation is done earlier than insert. Looking at
the echo output, INSERTs are indeed done first than DELETEs. The work around
that I have now is to call flush() right after the deletion of address
instances in each user.
My question is - what is the precedence of insert, delete and update in
session flush? It would also be helpful if someone can explain the overview
of the mechanics of flushing in SQLAlchemy.
Attached is the python script that I wrote to understand why this problem
mentioned above happens.
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.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column, UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.types import String, Integer
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy import create_engine
engine = create_engine('postgres://postgres:data01@localhost:5432/flush_test')
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
__table_args__ = (
UniqueConstraint('name',
name='users_name_key'),
{})
id = Column(Integer, primary_key=True)
name = Column(String(20), nullable=False)
def __init__(self, name):
self.name = name
def __repr__(self):
return User('%s') % self.name
class Address(Base):
__tablename__ = 'addresses'
__table_args__ = (
UniqueConstraint('email_address',
name='addresses_email_address_key'),
ForeignKeyConstraint(['username'],
['users.name'],
onupdate='cascade'),
{}
)
id = Column(Integer, primary_key=True)
email_address = Column(String(20), nullable=False)
username = Column(String(20), nullable=False)
user = relationship(User, backref=backref('addresses', cascade='all, delete-orphan'))
def __init__(self, email_address):
self.email_address = email_address
def __repr__(self):
return Address('%s') % self.email_address
metadata = Base.metadata
metadata.create_all(engine)
Session = sessionmaker(bind=engine, autoflush=False)
session = Session()
session.rollback()
# begin init code that puts the persisted instance into the database
if session.query(User).filter_by(name='user1').count()==0:
user1 = User('user1')
user1.addresses.append(Address('us...@email.com'))
session.add(user1)
session.commit()
else:
user1=session.query(User).filter_by(name='user1').one()
if len(user1.addresses)==0:
user1.addresses.append(Address('us...@email.com'))
session.commit()
# end init code
engine.echo = True
for address in user1.addresses:
session.delete(address)
user1.addresses.append(Address('us...@email.com'))
# After the flush line below unique constraint error for address table is thrown,
# echo output shows that insertion of child item was done first before delete, although we call delete first.
# This does not happen if we flush after delete.
session.flush()
RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of jos.carpente...@yahoo.com Sent: 26 July 2011 18:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Updating records in table not working I'm using Postgres as a database. I try to create new records or update existing records with data. The data is parsed from a csv file. Creating new records works fine. But when a record already exists, the update fails with: IntegrityError: (IntegrityError) duplicate key value violates unique constraint stock_item_pkey I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' I've also tried updata, but that fails too and als mentions a depreciated statement. The new data is going to a single table. The PrimaryKey is the item number (item with value itemno in snippet below). Since the item is unique, I don't let Postgres create an id. new = Item(item=itemno, ...) db.session.add(new) db.session.commit() I'm pretty new with SA and I might overlook something. How can I solve this? I *think* you should be able to use session.merge instead: http://www.sqlalchemy.org/docs/orm/session.html#merging temp = Item(item=itemno, ...) new = db.session.merge(temp) db.session.commit() (note that 'merge' returns a new object attached to the session) 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] Re: Unable to update Postgres because of natural primary key
Hi, instead of db.session.add, what you want is: import = db.session.merge(import) See http://www.sqlalchemy.org/docs/orm/session.html#merging : It examines the primary key of the instance. If it’s present, it attempts to load an instance with that primary key (or pulls from the local identity map Maybe you were confused by the heading Adding New *or Existing* Items in http://www.sqlalchemy.org/docs/orm/session.html#adding-new-or-existing-items ... here the existing part only applies to *detached* instances (ones that were previously associated with a session but have been removed), not to *transient* ones (new instances that SQLAlchemy hasn't already seen). Transient instances are assumed new by session.add, it doesn't query the database to check if the primary key exists. See Quickie Intro to Object States http://www.sqlalchemy.org/docs/orm/session.html#quickie-intro-to-object-states and then the rest of the Session tutorial; that should get you going. 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/-/6lI0LZnLNpYJ. 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: RE: [sqlalchemy] Updating records in table not working
On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. 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/-/oCVN7_jgj4cJ. 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: RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Gunnlaugur Briem Sent: 27 July 2011 10:36 To: sqlalchemy@googlegroups.com Subject: Re: RE: [sqlalchemy] Updating records in table not working On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli Ah, I see. Thanks for the clarification. Cheers, 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] Re: order_by with property of related table
Hi, you need to join explicitly on A.b: SESSION.query(A).join(A.b).order_by(B.name) Full example: http://pastebin.com/uMqEa6Cr 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/-/1KPEOTrno04J. 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] session query against more databases
Hi all, I'm trying to use session to execute a query against two databases; is it possibile? Ex. sql: select db1.table1.col1, db2.table2.col2 from db1.table1 inner join db2.table2 on db1.table1.key = db2.table2.key With sessions: session.query(Table1).join(Table2, Table1.key==Table2.key).all() On internet I found old answers that say it is not possible, but it was the far 2007.. :-) Alessandro -- 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] Automatic generation of id columns
Hello,
I'm currently writing my own version of the magic orm. I'd like it
to generate id columns automatically. I tried it like shown below.
When using the code I get an exception:
ArgumentError: Mapper Mapper|Version|version could not assemble any
primary key columns for mapped table 'Join object on content(55261328)
and version(64443600)'
The problem seems to be in the WithId metaclass. When I put an id =
Column(... primary = true) by hand on the Version class everything
works. I was expecting the metaclass to do the same, but apparently
there must be some difference,
Any idea how I can make the Base/Content/Version classes have id
columns (which work with polymorphism) automatically? I'd like to stay
away from mixin class all over the place.
-Matthias
Code:
class WithId(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if 'id' not in dict_:
dict_['id'] = Column('id', Integer,
ForeignKey('content.id'), primary_key=True)
DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=WithId)
class Content(db.Base):
id = db.Column('id', db.Integer, primary_key=True)
type = db.Column(db.String(250))
@classmethod
def get_class_name(cls):
'''Convert CamelCase class name to underscores_between_words
table name.'''
name = cls.__name__
return (
name[0].lower() +
re.sub(r'([A-Z])', lambda m:_ + m.group(0).lower(),
name[1:])
)
@db.declared_attr
def __tablename__(cls):
return cls.get_class_name()
@db.declared_attr
def __mapper_args__(cls):
args = { 'polymorphic_identity' : cls.get_class_name() }
ContentClass = cls.__bases__[-1]
if ContentClass is db.Base:
args['polymorphic_on'] = cls.type
else:
args['inherit_condition'] = (cls.id == ContentClass.id)
return args
class Version(Content):
timestamp = Column(DateTime, default=datetime.datetime.now)
message = Column(UnicodeText)
#author = attribute(User, backref =
collectionAttribute('authoredVersions'))
--
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] Automatic generation of id columns
On Jul 27, 2011, at 2:12 AM, Matthias wrote:
Hello,
I'm currently writing my own version of the magic orm. I'd like it
to generate id columns automatically. I tried it like shown below.
When using the code I get an exception:
ArgumentError: Mapper Mapper|Version|version could not assemble any
primary key columns for mapped table 'Join object on content(55261328)
and version(64443600)'
The problem seems to be in the WithId metaclass. When I put an id =
Column(... primary = true) by hand on the Version class everything
works. I was expecting the metaclass to do the same, but apparently
there must be some difference,
Any idea how I can make the Base/Content/Version classes have id
columns (which work with polymorphism) automatically? I'd like to stay
away from mixin class all over the place.
Without looking closely, just the usage of a metaclass is frequently very
difficult. I know that you don't want to use a mixin, but why not specify your
class as part of the declarative base ?
Base = declarative_base(cls=WithIdBase)
This question has come up at least twice, maybe three times, in the last few
weeks, so it seems as though the mixin section should get an explicit section
about you can use them as the Base also - right now its only in the docstring.
Another use case that was identified was that of base classes that are not the
Base, but are in the middle of things.We might add a function to
declarative_base to build such a base, something like mybase =
declarative_base(cls=WithIdBase, derive_from_base=Base). It would be a small
patch.
-Matthias
Code:
class WithId(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if 'id' not in dict_:
dict_['id'] = Column('id', Integer,
ForeignKey('content.id'), primary_key=True)
DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=WithId)
class Content(db.Base):
id = db.Column('id', db.Integer, primary_key=True)
type = db.Column(db.String(250))
@classmethod
def get_class_name(cls):
'''Convert CamelCase class name to underscores_between_words
table name.'''
name = cls.__name__
return (
name[0].lower() +
re.sub(r'([A-Z])', lambda m:_ + m.group(0).lower(),
name[1:])
)
@db.declared_attr
def __tablename__(cls):
return cls.get_class_name()
@db.declared_attr
def __mapper_args__(cls):
args = { 'polymorphic_identity' : cls.get_class_name() }
ContentClass = cls.__bases__[-1]
if ContentClass is db.Base:
args['polymorphic_on'] = cls.type
else:
args['inherit_condition'] = (cls.id == ContentClass.id)
return args
class Version(Content):
timestamp = Column(DateTime, default=datetime.datetime.now)
message = Column(UnicodeText)
#author = attribute(User, backref =
collectionAttribute('authoredVersions'))
--
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] Updating records in table not working
On Jul 27, 2011, at 5:52 AM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Gunnlaugur Briem Sent: 27 July 2011 10:36 To: sqlalchemy@googlegroups.com Subject: Re: RE: [sqlalchemy] Updating records in table not working On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli Ah, I see. Thanks for the clarification. Whether the object has a key or not is what decides between it being transient-pending or detached-persistent once add()-ed back to the session: from sqlalchemy.orm import attributes attributes.instance_state(myobject).key is not None where instance_state() is going to give you the ._sa_instance_state attribute we stick on there, but we keep access through the public function. The .key is stuck on the object after it gets through a flush(), or when we construct it from an incoming row. Otherwise there is not a .key and the object is transient-pending. We originally had save() and update() because we copied Hibernate's scheme exactly, as well as save_or_update() which in Hibernate's case does a guess. In SQLAlchemy we have it much easier due to Python's open ended nature, we just check if we put a key or not.So we just made it add() to simplify. Cheers, 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. -- 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] Question on the precedence of insert and delete in session flush
On Jul 27, 2011, at 3:34 AM, ammar azif wrote: Hi, I am using SQLAlchemy 0.6.4 with postgres db. I have two tables - users and addresses tables with addresses table having a foreign key constraint referencing the users table. Each address record is identified by a unique constraint key 'email_address'. In my test case, each user instance have a collection of addresses. For each user instance, I want to delete every address instance in the addresses collection that the user instance has and then add new address instances (they may have the same unique key that the previously deleted address instance had). The problem I am having now is that at the end of the flush call, unique constraint error for 'email_address' from addresses table is thrown even though delete operation is done earlier than insert. Looking at the echo output, INSERTs are indeed done first than DELETEs. The work around that I have now is to call flush() right after the deletion of address instances in each user. My question is - what is the precedence of insert, delete and update in session flush? It would also be helpful if someone can explain the overview of the mechanics of flushing in SQLAlchemy. Attached is the python script that I wrote to understand why this problem mentioned above happens. Right so, the unit of work was rewritten in version 0.6, because it was desperately needed, but also one issue I wanted to see if it could be solved was the one case that the UOW can't handle currently without direct intervention, that of the unique constraint that needs to be deleted before a new one is inserted. It should be noted that for an object where the primary key itself is the thing that might conflict for an outgoing / incoming situation, the UOW turns that operation into an UPDATE.But for an arbitrary column with a unique on it we don't have the mechanics in place to do it that way, nor would I want to . Id much rather have a delete + insert be a DELETE + INSERT in all cases. So with the UOW rewrite, its very clear cut how the order of steps is determined, and the architecture is fairly amenable to a strategy that would freely mix DELETE, INSERT and UPDATE. However, I didn't go this far with the rewrite. I tried a bit, trying to have the topological sort also take into account individual INSERTS that need to come before DELETES, instead of it being an overarching two step process, but there seemed to be a lot of really mindbendy types of cases where the fact that DELETES are unconditionally after the INSERT/UPDATEs makes things work out really well.The 0.6 rewrite had the highest priority on not introducing any new bugs vs. the previous version, which had been very stable for a long time (although impossible to work with) so I didn't go further with that idea, at that time. (There of course is no reason someone can't try to work with it some more) The current scheme is INSERT/UPDATES first, DELETES second, and the original idea is mostly from Hibernate's procedure (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html#objectstate-flushing), which I seem to recall was not as verbose as that particular description is now. I have an architectural document I can send you under separate cover (it is not public yet). -- 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] writing a (sub)query
I have the following statement : SELECT name, id, division, value, FROM ( SELECT name, id, division,value, max(value) over (partition by division) as max_val FROM tab1 ) WHERE value = max_val I try to turn this sql statement into a Query object I tried this sqlquery=session.query(sometab) sqlquery.statement= SQL QUERY GIVEN ABOVE but it does not work because statement cannot be set directly. What is get-around for this? Can this statement be written as a subquery? If yes, how? I saw several example of subqueries on the internet but none of these seem to be suitable for this statement. Thanking in advance Eddie -- 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] writing a (sub)query
0.7 has support for window functions. I haven't tried it in a subquery. http://www.sqlalchemy.org/docs/core/tutorial.html?highlight=window#window-functions -- Mike Conley On Wed, Jul 27, 2011 at 9:16 AM, Eduardo ruche...@googlemail.com wrote: I have the following statement : SELECT name, id, division, value, FROM ( SELECT name, id, division,value, max(value) over (partition by division) as max_val FROM tab1 ) WHERE value = max_val I try to turn this sql statement into a Query object I tried this sqlquery=session.query(sometab) sqlquery.statement= SQL QUERY GIVEN ABOVE but it does not work because statement cannot be set directly. What is get-around for this? Can this statement be written as a subquery? If yes, how? I saw several example of subqueries on the internet but none of these seem to be suitable for this statement. Thanking in advance Eddie -- 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] storedprocedure with input parameter
I like to use a stored procure which needs a input parameter in something like this: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(2)).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) result = session.query(seltestm).get(73) above works, but I would really need to replace the hardcoded 2 with a function, i.e.: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(getSomeUserValue())).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) # set the SomeUserValue here and then do result = session.query(seltestm).get(73) tried using functools.partial but I get a InterfaceError exception. Werner -- 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] Question on the precedence of insert and delete in session flush
And the recipe I have used is to issue a flush() after the deletes and before the inserts. In most cases this is sufficient to get things to work in the right order. I can imagine that there are some complex data management use cases where that is not sufficient. It works for your sample as the comment in your code indicates. This should not change the performance of the app since we are only changing the sequence of statements, not introducing additional transaction overhead. When I run into scenarios where the sequence of issuing SQL is significant, I'm not sure how much control I want to give up to the UOW code. After all if sequence is important this could very well be application dependent and the UOW might have to get really sophisticated to guess right. That increases the risk of taking a performance hit in the 90+% of cases where it doesn't matter. -- Mike Conley -- 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] storedprocedure with input parameter
On 07/27/2011 10:42 AM, werner wrote: I like to use a stored procure which needs a input parameter in something like this: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(2)).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) result = session.query(seltestm).get(73) above works, but I would really need to replace the hardcoded 2 with a function, i.e.: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(getSomeUserValue())).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) # set the SomeUserValue here and then do result = session.query(seltestm).get(73) tried using functools.partial but I get a InterfaceError exception. Werner I believe you want to replace getSomeUserValue() with sa.bindparam(callable_=getSomeUserValue). See the docs at http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.bindparam. As an aside, do you really want to map a class against a dynamic query? I'm not sure how well the ORM deals with that. At the very least, I think you need to ensure that SomeUserValue does not change while using the session. -Conor -- 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: Automatic generation of id columns
Thanks for your help!
I found the solution :) Instead of doing
dict_['id'] = Column(...)
I just do
cls.id = Column(...)
and it works. Kudos to the writer of this wiki entry
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoSequenceGeneration
. The comments in there led me to the solution finally.
-Matthias
On 27 Jul., 15:38, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 27, 2011, at 2:12 AM, Matthias wrote:
Hello,
I'm currently writing my own version of the magic orm. I'd like it
to generate id columns automatically. I tried it like shown below.
When using the code I get an exception:
ArgumentError: Mapper Mapper|Version|version could not assemble any
primary key columns for mapped table 'Join object on content(55261328)
and version(64443600)'
The problem seems to be in the WithId metaclass. When I put an id =
Column(... primary = true) by hand on the Version class everything
works. I was expecting the metaclass to do the same, but apparently
there must be some difference,
Any idea how I can make the Base/Content/Version classes have id
columns (which work with polymorphism) automatically? I'd like to stay
away from mixin class all over the place.
Without looking closely, just the usage of a metaclass is frequently very
difficult. I know that you don't want to use a mixin, but why not specify
your class as part of the declarative base ?
Base = declarative_base(cls=WithIdBase)
This question has come up at least twice, maybe three times, in the last few
weeks, so it seems as though the mixin section should get an explicit
section about you can use them as the Base also - right now its only in the
docstring.
Another use case that was identified was that of base classes that are not
the Base, but are in the middle of things. We might add a function to
declarative_base to build such a base, something like mybase =
declarative_base(cls=WithIdBase, derive_from_base=Base). It would be a
small patch.
-Matthias
Code:
class WithId(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if 'id' not in dict_:
dict_['id'] = Column('id', Integer,
ForeignKey('content.id'), primary_key=True)
DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=WithId)
class Content(db.Base):
id = db.Column('id', db.Integer, primary_key=True)
type = db.Column(db.String(250))
@classmethod
def get_class_name(cls):
'''Convert CamelCase class name to underscores_between_words
table name.'''
name = cls.__name__
return (
name[0].lower() +
re.sub(r'([A-Z])', lambda m:_ + m.group(0).lower(),
name[1:])
)
@db.declared_attr
def __tablename__(cls):
return cls.get_class_name()
@db.declared_attr
def __mapper_args__(cls):
args = { 'polymorphic_identity' : cls.get_class_name() }
ContentClass = cls.__bases__[-1]
if ContentClass is db.Base:
args['polymorphic_on'] = cls.type
else:
args['inherit_condition'] = (cls.id == ContentClass.id)
return args
class Version(Content):
timestamp = Column(DateTime, default=datetime.datetime.now)
message = Column(UnicodeText)
#author = attribute(User, backref =
collectionAttribute('authoredVersions'))
--
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
athttp://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] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think 0.6) it
is not.
If I move the echo=True before the select, both the select and delete are
echoed.
It looks like there might be a subtle difference since 0.5 that keeps the
logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all the way
before the connect() to get it to echo.
--
Mike Conley
--
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] Selecting the right table instance in a self referential join
I have the following mapper:
orm.mapper(Xxx,xxx_table, inherits=Resource,
polymorphic_identity=u'xxx',
properties={'children' : orm.relation(Xxx,
backref=orm.backref('parent', remote_side=[Xxx.c.id]),
primaryjoin=xxx_table.c.rid==xxx_table.c.parent_id)})
When I issue the following join, I get as the selected entity the parent
side rather than the child side of the join.
query = sqlalchemy.orm.query(Xxx)
query = query.join('parent', aliased=True)
query = query.filter(some criterion)
The SQL that is generated is as follows:
SELECT anon_1.resource_id AS anon_1_resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT
resource.id AS resource_id
FROM resource INNER JOIN xxx ON resource.id =
xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name .
What I really want is
SELECT resource_id AS resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT
resource.id AS resource_id
FROM resource INNER JOIN xxx ON resource.id =
xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name .
Any help is appreciated.
--
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/-/Lm2ZI32QbvEJ.
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] engine.echo not working as expected
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Mike Conley
Sent: 27 July 2011 17:43
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think
0.6) it is not.
If I move the echo=True before the select, both the select and delete
are echoed.
It looks like there might be a subtle difference since 0.5 that keeps
the logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all
the way before the connect() to get it to echo.
This is explained in the note at the bottom of
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging.
(not that that necessarily helps you, but it does at least say that it
is expected behaviour)
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.
Re: [sqlalchemy] engine.echo not working as expected
I saw that, but unless setting echo actually changes the Python logger
configuration I don't see how it applies here.
--
Mike Conley
On Wed, Jul 27, 2011 at 12:31 PM, King Simon-NFHD78
simon.k...@motorolasolutions.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Mike Conley
Sent: 27 July 2011 17:43
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think
0.6) it is not.
If I move the echo=True before the select, both the select and delete
are echoed.
It looks like there might be a subtle difference since 0.5 that keeps
the logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all
the way before the connect() to get it to echo.
This is explained in the note at the bottom of
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging.
(not that that necessarily helps you, but it does at least say that it
is expected behaviour)
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.
--
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] engine.echo not working as expected
No Python logging calls are emitted, which means, log.info() and log.debug()
*are not called at all*, if logging.isEnabledFor() returns False, which itself
is only checked upon Connection construction. These calls are all
unreasonably expensive so they aren't used if not necessary.
That's what the paragraph means when it says only emitting log statements when
the current logging level.
On Jul 27, 2011, at 2:20 PM, Mike Conley wrote:
I saw that, but unless setting echo actually changes the Python logger
configuration I don't see how it applies here.
--
Mike Conley
On Wed, Jul 27, 2011 at 12:31 PM, King Simon-NFHD78
simon.k...@motorolasolutions.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Mike Conley
Sent: 27 July 2011 17:43
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think
0.6) it is not.
If I move the echo=True before the select, both the select and delete
are echoed.
It looks like there might be a subtle difference since 0.5 that keeps
the logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all
the way before the connect() to get it to echo.
This is explained in the note at the bottom of
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging.
(not that that necessarily helps you, but it does at least say that it
is expected behaviour)
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.
--
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] session query against more databases
A single SQL statement cannot emit a query against two distinct database connections.There are ways to get a single database connection to access two databases behind the scenes using a technology like Oracle's DBLINK. I'm not sure what other vendors provide for this. This all assumes by database you mean, two different database servers. If you mean different databases or schemas within the same server, that involves vendor-specific configurations; check the docs for the database you're using. On Jul 27, 2011, at 7:21 AM, Alessandro wrote: Hi all, I'm trying to use session to execute a query against two databases; is it possibile? Ex. sql: select db1.table1.col1, db2.table2.col2 from db1.table1 inner join db2.table2 on db1.table1.key = db2.table2.key With sessions: session.query(Table1).join(Table2, Table1.key==Table2.key).all() On internet I found old answers that say it is not possible, but it was the far 2007.. :-) Alessandro -- 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] Cascade Deletes
On Jul 25, 2011, at 9:47 AM, Aviv Giladi wrote:
I can't seem to make cascade deletes work in sqlalchemy.
I have a parent class (called Rating), a sub class (Subrating) and a
third class called SubRatingProperty.
There is a one-to-one relationship between Rating and SubRating - each
Rating can only have one specific SubRating object. Next, the
SubRatingProperty refers to a row in a table with fixed values. There
are 3 SubRatingProperty entries - property1, property2 and property3.
The SubRating class can have one or more of either property1,
property2 and property3, therefore the relationship is many-to-many (a
SubRatingProperty can have more than one properties, and for example
property1 can be assigned to more than one SubRatingProperty's).
Here is the code that defines all of this:
subrating_subratingproperty_association =
Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id',
Integer, ForeignKey('subrating_properties.id')))
class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
secondary=subrating_subratingproperty_association,
backref=subrating_properties)
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
uselist=False))
Everything works fine, but I can't figure out how to do cascade
deletes. I am deleting Rating objects, and when I do, I would like the
according SubRating object to be deleted, as well as all the entries
in the association table. So deleting Rating1 would delete its
SubRating, as well as all the connection between the SubRating and
SubRatingProperty's.
I have tried adding cascade=all to the relationship call,
you have two relationships() here to build the full chain so you'd need
cascade='all, delete-orphan' on both Rating.subrating as well as
SubRating.subrating_properties (use the backref() function instead of a string
to establish the cascade rule on that end.
and I also
tried adding ondelete=cascade) to the ForeignKey call.
if all the involved FOREIGN KEYs are generated with ON DELETE CASCADE as this
would accomplish, as long as you are not on SQLIte or MySQL MyISAM the deletes
will be unconditional.
--
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] Newbie question
Howdy, I'm aggregating data from several Sqlite files into a Postgres db. The sqlite files are storage for several apps I use: Shotwell, Firefox, Zotero, Banshee ... I just watch and pull from them. I've been using import sqlite3 so far, dumping sql from sqlite, using it to create the Postgres tables. I then add columns to meet my own needs. I now can diff 2 sqlite files, so I know what rows need updating and adding in the Postgres tables. I feel I should be using Sqlalchemy, but have been intimidated by the wealth of choices SA offers. I don't want to start down the wrong road. However, as I look towards coding change merging, and the new level of complexity it presents, I think it's time to take the plunge. Data specs: - source data lives in other-owned files - replicate source data tables in Postgres - add columns to Postgres tables - keep Postgres synced with sqlite sources My proclivities: - comfortable in Python, SQL not so much - roadmap - pull into the Postgres db from other sources - file system content - email - other db's: Mysql, rdf, ... - feed Sphinxsearch from the Postgres db I would greatly appreciate any suggestions on how to proceed. Thanks, Kent -- 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] Newbie question
On Jul 27, 2011, at 3:21 PM, Kent Tenney wrote: Howdy, I'm aggregating data from several Sqlite files into a Postgres db. The sqlite files are storage for several apps I use: Shotwell, Firefox, Zotero, Banshee ... I just watch and pull from them. I've been using import sqlite3 so far, dumping sql from sqlite, using it to create the Postgres tables. I then add columns to meet my own needs. I now can diff 2 sqlite files, so I know what rows need updating and adding in the Postgres tables. I feel I should be using Sqlalchemy, ok well what problems do you have currently that you'd like to solve ? but have been intimidated by the wealth of choices SA offers. It offers choices in that you can A. use core only or B. the ORM, as well as choices in that it works with whatever kind of schema you'd like, which is the same choice you have anyway, and in this case it seems you have already made. Your app sounds like kind of a nuts and bolts table-to-table thing, i.e. is SQL centric, so using constructs like table.select() and table.insert() could perhaps reduce the verbosity of generating those statements by hand, the Table construct itself can turn the equation of what columns am i dealing with here? into a data driven one (the Table is a datastructure, which stores a list of Column objects - a data driven description of a schema). - comfortable in Python, SQL not so much dont sell yourself short, you're moving rows and adding columns and that's a fair degree of knowledge right there. - pull into the Postgres db from other sources - file system content - email - other db's: Mysql, rdf, ... - feed Sphinxsearch from the Postgres db for all of these you'd probably want to figure out some intermediary format that everything goes into, then goes to the database. Depending on how much this format is hardwired to the SQL schema or not, as well as if you're generally dealing with one big table to store a format versus many tables storing a more normalized structure, would determine how well the ORM may or may not be useful. The ORM is good when you have multiple tables in some hierarchical structure that is to be related to an object hierarchy. For a straight up I'd like the columns in this Excel spreadsheet to become columns in a new database table, it might be overkill. -- 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] sqlalchemy + beaker cache
Hi there,
I am trying to follow the setup in this example
http://www.sqlalchemy.org/trac/browser/examples/beaker_caching to
enable beaker caching in sqlalchemy. However, I ran into an issue.
#1. When I try to cache a relation that happens to be an association
proxy I get the following error:
AttributeError: 'AssociationProxy' object has no attribute 'property'
This is how my query looks like:
def get_user(user_id):
return Session.query(User).\
options(FromCache('default', 'user')).\
options(RelationshipCache('default', 'user_groups',
User.groups)).\
get(user_id)
Anyone ran into this problem?
--
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: Selecting the right table instance in a self referential join
I seem to have solved it by aliasing the first instance too query = sqlalchemy.orm.query(Xxx) *alias = SA.orm.aliased(Xxx)* query = query.join(*(alias,'parent')*, aliased=True) query = query.filter(some criterion) But this basically succeeded by magic when I just tried all sorts of stuff. I can't really understand why an addition of an alias caused the SQL not have an additional alias. Is there a place in the doc that explains this? I don't feel safe with these magical solutions, they tend to break on SA upgrades. -- 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/-/Uv0aBPk1sS4J. 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] Order by ordinal position
Most databases allow ordinal numbers as expressions in order by clauses,
some even in group by clauses. And in earlier versions of SQLAlchemy it
had in fact been possible to express these as integers, e.g.
query.order_by(1, 3, desc(2)).
However, in version 0.7.1 this yields an SQL expression object or
string expected error. To make use of this feature you now need to
write query.order_by('1', '3', desc('2')) which is not so readable and
convenient as the above. Has this been changed by intent?
I know, using ordinals has some drawbacks and may be considered a bad
habit, but they can still be useful in some situations. E.g. it makes it
possible to decorate arbitrary queries of the same structure for use in
an autosuggest AJAX controller with an order_by(1).
-- Christoph
--
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: sqlalchemy + beaker cache
Another issue I run into intermittently is the following:
TypeError: can't pickle function objects
Module myproject.lib.account_api:98 in get_user view
get(user_id)
Module sqlalchemy.orm.query:637 in get view
return self._get(key, ident)
Module sqlalchemy.orm.query:1968 in _get view
return q.one()
Module sqlalchemy.orm.query:1656 in one view
ret = list(self)
Module myproject.model.caching:75 in __iter__ view
return self.get_value(createfunc=lambda: list(Query.__iter__(self)))
Module myproject.model.caching:93 in get_value view
ret = cache.get_value(cache_key, createfunc=createfunc)
Module beaker.cache:214 in get view
return self._get_value(key, **kw).get_value()
Module beaker.container:299 in get_value view
self.set_value(v)
Module beaker.container:332 in set_value view
self.namespace.set_value(self.key, (storedtime, self.expire_argument,
value))
Module beaker.ext.memcached:67 in set_value view
self.mc.set(self._format_key(key), value)
Module memcache:515 in set view
return self._set(set, key, val, time, min_compress_len)
Module memcache:725 in _set view
store_info = self._val_to_store_info(val, min_compress_len)
Module memcache:697 in _val_to_store_info view
pickler.dump(val)
Module copy_reg:70 in _reduce_ex view
raise TypeError, can't pickle %s objects % base.__name__
TypeError: can't pickle function objects
On Jul 27, 2:05 pm, espresso maker espressso.ma...@gmail.com wrote:
Hi there,
I am trying to follow the setup in this
examplehttp://www.sqlalchemy.org/trac/browser/examples/beaker_cachingto
enable beaker caching in sqlalchemy. However, I ran into an issue.
#1. When I try to cache a relation that happens to be an association
proxy I get the following error:
AttributeError: 'AssociationProxy' object has no attribute 'property'
This is how my query looks like:
def get_user(user_id):
return Session.query(User).\
options(FromCache('default', 'user')).\
options(RelationshipCache('default', 'user_groups',
User.groups)).\
get(user_id)
Anyone ran into this problem?
--
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] Selecting the right table instance in a self referential join
On Jul 27, 2011, at 1:14 PM, Moshe C. wrote:
I have the following mapper:
orm.mapper(Xxx,xxx_table, inherits=Resource,
polymorphic_identity=u'xxx',
properties={'children' : orm.relation(Xxx,
backref=orm.backref('parent', remote_side=[Xxx.c.id]),
primaryjoin=xxx_table.c.rid==xxx_table.c.parent_id)})
When I issue the following join, I get as the selected entity the parent side
rather than the child side of the join.
query = sqlalchemy.orm.query(Xxx)
query = query.join('parent', aliased=True)
query = query.filter(some criterion)
The SQL that is generated is as follows:
SELECT anon_1.resource_id AS anon_1_resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT
resource.id AS resource_id
FROM resource INNER JOIN xxx ON resource.id =
xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name .
What I really want is
SELECT resource_id AS resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT
resource.id AS resource_id
FROM resource INNER JOIN xxx ON resource.id =
xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name .
Any help is appreciated.
Let me note the mailing list posting guidelines at
http://www.sqlalchemy.org/support.html#mailinglist . The above snippets are
out of context, incomplete and inaccurate, forcing me to guess and spend time
reproducing a test, which in this case is a SQLAlchemy bug - a self-referential
join between the child table of a joined-table inheriting mapper to itself is
an extremely complicated scenario. The project moves forward with user input
of course so your cooperation is appreciated !
Your issue is #2234 at http://www.sqlalchemy.org/trac/ticket/2234 and a one
line patch is attached to it. It may go to 0.7.3 because 0.7.2 is very delayed
and backlogged with a lot of small issues that need tests completed.
aliased=True applies an adapter to the query which is being inappropriately
extrapolated to the lead entity here - it is ordinarily applied to all
occurrences of the target class subsequent to the join(), but in the case of a
join to a joined table inh, its getting stuck in the polymorphic on list as
well which is inappropriate.
Usage of the alias() function here excludes the target of the join from the
polymorphic on list so the adaptation of the parent is not applied in that
case.
--
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] Order by ordinal position
On Jul 27, 2011, at 5:30 PM, Christoph Zwerschke wrote:
Most databases allow ordinal numbers as expressions in order by clauses, some
even in group by clauses. And in earlier versions of SQLAlchemy it had in
fact been possible to express these as integers, e.g. query.order_by(1, 3,
desc(2)).
However, in version 0.7.1 this yields an SQL expression object or string
expected error. To make use of this feature you now need to write
query.order_by('1', '3', desc('2')) which is not so readable and convenient
as the above. Has this been changed by intent?
Well of course its going through the literal_as_text() function which ensures
what's given can be rendered as SQL. The function was sloppier in 0.6 and we
had user confusion when clearly non-complaint objects were passed through to
various places (such as and_((a, ), (b,)), etc.). order_by() is
accepting SQL expressions, not values, so it is consistent that values should
be coerced to SQL first in this case literal_column(1), etc.
If you want uber-readable, I'd do this:
def ordinal(n):
return literal_column(str(n))
ordinal(1), ordinal(2).desc(), etc.
I'm not actually understanding what ORDER BY 1 is getting you here in any
case or how this relates to AJAX.
I know, using ordinals has some drawbacks and may be considered a bad habit,
but they can still be useful in some situations. E.g. it makes it possible to
decorate arbitrary queries of the same structure for use in an autosuggest
AJAX controller with an order_by(1).
-- Christoph
--
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] Re: Cascade Deletes
Hi,
I am actually using both MySQL and SQLite (one on the dev machine, one
on the server).
Does that make a difference?
On Jul 27, 12:26 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 25, 2011, at 9:47 AM, Aviv Giladi wrote:
I can't seem to make cascade deletes work in sqlalchemy.
I have a parent class (called Rating), a sub class (Subrating) and a
third class called SubRatingProperty.
There is a one-to-one relationship between Rating and SubRating - each
Rating can only have one specific SubRating object. Next, the
SubRatingProperty refers to a row in a table with fixed values. There
are 3 SubRatingProperty entries - property1, property2 and property3.
The SubRating class can have one or more of either property1,
property2 and property3, therefore the relationship is many-to-many (a
SubRatingProperty can have more than one properties, and for example
property1 can be assigned to more than one SubRatingProperty's).
Here is the code that defines all of this:
subrating_subratingproperty_association =
Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id',
Integer, ForeignKey('subrating_properties.id')))
class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
secondary=subrating_subratingproperty_association,
backref=subrating_properties)
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
uselist=False))
Everything works fine, but I can't figure out how to do cascade
deletes. I am deleting Rating objects, and when I do, I would like the
according SubRating object to be deleted, as well as all the entries
in the association table. So deleting Rating1 would delete its
SubRating, as well as all the connection between the SubRating and
SubRatingProperty's.
I have tried adding cascade=all to the relationship call,
you have two relationships() here to build the full chain so you'd need
cascade='all, delete-orphan' on both Rating.subrating as well as
SubRating.subrating_properties (use the backref() function instead of a
string to establish the cascade rule on that end.
and I also
tried adding ondelete=cascade) to the ForeignKey call.
if all the involved FOREIGN KEYs are generated with ON DELETE CASCADE as this
would accomplish, as long as you are not on SQLIte or MySQL MyISAM the
deletes will be unconditional.
--
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] Database-side data mangling
Good day, I'm trying to figure out how to do something similar to the Symmetric Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ SymmetricEncryption), only on the database side, not in Python. I have a suspicion that @compiles decorator may provide a solution, but having trouble understaning how to apply it to my case. For simplicity, let's imagine a field which stores data in upper case but always returns it in lower case... so it needs to generate SQL similar to INSERT INTO tablename VALUES (..., upper(...), ...) on insert and SELECT ..., lower(fieldname) as fieldname, ... FROM tablename on select. I'm using orm and I imagine the final result would look like class MyModel(Base): ... myfield = AlwaysLowercaseColumn(sqlalchemy.String) or class MyModel(Base): ... myfield = sqlalchemy.Column(AlwaysLowercaseString) Thanks, Sergey -- 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] Database-side data mangling
On Jul 27, 2011, at 8:56 PM, Sergey V. wrote: Good day, I'm trying to figure out how to do something similar to the Symmetric Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ SymmetricEncryption), only on the database side, not in Python. I have a suspicion that @compiles decorator may provide a solution, but having trouble understaning how to apply it to my case. For simplicity, let's imagine a field which stores data in upper case but always returns it in lower case... so it needs to generate SQL similar to INSERT INTO tablename VALUES (..., upper(...), ...) this can be assigned, (1) i.e. myobject.fieldname = func.upper(somename) which you can apply with a @validates decorator (2) on insert and SELECT ..., lower(fieldname) as fieldname, ... FROM tablename for this you'd use column_property(). (3) for the SQL functions themselves we're using func (4) 1: http://www.sqlalchemy.org/docs/orm/session.html#embedding-sql-insert-update-expressions-into-a-flush 2: http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators 3: http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes 4: http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.func For a slightly old example of some of this kind of thing (probably more complicated than you'd need here), see the PostGIS example under examples/postgis/. -- 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.
