[sqlalchemy] SQLite objects getting closed in wrong thread, if session not explicitly closed
I'm using SQLAlchemy 1.0.8 with an SQLite file-based database, running as part of a Flask-based server, and getting some strange results. I'm seeing this exception raised: ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 8088 and this is thread id 8672 The place where the exception is raised is in _finalize_fairy in pool.py, and that's being called from the lambda in pool.py line 491. That in turn is getting invoked from a completely unrelated piece of code, implying that this is perhaps just coming off the back of a Python garbage collection cycle or similar. The pool type at the time the exception is raised is a NullPool object, but obviously _finalize_fairy is getting called at an unexpected time when some of the operations are not valid. This only happens when I don't explicitly close a session, which I accept is a bug in my own code. I've fixed that so I don't trigger this behaviour again. But is it correct (and safe) for _finalize_fairy to be called from arbitrary threads like this? If there are thread-sensitive situations, such as when using SQLite, perhaps attaching the clean-up logic to the callback on a weakref is the wrong thing to do? Not that I'm sure what the alternative would be... Thanks, Ben -- 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/d/optout.
[sqlalchemy] Re: Unexpected IntegrityError when trying to add new related element
On Jul 21, 3:35 pm, Conor wrote: > > It means that there is a Child row already in the database with > parent_id=1234. When you reassign p.children to not include that child, > SQLAlchemy detects that the child object is now an orphan (has no > parent). Based on your cascade rules (cascade="all"), SQLAlchemy will > try to NOT delete the child, but instead set its parent_id to NULL (the > only sensible alternative to not deleting the child). > > If you want the child to be deleted in this case, change the cascade to > "all,delete-orphan". Otherwise, you need to ensure that the child is > kept in p.children, e.g. p.children += [Child(details=x) for x in x_list]. Aah, thank you. That appears to have been exactly the problem. It's hard to find many examples of working with objects like this in the docs so I wasn't sure how to delete the old list and replace it with a new list. It looks like the cascade="all,delete-orphan" is the right thing to do for all ownership relations. -- Ben Sizer -- 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] Unexpected IntegrityError when trying to add new related element
I have 2 classes:
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = relationship("Child", cascade="all")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'),
nullable=False)
details = Column(Text(), nullable=False)
I recently added nullable=False to the Child.parent_id column, and now
the following code produces an error.
p = self.session.query(Parent).filter_by(id=1234).one()
p.children = [Child(details=x) for x in x_list] # don't think
content of x_list is relevant
self.session.commit()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.1-py2.7.egg
\sqlalchemy\engine\default.py", line 325, in do_execute
cursor.execute(statement, parameters)
IntegrityError: (IntegrityError) child.parent_id may not be NULL
u'UPDATE child SET parent_id=? WHERE child.id = ?' (None, 1)
Why is it generating this particular UPDATE statement? Shouldn't it
add the correct parent_id from the Parent object, not None? Even if I
add 'parent_id=1234' into the Child() constructor, it still attempts
to set parent_id to None with this UPDATE.
What am I doing wrong?
--
Ben Sizer
--
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: type safety using sqlite
Ok, this seems to do the trick for my use case, but I'd be curious to
see if there's a better way or if there are things that should be
fixed here.
from types import IntType, LongType
from sqlalchemy.exc import ArgumentError
from sqlalchemy import event
from sqlalchemy.orm import mapper
def _check_integral_type(target, value, oldvalue, initiator):
if not isinstance(value, IntType) and not isinstance(value,
LongType):
raise ArgumentError("value is not numeric")
return value
@event.listens_for(mapper, "mapper_configured")
def _setup_int_listeners(mapper, class_):
for prop in mapper.iterate_properties:
if hasattr(prop, 'columns'):
if isinstance(prop.columns[0].type, Integer):
event.listen(getattr(class_, prop.key), "set",
_check_integral_type, retval=True)
--
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: type safety using sqlite
Thanks very much Michael, that should be more than enough information for me to find a solution. -- Ben Sizer -- 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] type safety using sqlite
I have a Column(Integer) called object_id. I assign to it a string or unicode value, eg. object_id = u"not an integer". To my surprise, this doesn't raise any kind of exception when the row is committed. I can then expunge the session and request that row back, getting a Unicode object for that column. I understand that sqlite is very weakly typed and that you can do this sort of thing easily. But I thought that SQLAlchemy would apply some logic in the middle to ensure that an Integer column only takes something integral. I would understand if I'd passed "30" or some other string that could be coerced to an integer, but this doesn't fit that constraint. So, 2 questions: a) Is this expected behaviour? b) How can I catch this, ideally at the SQLAlchemy level, so that I can't accidentally store a string as an integer? -- Ben Sizer -- 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: Slightly confusing error when session set up incorrectly
On Jun 29, 7:30 pm, Michael Bayer wrote: > On Jun 29, 2011, at 12:24 PM, Ben Sizer wrote: > >OK, this happens a lot with me, if anyone can please recommend what university >I should go to in order to learn to speak english correctlyHere's the >sentence: It's not incorrect, just ambiguous. I expect most people would have read it the way you intended it. But this wasn't my point and it's not directly relevant to my issue: whether it maintains an open connection for each engine or each request doesn't matter - what is important is that it won't make any attempt to connect at all until this point. It's arguably implied, but not explicitly stated. > > There is a similar implication > > in the 'What does the Session do?' paragraph also. > > Not seeing the "implication" in that one, please add some details to ticket > 2204 as to the verbiage you find confusing (log in as guest/guest). The part I meant is: "The Session begins in an essentially stateless form. Once queries are issued or other objects are persisted with it, it requests a connection resource from an Engine that is associated either with the Session itself or with the mapped Table objects being operated upon." This part isn't confusing, it's just that again it does not explicitly state that no connection happens at the start. Saying "When X occurs, Y also occurs" isn't logically the same as "Before X occurs, Y never occurs". So I've not added this to the ticket because I don't this anything about this needs to change. I have just been suggesting something extra to be added in the session config/creation docs: eg., "Note, this does not actually connect to the database yet. Only when your first query is issued will a physical connection be made." > >>> I expect I am not unusual > >>> in wanting an error in session configuration to fail as soon as > >>> possible, > > >> Nobody has ever raised this issue before to my recollection. > > > The problem with show-stopper bugs is that people often just give up > > and switch to something else rather than tell you about it. ;) > > "show stopper bug" is quite an exaggeration over what is essentially a small > documentation insufficiency. To you (and now to me), it's a small documentation insufficiency, because we know what the problem was. But to me 24 hours ago, this was a problem that stopped my app running entirely. > So there is a class of users, and a large one at that, that experience bugs > both large and small and don't immediately abandon the project. This class of > users continues to expand as the project has grown much stronger over the > course of many years, responding to user requests, competing products, etc. > There are of course users who abandon the project within 5 minutes, and I > would say if their level of tenacity is that low then they're probably better > off with whatever tool they end up using. Yes, of course. sqlalchemy is the best product of its kind, which is why I am here! I was just pointing out that sometimes it's the stuff that -isn't- reported that is actually really important for user retention. It just happens to be one of the things we tracked on the last product I worked on, and I got an eye-opening view into how most users stop using a product for reasons completely different to the ones people complain about. :) My level of tenacity is pretty low, I'll admit! But, I already fixed my bug, and with your help also understand the fix. I just came here to try and help others who might hit the same thing. If you don't think my suggestions help, that's fine. I do accept your point about not being able to adjust the docs to suit every individual user. > > [...] that means there would be 4 ways > > of approaching this, which seems a little much. > > I'm not sure what the "four ways" are here. As a user who has no real restriction on configuration, where my use case is simply, "connect to a database and get a session for it", it appears there are 4 different ways I can do that, and the docs tell me how to do all these but don't really explain why, meaning I was not sure whether I was using the wrong approach or a sub-optimal one. 1) import Session directly from sqlalchemy.orm.session and bind that to the engine with the keyword argument on creation. -- "Session is a regular Python class which can be directly instantiated." 2) call sessionmaker() to get a factory for unbound Session subclasses, and call .configure(bind=whatever) to bind it. -- "You can also associate a Engine with an existing sessionmaker() using the sessionmaker.configure() method" 3) call sessionmaker() to get
[sqlalchemy] Re: Slightly confusing error when session set up incorrectly
On Jun 29, 6:46 am, Michael Bayer wrote: > On Jun 28, 2011, at 9:34 PM, Ben Sizer wrote: > > > What does the "None None" signify? Would it be possible to change this > > exception to be a bit more descriptive and a little less cryptic? eg. > > Include the URI that failed? > > That error is raised by SQLite and we just propagate it out. Ok, that's a bit annoying, but it does sound like something that is not your problem to solve. Maybe if I get time I'll contact the pysqlite maintainers as I think it would be useful to have a pseudo- statement in there at least. > > Additionally, I notice that sqlalchemy doesn't attempt to make an > > actual connection to the database until you perform the first query. > > That is true, all connections/transactions are lazy initializing. Sorry > this was a surprise, though I don't think this is much of an issue once you > get used to dealing with lazy initializing objects. > > The formal pattern at play with SQLAlchemy's connection pool, Engine and > Session behavior is called the Proxy Pattern, a decent description is at > http://sourcemaking.com/design_patterns/proxy. Yeah, that's all fine. I don't have a problem with this choice, just that it's not immediately obvious when following the docs. > the lazy initializing behavior of the Session is documented: > > http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-transaction Not exactly. It says it maintains a connection for each SQL statement, which is not logically equivalent to saying there is no connection unless there has been an SQL statement. There is a similar implication in the 'What does the Session do?' paragraph also. > But that doesn't have anything to do with the Engine, which is its own thing. > As far as the Engine, the docs currently use the term "connect" in > conjunction with create_engine() which is for simplicities' sake, but is > technically inaccurate, perhaps come up with some term other than "connect", > "configure a connection source" perhaps. We can add an explicit sentence to > the top ofhttp://www.sqlalchemy.org/docs/core/connections.html. I must admit I don't fully understand engines, connections, sessions, etc. I will study the docs further! However when writing manual DB code I would usually have a step where I initialise the driver (which I had been thinking of as the create_engine stage), a step where I can issue statements (which I think of as the session stage), and a point temporally between those where I explicitly connect to the physical storage, which I had thought was automatically taking place when creating the engine but I now realise only happens on demand. It's not a problem as such, just a surprise. > > I expect I am not unusual > > in wanting an error in session configuration to fail as soon as > > possible, > > Nobody has ever raised this issue before to my recollection. The problem with show-stopper bugs is that people often just give up and switch to something else rather than tell you about it. ;) (Of course in this case, the bug is mine, but developers do like to blame their tools...) > I'm not sure lots of users are phased whether the stack trace starts at the > Session.configure() line or if it starts later as soon as their first unit > test tries to hit the database - they get the same error, see that the > connection URL is bad, and fix it. But this is exactly the problem: I had one unit test for setting up the database, and one for actually using the database. The first one passed - the second one failed. So I immediately think, "my usage of the database is wrong", not "I set up the database connection wrongly", because all the code for setting up the connection was covered the test that passed, too. That is what the unit tests were there for, after all - to separate out where a problem appears to be coming from, by splitting your code coverage across tests. > I suppose inefficiency is the only issue but its so unnecessarily > inefficient, most people would consider it to be wrong behavior. A Session > may be configured such that depending on what's requested of it, it can > connect to any number of different engines - connecting to > several/dozens/hundreds of engines unconditionally upon construction is not > an option. Yeah, that's fine. I just think it would be good to have had something about this documented right in at the top of the Session docs so that anyone starting out or writing unit tests for this sort of thing knows that they're not actually testing the db connection just because they made an engine and Session without error. Anywhere you pass in invalid data, you hope that the system catches that as soo
[sqlalchemy] Slightly confusing error when session set up incorrectly
Hello all,
When using sqlalchemy 0.7 with sqlite, if I enter the path URI
incorrectly I will get an error like this:
OperationalError: (OperationalError) unable to open database file
None None
What does the "None None" signify? Would it be possible to change this
exception to be a bit more descriptive and a little less cryptic? eg.
Include the URI that failed?
Additionally, I notice that sqlalchemy doesn't attempt to make an
actual connection to the database until you perform the first query.
That means that code like this will appear to work:
Session = sessionmaker()
engine = create_engine('sqlite:///%s' % "invalid_filename")
Base.metadata.bind = engine
db_session = Session(bind=engine)
Yet eventually, when you make a query within your app code, you'll get
an exception because the URI was wrong. In my case, due to the
exception text being a little vague, I thought this was a problem with
my app's db access patterns (as I do things like delete the database
file manually) when it was just a problem with the initial connection.
I found a way to trigger this error earlier, by issuing
engine.connect() in the above routine, but I notice that this isn't
explicitly documented in "Using The Session" (http://
www.sqlalchemy.org/docs/orm/session.html). I expect I am not unusual
in wanting an error in session configuration to fail as soon as
possible, so if it's not possible or efficient to do this
automatically as part of creating the session, perhaps this part of
the docs could be clarified so that new users in future will know
exactly what to call to test this configuration? (In fact, this part
of the docs is a bit confusing in general - there seem to be a fair
few permutations of how to approach it - pass arguments to
sessionmaker? or to Session.configure? or to Session's constructor? -
and it's not clear why they all have to exist.)
--
Ben Sizer
--
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.
