Re: How to handle changing formerly-unnamed constraints (and retrying after errors in migration scripts)
Aha, thanks for that. For the benefit of anyone who comes across this
later, my final script looked much like this:
def downgrade():
try:
inner_transaction = op.get_bind().begin_nested() # establish a
savepoint
with op.batch_alter_table("user_authentication_method") as batch_op:
batch_op.drop_constraint(composite_key_name, type_='unique')
except ProgrammingError:
# Try again, with the name we probably have for it, at least in
Postgres
inner_transaction.rollback()
probable_name = "user_authentication_method_pkey"
with op.batch_alter_table("user_authentication_method") as batch_op:
batch_op.drop_constraint(probable_name, type_='unique')
No guarantees that the above is /correct/ but it catches the exception and
seems to work so far.
Thanks again.
--
Ben
On Wednesday, 20 April 2016 14:52:14 UTC+1, Mike Bayer wrote:
On 04/20/2016 07:42 AM, Ben Sizer wrote:
> I had an unnamed UniqueConstraint, and autogenerated upgrade scripts
> that passed in None as the name - these worked perfectly. Now I am
> discovering that the downgrade doesn't work, as there is no name to
> refer to the constraint (i.e. "sqlalchemy.exc.CompileError: Can't emit
> DROP CONSTRAINT for constraint UniqueConstraint(); it has no name")
>
> So, I'm trying to add a name in the model, and edit the migration
> scripts accordingly. My strategy was this:
>
> - the upgrade function would add the constraint with a name in future
> - the downgrade function needs to be able to delete the constraint
> whatever it was called
>
> The first is easy enough and seems to have no problems.
>
> The second is tricky; if the name passed to drop_constraint is wrong, it
> raises the expected error (e.g. sqlalchemy.exc.ProgrammingError:
> (psycopg2.ProgrammingError) constraint "provider_name_user_id_key" of
> relation "user_authentication_method" does not exist)
> What I'd like to do, is catch that exception, and try another name (e.g.
> the name that I know was autogenerated, in this case
> 'user_authentication_method_pkey') - however, attempting to catch the
> error and perform a further operation emits a Postgres internal error
> that suggests I need to end the current transaction and start a new one
> (i.e. sqlalchemy.exc.InternalError: (psycopg2.InternalError) current
> transaction is aborted, commands ignored until end of transaction block)
> and I can't find out how to do that. I tried calling rollback() on a
> session created around the return value of get_bind() but the error was
> the same.
You should be able to use a savepoint around that operation to prevent
this issue from happening. This works for simple things like INSERTs
and hopefully works for DDL as well. Either with an ORM Session or with
the Connection, the begin_nested() method gives you a savepoint
transaction.
>
> To complicate matters, one of the possible DBs is SQLite so I have to do
> this via op.batch_etc. Thankfully that data is not essential so deleting
> the whole DB is an option there - not so much for the Postgres data.
For SQLite I've always been a proponent of dropping the whole thing and
recreating from scratch. The existence of batch mode is due to all the
pressure I get from users who don't want to do it that way, but I still
favor not trying to make SQLite do real migrations - its creators
disagree with this use case hence they've never implemented it.
>
> So my questions are:
>
> 1) How can I handle this drop_constraint call when we didn't specify a
> constraint name in the first place?
> 2) If the best way is for me to just attempt to drop constraints by
> name, trying several names, how can I catch or avoid the first error and
> try subsequent constraint names?
>
> Thanks,
> Ben
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
How to handle changing formerly-unnamed constraints (and retrying after errors in migration scripts)
I had an unnamed UniqueConstraint, and autogenerated upgrade scripts that passed in None as the name - these worked perfectly. Now I am discovering that the downgrade doesn't work, as there is no name to refer to the constraint (i.e. "sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint UniqueConstraint(); it has no name") So, I'm trying to add a name in the model, and edit the migration scripts accordingly. My strategy was this: - the upgrade function would add the constraint with a name in future - the downgrade function needs to be able to delete the constraint whatever it was called The first is easy enough and seems to have no problems. The second is tricky; if the name passed to drop_constraint is wrong, it raises the expected error (e.g. sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) constraint "provider_name_user_id_key" of relation "user_authentication_method" does not exist) What I'd like to do, is catch that exception, and try another name (e.g. the name that I know was autogenerated, in this case 'user_authentication_method_pkey') - however, attempting to catch the error and perform a further operation emits a Postgres internal error that suggests I need to end the current transaction and start a new one (i.e. sqlalchemy.exc.InternalError: (psycopg2.InternalError) current transaction is aborted, commands ignored until end of transaction block) and I can't find out how to do that. I tried calling rollback() on a session created around the return value of get_bind() but the error was the same. To complicate matters, one of the possible DBs is SQLite so I have to do this via op.batch_etc. Thankfully that data is not essential so deleting the whole DB is an option there - not so much for the Postgres data. So my questions are: 1) How can I handle this drop_constraint call when we didn't specify a constraint name in the first place? 2) If the best way is for me to just attempt to drop constraints by name, trying several names, how can I catch or avoid the first error and try subsequent constraint names? Thanks, Ben -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[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] 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: Unexpected IntegrityError when trying to add new related element
On Jul 21, 3:35 pm, Conor conor.edward.da...@gmail.com 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] type safety using sqlite
I have a Column(Integer) called object_id. I assign to it a string or unicode value, eg. object_id = unot 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: 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] 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: Slightly confusing error when session set up incorrectly
On Jun 29, 7:30 pm, Michael Bayer mike...@zzzcomputing.com 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 a factory for unbound Session subclasses, and bind each to the engine with the keyword argument on creation. -- you can also associate individual Session objects with an Engine on each invocation 4) call sessionmaker(bind=some_engine) to get a factory for bound Sessions and create an already-bound instance. However, to standardize how sessions are configured and acquired, the sessionmaker() function
[sqlalchemy] Re: Slightly confusing error when session set up incorrectly
On Jun 29, 6:46 am, Michael Bayer mike...@zzzcomputing.com 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 soon as possible so that the problem's narrowed down, ideally to the exact statement that is incorrect. In this case, it's not efficient to do so, therefore it would be great for the docs to explicitly highlight that fact. Feel free to suggest what verbiage you're looking for, it's not hitting me strongly what the confusion
[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.
