[sqlalchemy] problem with backref
Hi, I have a strange (for me) problem with backref.
SQLAlchemy version is 0.3.1-1 on Debian Etch.
Here is the code:
from sqlalchemy import *
db = create_engine('postgres://manlio:[EMAIL PROTECTED]/test')
metadata = BoundMetaData(db)
a = Table(
'a', metadata,
Column('x', String),
Column('y', String),
PrimaryKeyConstraint('x', 'y')
)
b = Table(
'b', metadata,
Column('x', String),
Column('y', String),
Column('z', String),
PrimaryKeyConstraint('x', 'y'),
ForeignKeyConstraint(['x', 'y'], ['a.x', 'a.y'])
)
metadata.create_all()
class A(object):
def __init__(self, x, y):
self.x = x
self.y = y
class B(object):
def __init__(self, x, y, z):
self.x = x
self.y = y
self.z = z
mapper(A, a)
mapper(
B, b,
properties={
'a': relation(
A, backref=backref('b', lazy=False, uselist=False,
cascade='all, delete-orphan'),
uselist=False, cascade='all, delete-orphan'
)
}
)
try:
conn = db.connect()
trans = conn.begin()
sess = create_session()
o = A('1', '2')
o.b = B(o.x, o.y, '3')
sess.save(o)
sess.flush()
sess.close()
trans.commit()
trans = conn.begin()
sess = create_session()
sess.update(o)
print o.b.z
del o.b
sess.flush()
sess.close()
trans.commit()
print o.b
s = a.select()
print conn.execute(s).fetchall()
finally:
metadata.drop_all()
The problem here is that when I delete o.b, SQLAlchemy deletes the rows
from 'a' table, too.
Moreover the programs blocks at metadata.drop_all().
Now:
1) when I remove the cascade rule from backref I obtain:
sqlalchemy.exceptions.AssertionError: Dependency rule tried to
blank-out primary key column 'b.x' on instance '[EMAIL PROTECTED]'
2) when I remove the cascade rule from relation I obtain:
sqlalchemy.exceptions.InvalidRequestError: Parent instance class
'__main__.A' is not bound to a Session, and no contextual session is
established; lazy load operation of attribute 'b' cannot proceed
3) when I remove the cascade rule from both backref and relation, I
obtain:
sqlalchemy.exceptions.InvalidRequestError: Parent instance class
'__main__.A' is not bound to a Session, and no contextual session is
established; lazy load operation of attribute 'b' cannot proceed
4) if I set the cascade rule to all I obtain:
sqlalchemy.exceptions.InvalidRequestError: Instance '__main__.B object
at 0xb794068c' is not attached or pending within this session
What's the problem?
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
Manlio Perillo ha scritto: Hi, I have a strange (for me) problem with backref. SQLAlchemy version is 0.3.1-1 on Debian Etch. Here is the code: [...] from sqlalchemy import * trans = conn.begin() sess = create_session() sess.update(o) print o.b.z del o.b A simple workaround is to remove cascade rules from the mapper and to do: sess.delete(o.b) However this require the explicit use of the session. Regards Manlio Perillo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
[italian mode]
Come va Manlio? Ci sente anche qui alla fine :-) io per la fine dell'anno
dovrei riuscire a finire il progetto su cui sto lavorando e potrò riprendere
finalmente il mio lavoro anche con python.it!
[/italian mode]
1st:
In order to use a transaction with a pre-existent connection, you should
bind the session to the connection. This can be done by creating the session
this way:
sess = create_session(bind_to=conn)
This is useful if you want to work both with the SQL layer and with the ORM
layer of SA. If you just want to use the ORM layer (like you appear to do),
forget the 'conn' and 'trans' in your code, and just do
sess = create_session()
trans = sess.create_transaction()
and just use trans.rollback() or trans.commit() in your code.
2nd:
If I got what you want: you want to be able to remove the 'B' instance from
the 'A' instance without removing the 'A' object from the db, right?
session.delete() is the way to go, because that's the way you remove objects
using the SA ORM. And you must pull out the 'cascade='all'' from the 'a'
relation, because if you instruct SA to do this, it will try to remove its
related object (the 'A' instance), which is not what you wont.
Look at this and tell me if it produces the result you would expect:
mapper(A, a)
mapper(
B, b,
properties={
'a': relation(
A, backref=backref('b', lazy=False, uselist=False,
cascade='all'),
uselist=False )
}
)
sess = create_session()
o = A('1', '2')
o.b = B(o.x, o.y, '3')
sess.save(o)
sess.flush()
sess.expunge(o)
sess.clear()
del o
o = sess.query(A).get_by(x=1, y=2)
print o.b.z
sess.delete(o.b)
sess.flush()
sess.close()
print o.b
s = a.select()
print s.execute().fetchall()
--
Alan Franzoni [EMAIL PROTECTED]
-
Togli .xyz dalla mia email per contattarmi.
Remove .xyz from my address in order to contact me.
-
GPG Key Fingerprint (Key ID = FE068F3E):
5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] scalar subqueries
Hi. Is it possible to write something like: SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states The problem is how to setup up the where condition in the subquery: is this possible? Thanks Manlio Perillo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MySQL Functions
Hi there,
I'm looking for a little help using MySQL functions in a WHERE clause
with SQLAlchemy.
The query I'd like to do is something like SELECT * FROM x WHERE
YEAR(FROM_UNIXTIME(timestamp)) == '2006';
Here's how I've tried to implement it:
metadata = MetaData()
articles_table = Table('blog_articles', metadata,
Column('id', Integer, primary_key = True),
Column('timestamp', Integer),
Column('section_id', Integer, ForeignKey('blog_sections.id')),
Column('title', String(255)),
Column('text', TEXT)
)
class Article(object):
def __init__(self, timestamp, title, text):
self.article_timestamp = timestamp
self.article_title = title
self.article_text = text
mapper(Article, articles_table)
db = create_engine(settings.db_string)
session = create_session(bind_to=db)
result =
session.query(Article).select(func.year(func.from_unixtime(Article.c.timestamp))=='2006')
This throws the following error:
(ProgrammingError) function from_unixtime(integer) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
I tried a few things to explicitly cast the row type, but nothing
seemed to work.
This:
session.query(Article).select(func.year(func.from_unixtime(cast(Article.c.timestamp,
DateTime)))=='2006')
threw the following error:
(ProgrammingError) cannot cast type integer to timestamp with time
zone)
Any suggestions?
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
Alan Franzoni ha scritto: [italian mode] Come va Manlio? Ci sente anche qui alla fine :-) io per la fine dell'anno dovrei riuscire a finire il progetto su cui sto lavorando e potrò riprendere finalmente il mio lavoro anche con python.it http://python.it! [/italian mode] Hi Alan! 1st: In order to use a transaction with a pre-existent connection, you should bind the session to the connection. This can be done by creating the session this way: sess = create_session(bind_to=conn) This is useful if you want to work both with the SQL layer and with the ORM layer of SA. If you just want to use the ORM layer (like you appear to do), forget the 'conn' and 'trans' in your code, and just do sess = create_session() trans = sess.create_transaction() and just use trans.rollback() or trans.commit() in your code. In my code I use: trans = conn.begin() sess = create_session(bind_to=conn) The example posted was wrong, sorry. 2nd: If I got what you want: you want to be able to remove the 'B' instance from the 'A' instance without removing the 'A' object from the db, right? Right. session.delete() is the way to go, because that's the way you remove objects using the SA ORM. Yes, I just discovered this by myself. The problem is that it requires the explicit use of the session. In my case this means that I can not delete the object inside a method of my class. class A(object): def enableB(enable=True): if enable: self.b = B(...) else: del self.b Not a big problem, however. Thanks and regards Manlio Perillo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
The problem is that it requires the explicit use of the session. In my case this means that I can not delete the object inside a method of my class. class A(object): def enableB(enable=True): if enable: self.b = B(...) else: del self.b Not a big problem, however. There's the 'threadlocal' that should let you work as you like, but I've read its use is discouraged because it could lead to strange bugs. BTW, couldn't you just pass your session to the methods requiring it? class A(object): def enableB(enable=True, session): if enable: self.b = B(...) else: session.delete(self.b) -- Alan Franzoni [EMAIL PROTECTED] - Togli .xyz dalla mia email per contattarmi. Remove .xyz from my address in order to contact me. - GPG Key Fingerprint (Key ID = FE068F3E): 5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL Functions
i dont know much about FROM_TIMESTAMP but from the MySQL docs it seems like it deals with a UNIX_TIMESTAMP value, which perhaps is different from an integer (although it looks like an int from the examples. maybe its a long?). The steps to make this work are to get it to work from your MySQL command line first. then use echoing in SA to insure that its producing the identical SQL to the working string. as a last resort, sometimes you have to make small test programs using straight DBAPI to insure that theres no weirdness happening with MySQLdb. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
the original mapping essentially expressed this relationship:
A -- cascade=all, delete-orphan -- B
in both directions. What that means is, there cannot be an A with no B
in the database, and there cannot be a B with no A in the database,
i.e. its either A-B or both will be deleted. its like an oxygen
molecule, or something.
what you need to do is decide which of A and B can exist on its own
without a parent relationship. since you want to delete rows from B
and not A, that would indicate that the mapping should be:
mapper(A, a)
mapper(
B, b,
properties={
'a': relation(
A, backref=backref('b', lazy=False, uselist=False,
cascade='all, delete-orphan'),
uselist=False
)
}
)
i.e. the delete-orphan cascade is only in the direction from A-B.
the cascade from B-A is left at its default value of save-update, so
delete operations dont propigate from B's to A's.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL Functions
The initial query I posted definitely works--I've been using something very much like it for a long time in my existing application. I've only recently discovered SA, though, and I'm trying to incorporate it into my design. FROM_TIMESTAMP can definitely take an integer input. Anyway, 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL Functions
i just tried out your example and it executes fine using mysql 5, query output is: SELECT blog_articles.timestamp AS blog_articles_timestamp, blog_articles.text AS blog_articles_text, blog_articles.id AS blog_articles_id, blog_articles.title AS blog_articles_title FROM blog_articles WHERE year(from_unixtime(blog_articles.timestamp)) = %s ORDER BY blog_articles.id params: ['2006'] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
