Re: [sqlalchemy] Anybody have twophase/zope.sqlalchemy/MySQL working?
On Thursday, 9 January 2014 09:41:40 UTC-8, Jeff Dairiki wrote:
Okay, I've traced things out a bit more.
If the session state is not STATUS_INVALIDATED (aka STATUS_CHANGED),
SessionDataManager.commit() does a self._finish('no work'). That is
where self.tx gets set to None (this --- correctly --- then
causes .tpc_vote() and .tpc_finish() to be no-ops.)
So here's the crux of the biscuit: in two-phase-commit mode (at least
with MySQL) the sqlalchemy session (or session transaction) must be either
committed or explicitly rolled back before it is closed.
SessionDataManager.commit() does not do a rollback.
Example code:
import sqlalchemy as sa
engine = sa.create_engine('mysql://guest@furry/test',
echo='debug',
echo_pool='debug')
Sess = sa.orm.sessionmaker(bind=engine, twophase=True)
sess = Sess()
sess.query(sa.null()).scalar()
#sess.rollback()
sess.close()
Edited log output:
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 checked out from pool
INFO:sqlalchemy.engine.base.Engine:BEGIN TWOPHASE (implicit)
INFO:sqlalchemy.engine.base.Engine:XA BEGIN %s
INFO:sqlalchemy.engine.base.Engine:('_sa_a38de3b7234d5fe8ad55d2bb13ec195c',)
INFO:sqlalchemy.engine.base.Engine:SELECT NULL AS anon_1
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 rollback-on-return
INFO:sqlalchemy.pool.QueuePool:Invalidate connection
_mysql.connection open to 'furry' at 29a3370 (reason:
OperationalError:(1399, 'XAER_RMFAIL: The command cannot be executed when
global transaction is in the ACTIVE state'))
DEBUG:sqlalchemy.pool.QueuePool:Closing connection _mysql.connection
open to 'furry' at 29a3370
Here, no attempt is made to terminate the two-phase (XA) transaction
until the connection is returned to the pool, at which point a
plain one-phase 'ROLLBACK' is issued. MySQL does not like this,
thus the XAER_RMFAIL error.
Uncommenting the 'sess.rollback()' in the above example results in an
'XA END and 'XA ROLLBACK' being emitted before the connection is
returned to the pool, properly ending the two-phase (XA) transaction.
This eliminates the XAER_RMFAIL error, and results in proper recycling
of the pooled connection.
Yup, your analysis here looks correct to me:
- The SessionTransaction.close() does not call transaction.close() and in
turn transaction.rollback() because the connection's autoclose is True as
``conn is not bind``
-
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/orm/session.py#L431
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/orm/session.py#L297
When the connection is returned to the pool, it will rollback-on-return and
that's throwing the exception as it is simply calling
``pool._dialect.do_rollback(fairy)`` -
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/pool.py#L408
When you call session.rollback() the engine's ``do_rollback_twophase()`` is
being called which executes the appropirate XA ROLLBACK :xid
-
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/dialects/mysql/base.py#L2086
I think the ``pool._dialect.do_rollback`` and ``pool._dialect.do_commit``
calls in _finalize_fairy need to take account of the twophase nature of the
connection.
I don't think zope.sqlalchemy should be involved here, as the pool
configures how a connection should be closed with reset_on_return:
http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#sqlalchemy.pool.QueuePool.__init__.params.reset_on_return
Worth reporting as a SQLAlchemy bug, though fixing it cleanly looks rather
tricky.
You might be able to rig something together with event listeners, but it
looks tricky. For the pools reset event you won't have access to the xid
and is_prepared of the TwoPhaseTransaction, so you'll need to store these
on the connection during:
http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.PoolEvents.reset
http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.begin_twophase
http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.prepare_twophase
I think this should do it (untested):
from sqlalchemy import event
@event.listens_for(SomeEngine, 'begin_twophase')
def receive_begin_twophase(conn, xid):
conn._note_xid_prepared = (xid, False)
@event.listens_for(SomeEngine, 'prepare_twophase')
def receive_prepare_twophase(conn, xid):
conn._note_xid_prepared = (xid, True)
@event.listens_for(SomeEngineOrPool, 'reset')
def receive_reset(dbapi_con, con_record):
conn = con_record.connection
if not hasattr(conn, '_note_xid_prepared'):
Re: [sqlalchemy] Multiple inheritance issue
Thank you very much MichaeI was in fact thinking that it was possible to
not found support for that feature. But as you said, the compiler didn't
shout at me and the application was running fine so I was thinking that I
was doing things right.
I'll go for the JOIN.
Best,
Enrico
On Thursday, January 9, 2014 8:15:14 PM UTC+1, Michael Bayer wrote:
On Jan 9, 2014, at 11:32 AM, Enrico Bottani bei...@mac.com javascript:
wrote:
Hello guys,
I'm building a web service to provide data to our SDK and I'm facing a
wired problem. I have created 4 different models for my needs, a base class
that is called *Unit*, two derivatives from the base class *VideoUnit*,
*QuestionUnit*, and the last class is a derivative of both *VideoUnit*and
*QuestionUnit*, that is called, with lot of fantasy, *VideoQuestionUnit*.
Everything was working fine till the point I was trying to serialize this
data to be transmitted with JSON.
I successfully gather JSON from *VideoUnit* and *QuestionUnit *(thanks to
plaeshttp://stackoverflow.com/questions/7102754/jsonify-a-sqlalchemy-result-set-in-flask)
but now I can't figure out why when I try to make the same thing with
*VideoQuestioUnit
*the program crash telling me: *KeyError: 'questions'*
I can imagine that there is something wrong when *VideoQuestionUnit* try
to access the *questions* data declared in *QuestionUnit*(also my dog
get that I know), but I don't understand what's wrong with that.
Did someone have an idea of what I'm doing wrong?
first of all, SQLAlchemy’s inheritance mapping system has no direct
support for multiple inheritance at all. True multi-table multiple
inheritance in SQL is a highly esoteric and complex pattern and I doubt
SQLAlchemy is ever going to go there, at least fully; just getting single
hierarchy inheritance to work fully has taken many years and was enormously
complicated. While I see here that at least VideoQuestionUnit doesn’t
have a table of its own, still the fact that it is subclassing two other
joined-inh subclasses isn’t something the ORM has any idea what to do with.
Loading a VideoQuestionUnit means it would need to join to both of the
inherited tables, like a polymorphic load does, but there’s no mechanism to
produce one object deriving from more than one mapped class. I’m surprised
the mapping doesn’t just fail immediately.
secondly, well actually I’m not sure there’s a “secondly” here :). I
guess the way you can load and persist VideoQuestionUnit objects is by
mapping it to a JOIN of all three tables at once - and you’d also need to
probably add “concrete=True” to the mapping so that it doesn’t attempt to
use any of the superclass mappings for persistence or querying.Not
something I’ve tried though it might be workable.
*CODE:*
class Unit(db.Model):
id = db.Column(db.Integer, unique = True, primary_key = True)
name = db.Column(db.String(128), index = True, unique = False)
type = db.Column(db.SmallInteger)
brand_id = db.Column(db.Integer, db.ForeignKey('brand.id'))
@property
def serialize(self):
print Serialize on superclass
pass
class VideoUnit(Unit):
uri = db.Column(db.String(256), index = False, unique = False)
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Link' : self.uri
}
class QuestionUnit(Unit):
questions = db.relationship('Question', backref = 'QuestionUnit', lazy
= 'dynamic')
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Questions' : self.serialize_questions
}
@property
def serialize_questions(self):
return [item.serialize for item in self.questions]
class VideoQuestionUnit(VideoUnit, QuestionUnit):
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Link' : self.uri,
'Questions' : self.serialize_questions
}
@property
def serialize_questions(self):
return [item.serialize for item in self.questions]
Thanks in advance,
Enrico
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
[sqlalchemy] Polymorphic joined-table inheritance
Hello all,
I'm trying to use SQLAlchemy inheritance in a (company's) project and I
cannot seem to make it work.
First, the versions:
- Ubuntu: 12.10
- Python: 2.7.3
- SQLAlchemy: 0.9.1
- PostgreSQL: 9.1
- sqlite: 3.7.13
- Pyramid: 1.4.2
All of the problems happen in both PostgreSQL and sqlite.
I have a ClientVip class that inherits from Client. This is the trimmed
code:
##
class Client(Base):
__tablename__ = 'client'
id = Column(Integer, primary_key=True)
ctype = Column(String(16), nullable=False)
webaddid = Column(Integer, ForeignKey('web.id'), nullable=False)
parentid = Column(Integer, ForeignKey('client.id'), nullable=True)
refinternal = Column(Integer, unique=True, nullable=False)
...
notestx = Column(String(256), nullable=True)
notesclient = Column(String(256), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'basic',
'polymorphic_on': ctype
}
def __init__(self, webaddid=None, parentid=None, refinternal=None,
...
notestx=None, notesclient=None):
# self.ctype = basic ### This is actually commented, but might
be important
self.webaddid = webaddid
self.parentid = parentid
self.refinternal = refinternal
...
self.notesclient = notesclient
class ClientVip(Client):
__tablename__ = 'clientvip'
id = Column(Integer, ForeignKey('client.id'), primary_key=True)
legalname = Column(String(128), nullable=True)
commercialname = Column(String(64), nullable=True)
...
isclienttop = Column(Boolean, nullable=False)
numlisttop = Column(Integer, nullable=True)
# Fill the column 'ctype' with the value 'vip'
__mapper_args__ = {
'polymorphic_identity':'vip',
}
def __init__(self, clientid=None, legalname=None, commercialname=None,
...
isclienttop=False, numlisttop=None, **kwargs):
# First initialize the basic client
super(ClientVip, self).__init__(**kwargs)
# And then the VIP client
# self.ctype = vip### This is actually commented, but might
be important
self.clientid = clientid
self.legalname = legalname
self.commercialname = commercialname
...
self.isclienttop = isclienttop
self.numlisttop = numlisttop
##
I have checked the code in
-
http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html?highlight=inheritance#module-examples.inheritance
-
http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/inheritance/joined.html
and I can't find anything different, but maybe are my eyes.
Problem: Querying client and clientvip
---
With this code I try to query all the clients [ clients =
DBSession.query(Client) ]
and this is where the problems start, because the query it is issuing is:
##
SELECT client.id AS client_id, client.ctype AS client_ctype,
client.refinternal AS client_refinternal,
...
client.notestx AS client_notestx, client.notesclient AS client_notesclient
FROM client
WHERE client.ctype IN (NULL)
##
Why is there a where clause at all? Should not the query finish with the
FROM client line?
And besides, why does it think that ctype must be NULL (instead of basic
or vip, for instance)?
If a force to query with a filter [ clients =
DBSession.query(Client).filter(Client.ctype == basic) ]
this is the query I get:
##
SELECT client.id AS client_id, client.ctype AS client_ctype,
client.refinternal AS client_refinternal,
...
client.notestx AS client_notestx, client.notesclient AS client_notesclient
FROM client
WHERE client.ctype = %(ctype_1)s AND client.ctype IN (NULL)
##
which also looks for NULL values in the where clause.
And what happens if I query the ClientVip?
Well, there are no where clauses, so we are good:
##
SELECT clientvip.id AS clientvip_id, client.id AS client_id, client.ctype
AS client_ctype, client.refinternal AS client_refinternal,
...
clientvip.isclienttop AS clientvip_isclienttop, clientvip.numlisttop AS
clientvip_numlisttop
FROM client JOIN clientvip ON client.id = clientvip.id
##
Appendix: Inserting client
-
You might have seen that in the __init__ methods I have commented out the
self.ctype = 'basic'
When this line is commented out (as it should) and I try to insert a
Client, I get the following error:
##
IntegrityError: (IntegrityError) null value in column ctype violates
not-null constraint
##
which leads me to think that the polymorphism I am trying to get is not
working properly, becasue
I shouldn't need to force a value on the ctype column.
A plea for help
-
I have been looking at this code for quite some time and I can't figure out
what I am missing.
If any of you have any idea of what I could be possibly doing wrong, or any
ideas that I could
try, I will be very very happy to hear them, because I have run out of
ideas
Re: [sqlalchemy] Why ORM objects in a session expire immediately after a failure in flush?
On Thu, Jan 9, 2014 at 9:59 PM, Michael Bayer mike...@zzzcomputing.com wrote: I do notice that you’re catching an IntegrityError.The typical pattern when writing code that wants to catch these and then continue is to run the individual set of questionable operations within a SAVEPOINT, that is a begin_nested()/commit() block. Recent versions of SQLAlchemy have the behavior such that when a flush() exception rolls back the SAVEPOINT, objects that were not modified within the SAVEPOINT are not expired; only those objects that changed within the save point’s scope do. How recent does recent mean there? (just curious) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
I'm developing an application using the ORM, and I am getting into all
sorts of trouble with what I think should be a pretty simple data model.
I've tried following the ORM tutorial from the docs, but it seems to get me
confused every time I try. So I'm looking for something else that maybe
takes a different approach. Or maybe someone can point out what's wrong for
me in the following - but even then, pointers to other tutorial material
would be useful, as I don't really want to end up pestering the list every
time I hit an issue :-)
My data model is fairly straightforward. I have a Package class, with a
name. I then have a Release class - each Release is associated with a
single package, and has a unique version. Releases have a few children -
Classifiers, Dependencies, and URLs. All of these are multi-valued with no
natural key (at least, not one worth worrying about). There is some extra
data associated with Releases and URLs, but that's not too important.
(People may recognise this as the PyPI data model). This is a pretty
trivial parent/child one-to-many hierarchy, and I didn't expect it to be
hard to model.
The obvious (to me!) model is basically (trimmed down a bit):
class Package(Base):
__tablename__ = 'packages'
# Use a synthetic key, as package names can change
id = Column(Integer, primary_key=True)
name = Column(String, unique=True, nullable=False)
releases = relationship(Release, backref=package,
cascade=all, delete-orphan)
class Release(Base):
__tablename__ = 'releases'
id = Column(Integer, primary_key=True)
package_id = Column(Integer, ForeignKey('packages.id'), nullable=False)
version = Column(String, nullable=False) # Unique within package
classifiers = relationship(Classifier, backref=release,
cascade=all, delete-orphan)
class Classifier(Base):
__tablename__ = 'classifiers'
id = Column(Integer, primary_key=True)
release_id = Column(Integer, ForeignKey('releases.id'), nullable=False)
classifier = Column(String, nullable=False)
So far, so good. But if I want to create a new Release, things get messy.
This is my basic function:
def new_release(package, version, data):
r = Release(version)
r.package = Package(package)
# Populate the data in r, and create child items
return r
It's that package reference that messes me up. If the release is for a new
package, then when I merge the release into the session, the package is
created. But if it's for an existing package, a new package is created
(which gives a constraint error if the package name is unique, and
duplicates if it's not) rather than the session recognising that it's an
existing package and linking the release to it.
If I was working at the SQL core level, I'd expect to have to query for the
package and create it if it wasn't there. But I'm not sure I can do that
with a session, as things get cached in memory by the unit of work stuff,
and I don't know if a query for the release could miss a package that's
pending insertion, or if the query could cause auto-flushing which might
cause other issues (either with performance or integrity). Because the
session is managing the in-memory caching and the transaction management
by magic, I don't want to interfere with its mechanisms any more than I
have to. If I have to keep track of what's gone to the database, and query
for existing instances and manage the transactions, I probably should just
use the SQL layer directly (I have a lot of experiences with databases, but
very little with ORMs, so pure DB code isn't too scary for me, but on the
other hand I don't know what benefits the ORM should be giving me that I'm
not seeing).
Is this an application that doesn't actually benefit from the ORM? Or am I
using it wrongly, and my problems come from misunderstanding the way it
should be used? As I say, I've a lot of database experience but very little
with ORMs, so maybe I have an unbalanced view of how much data management
the ORM should be able to handle for me.
The particular problem here is what's affecting me right now - but I'd be
even more interested in a good ORM for experienced SQL developers
tutorial that tells me how the ORM differs from the core level (and where
its benefits lie).
Thanks,
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Anybody have twophase/zope.sqlalchemy/MySQL working?
On Jan 10, 2014, at 3:32 AM, Laurence Rowe laurencer...@gmail.com wrote:
On Thursday, 9 January 2014 09:41:40 UTC-8, Jeff Dairiki wrote:
Okay, I've traced things out a bit more.
If the session state is not STATUS_INVALIDATED (aka STATUS_CHANGED),
SessionDataManager.commit() does a self._finish('no work'). That is
where self.tx gets set to None (this --- correctly --- then
causes .tpc_vote() and .tpc_finish() to be no-ops.)
So here's the crux of the biscuit: in two-phase-commit mode (at least
with MySQL) the sqlalchemy session (or session transaction) must be either
committed or explicitly rolled back before it is closed.
SessionDataManager.commit() does not do a rollback.
Example code:
import sqlalchemy as sa
engine = sa.create_engine('mysql://guest@furry/test',
echo='debug',
echo_pool='debug')
Sess = sa.orm.sessionmaker(bind=engine, twophase=True)
sess = Sess()
sess.query(sa.null()).scalar()
#sess.rollback()
sess.close()
Edited log output:
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 checked out from pool
INFO:sqlalchemy.engine.base.Engine:BEGIN TWOPHASE (implicit)
INFO:sqlalchemy.engine.base.Engine:XA BEGIN %s
INFO:sqlalchemy.engine.base.Engine:('_sa_a38de3b7234d5fe8ad55d2bb13ec195c',)
INFO:sqlalchemy.engine.base.Engine:SELECT NULL AS anon_1
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 rollback-on-return
INFO:sqlalchemy.pool.QueuePool:Invalidate connection _mysql.connection
open to 'furry' at 29a3370 (reason: OperationalError:(1399, 'XAER_RMFAIL:
The command cannot be executed when global transaction is in the ACTIVE
state'))
DEBUG:sqlalchemy.pool.QueuePool:Closing connection _mysql.connection
open to 'furry' at 29a3370
Here, no attempt is made to terminate the two-phase (XA) transaction
until the connection is returned to the pool, at which point a
plain one-phase 'ROLLBACK' is issued. MySQL does not like this,
thus the XAER_RMFAIL error.
Uncommenting the 'sess.rollback()' in the above example results in an
'XA END and 'XA ROLLBACK' being emitted before the connection is
returned to the pool, properly ending the two-phase (XA) transaction.
This eliminates the XAER_RMFAIL error, and results in proper recycling
of the pooled connection.
Yup, your analysis here looks correct to me:
- The SessionTransaction.close() does not call transaction.close() and in
turn transaction.rollback() because the connection's autoclose is True as
``conn is not bind`` -
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/orm/session.py#L431
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/orm/session.py#L297
When the connection is returned to the pool, it will rollback-on-return and
that's throwing the exception as it is simply calling
``pool._dialect.do_rollback(fairy)`` -
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/pool.py#L408
When you call session.rollback() the engine's ``do_rollback_twophase()`` is
being called which executes the appropirate XA ROLLBACK :xid -
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/dialects/mysql/base.py#L2086
I think the ``pool._dialect.do_rollback`` and ``pool._dialect.do_commit``
calls in _finalize_fairy need to take account of the twophase nature of the
connection.
maybe. I was thinking, is the Session really doing the right thing here by
not getting involved, but I think yeah that still might be appropriate. so
we’d need to carry along some extra information about the transaction with the
connection so that do_rollback() and do_commit() can pick up on that.
or maybe session.close() needs to do some bookkeeping with the
SessionTransaction. not sure.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
It sounds to me like the problem you're having has to do with how you are
getting the reference to the package, which isn't shown in your example.
How are you getting it?
The session doesn't do anything by magic, even if it seems that way
sometimes. It just manages things behind the scenes.
If you want a new Package, create a new Package object. If you want an
exisiting package, query for it. Just like you would in bare SQL code.
If you don't know which you need, try searching and create it if it isn't
found. I usually do something like:
try:
pkg = session.query(Package).filter(condition-goes-here)
except sa.orm.exc.NotFound:
pkg = Package()
# populate attributes and add to session
This is off the top of my head and from memory, so beware of possible
(probable) errors, but hopefully you get the idea.
Unfortunately (fortunately?) the SQLAlchemy docs are good enough that there
isn't a huge impetus for people outside the project to write tutorials,
blog posts, etc. I'd like to see more of that kind of thing as well, but
everyone has limited time, I guess.
Good luck!
On Fri, Jan 10, 2014 at 7:44 AM, Paul Moore p.f.mo...@gmail.com wrote:
I'm developing an application using the ORM, and I am getting into all
sorts of trouble with what I think should be a pretty simple data model.
I've tried following the ORM tutorial from the docs, but it seems to get me
confused every time I try. So I'm looking for something else that maybe
takes a different approach. Or maybe someone can point out what's wrong for
me in the following - but even then, pointers to other tutorial material
would be useful, as I don't really want to end up pestering the list every
time I hit an issue :-)
My data model is fairly straightforward. I have a Package class, with a
name. I then have a Release class - each Release is associated with a
single package, and has a unique version. Releases have a few children -
Classifiers, Dependencies, and URLs. All of these are multi-valued with no
natural key (at least, not one worth worrying about). There is some extra
data associated with Releases and URLs, but that's not too important.
(People may recognise this as the PyPI data model). This is a pretty
trivial parent/child one-to-many hierarchy, and I didn't expect it to be
hard to model.
The obvious (to me!) model is basically (trimmed down a bit):
class Package(Base):
__tablename__ = 'packages'
# Use a synthetic key, as package names can change
id = Column(Integer, primary_key=True)
name = Column(String, unique=True, nullable=False)
releases = relationship(Release, backref=package,
cascade=all, delete-orphan)
class Release(Base):
__tablename__ = 'releases'
id = Column(Integer, primary_key=True)
package_id = Column(Integer, ForeignKey('packages.id'),
nullable=False)
version = Column(String, nullable=False) # Unique within package
classifiers = relationship(Classifier, backref=release,
cascade=all, delete-orphan)
class Classifier(Base):
__tablename__ = 'classifiers'
id = Column(Integer, primary_key=True)
release_id = Column(Integer, ForeignKey('releases.id'),
nullable=False)
classifier = Column(String, nullable=False)
So far, so good. But if I want to create a new Release, things get messy.
This is my basic function:
def new_release(package, version, data):
r = Release(version)
r.package = Package(package)
# Populate the data in r, and create child items
return r
It's that package reference that messes me up. If the release is for a new
package, then when I merge the release into the session, the package is
created. But if it's for an existing package, a new package is created
(which gives a constraint error if the package name is unique, and
duplicates if it's not) rather than the session recognising that it's an
existing package and linking the release to it.
If I was working at the SQL core level, I'd expect to have to query for
the package and create it if it wasn't there. But I'm not sure I can do
that with a session, as things get cached in memory by the unit of work
stuff, and I don't know if a query for the release could miss a package
that's pending insertion, or if the query could cause auto-flushing which
might cause other issues (either with performance or integrity). Because
the session is managing the in-memory caching and the transaction
management by magic, I don't want to interfere with its mechanisms any
more than I have to. If I have to keep track of what's gone to the
database, and query for existing instances and manage the transactions, I
probably should just use the SQL layer directly (I have a lot of
experiences with databases, but very little with ORMs, so pure DB code
isn't too scary for me, but on the other hand I don't know what benefits
the ORM should be giving me that I'm not seeing).
Is this an application that doesn't
Re: [sqlalchemy] Polymorphic joined-table inheritance
I'm not really sure, but you probably need to be calling super() somewhere
in Client.__init__, which you don't seem to be doing.
The main difference between your code and what I would usually do is that I
almost never override __init__ in my models. I expect some of the setup
for inheritance happens in DeclarativeBase (Base in your example) and that
isn't getting called, so never happens.
Try removing your __init__ methods as an easy troubleshooting step, and if
that works you know your problem is there.
On Fri, Jan 10, 2014 at 6:08 AM, david.ceresu...@gmail.com wrote:
Hello all,
I'm trying to use SQLAlchemy inheritance in a (company's) project and I
cannot seem to make it work.
First, the versions:
- Ubuntu: 12.10
- Python: 2.7.3
- SQLAlchemy: 0.9.1
- PostgreSQL: 9.1
- sqlite: 3.7.13
- Pyramid: 1.4.2
All of the problems happen in both PostgreSQL and sqlite.
I have a ClientVip class that inherits from Client. This is the trimmed
code:
##
class Client(Base):
__tablename__ = 'client'
id = Column(Integer, primary_key=True)
ctype = Column(String(16), nullable=False)
webaddid = Column(Integer, ForeignKey('web.id'), nullable=False)
parentid = Column(Integer, ForeignKey('client.id'), nullable=True)
refinternal = Column(Integer, unique=True, nullable=False)
...
notestx = Column(String(256), nullable=True)
notesclient = Column(String(256), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'basic',
'polymorphic_on': ctype
}
def __init__(self, webaddid=None, parentid=None, refinternal=None,
...
notestx=None, notesclient=None):
# self.ctype = basic ### This is actually commented, but might
be important
self.webaddid = webaddid
self.parentid = parentid
self.refinternal = refinternal
...
self.notesclient = notesclient
class ClientVip(Client):
__tablename__ = 'clientvip'
id = Column(Integer, ForeignKey('client.id'), primary_key=True)
legalname = Column(String(128), nullable=True)
commercialname = Column(String(64), nullable=True)
...
isclienttop = Column(Boolean, nullable=False)
numlisttop = Column(Integer, nullable=True)
# Fill the column 'ctype' with the value 'vip'
__mapper_args__ = {
'polymorphic_identity':'vip',
}
def __init__(self, clientid=None, legalname=None, commercialname=None,
...
isclienttop=False, numlisttop=None, **kwargs):
# First initialize the basic client
super(ClientVip, self).__init__(**kwargs)
# And then the VIP client
# self.ctype = vip### This is actually commented, but might
be important
self.clientid = clientid
self.legalname = legalname
self.commercialname = commercialname
...
self.isclienttop = isclienttop
self.numlisttop = numlisttop
##
I have checked the code in
-
http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html?highlight=inheritance#module-examples.inheritance
-
http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/inheritance/joined.html
and I can't find anything different, but maybe are my eyes.
Problem: Querying client and clientvip
---
With this code I try to query all the clients [ clients =
DBSession.query(Client) ]
and this is where the problems start, because the query it is issuing is:
##
SELECT client.id AS client_id, client.ctype AS client_ctype,
client.refinternal AS client_refinternal,
...
client.notestx AS client_notestx, client.notesclient AS client_notesclient
FROM client
WHERE client.ctype IN (NULL)
##
Why is there a where clause at all? Should not the query finish with the
FROM client line?
And besides, why does it think that ctype must be NULL (instead of basic
or vip, for instance)?
If a force to query with a filter [ clients =
DBSession.query(Client).filter(Client.ctype == basic) ]
this is the query I get:
##
SELECT client.id AS client_id, client.ctype AS client_ctype,
client.refinternal AS client_refinternal,
...
client.notestx AS client_notestx, client.notesclient AS client_notesclient
FROM client
WHERE client.ctype = %(ctype_1)s AND client.ctype IN (NULL)
##
which also looks for NULL values in the where clause.
And what happens if I query the ClientVip?
Well, there are no where clauses, so we are good:
##
SELECT clientvip.id AS clientvip_id, client.id AS client_id, client.ctype
AS client_ctype, client.refinternal AS client_refinternal,
...
clientvip.isclienttop AS clientvip_isclienttop, clientvip.numlisttop AS
clientvip_numlisttop
FROM client JOIN clientvip ON client.id = clientvip.id
##
Appendix: Inserting client
-
You might have seen that in the __init__ methods I have commented out the
self.ctype =
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
On Friday, 10 January 2014 16:27:12 UTC, Kevin H wrote: It sounds to me like the problem you're having has to do with how you are getting the reference to the package, which isn't shown in your example. How are you getting it? The new_release() function is what I do - I create a new Package object. I was under the impression that when I did a session.merge() that would match up existing objects. I'm probably being naive in that, though... The session doesn't do anything by magic, even if it seems that way sometimes. It just manages things behind the scenes. Hmm - managing things behind the scenes is what I was meaning by magic. The problem is that I can't find any clear reference about what is, and what is not, managed behind the scenes. As I said, it's more about my understanding (and hence about me knowing what code I need to write) than about SQLAlchemy doing anything wrong. If you want a new Package, create a new Package object. If you want an exisiting package, query for it. Just like you would in bare SQL code. If you don't know which you need, try searching and create it if it isn't found. I usually do something like: try: pkg = session.query(Package).filter(condition-goes-here) except sa.orm.exc.NotFound: pkg = Package() # populate attributes and add to session OK. That's essentially what I was hoping to avoid. Largely because of that query - I may not yet have committed the package to the database. For a more extended example, suppose I do the following: p = Package(pip) session.merge(p) # I could do session.add here, but I'm not 100% clear why just doing merge isn't better in case I'm not sure if pip is already present Somewhere a little later, in other code where I haven't committed yet, but I don't have a reference to p available: r = Release(1.5) r.package = Package(pip) Can I query for pip here? There's been no commit yet, and there may not even have been a flush (I've had problems with null foreign keys so I've had to force autoflush off in a few places). Essentially, will a query locate an object that's in session.new but which hasn't been flushed to the database yet? This is the crux of my issue. I really don't understand why I'm getting null foreign keys on autoflush, but switching autoflush off seems to fix it. But maybe that's what's giving me these issues, so maybe I need to turn autoflush back on. But then what do I do about my noll FKs? Unfortunately (fortunately?) the SQLAlchemy docs are good enough that there isn't a huge impetus for people outside the project to write tutorials, blog posts, etc. I'd like to see more of that kind of thing as well, but everyone has limited time, I guess. Without wishing to seem critical, I find the ORM docs pretty difficult to follow. They seem to jump around between schema design (DDL) and usage (select and DML) in a pretty haphazard fashion, and the information about transactional control and session management seems to be spread around the various sections. That's basically just my perspective, and may reflect my experience, but it is frustrating. Agreed entirely about people having limited time, and the docs are certainly far better than a lot that's around. Maybe I'll just have to have a thorough read of the docs before I carry on coding. Feels like I'm making more problems than I'm solving right now. Or maybe stick to what I know and drop back to the SQL core stuff. Thanks for the help, Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
On Fri, Jan 10, 2014 at 10:55 AM, Paul Moore p.f.mo...@gmail.com wrote: On Friday, 10 January 2014 16:27:12 UTC, Kevin H wrote: It sounds to me like the problem you're having has to do with how you are getting the reference to the package, which isn't shown in your example. How are you getting it? The new_release() function is what I do - I create a new Package object. I was under the impression that when I did a session.merge() that would match up existing objects. I'm probably being naive in that, though... I don't think merge() does what you think it does. (Beware...I don't ever really use it, so I am not an expert in it's use). My understanding is that merge takes an input instance, looks it up or creates a new instance based on the primary key of the input instance, and copies data from the input instance to the lookedup/created one, and then returns that. If your input instance doesn't have a primary key...well, that doesn't seem to make sense and I really don't know what happens. maybe this is the root of your problem? Personally, I stay away from merge() if at all possible, since it can be way more complex than I want to deal with. I would recommend trying to do things in the simplest way you can get away with, and see whether that can help you increase your understanding. Try using add() and see if that helps you grok a bit more. The session doesn't do anything by magic, even if it seems that way sometimes. It just manages things behind the scenes. Hmm - managing things behind the scenes is what I was meaning by magic. The problem is that I can't find any clear reference about what is, and what is not, managed behind the scenes. As I said, it's more about my understanding (and hence about me knowing what code I need to write) than about SQLAlchemy doing anything wrong. If you want a new Package, create a new Package object. If you want an exisiting package, query for it. Just like you would in bare SQL code. If you don't know which you need, try searching and create it if it isn't found. I usually do something like: try: pkg = session.query(Package).filter(condition-goes-here) except sa.orm.exc.NotFound: pkg = Package() # populate attributes and add to session OK. That's essentially what I was hoping to avoid. Largely because of that query - I may not yet have committed the package to the database. I don't think it has to be committed yet, it just has to exist in the session. See below. For a more extended example, suppose I do the following: p = Package(pip) session.merge(p) # I could do session.add here, but I'm not 100% clear why just doing merge isn't better in case I'm not sure if pip is already present Because p doesn't have a primary key yet? I'm kind of guessing...but I really think you need a pkey on the input object to merge(). Somewhere a little later, in other code where I haven't committed yet, but I don't have a reference to p available: r = Release(1.5) r.package = Package(pip) Can I query for pip here? There's been no commit yet, and there may not even have been a flush (I've had problems with null foreign keys so I've had to force autoflush off in a few places). Essentially, will a query locate an object that's in session.new but which hasn't been flushed to the database yet? If you use session.add() then yes, you can query for pip there. The ORM will find it. On Postgres, at least, it seems that when you add it to the session, it grabs a value from the sequence used for the primary key, but doesn't yet add the actual row (in this case...other cases might be more complex) for the package. So the session knows what the primary key will be once you commit the package, and can use that to find the package. At least that's how it looks like it works, and how I think about it. I don't claim to know what it actually does. This is the crux of my issue. I really don't understand why I'm getting null foreign keys on autoflush, but switching autoflush off seems to fix it. But maybe that's what's giving me these issues, so maybe I need to turn autoflush back on. But then what do I do about my noll FKs? I'm not sure what to say about this. Probably we're hitting the limits of my understanding here. Unfortunately (fortunately?) the SQLAlchemy docs are good enough that there isn't a huge impetus for people outside the project to write tutorials, blog posts, etc. I'd like to see more of that kind of thing as well, but everyone has limited time, I guess. Without wishing to seem critical, I find the ORM docs pretty difficult to follow. They seem to jump around between schema design (DDL) and usage (select and DML) in a pretty haphazard fashion, and the information about transactional control and session management seems to be spread around the various sections. That's basically just my perspective, and may reflect my experience, but it is frustrating. Agreed entirely about
Re: [sqlalchemy] Polymorphic joined-table inheritance
On Jan 10, 2014, at 7:08 AM, david.ceresu...@gmail.com wrote:Problem: Querying client and clientvip---With this code I try to query all the clients [ clients = DBSession.query(Client) ]and this is where the problems start, because the query it is issuing is:##SELECT client.id AS client_id, client.ctype AS client_ctype, client.refinternal AS client_refinternal, ...client.notestx AS client_notestx, client.notesclient AS client_notesclient FROM client WHERE client.ctype IN (NULL)##Why is there a where clause at all? Should not the query finish with the "FROM client" line?And besides, why does it think that ctype must be NULL (instead of "basic" or "vip", for instance)?that’s a very strange output, the IN is applied to single-table inheritance mappings in order to limit the types of returned classes to those which are queried. The mapping you’ve illustrated should not have this issue and in fact as Client is at the base, there shouldn’t be any IN at all.I’ve copied your mappings verbatim to a test case, a query against Client produces this:SELECT client.id AS client_id, client.ctype AS client_ctype, client.parentid AS client_parentid, client.refinternal AS client_refinternal, client.notestx AS client_notestx, client.notesclient AS client_notesclientFROM clientso more specifics would be needed to determine why this obviously erroneous output is being produced. The constructor of Client is not involved with this issue as a Query only works against the class, not an instance.Appendix: Inserting client-You might have seen that in the __init__ methods I have commented out the " self.ctype = 'basic' "When this line is commented out (as it should) and I try to insert a Client, I get the following error:##IntegrityError: (IntegrityError) null value in column "ctype" violates not-null constraint##which leads me to think that the polymorphism I am trying to get is not working properly, becasueI shouldn't need to force a value on the "ctype" column.you absolutely don’t need to worry about “ctype”, nor do you need to worry about calling super(). the polymorphic identity is set on the instance using an on-init event. I can again use your mappings as is, then run code like this:c1 = Client(refinternal=5)sess.add(c1)sess.commit()and the output:INSERT INTO client (ctype, parentid, refinternal, notestx, notesclient) VALUES (?, ?, ?, ?, ?)2014-01-10 12:39:14,949 INFO sqlalchemy.engine.base.Engine ('basic', None, 5, None, None)2014-01-10 12:39:14,949 INFO sqlalchemy.engine.base.Engine COMMITso no problems there either.Both issues seem to focus around the polymorphic_identity configuration of your setup but the code examples you’ve sent don’t indicate any such issue.If you can attach a simple “test.py” similar to the one attached which reproduces the issue, we can fix your problem immediately.from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Client(Base):
__tablename__ = 'client'
id = Column(Integer, primary_key=True)
ctype = Column(String(16), nullable=False)
#webaddid = Column(Integer, ForeignKey('web.id'), nullable=False)
parentid = Column(Integer, ForeignKey('client.id'), nullable=True)
refinternal = Column(Integer, unique=True, nullable=False)
notestx = Column(String(256), nullable=True)
notesclient = Column(String(256), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'basic',
'polymorphic_on': ctype
}
def __init__(self, webaddid=None, parentid=None, refinternal=None,
notestx=None, notesclient=None):
self.webaddid = webaddid
self.parentid = parentid
self.refinternal = refinternal
self.notesclient = notesclient
class ClientVip(Client):
__tablename__ = 'clientvip'
id = Column(Integer, ForeignKey('client.id'), primary_key=True)
legalname = Column(String(128), nullable=True)
commercialname = Column(String(64), nullable=True)
isclienttop = Column(Boolean, nullable=False)
numlisttop = Column(Integer, nullable=True)
# Fill the column 'ctype' with the value 'vip'
__mapper_args__ = {
'polymorphic_identity':'vip',
}
def __init__(self, clientid=None, legalname=None, commercialname=None,
isclienttop=False, numlisttop=None, **kwargs):
# First initialize the basic client
super(ClientVip, self).__init__(**kwargs)
self.clientid = clientid
self.legalname = legalname
self.commercialname = commercialname
self.isclienttop = isclienttop
self.numlisttop = numlisttop
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
c1 = Client(refinternal=5)
c2 = ClientVip(refinternal=6)
sess.add(c1)
sess.add(c2)
sess.commit()
clients = set(sess.query(Client).all())
assert clients == set([c1, c2])
Re: [sqlalchemy] Why ORM objects in a session expire immediately after a failure in flush?
changelog in 0.8.0b1: http://docs.sqlalchemy.org/en/rel_0_9/changelog/changelog_08.html#change-1df6e3552ee895cd48952f95c0f0730a ticket: http://www.sqlalchemy.org/trac/ticket/2452 I wonder if offering that the automatic rollback() on flush() might be optionally disabled (which means, a corrupted flush is in danger of being committed if the user is not careful) would be a compromise for this behavior in the bigger sense. On Jan 10, 2014, at 8:41 AM, Claudio Freire klaussfre...@gmail.com wrote: On Thu, Jan 9, 2014 at 9:59 PM, Michael Bayer mike...@zzzcomputing.com wrote: I do notice that you’re catching an IntegrityError.The typical pattern when writing code that wants to catch these and then continue is to run the individual set of questionable operations within a SAVEPOINT, that is a begin_nested()/commit() block. Recent versions of SQLAlchemy have the behavior such that when a flush() exception rolls back the SAVEPOINT, objects that were not modified within the SAVEPOINT are not expired; only those objects that changed within the save point’s scope do. How recent does recent mean there? (just curious) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
On Jan 10, 2014, at 8:44 AM, Paul Moore p.f.mo...@gmail.com wrote: So far, so good. But if I want to create a new Release, things get messy. This is my basic function: def new_release(package, version, data): r = Release(version) r.package = Package(package) # Populate the data in r, and create child items return r It's that package reference that messes me up. If the release is for a new package, then when I merge the release into the session, the package is created. But if it's for an existing package, a new package is created (which gives a constraint error if the package name is unique, and duplicates if it's not) rather than the session recognising that it's an existing package and linking the release to it. If I was working at the SQL core level, I'd expect to have to query for the package and create it if it wasn't there. But I'm not sure I can do that with a session, as things get cached in memory by the unit of work stuff, and I don't know if a query for the release could miss a package that's pending insertion, or if the query could cause auto-flushing which might cause other issues (either with performance or integrity). Because the session is managing the in-memory caching and the transaction management by magic, I don't want to interfere with its mechanisms any more than I have to. If I have to keep track of what's gone to the database, and query for existing instances and manage the transactions, I probably should just use the SQL layer directly (I have a lot of experiences with databases, but very little with ORMs, so pure DB code isn't too scary for me, but on the other hand I don't know what benefits the ORM should be giving me that I'm not seeing). there’s various patterns for dealing with the very common issue of “create unique object if not exists, else use the current one”. One that I frequently point to is the unique object recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject uniqueobject is a generalization. You can of course just run the specific query yourself. this is not in any way interfering with the ORM mechanisms - the unit of work is pretty much a simple identity dictionary in this sense, either a key exists for a given identity or it doesn’t (but keep in mind, “identity” to the unit of work means, “primary key identity”. Not any of the other columns that are just part of unique constraints). As mentioned elsewhere, Session.merge() kind of does this as well, but merge() only does so with the primary key of the instance, not for arbitrary fields, and I would agree that merge() is often more trouble than it’s worth - its complexity is that it can cascade the merge operation along relationships, not often needed for simple cases. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] SQLAlchemy not executing query
Thank you that worked. On Thu, Jan 9, 2014 at 7:47 PM, Michael Bayer mike...@zzzcomputing.comwrote: The statement is likely being invoked but is in a transaction that isn’t getting committed (assuming you’re using commit() with pyodbc). SQLAlchemy has an “autocommit” feature that by default looks for SQL strings that indicate a COMMIT should occur. So in this case you should make sure there’s an explicit transaction or explicit autocommit. In 0.7 you’re probably best off like this: conn = engine.connect() trans = conn.begin() conn.execute(“your sql”) trans.commit() or use the “autocommit” option: conn = engine.connect() conn.execution_options(autocommit=True).execute(“your sql”) On Jan 9, 2014, at 6:39 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, I am using SQLAlchemy version 0.7.6 with pyodbc to connect to MSSQL 2012. Currently I am using SQLAlchemy only for its connection pooling etc. So, at the moment I only use the engine.execute function to execute string queries. Weirdly, the following query seems to have no effect at all: SET NOCOUNT ON; SET ROWCOUNT 1 WHILE 1 = 1 BEGIN DELETE from MyTable where MyDate = '2030' IF @@rowcount 1 BREAK; END SET ROWCOUNT 0; SET NOCOUNT OFF; Running the above query using pyodbc directly, works. But with SQLAlchemy it has no effect. There is no error thrown, just a silent failure to execute. I enabled ODBC tracing and I found this: python -c imp 85bc-7d4c EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO) HDBC0x0057B770 HWND0x WCHAR * 0x07FEF8CB8F08 [ -3] **\ 0 SWORD -3 WCHAR * 0x07FEF8CB8F08 Invalid buffer length! [-3] SWORD -3 SWORD * 0x UWORD0 SQL_DRIVER_NOPROMPT However, the above was also present when I ran the query through pyodbc only. So, this probably indicates a more general problem with the set up, rather than something particular with SQLAlchemy. Any idea what might be causing this? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
On Friday, 10 January 2014 17:52:45 UTC, Michael Bayer wrote: there’s various patterns for dealing with the very common issue of “create unique object if not exists, else use the current one”. One that I frequently point to is the unique object recipe: OK, so looking at that seems to imply that pkg = Package(name=pip) session.add(pkg) is_it_there = session.query(Package).filter(Package.name == 'pip').first() assert is_it_there is pkg will fail the assertion (unless there is a flush). Otherwise, I don't see why the unique cache is needed in the recipe you reference. I think I'm still missing something here... Paul. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Custom logic for query execution
Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? 2. Is there a better\recommended approach? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
On Jan 10, 2014, at 1:42 PM, Paul Moore p.f.mo...@gmail.com wrote: On Friday, 10 January 2014 17:52:45 UTC, Michael Bayer wrote: there’s various patterns for dealing with the very common issue of “create unique object if not exists, else use the current one”. One that I frequently point to is the unique object recipe: OK, so looking at that seems to imply that pkg = Package(name=pip) session.add(pkg) is_it_there = session.query(Package).filter(Package.name == 'pip').first() assert is_it_there is pkg will fail the assertion (unless there is a flush). if autoflush is turned on, it will pass the assertion. the Session is flushed before any SQL query when autoflush is left at its default of True. So first pkg goes in via INSERT, the SELECT is emitted, locates the row with the identity of pkg, locates “pkg” itself in the identity map, and returns it. Otherwise, I don't see why the unique cache is needed in the recipe you reference. the cache isn’t needed but is nice so that many objects all doing the same thing don’t have to keep hitting the database for an object that’s already local in memory. keep in mind, the Query always emits SQL, except in the case of the get() method which will look locally in the identity map first. Otherwise, as rows are returned from the SQL statement, the primary key identity of each row is matched to whatever objects are already in the identity map. That’s how the identity map works. You still get the SQL being emitted in most cases so it’s not quite a “cache”. More detail on this here: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#is-the-session-a-cache signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Custom logic for query execution
On Jan 10, 2014, at 1:50 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? 2. Is there a better\recommended approach? technically, there’s no such operation as “retry a query” in the field of “database failures”, assuming we’re talking about disconnects. There’s only, “retry a transaction”. So it’s typically not so simple to build a generic “retry query” feature - a disconnect means the whole transaction is gone, so whatever state was already built up is gone as well. In this regard engine.execute() is kind of hiding a lot of this in that it begins/commits a transaction on its own, but for more substantial applications I’d advise having your application be aware of the scope of transactions. Especially if using the ORM, it’s pretty much a requirement as the ORM defers/bundles CRUD operations into batches. Unless you never perform two queries in a row, your app will perform much better and also will integrate correctly with transaction isolation.I typically advise that this scope is managed in just one place, as opposed to explicitly boilerplated throughout the application, a common antipattern. if you’re anticipating frequent database failures, and excepting out is not an option, you might want to go with a “pessimistic” listener as that described at http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic. Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Custom logic for query execution
On Fri, Jan 10, 2014 at 3:50 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? Let me first point out, that you cannot consider all queries equal, and retry-able. First problem you'll face, is secondary effects. Update and inserts obviously, but secondary effects hidden in select queries could also be an issue (say, triggers or something else that has a secondary effect). Second problem you'll face are transactions. When a failure occurs, you usually have no choice to retry a single query, you have to retry the whole transaction. So doing it at the Engine level may prove a bad choice with the above in mind. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Custom logic for query execution
Thanks for you feedback. I will take a look at the pessimistic listener On Fri, Jan 10, 2014 at 2:02 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 10, 2014, at 1:50 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? 2. Is there a better\recommended approach? technically, there’s no such operation as “retry a query” in the field of “database failures”, assuming we’re talking about disconnects. There’s only, “retry a transaction”. So it’s typically not so simple to build a generic “retry query” feature - a disconnect means the whole transaction is gone, so whatever state was already built up is gone as well. In this regard engine.execute() is kind of hiding a lot of this in that it begins/commits a transaction on its own, but for more substantial applications I’d advise having your application be aware of the scope of transactions. Especially if using the ORM, it’s pretty much a requirement as the ORM defers/bundles CRUD operations into batches. Unless you never perform two queries in a row, your app will perform much better and also will integrate correctly with transaction isolation.I typically advise that this scope is managed in just one place, as opposed to explicitly boilerplated throughout the application, a common antipattern. if you’re anticipating frequent database failures, and excepting out is not an option, you might want to go with a “pessimistic” listener as that described at http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic . Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: How to stop SQLAlchemy from adding an ON UPDATE clause to a TIMESTAMP column by default
I realize this thread is ancient, but I'm resurrecting it for Googleable
posterity since I just ran across the same issue.
The problem is that MySQL helpfully inserts the ON UPDATE cheese unless
you specify a default and/or a NULL/NOT NULL value in the CREATE TABLE
query.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
Unfortunately, I haven't yet been able to get sqlalchemy to actually send
such a query, this being my attempt:
time = Column(
TIMESTAMP(), primary_key=True,
default=datetime.min, nullable=False)
Will reply again if I manage to get a TIMESTAMP column without the ON
UPDATE stuff.
On Monday, November 28, 2011 5:09:35 PM UTC-8, Michael Bayer wrote:
There's some more happening on your end. Rest assured DEFAULT and ON
UPDATE are not generated without very specific and explicit instructions -
the default and onupdate keywords would need to be passed to your
Column - engine arguments have nothing to do with it.If it were me I'd
stick a pdb into Column to intercept it happening.
Here is the output of your program:
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine SELECT
DATABASE()
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'lower_case_table_names'
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine
DROP TABLE foo
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine COMMIT
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ROLLBACK
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine
CREATE TABLE foo (
id CHAR(36) NOT NULL,
`dateAdded` TIMESTAMP,
reason TEXT,
PRIMARY KEY (id)
)
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,275 INFO sqlalchemy.engine.base.Engine COMMIT
On Nov 28, 2011, at 5:38 PM, Ben Hayden wrote:
Hmm... well this is a weird problem then. I ran the provided code, and got
the same result you did, with the DEFAULT ON UPDATE missing. However, I
added a couple lines:
*from sqlalchemy.ext.declarative import declarative_base*
*from sqlalchemy import Table, CHAR, TIMESTAMP, TEXT, schema, Column*
*from uuid import uuid4 as uuid*
*Base = declarative_base()*
*class Foo(Base):*
*__tablename__ = 'foo'*
*#column definitions*
*id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True,
nullable=False)*
*date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)*
*reason = Column(u'reason', TEXT())*
*from sqlalchemy.dialects import mysql*
*print schema.CreateTable(Foo.__table__).compile(dialect=mysql.dialect())*
*Base.metadata.bind = db.generate_engine()*
*Base.metadata.drop_all()*
*Base.metadata.create_all() *
The create table that was actually generated in the db is still:
*CREATE TABLE `foo` (*
* `id` char(36) NOT NULL,*
* `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,*
* `reason` text,*
* PRIMARY KEY (`id`)*
*) ENGINE=InnoDB DEFAULT CHARSET=latin1;*
My generate_engine method is a little helper method that returns an engine
with the following params:
*create_engine('mysql://%s:%s@%s/%s' % (*
* config.get('database', 'user'),*
* urllib.quote_plus(config.get('database', 'pass')),*
* config.get('database', 'host'),*
* config.get('database', 'name')),*
* convert_unicode=True, pool_size=20, pool_recycle=60,*
* connect_args={'use_unicode': True, 'charset':
'utf8', 'compress': True})*
Am I unknowingly passing a default I shouldn't to SQLA that is causing the
generation of the table to add those defaults? Or is there an option in
MySQL that I unknowingly have turned on?
My versions:
Python 2.7
SQLA 0.7.*
MySQL version 5.5.11
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
Re: [sqlalchemy] Re: How to stop SQLAlchemy from adding an ON UPDATE clause to a TIMESTAMP column by default
My solution, since sqlalchemy seems to be ignoring the nullable and default
kwargs, is this:
time = Column(
TIMESTAMP(), primary_key=True,
server_default=text('-00-00 00:00:00'))
The default is just never used.
On Friday, January 10, 2014 12:20:45 PM UTC-8, Steve Johnson wrote:
I realize this thread is ancient, but I'm resurrecting it for Googleable
posterity since I just ran across the same issue.
The problem is that MySQL helpfully inserts the ON UPDATE cheese unless
you specify a default and/or a NULL/NOT NULL value in the CREATE TABLE
query.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
Unfortunately, I haven't yet been able to get sqlalchemy to actually send
such a query, this being my attempt:
time = Column(
TIMESTAMP(), primary_key=True,
default=datetime.min, nullable=False)
Will reply again if I manage to get a TIMESTAMP column without the ON
UPDATE stuff.
On Monday, November 28, 2011 5:09:35 PM UTC-8, Michael Bayer wrote:
There's some more happening on your end. Rest assured DEFAULT and ON
UPDATE are not generated without very specific and explicit instructions -
the default and onupdate keywords would need to be passed to your
Column - engine arguments have nothing to do with it.If it were me I'd
stick a pdb into Column to intercept it happening.
Here is the output of your program:
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine SELECT
DATABASE()
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'lower_case_table_names'
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine
DROP TABLE foo
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine COMMIT
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ROLLBACK
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine
CREATE TABLE foo (
id CHAR(36) NOT NULL,
`dateAdded` TIMESTAMP,
reason TEXT,
PRIMARY KEY (id)
)
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,275 INFO sqlalchemy.engine.base.Engine COMMIT
On Nov 28, 2011, at 5:38 PM, Ben Hayden wrote:
Hmm... well this is a weird problem then. I ran the provided code, and
got the same result you did, with the DEFAULT ON UPDATE missing. However,
I added a couple lines:
*from sqlalchemy.ext.declarative import declarative_base*
*from sqlalchemy import Table, CHAR, TIMESTAMP, TEXT, schema, Column*
*from uuid import uuid4 as uuid*
*Base = declarative_base()*
*class Foo(Base):*
*__tablename__ = 'foo'*
*#column definitions*
*id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True,
nullable=False)*
*date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)*
*reason = Column(u'reason', TEXT())*
*from sqlalchemy.dialects import mysql*
*print schema.CreateTable(Foo.__table__).compile(dialect=mysql.dialect())*
*Base.metadata.bind = db.generate_engine()*
*Base.metadata.drop_all()*
*Base.metadata.create_all() *
The create table that was actually generated in the db is still:
*CREATE TABLE `foo` (*
* `id` char(36) NOT NULL,*
* `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,*
* `reason` text,*
* PRIMARY KEY (`id`)*
*) ENGINE=InnoDB DEFAULT CHARSET=latin1;*
My generate_engine method is a little helper method that returns an
engine with the following params:
*create_engine('mysql://%s:%s@%s/%s' % (*
* config.get('database', 'user'),*
* urllib.quote_plus(config.get('database', 'pass')),*
* config.get('database', 'host'),*
* config.get('database', 'name')),*
* convert_unicode=True, pool_size=20,
pool_recycle=60,*
* connect_args={'use_unicode': True, 'charset':
'utf8', 'compress': True})*
Am I unknowingly passing a default I shouldn't to SQLA that is causing
the generation of the
