[sqlalchemy] [alembic] equivalent to `makemigrations --check`

[Chris Withers]

Hi,

I got a bounce-back from attempting to email the old alembic google group, so 
trying here instead!

Does Alembic have an equivalent to django's "makemigrations --check"?

This is a thing you can use in CI on pull requests to ensure no changes
have been made to the model that are not reflected in migrations.

I guess this boils down to "is the current db state plus any outstanding
migrations equal to the model in this checkout"...

cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/69a5ab37-ada6-4224-8473-423f13481c57%40withers.org.

Re: [sqlalchemy] Re: testing patterns with sqlalchemy 2.0

[Chris Withers]

On 01/09/2022 20:00, Jonathan Vanasco wrote:


 > Create an empty schema from the models using create_all?

This is what I usually do with smaller projects.  


When taking this approach, how do you ensure the accumulated schema 
migrations end up with a database that matches the one that create_all 
gives you?


When using unittest, some tests will use a fresh DB per test-run, others 
per-class, and others per-test. 


Yeah, pytest fixture scoping gives a great way to set these up.

Sometimes the tests dictate that, other 
times I control that with env vars.  That gives us the flexibility to 
work on a small section and do efficient test runs during development.


Creating a database for every unit test feels like something that would 
be slow enough to be annoying. How are you creating databases such that 
it's fast enough for this not to be the case?


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d779d7ad-1eff-e4e8-1f9c-9979b80a9146%40withers.org.

[sqlalchemy] testing patterns with sqlalchemy 2.0

[Chris Withers]

Hi All,

Are there any libraries (or anything in sqlalchemy itself!) that cover 
the pattern of running unit tests in against a database such that each 
test gets its own sterile environment in which to run? Postgres, if it 
helps. I've done some stuff with running in a subtransaction and rolling 
back at the end of the test before, but wanted to see if anything had 
become common enough to end up in a popular library yet...


What's the recommended way of getting a blank db to test against? Run 
alembic migrations in a session-level fixture? Create an empty schema 
from the models using create_all? Something else?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/a26f0df3-ec7a-bd11-9c2f-b8711499e03d%40withers.org.

Re: [sqlalchemy] Masking SAWarning: Flushing object ... with incompatible polymorphic identity

[Chris Withers]

On 20/06/2019 16:00, Mike Bayer wrote:



On Thu, Jun 20, 2019, at 3:14 AM, Chris Withers wrote:

Hi All,

I'm getting this warning:

SAWarning: Flushing object  with incompatible
polymorphic identity ; the object may not refresh
and/or load correctly (this warning may be suppressed after 10 
occurrences)

    (state_str(state), dict_[polymorphic_key]),

How can I indicate in my code that this is intentional and no warning
should be omitted?


you would need to change its class, or replace it with a new object of 
the correct class.  in both cases you probably need to pull the object 
out of the Session and put it in again.   if you are making a new 
object, you would use make_transient_to_detached() to give the object 
a persistent primary key identity then use session.add() to put it 
back in again:


https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=make_transient#sqlalchemy.orm.session.make_transient_to_detached

if you are doing "obj.__class__ = newclass", you probably need to do 
the same thing plus more because the identity key needs to have the 
new class in it, so you could make_transient() to erase the old 
identity key and then make_transient_to_detached() to give it back a 
new identity key, then session.add() it.


in both of *those* cases, the history of the object is reset to 
"clean", so anything you actually want to persist on the object needs 
to be applied after you've recreated it as a new class with a new 
identity key.


still another way, that would not reset the history, you can also 
manipulate state.key directly to have the correct identity key and 
then replace the object, e.g.


session.expunge(obj)
obj.__class__ = newclass
obj.polymorphic_identity = 'new_identity'
inspect(obj).key = sqlalchemy.orm.identity_key(instance=obj)
session.add(obj)

I'd probably want to make a new object and not change __class__, 
because changing __class__ in the general case indicates you're going 
to have an object where its __dict__ can be in an invalid state vs. 
what the class expects.


Yikes, that escalated quickly.

It probably makes sense just to lose the polymorphic-ness in this case, 
I'm not getting enough from it to make the above sensible...


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/4e56afd0-14ca-e767-7e54-39d0813b8f1a%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Masking SAWarning: Flushing object ... with incompatible polymorphic identity

[Chris Withers]

On 20/06/2019 16:00, Mike Bayer wrote:



this is not reproducing for me:

from sqlalchemy import util
import warnings
from sqlalchemy import exc

warnings.filterwarnings("ignore", category=exc.SAWarning)

util.warn_limited(
     "Flushing object %s with "
     "incompatible polymorphic identity %r; the "
     "object may not refresh and/or load correctly",
     (object(), 'foo'),
)



with the filter I get no output


Yay!  :-) I hate warn filtering, it never seems to work right 
for me, sorry, this is probably a rabbit hole that's not worth worrying 
about.


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/10f5c0b9-b181-2542-efd4-88ddd7630765%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Masking SAWarning: Flushing object ... with incompatible polymorphic identity

[Chris Withers]

On 20/06/2019 18:50, Jonathan Vanasco wrote:



On Thursday, June 20, 2019 at 3:14:06 AM UTC-4, Chris Withers wrote:


How can I indicate in my code that this is intentional and no warning
should be omitted?



Personal option:

I would not mask these.  I would let them persist and probably add a 
unittest to ensure they are invoked in certain situations.


Masking them runs the risk of masking other SqlAlchemy warnings that 
you'll want to know about.


Oh, I totally agree, it's more that I couldn't get them to mask *at 
all*. Once I got something working I was going to go back to a regex for 
just this case, but see other email...


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/35825814-e942-fd7d-4aac-ce35ecc1a9d7%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: merging old versions

[Chris Withers]

>> On Thu, Jun 20, 2019 at 2:37 AM Chris Withers > <mailto:ch...@withers.org>> wrote:
>>
>> Hi All,
>>
>> I have some versions that make use of the third party package I no
>> longer use, how do I collapse down alembic revisions that have
>> already
>> been executed everywhere?
>>
>> I found
>> 
https://stackoverflow.com/questions/34491914/alembic-how-to-merge-all-revision-files-to-one-file

>>
>> but that doesn't feel right...

On 20/06/2019 19:00, Mike Bayer wrote:


I think the basic idea is to create a database and codebase in the 
state of the target revision. Then autogenerate a migration from 
nothing to that revision - just like you would do when starting to use 
alembic from an existing schema. From there you can change the slug on 
it so that it works as the down_revision of later migrations and clear 
out the old unused migrations that you're replacing.


- Michael



I think that's what the stackoverflow answer says too. 


Yep.

At the moment 
that might be the most expedient approach.  


Okay, TBH here the case is more that I have a couple of migrations that 
use a package I want to remove as a dependency (sqlalchemy-searchable, 
which is really good, fwiw, just turns out "like" querying is actually 
going to work better for my needs), but I think I can just remove those 
bits once all the migrations are run through...


However I can see that 
Alembic might benefit from having a special option to autogenerate a 
model into Python code assuming no database to start with.    Feel free 
to propose though Alembic is suffering from lack of contributors right now.


Gotcha, I'll come with a PR rather just asking for something, but I'm 
suffering from the same problem you are ;-)


thanks as always,

Chris

--
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/b8fe3ab2-042a-a789-07a4-f874ef8c34eb%40withers.org.
For more options, visit https://groups.google.com/d/optout.

merging old versions

[Chris Withers]

Hi All,

I have some versions that make use of the third party package I no 
longer use, how do I collapse down alembic revisions that have already 
been executed everywhere?


I found 
https://stackoverflow.com/questions/34491914/alembic-how-to-merge-all-revision-files-to-one-file 
but that doesn't feel right...


Chris

--
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/933cd6aa-0e35-8716-3725-56947157103b%40withers.org.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Masking SAWarning: Flushing object ... with incompatible polymorphic identity

[Chris Withers]

Hi All,

I'm getting this warning:

SAWarning: Flushing object  with incompatible 
polymorphic identity ; the object may not refresh 
and/or load correctly (this warning may be suppressed after 10 occurrences)

    (state_str(state), dict_[polymorphic_key]),

I know why: I'm changing the polymorphic type of a row, but this is 
intentional (it's handling a PUT to the entity the row represents) and I 
don't believe I'll hit refresh or load problems since once the 
transaction is committed, I'm done with the session.


So, two questions:

How can I indicate in my code that this is intentional and no warning 
should be omitted?


Why does the following warning filter not work?

warnings.filterwarnings("ignore", category=SADeprecationWarning)
warnings.filterwarnings("ignore", category=SAWarning)

The SADeprecationWarning filter *does* work (and is somewhat legit as 
they're coming from a third party library that's awaiting a fix), but 
the SAWarning doesn't catch the above.


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/04e4866e-6636-626c-c4f3-16e31849aad2%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 10/06/2019 15:40, Mike Bayer wrote:




Okay, so sounds like in an ideal world, the framework should provide a
way to sub out that transaction middleware when unit testing and then
for functional testing, I just need to fall back to dropping everything
in the db, or having a fresh db created from scratch?



why can't you use the rollback fixture for functional testing?  whether 
or not you can assert that commit() was called, you can still have an 
enclosing transaction that gets rolled back.


Sorry, I missed this. This sounds right, but I seem to remember hitting 
problems with this, and then I got pulled onto another project.


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b611a4a4-a019-034b-f431-90dc35d4f082%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 05/06/2019 20:47, Mike Bayer wrote:




The panacea I'm after is to be able to run the DDL in a transaction, run
each test in a subtransaction off that which is rolled back at the end
of each test, but also be able to check that the code under test is
doing session.commit() where it should. Where the pattern we're
discussing, I certainly have the first two, but as you can see from what
I've just pasted above, the third one isn't there, but is it possible?



so your app code calls .flush(), .commit(), and .close() explicitly 
within each persistence method?  


Not really, it's more framework code, but most frameworks nowadays 
provide testing methods along the lines of:


client.get('/something?whatever=1')

...and they exercise the full request handling code, that tends to 
include some form of session lifecycle middleware or what have you.


and you need to make sure the .commit() 
is in the middle?   


Yep.

I'd probably use mock.patch for that level of 
granularity.   Usually I'd not have specific business methods calling 
commit() and close() at all, at the very least not .close().


Okay, so sounds like in an ideal world, the framework should provide a 
way to sub out that transaction middleware when unit testing and then 
for functional testing, I just need to fall back to dropping everything 
in the db, or having a fresh db created from scratch?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/c5c0a17c-0671-b86c-842e-f03cded132ba%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 05/06/2019 17:15, Mike Bayer wrote:



How come close() doesn't rollback the SessionTransaction if it throws it
away?


that's currently what .close() does, it discards the connection.   this 
is safe because the connection pool ensures transactions are rolled 
back.   This might have to change with some of the 2.0 things I'm 
thinking about but it's not clear yet.


Anyway, you don't get a rollback here because the session is bound to an 
external connection so the connection pool is not involved.   


Ah, right, so under normal conditions, if I just close a session, any 
transaction and subtransactions in flight will be rolled back when the 
connection is returned to the pool?



if you 
want to roll back the work that the application did, that would be your 
sub_transaction.rollback():


     sub_transaction = conn.begin_nested()
     try:

     session = Session()

     # code under test:
     event = Event(text='some stuff got done')
     session.add(event)
     session.flush()
     session.close()

     finally:
     sub_transaction.rollback()
     assert session.query(Event).count() == 0


Sure, but the test then fails when the code is correct:

try:
Base.metadata.create_all(bind=conn, checkfirst=False)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
try:

session = Session()

# code under test:
event = Event(text='some stuff got done')
session.add(event)
session.flush()
session.commit()
session.close()

# test:
sub_transaction.rollback()

assert session.query(Event).count() == 1

finally:
sub_transaction.rollback()

finally:
transaction.rollback()

The panacea I'm after is to be able to run the DDL in a transaction, run 
each test in a subtransaction off that which is rolled back at the end 
of each test, but also be able to check that the code under test is 
doing session.commit() where it should. Where the pattern we're 
discussing, I certainly have the first two, but as you can see from what 
I've just pasted above, the third one isn't there, but is it possible?


the test harness is giving you two choices.  you can look at the state 
of the DB after your program has done some things and *before* your 
harness has reversed its work, or you can look at the state of the DB 
*after* your harness has reversed its work. 


Unless I'm missing something, neither of these let the test confirm that 
the code under test has called commit() when it should.


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d7f7c9ac-2d32-6e60-efac-f86b501683f7%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 05/06/2019 16:41, Mike Bayer wrote:



Which gives me:

$ python sessions_are_weird.py
Traceback (most recent call last):
  File "sessions_are_weird.py", line 40, in 
    assert session.query(Event).count() == 0
AssertionError

Whereas after the rollback, I'd expect that count to be zero...


this is not working because of this:

     session.close()

     # test:
     session.rollback()


the session is closed first so rollback() will do nothing.


Yeah, that was my point: That session.close() appears to be the problem. 
It's a normal and required part of the code under test, but it throws 
away the SessionTransaction without rolling it back, so by the time the 
test does session.rollback(), it's doing it on a new SessionTransaction 
and so has no effect and the assertion fails because there event created 
is still around...


Put differently, the web app closes the session at the end of its 
request handling, which seems legit, right?


How come close() doesn't rollback the SessionTransaction if it throws it 
away?


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/806b6413-8537-8813-5c32-1238086300dd%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

(sorry, meant to send this to the list)

On 05/06/2019 15:52, Mike Bayer wrote:



That session.close() appears to be the problem. It's a normal and 
required part of the code under test, but it throws away the 
SessionTransaction without rolling it back, so by the time the test 
does session.rollback(), it's doing it on a new SessionTransaction and 
so has no effect and the assertion fails because there event created 
is still around...


Is this a bug or am I just confusing myself and everyone else?


I can run your script if you remove the "diary" thing from it


Hmm, I wonder what's different?

I'm on Python 3.7.1, SQLAlchemy 1.3.4, Postgres 11.3, here's a totally 
self contained script:


import os

from sqlalchemyimport Column, Integer, Text
from sqlalchemyimport create_engine
from sqlalchemy.ext.declarativeimport declarative_base
from sqlalchemy.ormimport sessionmaker

Base = declarative_base()

Session = sessionmaker()

class Event(Base):
__tablename__ ='entry' id = Column(Integer(),primary_key=True)
text = Column(Text)


engine = create_engine(os.environ['TEST_DB_URL'])
Session.configure(bind=engine)

conn = engine.connect()
transaction = conn.begin()
try:
Base.metadata.create_all(bind=conn,checkfirst=False)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
try:

session = Session()

# code under test: event = Event(text='some stuff got done')
session.add(event)
session.flush()
session.close()

# test: session.rollback()
assert session.query(Event).count() ==0 finally:
sub_transaction.rollback()

finally:
transaction.rollback()


Which gives me:

$ python sessions_are_weird.py
Traceback (most recent call last):
  File "sessions_are_weird.py", line 40, in 
    assert session.query(Event).count() == 0
AssertionError

Whereas after the rollback, I'd expect that count to be zero...

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d8b26fa3-aca9-7391-f1b8-c7900368b266%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 04/06/2019 23:21, Mike Bayer wrote:


On Tue, Jun 4, 2019, at 4:33 PM, Chris Withers wrote:


So, how do I roll back the further subtransaction created by the web
framework instantiating Session from a sessionmaker bound to the
connection in which begin_nested() has been called, which under non-test
running would actually be a top level transaction assuming I understand
the pattern correctly, in such as way that if the code-under-test has
committed on is session, the session being used to check expectations in
the unit test will see the results, but if it that commit has been
forgotten, it will not?


I'm not following all your code but if there are two sessions in play 
I'd probably try to avoid that, there should be only one Session you 
care about.  the test fixtures should be external to everything and 
make sure there's just the one session.   if there are two in play, 
I'm not sure how they both get bound to your test transaction.


Even this doesn't appear to be enough, here's the simplest reproducer 
script I can get to:


import os

from diary.modelimport Session, Base, Event, Types
from sqlalchemyimport create_engine

engine = create_engine(os.environ['TEST_DB_URL'])
Session.configure(bind=engine)

conn = engine.connect()
transaction = conn.begin()
try:
Base.metadata.create_all(bind=conn,checkfirst=False)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
try:

session = Session()

# code under test: event = 
Event(date='2019-06-02',type=Types.done,text='some stuff got done')
session.add(event)
session.flush()
session.close()

# test: session.rollback()
assert session.query(Event).count() ==0 finally:
sub_transaction.rollback()

finally:
transaction.rollback()


That session.close() appears to be the problem. It's a normal and 
required part of the code under test, but it throws away the 
SessionTransaction without rolling it back, so by the time the test does 
session.rollback(), it's doing it on a new SessionTransaction and so has 
no effect and the assertion fails because there event created is still 
around...


Is this a bug or am I just confusing myself and everyone else?

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/55721019-2de3-12b4-3796-90ca34b5ea4c%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 04/06/2019 23:21, Mike Bayer wrote:



I'm not following all your code but if there are two sessions in play 
I'd probably try to avoid that, there should be only one Session you 
care about. 


This comes back to something I asked you about on Twitter a while ago: 
the code under test gets its session by calling a sessionmaker; how can 
I have that return an existing session, which appears to be what you're 
suggesting, rather than a new suggestion, which appears to be all they 
can do.


the test fixtures should be external to everything 


I don't understand what you mean by this.

and make 
sure there's just the one session.   if there are two in play, I'm not 
sure how they both get bound to your test transaction.


I believe they start a nested transaction?

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/50bb9529-f98b-24aa-484f-1274c8fe3290%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] when does session.transaction come into being?

[Chris Withers]

On 04/06/2019 14:47, Mike Bayer wrote:



On Tue, Jun 4, 2019, at 3:05 AM, Chris Withers wrote:

Hi All,

What creates session.transaction? I can't spot get __getattr__ magic,
but the only place in the code I see it being created is in .begin(...),
which has a docstring saying that it should no longer be used, so I feel
like I must be missing something?



self.begin() is called inside the __init__ method of the Session when 
autocommit is at the default of False.


Okay, I must have missed that.

Just double checking: I thought you said that Session() would not block?
If it's calling self.begin(), how come that isn't a blocking operation?

cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e8bd076d-3d1e-a21f-35a9-6df6a94f0a65%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 04/06/2019 14:49, Mike Bayer wrote:



On Tue, Jun 4, 2019, at 2:15 AM, Chris Withers wrote:
Now, what I'm trying to test is that I haven't forgotten to include 
the "with session.transaction". The problem is that, without the 
transaction.rollback(), the test passes regardless of whether the 
"with session.transaction" is there, and with it there, it always fails.


What's the best way for writing tests that have the database setup DDL 
run in a transaction that's rolled back at the end of the session, 
each test in a subtransaction that gets rolled back at the end of each 
test, and also test that code under test is committing or rolling back 
as required?



So when you make the Session it has a .transaction that you start with, 
when you come out of your tests, that should be the same .transaction 
when you get the Session back.  If it's different, then the test did not 
leave the Session in the same state.  does that work ?


I think what I'm getting to here is that there are two Session instances
here, one that's set up by the web app under test, and one by pytest as 
a fixture.


However, if I understand correctly, they're both running inside the 
sub-transaction returned by engine.connect().begin_nested(), which is in 
turn inside the transaction returned by engine.connect().begin().


So, how do I roll back the further subtransaction created by the web 
framework instantiating Session from a sessionmaker bound to the 
connection in which begin_nested() has been called, which under non-test 
running would actually be a top level transaction assuming I understand 
the pattern correctly, in such as way that if the code-under-test has 
committed on is session, the session being used to check expectations in 
the unit test will see the results, but if it that commit has been 
forgotten, it will not?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/471aa85f-776f-b899-1fe8-2f3cc009da38%40withers.org.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] when does session.transaction come into being?

[Chris Withers]

Hi All,

What creates session.transaction? I can't spot get __getattr__ magic, 
but the only place in the code I see it being created is in .begin(...), 
which has a docstring saying that it should no longer be used, so I feel 
like I must be missing something?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f0a290f1-6af6-01d5-2b45-c6dd72cc982b%40withers.org.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] testing that a session is committed correctly

[Chris Withers]

Hi All,

I'm working with the pattern described at 
https://docs.sqlalchemy.org/en/13/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites 
along with pytest and FastAPI, an async web app framework with good 
support for running blocking code.


So, I have my fixtures:

@pytest.fixture(scope='session')
def db(client):
engine = Session.kw['bind']
conn = engine.connect()
transaction = conn.begin()
try:
Base.metadata.create_all(bind=conn,checkfirst=False)
yield conn
finally:
transaction.rollback()
Session.configure(bind=engine)

@pytest.fixture()
def transaction(db):
transaction = db.begin_nested()
try:
Session.configure(bind=db)
yield transaction
finally:
transaction.rollback()

@pytest.fixture()
def session(transaction):
return Session()

And a test:

def test_create_full_data(transaction, session, client):
response = client.post('/events/',json={
'date':'2019-06-02',
'type':'DONE',
'text':'some stuff got done' })
transaction.rollback()
actual = session.query(Event).one()
compare(actual.date,expected=date(2019,6,2))
compare(type(actual),expected=Done)
compare(response.json(),expected={
'id': actual.id,
'date':'2019-06-02',
'type':'DONE',
'text':'some stuff got done' })
compare(response.status_code,expected=201)

And some code:

@router.post("/",response_model=EventRead,status_code=201)
def create_object(
*,
session: Session = Depends(db_session),
event: EventCreate,
):
""" Create new Event. """ with session.transaction:
event = Event(**event.dict())
session.add(event)
session.flush()
session.expunge(event)
return event


Now, what I'm trying to test is that I haven't forgotten to include the 
"with session.transaction". The problem is that, without the 
transaction.rollback(), the test passes regardless of whether the "with 
session.transaction" is there, and with it there, it always fails.


What's the best way for writing tests that have the database setup DDL 
run in a transaction that's rolled back at the end of the session, each 
test in a subtransaction that gets rolled back at the end of each test, 
and also test that code under test is committing or rolling back as 
required?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/430d4156-d9ff-4349-5be5-62bee6ea4627%40withers.org.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Will calling a sessionmaker or closing a session block?

[Chris Withers]

Hi All,

Given this async function (asgi middleware, as it happens):

@app.middleware('http')
async def make_db_session(request: Request, call_next):
    request.state.db = Session()
    response = await call_next(request)
    request.state.db.close()
    return response

Would either the call to Session, which is a sessionmaker, or 
db.close(), be expected to block?
I can't remember whether instantiating a session will open a network 
connection, or whether close will similarly do network io, so thought 
I'd ask...


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/67f180de-a6c1-a28b-8c6f-b850b3775519%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] context manager for session lifecycle

[Chris Withers]

On 26/11/2018 06:15, Mike Bayer wrote:

On Sun, Nov 25, 2018 at 12:55 PM Chris Withers  wrote:


  > Soat the moment you can still say this:
  >
  >  with session.transaction:
  >
  > which will do the commit and rollback but not the close().

 Potentially silly question: when is it useful to commit the session but
 not close it?


the Session is maintaining the scopes of many different objects, which
are, a Connection, a transaction, and all the objects you're deailng
with.  It's pretty common folks want to commit their transaction and
then afterward keep working with all the objects they have attached to
the session to load more data or commit more changes.


Gotcha, so:

session = Session()
...
session.commit() / session.rollback() / savepoints, etc
...
session.close()

cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] context manager for session lifecycle

[Chris Withers]

On 18/11/2018 23:38, Mike Bayer wrote:
On Sun, Nov 18, 2018, 6:22 PM Chris Withers <mailto:ch...@withers.org> wrote:


 >> Does SQLAlchemy provide a context manager that handles the session
 >> lifecycle described here?
 >>

https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#managing-transactions
 >>
 >
 > I guess that documentation should be updated, that yes that workflow
 > is available, it's documented in terms of using "begin()" with
 > autocommit mode:
 >
 >

https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#session-autocommit

That chunk is a little confusing, I wish there was a (default) mode
that
said "start a transaction when I ask for one, don't have one open
otherwise" - "idle in transaction" does make Postgres DBAs sad...

This is how all dbapi drivers work.  There's no begin method.   The 
Session won't leave you idle in transaction because it doesn't start 
working on a connection (and hence start a transaction) until you start 
working with it.


Ah, okay.

Sowhat happens if you run SQL on your Session if you haven't called 
"begin()"?  Raise an error ? 


That would be fine :-)

 I would say, when you make your 
Session(), thats the start of your work in a defer to thread


Yep, using the context manager I snipped out above is working fine. 
(although I mistakenly tried to have the context manager wrapped 
*around* the deferToThread, and yeah, that turns out to end pretty badly 
;-) - not had Python segfault on my in a while!)



 > Soat the moment you can still say this:
 >
 >      with session.transaction:
 >
 > which will do the commit and rollback but not the close().

Potentially silly question: when is it useful to commit the session but
not close it?


Still curious about this, gues I could RTSL or find the bit of the 
manual, but I'd prefer to not misinterpret either!


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] context manager for session lifecycle

[Chris Withers]

On 16/11/2018 01:09, Mike Bayer wrote:

Does SQLAlchemy provide a context manager that handles the session
lifecycle described here?
https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#managing-transactions

I mean, it should be as simple as the following, right?

@contextmanager
def transaction(session):
  session.begin()
  try:
  yield
  session.commit()
  except:
  session.rollback()
  raise
  finally:
  session.close()

...I'd just prefer not to copy and paste that across libraries and
projects ;-)


I guess that documentation should be updated, that yes that workflow
is available, it's documented in terms of using "begin()" with
autocommit mode:

https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#session-autocommit


That chunk is a little confusing, I wish there was a (default) mode that 
said "start a transaction when I ask for one, don't have one open 
otherwise" - "idle in transaction" does make Postgres DBAs sad...



I see your example also uses begin(), but that's not what we have at
https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#managing-transactions,


Right, I guess my knowledge of begin() is hazier than I thought...


that's a Session without the begin() as it's not in autocommit.
Which I don't want people to use :)


I don't want autocommit, but I do want an explicit start and end of 
transaction, and indeed, session, particularly in my current project as 
all the SQLA stuff has to be done in a twisted deferToThread ;-)



Soat the moment you can still say this:

 with session.transaction:

which will do the commit and rollback but not the close().


Potentially silly question: when is it useful to commit the session but 
not close it?



there's kind of too many options with the Session right now to make
this one size fits all unless you have suggestions.


Indeed, would you believe after almost 10 years, I think sessionmaker() 
finally made sense to my brain?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] context manager for session lifecycle

[Chris Withers]

Hi All,

I'm sure I've asked this before, but a google through the archives 
couldn't find it.


Does SQLAlchemy provide a context manager that handles the session 
lifecycle described here?

https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#managing-transactions

I mean, it should be as simple as the following, right?

@contextmanager
def transaction(session):
    session.begin()
    try:
    yield
    session.commit()
    except:
    session.rollback()
    raise
    finally:
    session.close()

...I'd just prefer not to copy and paste that across libraries and 
projects ;-)


I remember Mike having good reasons against this, but I can't with them, 
so what would be bad about making sessions work like this:


with session:
   ...

...and have that do the above?

cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] adding entirely custom constraints

[Chris Withers]

awesome, thanks!

On 01/02/2018 15:50, Mike Bayer wrote:

On Thu, Feb 1, 2018 at 3:27 AM, Chris Withers <ch...@withers.org> wrote:

Hi,

So, I need to add an exclude constraint to a postgres table which has a
boolean column, but:

ProgrammingError: (psycopg2.ProgrammingError) data type boolean has no
default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default
operator class for the data type.

Sounds like the way to do this is:

https://www.postgresql.org/message-id/OfficeNetEmail.2b9.8648e2b7c49ce986.144ee6b67a2%40prod2

create table xx(b bool, tr tsrange, exclude using gist((b::int) with =, tr
with &&));

That, unfortunately makes SQLALchemy's ExcludeConstraint implementation sad:

sqlalchemy/util/_collections.py", line 194, in __getitem__
 return self._data[key]
KeyError: '(best::int)'

Before I go and submit a patch to ExcludeConstraint to support expressions
rather than just columns (that looks like a lot of work :-/), how can I just
add a constraint that I supply as text and put in __table_args__ somehow?

just DDL() as always
http://docs.sqlalchemy.org/en/latest/core/ddl.html?highlight=ddl#custom-ddl





cheers,

Chris

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
description.
--- 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] adding entirely custom constraints

[Chris Withers]

Hi,

So, I need to add an exclude constraint to a postgres table which has a 
boolean column, but:


ProgrammingError: (psycopg2.ProgrammingError) data type boolean has no 
default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a 
default operator class for the data type.


Sounds like the way to do this is:

https://www.postgresql.org/message-id/OfficeNetEmail.2b9.8648e2b7c49ce986.144ee6b67a2%40prod2

create table xx(b bool, tr tsrange, exclude using gist((b::int) with =, 
tr with &&));


That, unfortunately makes SQLALchemy's ExcludeConstraint implementation sad:

sqlalchemy/util/_collections.py", line 194, in __getitem__
    return self._data[key]
KeyError: '(best::int)'

Before I go and submit a patch to ExcludeConstraint to support 
expressions rather than just columns (that looks like a lot of work 
:-/), how can I just add a constraint that I supply as text and put in 
__table_args__ somehow?


cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] finding all tables with foreign keys to a model

[Chris Withers]

Hi All,

How can I introspect from a declaratively mapped model all the other 
models/tables that have foreign keys to it?

This keeps like something the ORM layer must know about...

cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: unit testing migration code

[Chris Withers]

Has anyone done anything like an equivalent of the following but for 
Alembic?


https://github.com/plumdog/django_migration_testcase

cheers,

Chris

On 01/12/2017 15:06, Mike Bayer wrote:

it's kind of a PITA but in Openstack we have fixtures which actually
run all the alembic (or sqlalchemy-migrate) migrations on a new
database.  Some of the more ambitious projects even write unit tests
in between each migration that use inspect() to check that the
database state is what's expected.

so to do things like that, you need a fixture which can:

1. create a new database (you probably need to produce a randomized
name for concurrency)
2. invoke alembic to each revision individually (you can do this
through alembic API:
http://alembic.zzzcomputing.com/en/latest/api/commands.html
3. have a dispatch which can call upon test cases linked to that rev,
like "def test_aabbccddee_does_thing_one()"
4. drops the database


and...that's how you do it !



On Thu, Nov 30, 2017 at 1:54 PM, Chris Withers <ch...@simplistix.co.uk> wrote:

Hi All,

How would I add test coverage for this sort of code?

https://coveralls.io/builds/14408741/source?filename=mortar_mixins%2Fmigrations.py

cheers,

Chris

--
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.


--
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.

Re: [PossibleSpam][5.0] Re: [sqlalchemy] Concise, Pythonic query syntax

[Chris Withers]

Fantastic, thanks!

On 06/11/2017 22:46, Jones, Bryan wrote:

All,

I've just updated the package to use the MIT license.

Bryan

On Mon, Nov 6, 2017 at 2:22 PM, Jones, Bryan <bjo...@ece.msstate.edu 
<mailto:bjo...@ece.msstate.edu>> wrote:


Chris,

I'm open to BSD or MIT as well. Looking, I see that SQLAlchemy is
MIT-license, so I can re-license it to that.

Bryan

On Mon, Nov 6, 2017 at 12:25 PM, Chris Withers <ch...@withers.org
<mailto:ch...@withers.org>> wrote:

Great looking library, shame about the license.

You particularly attached to GPL3 or would you be amenable to
BSD or MIT?

Chris

On 03/11/2017 21:52, Bryan Jones wrote:

All,

I've just released the pythonic_sqlalchemy_query package on
PyPI, which provides concise, Pythonic query syntax for
SQLAlchemy. For example, these two queries produce identical
results:

pythonic_query  =  session.User['jack'].addresses['j...@google.com
<mailto:j...@google.com>']
traditional_query  =  (
 # Ask for the Address...
 session.query(Address).
 # by querying a User named 'jack'...
 select_from(User).filter(User.name  ==  'jack').
 # then joining this to the Address 'j...@google.com
<mailto:j...@google.com>`.
 join(Address).filter(Address.email_address  ==  'j...@google.com 
<mailto:j...@google.com>')
For more information, see:
http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html
<http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html>

Thanks to Mike for his feedback and encouragement to post
this on PyPI. I've addressed the weaknesses he mentioned and
added more features and tests. Comments and feedback are
welcome. Enjoy!

Bryan

On Friday, June 16, 2017 at 4:54:36 PM UTC-5, Mike Bayer wrote:



On 06/15/2017 04:11 PM, Bryan Jones wrote:
> All,
>
> While working on my SQLAlchemy-based application, I
noticed an
> opportunity to provide a more concise, Pythonic query
syntax. For
> example, User['jack'].addresses produces a Query for
the Address of a
> User named jack. I had two questions
>
>  1. Has someone already done this? If so, would you
provide a link?
>  2. If not, would this be reasonable for inclusion in
SQLAlchemy, either
>     as an ORM example, or as a part of the core code
base? If so, I can
>     submit a pull request.
>


Hi Bryan -

thanks for working on this.

I can see a lot of variety of ways that systems like this
might work.
For example, I notice we are building up an expression,
but instead of
sending it to a function like
session.run_query(my_query), you've
flipped it around to say my_query.to_query(session).   We
do have a
similar approach with the "baked" query API, where you
build up
BakedQuery without a session then call
baked_query.for_session(session).

It seems like there's maybe a theme to this recipe which
is that it
makes a certain subset of query structures more succinct,
but at the
expense of serving only a limited set of types of
queries.  It seems
like an expression can either select from just the lead
entity, or from
a single column, then if I wanted more entities I need to
drop into
query.add_entity().  It's not clear how I'd select only a
SQL
expression, e.g. "SELECT lower(fullname) FROM jack", etc.
  I do like
how the functionality of __getitem__ is essentially
pluggable.   That's
a nice concept to add to a "query convenience" system.

There are other patterns like this, the most common are
entity-bound
query generators like "User.query" which these days is
mostly popular
with Flask.  There's a lot of query helpers and facades
around within
individual projects.   However in SQLAlchemy itself,
we've moved away
from providing or endorsing helpers like these built in
due to the fact
that they create one API for running the subset of
queries that happen
to fall under the convenience syntax, and then you have
to use a
different 

Re: [sqlalchemy] Concise, Pythonic query syntax

[Chris Withers]

Great looking library, shame about the license.

You particularly attached to GPL3 or would you be amenable to BSD or MIT?

Chris

On 03/11/2017 21:52, Bryan Jones wrote:

All,

I've just released the pythonic_sqlalchemy_query package on PyPI, 
which provides concise, Pythonic query syntax for SQLAlchemy. For 
example, these two queries produce identical results:


pythonic_query  =  session.User['jack'].addresses['j...@google.com']
traditional_query  =  (
 # Ask for the Address...
 session.query(Address).
 # by querying a User named 'jack'...
 select_from(User).filter(User.name  ==  'jack').
 # then joining this to the Address 'j...@google.com`.
 join(Address).filter(Address.email_address  ==  'j...@google.com')
For more information, see:
http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html

Thanks to Mike for his feedback and encouragement to post this on 
PyPI. I've addressed the weaknesses he mentioned and added more 
features and tests. Comments and feedback are welcome. Enjoy!


Bryan

On Friday, June 16, 2017 at 4:54:36 PM UTC-5, Mike Bayer wrote:



On 06/15/2017 04:11 PM, Bryan Jones wrote:
> All,
>
> While working on my SQLAlchemy-based application, I noticed an
> opportunity to provide a more concise, Pythonic query syntax. For
> example, User['jack'].addresses produces a Query for the Address
of a
> User named jack. I had two questions
>
>  1. Has someone already done this? If so, would you provide a link?
>  2. If not, would this be reasonable for inclusion in
SQLAlchemy, either
>     as an ORM example, or as a part of the core code base? If
so, I can
>     submit a pull request.
>


Hi Bryan -

thanks for working on this.

I can see a lot of variety of ways that systems like this might work.
For example, I notice we are building up an expression, but
instead of
sending it to a function like session.run_query(my_query), you've
flipped it around to say my_query.to_query(session).   We do have a
similar approach with the "baked" query API, where you build up
BakedQuery without a session then call
baked_query.for_session(session).

It seems like there's maybe a theme to this recipe which is that it
makes a certain subset of query structures more succinct, but at the
expense of serving only a limited set of types of queries.  It seems
like an expression can either select from just the lead entity, or
from
a single column, then if I wanted more entities I need to drop into
query.add_entity().  It's not clear how I'd select only a SQL
expression, e.g. "SELECT lower(fullname) FROM jack", etc. I do like
how the functionality of __getitem__ is essentially pluggable.  
That's
a nice concept to add to a "query convenience" system.

There are other patterns like this, the most common are entity-bound
query generators like "User.query" which these days is mostly popular
with Flask.  There's a lot of query helpers and facades around within
individual projects.   However in SQLAlchemy itself, we've moved away
from providing or endorsing helpers like these built in due to the
fact
that they create one API for running the subset of queries that
happen
to fall under the convenience syntax, and then you have to use a
different API for queries that fall outside of the convenience
syntax.
When a single product presents multiple, overlapping APIs, it
generally
causes confusion in learning the product.    It's easier for
people to
understand a particular convenience API as an entirely separate
add-on.

SQLAlchemy certainly suffers from this in any case, such as that
we have
both "classical mapping" and "declarative", "Core" and "ORM" querying
styles, things like that; though historically, we've put lots of
effort
into making it so that if you are using ORM Query, you really
don't need
to use Core at all for just about any structure of query, and
similarly
Declarative has totally replaced mapper() in virtually all cases.
  Long
ago we bundled a convenience library called SQLSoup, which I
eventually
broke out into a separate project, and then I later added the
"automap"
extension as a more fundamentals-based system to get the same effect
without using an alternate query API.

I've always encouraged people to write other kinds of query
languages on
top of SQLAlchemy's language.   There's another style that I've
yet to
see someone implement for SQLAlchemy, even though it's quite doable,
which is to parse Python AST into SQLAlchemy queries, with an
emphasis
on generator expressions acting like SELECT constructs.  There are
two
SQL libraries, one very old and unknown called GeniuSQL, and one
current
ORM called Pony, that use this approach.  I'm not a fan 

Re: [sqlalchemy] mapper.order_by deprecated?

[Chris Withers]

Gotcha. Is there any way to specify a default ordering for queries 
against a model?


(similar to that offered by the Django ORM?)

cheers,

Chris


On 08/05/2017 23:51, mike bayer wrote:
because, it only works for a really simplistic case, and for all the 
other cases, I have no idea what it currently does, nor what it should 
do.


Assume A and B have order_by.I have no idea what the output is, or 
should be, for:


s.query(A, B)
s.query(B, A)
s.query(A).join(B)
s.query(A).select_from(B).join(A)
s.query(A, B).select_from(B).join(A)
s.query(A.id)
s.query(A, B.id)
s.query(A.id, B)
s.query(A.id, B.id)
s.query(B.id, A.id)

etc





On 05/08/2017 06:21 AM, Chris Withers wrote:

Hi All,

I see mapper.order_by is deprecated in the latest release. Why is that?

cheers,

Chris





--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] mapper.order_by deprecated?

[Chris Withers]

Hi All,

I see mapper.order_by is deprecated in the latest release. Why is that?

cheers,

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: adding an auto increment column to an existing table

[Chris Withers]

On 17/01/2017 15:07, mike bayer wrote:



Because there's no data in a brand new table, the server default isn't
needed to create the not-null column.


No needed, but it is created, isn't that the point of autoincrement=True?


for postgresql, autoincrement=True means that if the column is marked
primary_key=True it will use the datatype SERIAL for that column, which
in PG does mean the sequence is generated and added as the server side
default.


Would you expect a SERIAL on Postgres to end up being an inteeger with a 
default of a sequence when viewed in psql?


What happens with autoincrement on a non-primary-key column? My 
experiences suggests it does nothing...



http://stackoverflow.com/a/19262262/216229 implies the same works
for Alembic, but it didn't work at all for me. So I was wondering if
that answer was wrong, or if I was doing something wrong.


Still wondering if this answer is wrong or if I'm doing something
wrong...


that SO answer is showing op.create_table().   your example was just for
op.add_column().  super different.


I have to admit that, at the column level, that's surprising to me. 
Where can I see the differences between a column created as part of 
create_table() verus as part of an add_column()?


cheers,

Chris

--
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.

Re: [sqlalchemy] Selecting from polymorphic tables without selecting polymorphic columns

[Chris Withers]

On 17/01/2017 18:38, mike bayer wrote:



On 01/17/2017 01:05 PM, Chris Withers wrote:

Potentially related issue, given:

class TheTable(Base):
__tablename__ = 'stuff'
__mapper_args__ = dict(
polymorphic_on='type',
polymorphic_identity='base',
)
type = Column(String)
col = Column(String)

class Model1(TheTable):
__mapper_args = dict(
polymorphic_identity='model1',
)

class Model2(TheTable):
__mapper_args = dict(
polymorphic_identity='model2',
)

session.add(Model1(col='a'))
session.add(Model2(col='b'))
session.flush()

I'd now expect:

self.session.query(Model1)

...to turn the Model1 instance, but in my tests it returns nothing.


you misspelled "__mapper_args__"


D'oh, thanks.

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Selecting from polymorphic tables without selecting polymorphic columns

[Chris Withers]

Potentially related issue, given:

class TheTable(Base):
__tablename__ = 'stuff'
__mapper_args__ = dict(
polymorphic_on='type',
polymorphic_identity='base',
)
type = Column(String)
col = Column(String)

class Model1(TheTable):
__mapper_args = dict(
polymorphic_identity='model1',
)

class Model2(TheTable):
__mapper_args = dict(
polymorphic_identity='model2',
)

session.add(Model1(col='a'))
session.add(Model2(col='b'))
session.flush()

I'd now expect:

self.session.query(Model1)

...to turn the Model1 instance, but in my tests it returns nothing.

self.session.query(TheTable) returns both rows, but both as TheTable 
instances, which I find a little surprising.


Chris

On 16/01/2017 17:54, mike bayer wrote:

issue

https://bitbucket.org/zzzeek/sqlalchemy/issues/3891/single-inh-criteria-should-be-added-for


is added.   Targeted at 1.2 as it will break applications unknowingly
relying upon the bug right now.

For now say func.count(Manager.employee_id), e.g. put the entity in the
columns clause.



On 01/16/2017 12:23 PM, Michael Williamson wrote:

Hello!

I have a use case where I want to select from a polymorphic table, but
without selecting any columns from that table. As a simple example,
consider selecting the count of all rows. When I write something like:

sess.query(func.count(1)).select_from(Manager).all()

It seems to be equivalent to:

sess.query(func.count(1)).select_from(Employee).all()

(where Manager inherits from Employee).

Is this intended, or is this a bug? If the former, what's the suggested
approach to writing such queries? To filter on the discriminator
explicitly?

For reference, I was able to reproduce the issue with a test case in
test/orm/inheritance/test_single.py:

def test_select_from_inherited_tables(self):
Manager, Engineer, Employee = (self.classes.Manager,
self.classes.Engineer, self.classes.Employee)

sess = create_session()
m1 = Manager(name='Tom', manager_data='data1')
e1 = Engineer(name='Kurt', engineer_info='knows how to hack')
sess.add_all([m1, e1])
sess.flush()

eq_(
sess.query(func.count(1)).select_from(Manager).all(),
[(1, )]
)

Thanks

Michael





--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] single table inheritance and instrument_class events

[Chris Withers]

On 17/01/2017 15:08, mike bayer wrote:


On 01/17/2017 06:15 AM, Chris Withers wrote:

Great, thanks. I assume has_inherited_table returns False where the
table is defined on the class itself?



it looks like has_inherited_table is just looking for non-None __table__
attribute up the inheritance chain.


Ah well, tests pass, must be fine *head back in sand* ;-)

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] single table inheritance and instrument_class events

[Chris Withers]

Great, thanks. I assume has_inherited_table returns False where the 
table is defined on the class itself?


(ie: normal models, and the "base" model in the case of single table 
inheritance)


Chris

On 16/01/2017 17:49, mike bayer wrote:

at the declarative level we have this:

http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.has_inherited_table


this might be better since you're really looking for a "table" up above

On 01/16/2017 12:05 PM, Chris Withers wrote:

Hi All,

If I'm using instrument_class events to add some constraints to a table,
what's the 'right' way to spot when it's a subclass is being
instrumented? (where I'm guessing I shouldn't add the constraints).

My current attempt is here:

https://github.com/Mortar/mortar_mixins/commit/b82bea1dbc1393e24116943804b192df056a70e7



...but that feels a bit hacky.

Thoughts?

Chris





--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] single table inheritance and instrument_class events

[Chris Withers]

Hi All,

If I'm using instrument_class events to add some constraints to a table, 
what's the 'right' way to spot when it's a subclass is being 
instrumented? (where I'm guessing I shouldn't add the constraints).


My current attempt is here:

https://github.com/Mortar/mortar_mixins/commit/b82bea1dbc1393e24116943804b192df056a70e7

...but that feels a bit hacky.

Thoughts?

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: adding an auto increment column to an existing table

[Chris Withers]

Okay, so this worked:

op.execute(CreateSequence(Sequence("observation_id_seq")))
op.add_column('observation', sa.Column(
'id', sa.Integer(), nullable=False,
server_default=sa.text("nextval('observation_id_seq'::regclass)")
))
op.drop_constraint('observation_pkey', table_name='observation')
op.create_primary_key('observation_pkey', 'observation', ['id'])

...but how come my original attempt didn't?

cheers,

Chris

On 10/01/2017 08:03, Chris Withers wrote:

So, I screwed up and realised I really want an auto-incrementing integer
as the primary key for a bunch of tables.

I've changed my models, got all the tests passing and now I need to get
the migrations done, I have:


op.add_column('observation',
  sa.Column('id', sa.Integer(), nullable=False,
  autoincrement=True))
op.drop_constraint('observation_pkey', table_name='observation')
op.create_primary_key('observation_pkey', 'observation', ['id'])


According to this answer, this should work:

http://stackoverflow.com/a/19262262/216229

...but when running the migration, I get:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) column "id"
contains null values
 [SQL: 'ALTER TABLE observation ADD COLUMN id INTEGER NOT NULL']

...so what am I doing wrong?

cheers,

Chris



--
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.

adding an auto increment column to an existing table

[Chris Withers]

So, I screwed up and realised I really want an auto-incrementing integer 
as the primary key for a bunch of tables.


I've changed my models, got all the tests passing and now I need to get 
the migrations done, I have:



op.add_column('observation',
  sa.Column('id', sa.Integer(), nullable=False,
  autoincrement=True))
op.drop_constraint('observation_pkey', table_name='observation')
op.create_primary_key('observation_pkey', 'observation', ['id'])


According to this answer, this should work:

http://stackoverflow.com/a/19262262/216229

...but when running the migration, I get:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) column "id" 
contains null values

 [SQL: 'ALTER TABLE observation ADD COLUMN id INTEGER NOT NULL']

...so what am I doing wrong?

cheers,

Chris

--
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.

Re: [sqlalchemy] Load sqlalchemy orm model from dict

[Chris Withers]

On 07/01/2017 00:20, Daniel Kraus wrote:

Hi!

mike bayer  writes:

you're looking for session.merge() but if you're looking to save on a
SELECT you might also want to send in load=False - and if you are
starting with a fresh (non-pickled) object you probably need to call
make_transient_to_detached first so that it acts like it was loaded from
the database first.

Nice. Works like I wanted :)
In fact, if your dict is created from a User object in the first place, 
I'd just keep that User object, detach it from the session and put it in 
your cache...


Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: patterns for automated tests of migrations

[Chris Withers]

Hi Tom,

This is certainly interesting, but even more heavyweight than I was 
looking for ;-)
I love the idea of really making sure the schema that results from 
running all the migrations is the same as the one from doing a 
create_all with your metadata.

Does alembic have anything like django's squashmigrations command yet?

Anyway, what I'm interested in is testing migrations in the presence of 
data, particularly migrations that have to do "fun stuff" with existing 
rows to create new rows. Those feel more like unit tests to me - "run a 
bunch of individual scenarios", rather than your excellent but 
integration-y "run them all and make sure they build the expected schema".


Thoughts?

Chris

On 23/11/2016 09:20, Tom Lazar wrote:

hi chris,

here’s how we do it in all our projects:

https://github.com/pyfidelity/rest-seed/blob/master/backend/backrest/tests/test_migrations.py

basically, our migrations start with an empty database, so we run them, dump 
the resulting SQL, then create a new database using the `metadata.create_all` 
feature and then normalize the results and compare. if there is a mismatch the 
test fails.

if you want to write more elaborate tests involving actual data, this should be 
a good starting point, though

HTH,

tom



On 23 Nov 2016, at 10:15, Chris Withers <ch...@simplistix.co.uk> wrote:

Hi All,

How do you go about writing automated tests for a migration?

I don't often do this, but when migrations involve data, I prefer to but now I 
don't know how ;-)
There's nothing in the docs, right? (or am I missing something obvious)

cheers,

Chris

--
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.


--
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.

Re: [sqlalchemy] regression with 1.1.0

[Chris Withers]

People use pg8000? ;-)

I jest, but under what circumstances does pg8000 make a better choice 
that psycopg2?


Chris

On 07/10/2016 14:40, mike bayer wrote:

FYI pg8000 raises ProgrammingError on these


On 10/07/2016 08:56 AM, Mike Bayer wrote:



On 10/07/2016 02:36 AM, Chris Withers wrote:

On 06/10/2016 15:04, Mike Bayer wrote:


So Mortar seems to be a heavily SQLAlchemy-integrated product.


It has a very small user base ;-)


I'd like
to point out that whenever we do these year-long release cycles, I put
out a lot of betas very early on that hopefully contain all the 
biggish
changes.  This one has been available on pypi since June 16.  
Nobody has

said anything about it.


Right, unfortunately my testing strategy is generally to use the latest
official releases (ie: what "pip install x" would pick) so as not to 
get

a lot of noise from the nightlies.

With SQLAlchemy, I'd happily make an exception, but how do I change my
travis.yml to have an axis for SQLAlchemy version that is "latest
stable" and "latest beta"?


there's a pip flag at that level.  I use jenkins myself but if we ask
the usual suspects on twitter we can probably find an answer :)






So, this code path used to previously raise an IntegrityError and now
raises a CompileError. What was the driver for that? I can certainly
see
there being code in the wild that was expecting an IntegrityError and
will now fail...


The change itself, including the text of that exception verbatim so 
that

google searches can find it, is described in detail here:
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#the-autoincrement-directive-is-no-longer-implicitly-enabled-for-a-composite-primary-key-column 




.


Right, that change seems sensible here, although not sure


I wouldn't have it raise that CompileError if I wasn't changing the API
on a very small set of users.   The suffering spread by the old way
autoincrement worked had to come to an end.





There's definitely code that looks for IntegrityError in the wild,
however such code (such as what I've written for Openstack) is 
doing so
in order to detect conditions that cannot be caught beforehand, 
such as
a row that conflicts with a unique constraint or a row that is 
rejected

as a concurrent update.

In this case, you have a Table that has been configured to not expect
the database to generate any kind of identifier for a primary key 
column

(which is the change here, previously, it was),


Sorry, don't follow the bit in brackets; the test model has no integer
columns.

present in the INSERT statement.  So it is known ahead of time that 
this
will either fail unconditionally, or the Table is 
mis-configured.The

change is specifically to alert users that this is now a
mis-configuration.   E.g. so that I wouldn't get a "regression!" email
:)  .


I think I follow that, but my test is verifying that:

session.add(Model(period='...'))
session.save()

(nb: Model is erroneously instantiated with no string primary key)

...gives an IntegrityError, which I think it what most people would
expect. There's no mis-configuration there, it's just a bad object
which, if inserted, would result in a row with now primary key.


Right this is the "it will fail unconditionally" choice.   I agree
sending to the DB and getting the error back is less mysterious.  But I
don't see a way to distinguish between someone who was relying upon
implicit autoincrement for composite PK and who isn't.   I can just flip
my mind and say, OK, your scenario is way more likely than the other
one, and we flip.  Then I get people saying "why are there
integrityerrors all the sudden w/ no explanation?" .   Sure, I'll make
it a warning. Release will be today.  Please test w/ the betas next
time.






There is existing precedent for SQLAlchemy catching a NULL primary key
value before the INSERT is actually emitted, the ORM has done this 
for a

long time.


This is for the identity map, right? That's interesting, what exception
does it raise in that circumstance? (More out of curiosity than
anything: I'm wondering why that exception wasn't raised in this test
case and I ended up with an IntegrityError)

cheers,

Chris





--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] regression with 1.1.0

[Chris Withers]

On 06/10/2016 15:04, Mike Bayer wrote:


So Mortar seems to be a heavily SQLAlchemy-integrated product.


It has a very small user base ;-)


I'd like
to point out that whenever we do these year-long release cycles, I put
out a lot of betas very early on that hopefully contain all the biggish
changes.  This one has been available on pypi since June 16.  Nobody has
said anything about it.


Right, unfortunately my testing strategy is generally to use the latest 
official releases (ie: what "pip install x" would pick) so as not to get 
a lot of noise from the nightlies.


With SQLAlchemy, I'd happily make an exception, but how do I change my 
travis.yml to have an axis for SQLAlchemy version that is "latest 
stable" and "latest beta"?



So, this code path used to previously raise an IntegrityError and now
raises a CompileError. What was the driver for that? I can certainly see
there being code in the wild that was expecting an IntegrityError and
will now fail...


The change itself, including the text of that exception verbatim so that
google searches can find it, is described in detail here:
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#the-autoincrement-directive-is-no-longer-implicitly-enabled-for-a-composite-primary-key-column
.


Right, that change seems sensible here, although not sure



There's definitely code that looks for IntegrityError in the wild,
however such code (such as what I've written for Openstack) is doing so
in order to detect conditions that cannot be caught beforehand, such as
a row that conflicts with a unique constraint or a row that is rejected
as a concurrent update.

In this case, you have a Table that has been configured to not expect
the database to generate any kind of identifier for a primary key column
(which is the change here, previously, it was),


Sorry, don't follow the bit in brackets; the test model has no integer 
columns.



present in the INSERT statement.  So it is known ahead of time that this
will either fail unconditionally, or the Table is mis-configured.The
change is specifically to alert users that this is now a
mis-configuration.   E.g. so that I wouldn't get a "regression!" email
:)  .


I think I follow that, but my test is verifying that:

session.add(Model(period='...'))
session.save()

(nb: Model is erroneously instantiated with no string primary key)

...gives an IntegrityError, which I think it what most people would 
expect. There's no mis-configuration there, it's just a bad object 
which, if inserted, would result in a row with now primary key.



There is existing precedent for SQLAlchemy catching a NULL primary key
value before the INSERT is actually emitted, the ORM has done this for a
long time.


This is for the identity map, right? That's interesting, what exception 
does it raise in that circumstance? (More out of curiosity than 
anything: I'm wondering why that exception wasn't raised in this test 
case and I ended up with an IntegrityError)


cheers,

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] regression with 1.1.0

[Chris Withers]

Hi All,

Since 1.1.0 was released yesterday, one of my library's nightly builds 
have started failing:


https://travis-ci.org/Mortar/mortar_mixins/jobs/165422873

Error was:

AssertionError: CompileError(u"Column 'model.period' is marked as a 
member of the primary key for table 'model', but has no Python-side or 
server-side default generator indicated, nor does it indicate 
'autoincrement=True' or 'nullable=True', and no explicit value is 
passed.  Primary key columns typically may not store NULL. Note that as 
of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for 
composite (e.g. multicolumn) primary keys if 
AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the 
columns in the primary key. CREATE TABLE statements are impacted by this 
change as well on most backends.",) raised, 'sqlalchemy.exc.IntegrityError'> expected


The test is here:

https://github.com/Mortar/mortar_mixins/blob/master/tests/test_temporal.py#L268

So, this code path used to previously raise an IntegrityError and now 
raises a CompileError. What was the driver for that? I can certainly see 
there being code in the wild that was expecting an IntegrityError and 
will now fail...


(I guess I also have a niggle that this feels like SQLAlchemy is doing 
more work than it was before, will this have performance implications?)


Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] changing the order of columns in primary keys, including when generated by mixins

[Chris Withers]

On 22/09/2016 14:55, Mike Bayer wrote:


On 09/22/2016 07:30 AM, Chris Withers wrote:

How do you control the order in which columns are added to a
multi-column primary key when using the declarative primary_key=True
syntax?
How about when one of those columns comes from a mixin?


without mixins, the columns are processed in the order that they were
created - they all have a global "created index" number that is
consulted outside of the order in which the objects appear on a
declarative class.


Right, so you'd just re-order the columns...


With mixins, that all gets screwed up.  In that case you'd use a
PrimaryKeyConstraint() object on the final mapped class and that will
express the ordering of the columns.


...which I guess I'd want to introspect and then form that final 
PrimaryKeyConstraint. How would I do that in this mixin:


https://github.com/Mortar/mortar_mixins/blob/master/mortar_mixins/temporal.py

...while also removing the primary key generated by the primary_key=True 
on the various columns?


cheers,

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] changing the order of columns in primary keys, including when generated by mixins

[Chris Withers]

Hi All,

How do you control the order in which columns are added to a 
multi-column primary key when using the declarative primary_key=True syntax?

How about when one of those columns comes from a mixin?

Context is in the mail below, any help gratefully received!

Chris


 Forwarded Message 
Subject: 	Re: [GENERAL] performance problems with bulk inserts/updates 
on tsrange with gist-based exclude constrains

Date:   Mon, 19 Sep 2016 09:41:33 -0700
From:   Jeff Janes <jeff.ja...@gmail.com>
To: Chris Withers <ch...@simplistix.co.uk>
CC: pgsql-general <pgsql-gene...@postgresql.org>



On Fri, Sep 16, 2016 at 2:01 AM, Chris Withers <ch...@simplistix.co.uk 
<mailto:ch...@simplistix.co.uk>> wrote:


   Hi All,

   I have quite a few tables that follow a pattern like this:

 Table "public.my_model"
  Column |   Type| Modifiers
   +---+---
  period | tsrange   | not null
  key| character varying | not null
  value  | integer   |
   Indexes:
 "my_model_pkey" PRIMARY KEY, btree (period, key)
 "my_model_period_key_excl" EXCLUDE USING gist (period WITH &&,
   key WITH =)
   Check constraints:
 "my_model_period_check" CHECK (period <> 'empty'::tsrange)


Try swapping the order of the columns in the exclude constraint.  You 
want the more selective criterion to appear first in the 
index/constraint.  Presumably "key with =" is the most selective, 
especially if many of your periods are unbounded.


Cheers,

Jeff

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Turning SAWarnings into exceptions

[Chris Withers]

Right, but then a bunch of other errors (ImportWarning?!, 
DeprecationWarning, etc) stop execution even reaching the code which 
might be causing the SAWarning.


It's really quite disappointing that Python's warning mechanisms don't 
report a full traceback...



On 15/09/2016 13:43, Mike Bayer wrote:


import warnings

warnings.simplefilter("error")



On 09/15/2016 08:07 AM, Chris Withers wrote:

Hi All,

How can I turn SAWarnings into exceptions?

I'm struggling with what to put into the PYTHONWARNINGS environment
variable :-S

cheers,

Chris





--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Turning SAWarnings into exceptions

[Chris Withers]

Hi All,

How can I turn SAWarnings into exceptions?

I'm struggling with what to put into the PYTHONWARNINGS environment 
variable :-S


cheers,

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] reflecting stored procedure names, enums, and the like

[Chris Withers]

Gotcha, would there be any interest in adding StoredProcedure objects?

For the immediate need of "drop everything", your suggestion sounds like 
the best way forward, but I wonder if solving the more generic 
"reflecting stored procedures" problem might help with other things...


Have any tools emerged for comparing two databases' schemas? I guess the 
stuff used to auto-generate migrations must largely solve this problem?


Sorry, I'm a bit rambly this morning...


On 15/08/2016 14:38, Mike Bayer wrote:



We should have reflection for PG ENUM in there already.   SP names and 
such, there's no "stored procedure" object, so if you wanted it to 
look like the thing you see here you'd make a StoredProcedure() class 
that's a subclass of DDLElement, etc.  I don't know that there's a 
hook to add new methods to the Inspector() as of yet though.  If 
you're just looking for a "drop everything" method I'd probably forego 
all that boilerplate and just have "drop_pg_sps", "drop_mysql_sps", 
etc. functions and key them off of engine.name in a dictionary or 
something.




On 08/15/2016 06:16 AM, Chris Withers wrote:

Hi All,

What's the best way (preferably database agnostic) to reflect stored
procedure names, enums names and other non-table-specific items?

I'm trying to improve this function, which is based on Mike's original
recipe:

https://github.com/Mortar/mortar_rdb/blob/eb99d549be02643d4d670db2ee52b93b0c386fb4/mortar_rdb/__init__.py#L134 




I'd like to expand to to basically delete as much as possible in a
database to give a clean starting ground for tests...

Chris





--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] reflecting stored procedure names, enums, and the like

[Chris Withers]

Hi All,

What's the best way (preferably database agnostic) to reflect stored 
procedure names, enums names and other non-table-specific items?


I'm trying to improve this function, which is based on Mike's original 
recipe:


https://github.com/Mortar/mortar_rdb/blob/eb99d549be02643d4d670db2ee52b93b0c386fb4/mortar_rdb/__init__.py#L134

I'd like to expand to to basically delete as much as possible in a 
database to give a clean starting ground for tests...


Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] chide 2.0.1 released!

[Chris Withers]

Hi All,

I'm please to announce a new release of chide, a tiny library for 
building sample objects.


This release includes the following:

- Fix nasty bug when using with sqlalchemy where related objects could
  get added to the session even though they were never requested, as a
  result of a backref on a third model.

Full docs are here:

http://chide.readthedocs.org/

Downloads are here:

https://pypi.python.org/pypi/chide

Compatible with Python 2.7, 3.4+:

https://travis-ci.org/cjw296/chide

Any problems, please give me a shout on the simplis...@googlegroups.com 
list!


cheers,

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: plain text versus html mail

[Chris Withers]

On 22/04/2016 03:04, Ben Finney wrote:

Chris Withers <ch...@simplistix.co.uk> writes:


[no text body]


When posting to mailing lists, please be sure to have a plain text body
with the full information. Not everyone wants to enable HTML in email
just to read a software announcement :-)


I used to have this view, but I'm afraid the world has moved on. I think 
plain text messages are more likely to be treated as spam than HTML now, 
and I appreciate the ability to format mails, even if Thunderbird does 
then butcher that formatting during delivery.


I dunno, what's the consensus?

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] chide 2.0.0 released! - sample objects for tests

[Chris Withers]

  
  
Hi All,

I've just released a new version of this tiny
library for making sample objects for testing.

Here's a SQLAlchemy example:

class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship('Child')

class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
value = Column(Integer)
Now we can build a source of sample data:

from chide import Collection
from chide.sqlalchemy import Set

data = Collection({
Parent: {'id': 1, 'child': Child},
Child: {'id': 3, 'value': 42}
})

And finally, we can use it:

>>> session = Sess
 i
on()
>>> samples = Set(data)
>>> session.add(samples.get(Parent))
>>> session.commit()
>>> session.query(Parent).one()

>>> _.child

The big change was realising that Collections tend to be module-level, so having them own the set of unique instances is bad.
So, that's now split out into a Set object, which should be instantiated one per test!

Documentation is here: http://chide.readthedocs.org/en/latest/
Development happens here: https://github.com/cjw296/chide/

cheers,



Chris





-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] where is InstanceState.key set?

[Chris Withers]

I ended up going with 
https://github.com/cjw296/chide/blob/master/chide/sqlalchemy.py#L26 
having read those.

Sounds about right?

On 21/04/2016 00:23, Mike Bayer wrote:


on the load side in loading.py and on the persistence side in 
session.py _register_newly_persistent.




On 04/20/2016 01:28 PM, Chris Withers wrote:

Hey All,

Where is InstanceState.key set?

I'm looking for the code that builds the key used in the identity_map of
the session. Turns out to be not so easy to find...






Chris





--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] where is InstanceState.key set?

[Chris Withers]

Hey All,

Where is InstanceState.key set?

I'm looking for the code that builds the key used in the identity_map of 
the session. Turns out to be not so easy to find...


Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] tiny new tool for making sample objects for tests

[Chris Withers]

  
  
Hi All,

I've just released this tiny
library for making sample objects for testing.

Here's a SQLAlchemy example:

class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship('Child')

class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
value = Column(Integer)
Now we can build a source of sample data:

from chide.sqlalchemy import C
 o
llection

samples = Collection({
Parent: {'id': 1, 'child': Child},
Child: {'id': 3, 'value': 42}
})
And finally, we can use it:

>>> session = Sess
 i
on()
>>> session.add(samples.make(Parent))
>>> session.commit()
>>> session.query(Parent).one()

>>> _.child

Hope this helps, if anyone finds bugs or has suggestions, please mail me!

cheers,

Chris




-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] in-place modification of queries?

[Chris Withers]

  
  
Mike,

Would you be interested in a pull request to add a Query and/or
Session option for this?

My use case right now is putting the query in a registry so other
callables down the line can refine the search criteria, but I may
well not being alone in thinking that:


query = self.session.query(Club)
query.filter(Club.name.ilike(club))
query.order_by('name')
possible = query.all()

...is easier to read than:

possible = self.session.query(Club)\
.filter(Club.name.ilike(club))\
.order_by('name')\
.all()

cheers,

Chris

On 28/12/2015 15:22, Mike Bayer wrote:


  not through the current API, no.  you'd need to write some modifier to
the @generative decorator and basically tinker with things to make it do
that.



On 12/28/2015 06:18 AM, Chris Withers wrote:

  
Hi All,

Is there anything I can do to make Query instance non-generative?

query = session.query(Foo)
query.filter(Foo.x==1)

...and have the query actually be modified rather than returning a new
query with the clause added?

cheers,

Chris


  
  



  




-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] in-place modification of queries?

[Chris Withers]

Hi All,

Is there anything I can do to make Query instance non-generative?

query = session.query(Foo)
query.filter(Foo.x==1)

...and have the query actually be modified rather than returning a new 
query with the clause added?


cheers,

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] can I use tables generated by ORM via sql expression language for select queries

[Chris Withers]

On 27/12/2015 04:44, Krishnakant wrote:

Hi,
The subject says it all.
I have classes inheriting the base and thus my tables are created 
using ORM.
But I wish to use sql expression language for queries, particularly 
bulk selects for faster performance.

So is this possible and how?


Sure, you can get the sql expression language - compatible table from 
your Base-inheriting class as follows:


YourClass.__table__

cheers,

Chris

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] postgres "similar to" in sqlalchemy expressions

[Chris Withers]

  
  
Hi All,

Just wanted to double check, is this still the best way to do this:

https://groups.google.com/forum/#!topic/sqlalchemy/6kZaWeqTpHA


foo.op("SIMILAR
  TO")(bar)

cheers,

Chris
  




-- 
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.

Re: [sqlalchemy] Re: set next value of postgres sequence

[Chris Withers]

Indeed, but that's not quite what I asked ;-)

I'm after setting the next value using, eg, setval:

http://www.postgresql.org/docs/current/static/functions-sequence.html

...or to do it on creation of the sequence:

http://www.postgresql.org/docs/current/static/sql-createsequence.html

I guess this might come into the stuff I was asking about in the 
"non-table DDL elements and MetaData objects" thread.


Chris

On 28/11/2015 18:04, Jonathan Vanasco wrote:

There is a `Sequence` object that can be delcared like a table

From the docs:

http://docs.sqlalchemy.org/en/latest/core/defaults.html#defining-sequences

seq  =  Sequence('some_sequence')
nextid  =  connection.execute(seq)
--
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.

__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__


--
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.

Re: [sqlalchemy] non-table DDL elements and MetaData objects

[Chris Withers]

Does the .listen example there work or does it need the patch to land?

On 01/12/2015 21:47, Jonathan Vanasco wrote:
I think this story may have some related info - 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3442/no-control-of-ddl-sequences-for-indexes-fk 



disclaimer -- I merely saw this earlier today and remembered it when 
seeing this post.

--
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.


--
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.

Re: [sqlalchemy] non-table DDL elements and MetaData objects

[Chris Withers]

On 02/12/2015 00:08, Mike Bayer wrote:

On 12/01/2015 02:49 PM, Chris Withers wrote:

 - once at 'database creation' time, so set up some stored
 procedures. (I know SQLAlchemy doesn't create databases itself, so
 interested in the correct approach for this)

So, thinking this through, how are non-table DDL elements added to a
MetaData object such that create_all can find them?

any DDL() object is eventable with after_create, before_create, some
basic docs at:

http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html#custom-ddl
Right, but I'm thinking of DDL events that aren't logically tied to the 
creation of a table.
For example: sequences, enums and stored procedures - these live at the 
same level as tables in my head, so the most-simple-thing-I-can-think-of 
would be:


Base = declarative_base()

class MyModel(Base):
__tablename__='foo'
...

my_sequence = Sequence('bar')

Base.metadata.add(my_sequence)

So then I could do:

Base.metadata.create_all(engine)

...to get both the table and the sequence created.

What's the right way to do this?

Chris

--
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.

Re: [sqlalchemy] after_create event for all uses of a mixin

[Chris Withers]

On 01/12/2015 10:10, Simon King wrote:
On Tue, Dec 1, 2015 at 8:17 AM, Chris Withers <ch...@simplistix.co.uk 
<mailto:ch...@simplistix.co.uk>> wrote:


Hi All,

Where can I find documentation on the parameters taken by
event.listen?
I have some listeners on instrument_class that pass
propagate=True, but I can't find docs on what that means?


I think the parameters depend on the type of event that you are 
listening for. "propagate" is a valid parameter for various 
ORM-related events:


http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html


Ah, that's what I was looking for thanks.


- one that fires on all table creation that uses a mixin, so that
I can register the created stored procedure as a trigger.

I feel like I'm looking for after_create event but with propagate=True, 
but that doesn't appear to exist.


Can listeners attach more listeners?

I feel like I want an instrument_class with propagate=True that adds a 
listener for after_create for each use of the mixin.


How would I go about doing that?

--
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.

Re: [sqlalchemy] non-table DDL elements and MetaData objects

[Chris Withers]

On Tue, Dec 1, 2015 at 8:17 AM, Chris Withers <ch...@simplistix.co.uk 
<mailto:ch...@simplistix.co.uk>> wrote:


I'm also looking for two events to listen to:

- once at 'database creation' time, so set up some stored
procedures. (I know SQLAlchemy doesn't create databases itself, so
interested in the correct approach for this)

So, thinking this through, how are non-table DDL elements added to a 
MetaData object such that create_all can find them?


I'm guessing there's already a story for this as a result of Posgtres 
ENUM types, and that should work for other non-table DDL elements such 
as stored procedures, sequences, etc?


cheers,

Chris

--
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] event.listen questions

[Chris Withers]

Hi All,

Where can I find documentation on the parameters taken by event.listen?
I have some listeners on instrument_class that pass propagate=True, but 
I can't find docs on what that means?


I'm also looking for two events to listen to:

- once at 'database creation' time, so set up some stored procedures. (I 
know SQLAlchemy doesn't create databases itself, so interested in the 
correct approach for this)


- one that fires on all table creation that uses a mixin, so that I can 
register the created stored procedure as a trigger.


cheers,

Chris

--
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] set next value of postgres sequence

[Chris Withers]

Hi All,

What's the recommended way to set the next value of a postgres sequence 
when using sqlalchemy?


cheers,

Chris

--
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.

Re: [sqlalchemy] best mysql dialect?

[Chris Withers]

Thanks, problem solved:

https://travis-ci.org/Mortar/mortar_rdb/builds/91273280

On 13/11/2015 16:16, Mike Bayer wrote:

python-mysql was forked as mysqlclient-python:


https://github.com/PyMySQL/mysqlclient-python

if you want pure Python use PyMySQL by the same author.




On 11/13/2015 08:33 AM, Chris Withers wrote:

Hello,

What's the best mysql dialect to use nowadays?

python-python aka mysqldb used to be my favourite, but the c extensions
make for some sadness.

I was happily using ​mysql-connector-python-rf, but 2.1.3 was released
today which appears to have broken the ability to pip install it.

So, what do people recommend?

cheers,

Chris



--
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] best mysql dialect?

[Chris Withers]

Hello,

What's the best mysql dialect to use nowadays?

python-python aka mysqldb used to be my favourite, but the c extensions 
make for some sadness.


I was happily using ​mysql-connector-python-rf, but 2.1.3 was released 
today which appears to have broken the ability to pip install it.


So, what do people recommend?

cheers,

Chris

--
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] getting the identity for objects not in a session

[Chris Withers]

Hi All,

Reading 
http://docs.sqlalchemy.org/en/rel_1_0/orm/internals.html?highlight=identitymap#sqlalchemy.orm.state.InstanceState.identity

I can understand why a new object not in a session won't have an identity.

However, that had me wondering, when a new object is added to the 
session, how does sqlalchemy know where in the identity map to put it?


cheers,

Chris

--
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.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Chris Withers]

On 26/09/2015 21:15, Mike Bayer wrote:



On 9/25/15 12:24 PM, Chris Withers wrote:

On 25/09/2015 13:58, Mike Bayer wrote:

session.query(A).filter(A.id>10).as_at(now))


you'd need to subclass Query and dig into Query.column_descriptions 
to get at the existing entities, then add all that criterion.
remind me where the docs are for plugging in a subclassed Query into 
a session?
it's just straight up subclassing.   there's some examples such as at 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery.


...which points to this:

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter

...which I like the feel of but can't quite get my head around how to 
make the TemporalOption apply to every table that uses a Temporal mixin 
that is involved in the query.


Chris

--
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.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Chris Withers]

On 25/09/2015 13:58, Mike Bayer wrote:

session.query(A).filter(A.id>10).as_at(now))


you'd need to subclass Query and dig into Query.column_descriptions to 
get at the existing entities, then add all that criterion.
remind me where the docs are for plugging in a subclassed Query into a 
session?


Chris

--
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.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Chris Withers]

On 25/09/2015 16:35, Jonathan Vanasco wrote:

fwiw, I struggled with this a while back and then gave up.

i ended up writing a few filter__xyz() functions that accept/return a 
query.  in the def, I join needed tables and filter.  instead of 
inspecting the query for tables, I just pass in some flags on how to act.


It's not pretty, but it works reliably and was fast to implement.

my queries now look like:

q = session.query(A).filter(A.id>10)
q = filter__as_at(q)


A function doesn't seem terrible, but django-much with the __? ewww ;-)

Chris

--
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] "No such event 'instrument_class' for target" for mixin that needs to listen to 'instrument_class'

[Chris Withers]

Hi All,

I have a mixin class here that I've factored out of one project as I 
want to use it in another one:


https://github.com/Mortar/mortar_mixins/blob/master/mortar_mixins/temporal.py

The problem is that importing the module seems to result in the 
following exception being raised:


>>> from mortar_mixins.temporal import Temporal
Traceback (most recent call last):
  File "", line 1, in 
  File "mortar_mixins/temporal.py", line 81, in 
listen(Temporal, 'instrument_class', add_constraints, propagate=True)
  File 
"/Users/chris/vcs/git/mortar_mixins/lib/python2.7/site-packages/sqlalchemy/event/api.py", 
line 89, in listen

_event_key(target, identifier, fn).listen(*args, **kw)
  File 
"/Users/chris/vcs/git/mortar_mixins/lib/python2.7/site-packages/sqlalchemy/event/api.py", 
line 28, in _event_key

(identifier, target))
sqlalchemy.exc.InvalidRequestError: No such event 'instrument_class' for 
target ''


My guess is something to do with it not being mixed into any classes at 
that point.


Weirdly, it seems to work fine, and all the tests run and pass, although 
the nose run will spew the above error in addition to running the tests.


To reproduce, just follow the instructions here:

https://github.com/Mortar/mortar_mixins/blob/master/README.rst#development

Any ideas what I should do to resolve this?

cheers,

Chris

--
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] funk session usage to add join conditions and where clauses

[Chris Withers]

Hi All,

Suppose I have the following:

from sqlalchemyimport Column, Integer, Text, ForeignKey, and_
from sqlalchemy.dialects.postgresqlimport TSRANGEas Range
from sqlalchemy.ext.declarativeimport declarative_base

Base = declarative_base()

class A(Base):
id = Column(Integer(),primary_key=True)
period = Column(Range(),nullable=False,primary_key=True)
data = Column(Text())

class B(Base):
id = Column(Integer(),primary_key=True)
period = Column(Range(),nullable=False,primary_key=True)
data = Column(Text())
a_id =  Column('a_id', Integer(), ForeignKey('a.id'))

data = Column(Text())

class C(Base):
id = Column(Integer(),primary_key=True)
period = Column(Range(),nullable=False,primary_key=True)
data = Column(Text())
b_id =  Column('b_id', Integer(), ForeignKey('b.id'))

How would I write something that molested the session such that instead 
of writing:


session.query(A, B, C)
   .select_from(A).join(B, and_(A.id==B.a_id,
A.period.contains(now),
B.period.contains(now)).
  .join(C, and_(B.id==C.b_id,
C.period.contains(now)))

...I could write:

session.query(A, B, C).as_at(now)

And instead of writing:

session.query(A).filter(A.id>10).filter(A.period.contains(now))

I could write:

session.query(A).filter(A.id>10).as_at(now))

Lazily yours,

Chris

--
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] views

[Chris Withers]

Hi All,

Is this still the best way to hand views, or are there later and greater 
things in 1.0+?


https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

How would I make a view behave like a normal declarative class (column 
attributes, etc), but while still having it create itself as part of 
create_all?


cheers,

Chris

--
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] funky session usage to add join conditions and where clauses

[Chris Withers]

Hi All,

Suppose I have the following:

from sqlalchemy import Column, Integer, Text, ForeignKey, and_from 
sqlalchemy.dialects.postgresql import TSRANGE as Rangefrom 
sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class A(Base):
id = Column(Integer(), primary_key=True)
period = Column(Range(), nullable=False, primary_key=True)
data = Column(Text())
class B(Base):
id = Column(Integer(), primary_key=True)
period = Column(Range(), nullable=False, primary_key=True)
data = Column(Text())
a_id =  Column('a_id', Integer(), ForeignKey('a.id'))

data = Column(Text())
class C(Base):
id = Column(Integer(), primary_key=True)
period = Column(Range(), nullable=False, primary_key=True)
data = Column(Text())
b_id =  Column('b_id', Integer(), ForeignKey('b.id'))


How would I write something that molested the session such that instead of 
writing:

session.query(A, B, C)
   .select_from(A).join(B, and_(A.id==B.a_id,
A.period.contains(now),
B.period.contains(now)).
  .join(C, and_(B.id==C.b_id,
C.period.contains(now)))


...I could write:

session.query(A, B, C).as_at(now)


And instead of writing:

session.query(A).filter(A.id>10).filter(A.period.contains(now))


I could write:

session.query(A).filter(A.id>10).as_at(now))


Lazily yours,

Chris

-- 
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.

Re: [sqlalchemy] views

[Chris Withers]

Also forgot to ask... What's support like in Alembic for creating views 
(especially if the views are described by a declarative class as I'm looking 
for below...)


> On 25 Sep 2015, at 08:13, Chris Withers <ch...@simplistix.co.uk> wrote:
> 
> Hi All,
> 
> Is this still the best way to hand views, or are there later and greater 
> things in 1.0+?
> 
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views
> 
> How would I make a view behave like a normal declarative class (column 
> attributes, etc), but while still having it create itself as part of 
> create_all?
> 
> cheers,
> 
> Chris
> 
> -- 
> 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.
> 
> __
> This email has been scanned by the Symantec Email Security.cloud service.
> For more information please visit http://www.symanteccloud.com
> __

-- 
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] "No such event 'instrument_class' for target" for mixin that needs to listen to 'instrument_class'

[Chris Withers]

Hi All,

I have a mixin class here that I've factored out of one project as I want 
to use it in another one:


https://github.com/Mortar/mortar_mixins/blob/master/mortar_mixins/temporal.py

The problem is that importing the module seems to result in the following 
exception being raised:

>>> from mortar_mixins.temporal import Temporal
Traceback (most recent call last):
  File "", line 1, in 
  File "mortar_mixins/temporal.py", line 81, in 
listen(Temporal, 'instrument_class', add_constraints, propagate=True)
  File 
"/Users/chris/vcs/git/mortar_mixins/lib/python2.7/site-packages/sqlalchemy/event/api.py",
 
line 89, in listen
_event_key(target, identifier, fn).listen(*args, **kw)
  File 
"/Users/chris/vcs/git/mortar_mixins/lib/python2.7/site-packages/sqlalchemy/event/api.py",
 
line 28, in _event_key
(identifier, target))
sqlalchemy.exc.InvalidRequestError: No such event 'instrument_class' for 
target ''

My guess is something to do with it not being mixed into any classes at 
that point.

Weirdly, it seems to work fine, and all the tests run and pass, although 
the nose run will spew the above error in addition to running the tests.

To reproduce, just follow the instructions here:


https://github.com/Mortar/mortar_mixins/blob/master/README.rst#development

Any ideas what I should do to resolve this?

cheers,

Chris

-- 
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.

Re: [sqlalchemy] "No such event 'instrument_class' for target" for mixin that needs to listen to 'instrument_class'

[Chris Withers]

On 24/09/2015 19:31, Mike Bayer wrote:
sqlalchemy.exc.InvalidRequestError: No such event 'instrument_class' 
for target ''


you need to make sure SQLAlchemy ORM is at least imported when that 
event handler is called and also you'd need to make sure 
propagate=True is on it because it's only a mixin.


Thanks, this appears to have done it:

https://github.com/Mortar/mortar_mixins/commit/95937f0e6657c440df5bf11533aa8d1461bf15f8

cheers,

Chris

--
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] session context manager

[Chris Withers]

Hi All,

I'm looking to use a session as a context manager, I've found 
session_scope here:


http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it

...but is there anything in the core?

I find of expected a session to do what session_scope does when used 
like so:


with Session() as session:
  ...

If I knocked up a pull request to make that happen, would it be accepted?

cheers,

Chris

--
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.

Re: [sqlalchemy] SQL Alchemy on Insert to DB

[Chris Withers]

On 17/02/2015 18:43, Javier Pajuelo wrote:

The weird part is that I just checked my candidates table and I see no
duplicates.
However, during the generation of a report that ties candidates, jobs,
and events, I get duplicate candidates.


My suspicion is that it's your code which does the scraping and creates 
the objects you're adding to the SQLAlchemy session that is the problem.


Chris

--
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.

Re: [sqlalchemy] SQL Alchemy on Insert to DB

[Chris Withers]

What does the code look like that generates 'candidates'?

Chris

On 17/02/2015 00:48, Javier Pajuelo wrote:

I get the following error:
|
sqlalchemy.exc.IntegrityError: (IntegrityError) column id is not 
unique u'INSERT INTO candidates (id, timeStamp, name, link) VALUES 
(?, ?, ?, ?)' (u'ORaE9+aCdP0', '2015-02-16 18:
19:58.00', u'Kunal Sethi', 
u'https://gambit.mycompas.com/staff/apptracedit.aspx?enc=1ID=ORaE9+aCdP0=bk=reqmgrrsbk=1stat=')

|


The code is as follows:
|
 def updateCandidateTable(self, candidates):
 session = self.Session()

 for candidate in candidates:

 print candidate in updateCandtable param: , candidate.name

 # Notes:
 # Maybe the first call needs merge, but the
 # following ones are updating and I need an update?
 try:
 session.add(candidate)
 session.commit()
 except:
 raise

|

Help, I am fairly new to mysql alchemy.

I just need to add and update candidates, but have no clue how can the 
column ID is not unique, since the code fails on the first insert.



Someone suggested that I call the code below, but then only the first 
candidate was entered and the rest were skipped.

|
for  record  in  records:
 try:
 with  session.begin_nested():
 session.merge(record)
 except:
 print  Skipped record%s  %  record
session.commit()
|


--
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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto:sqlalchemy@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__


--
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.

Re: [sqlalchemy] ValidityTime Column

[Chris Withers]

You should have a look at the Postgres range types in PG 9.2+...

Chris

On 27/11/2014 10:44, Giovanni Cavallero wrote:

Hi Michael,

many thanks for your help!

Is there any way to do this in an automatic way? I mean now i have the
following code


from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

MaPMTs = Table('MaPMTs',Base.metadata,
Column('id',Integer,primary_key=True),
Column('Serial_Number',String),
Column('Gain',Float),
Column('Validity_Start',DateTime,default=func.now()),
Column('Validity_End',DateTime),
)

class MaPMTs(Base):
 __table__ = MaPMTs

from sqlalchemy import create_engine
engine = create_engine('postgres://localhost/DB')

from sqlalchemy.orm import sessionmaker
DBSession = sessionmaker()
DBSession.configure(bind=engine)



and i obtain for example this table

ID Serial_Number  Gain
  Validity_Start
  Validity_End

1;   “FA0026”; 1.18;
2014-11-27 11:16:36.779973”; 2014-11-27
11:18:44.91704
2;   ”FA0026”; 1.08;
2014-11-27 11:18:44.91704;



typing  on shell

  s=DBSession()
  tube1=MaPMTs(Serial_Number='FA0026',Gain=1.18)
  s.add(tube1)
  s.commit()
  tube2=MaPMTs(Serial_Number='FA0026',Gain=1.08)
  s.add(tube2)
  s.commit()
  tube1_update =
s.query(MaPMTs).filter(MaPMTs.Serial_Number=='FA0026').first()
  tube1_update.Validity_End = tube2.Validity_Start
  s.commit()



I would obtain the same table without invoke a query. Is it possibile to
add some new lines in the code to do the same thing?

Giovanni





Il giorno 27/nov/2014, alle ore 01:18, Michael Bayer
mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com ha scritto:

idiomatically this would be:

tube1 = session.query(Phototubes).filter(get the first object).one()
tube2 = Phototubes(gain = new_gain, transactiondate =
tube1.transactiondate, serialnumber=tube1.serialnumber)
session.add(tube2)
session.commit()


That’s a SELECT then an INSERT.  In the event that you’re looking for
something more exotic than that (like, an INSERT that embeds the
SELECT)  let us know.



On Nov 26, 2014, at 5:37 AM, joecav...@hotmail.it
mailto:joecav...@hotmail.it wrote:

Hello,

i'm new on sqlalchemy and i have a problem.
I want to update the entry in a column of a table of a row using the
info of a new row. For example, i could have measured a value in a
certain date

classPhototubes(Base):
__tablename__='MaPMTs'
SerialNumber=Column(String, primary_key=True)
Gain = Column(Float)
TransactionDate=Column(
DateTime,
default=func.now())
)
Now, I would add a column End_Of_Validity = Column(DateTime) so
that if I measure a new value of the Gain of the same phototube (so I
add a new row with the same SerialNumber but different value of Gain)
I can update the previous entry of column End_Of_Validity with this
TransactionDate (otherwise empty). How can I do this?
Many Thanks,
Giovanni


--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.



--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.



__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__

--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
You received this message because you are subscribed to 

Re: [sqlalchemy] manual connection management interacting badly with sessions

[Chris Withers]

On 07/11/2014 21:30, Michael Bayer wrote:



So, turns out the pandas.io.sql.read_frame in the version of pandas I'm using 
has a con.commit() in it. wtf?!

Guess I'll raise that as a bug in Pandas if they haven't fixed it in a 
subsequent release…



didn’t pandas introduce SQLAlchemy integration?  
http://pandas.pydata.org/pandas-docs/dev/generated/pandas.read_sql_query.html  
accepts a SQLAlchemy Connection object.


Yep, I'm on pandas.old, attempting to upgrade is proving oh so much fun...

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] manual connection management interacting badly with sessions

[Chris Withers]

Hello,

So, short version: if I need a raw DBAPI connection and I have a 
SQLAlchemy Session, what's the correct way to manage that raw connection 
if I get it using session.connection().connection?


Versions:

Postgres 9.2
SQLAlchemy 0.8.2

Background:

I want to create a DataFrame using pandas.io.sql, but this requires a 
raw DBAPI connection while the rest of my app uses SQLAlchemy sessions. 
I bashed this in ipython notebook and ended up using 
session.connection().connection to get the raw connection.


Now, I'm abstracting some of my notebook experiments into library 
functions, and I found that unit testing a function that uses 
session.connection().connection causes a bunch of my other tests to fail 
even though all of my tests do a session.rollback() as part of the teardown.


What's the right way to get a raw connection? If it's 
session.connection().connection, what book-keeping do I need to do to 
stop stuff done in that connection not being rolled back by 
session.rollback()?


Side question: if session.connection() creates a new connection, how is 
stuff done in that connection not being isolate from stuff done in the 
session?


cheers,

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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.

Re: [sqlalchemy] manual connection management interacting badly with sessions

[Chris Withers]

On 07/11/2014 14:14, Michael Bayer wrote:


session.connection().connection is the same connection that session.rollback() 
will be referring towards.   though when you have that DBAPI connection (it is 
in fact still wrapped by ConnectionFairy), you shouldn’t call commit() or 
rollback() on that connection itself.

session.connection() doesn’t necessarily create a “new” connection, it returns 
“the connection that this Session is currently using”, and if there isn’t one, 
it creates it.

I don’t know your problem yet, you need to dig some more.   maybe try 
StaticPool  / AssertionPool to narrow it down?


So, turns out the pandas.io.sql.read_frame in the version of pandas I'm 
using has a con.commit() in it. wtf?!


Guess I'll raise that as a bug in Pandas if they haven't fixed it in a 
subsequent release...


Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] databases and asynchronous programming

[Chris Withers]

Hi All,

So, one of the projects I'm playing with at the moment is a big ball of 
asynchronous networking (tonnes of protocols, some tcp, some multicast) 
which I want to stick a webapi onto (normal requests + websocket) and 
probably do some database interaction with.


So, aside from figuring out which async path to go (twisted, tornado, 
tulip or good old fashioned asyncore), I have some concerns about 
database interaction.


First question: I'm guessing I should do all database access in a 
spawned off thread, lest it blocks?


Second question: if I end up using the ORM, I guess a safe approach 
would be to expunge the objects from the session before I return them 
from the thread that does the db interaction?


Any other advice people have in this area?

cheers,

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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.

Re: [sqlalchemy] tying multiple sessions together with one transaction

[Chris Withers]

Hi Simon,

On 26/02/2014 13:18, Simon King wrote:

I don't know exactly what your needs are, but here's the example from the docs:

 engine1 = create_engine('postgresql://db1')
 engine2 = create_engine('postgresql://db2')

 Session = sessionmaker(twophase=True)

 # bind User operations to engine 1, Account operations to engine 2
 Session.configure(binds={User:engine1, Account:engine2})


Interesting, didn't know you could do that. However, how does that scale 
to lots of mapped classes? I guess User and Account would need to have 
different declarative bases? Is there a way to say all classes for a 
particular base go to a particular engine?



 # commit.  session will issue a flush to all DBs, and a prepare
step to all DBs,
 # before committing both transactions
 session.commit()


...and I guess, if configured, this would wire in two phase commits 
correctly?


Partially innocent question: If one of the flushes you describe above 
fails (say with an integrity error), then the transactions would be left 
in need of a rollback on all engines and definitely no data would be 
committed?


cheers,

Chris



So in this case there are 2 engines, pointing at 2 different DBs.
Operations against the User class will use engine1, and operations
against the Account class will use engine2. session.commit() will use
two-phase operations to ensure that both get committed together.

I think the magic happens within the Session.get_bind() method:

   
http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#sqlalchemy.orm.session.Session.get_bind

   
http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#sqlalchemy.orm.session.Session.params.binds

Would that work in your situation?

Simon

On Wed, Feb 26, 2014 at 12:50 PM, Chris Withers ch...@simplistix.co.uk wrote:

Hi Simon,

The problem is that I want to use multiple sessions, I think.

How would I have connections to multiple databases with differing schemas
using a single Session?

cheers,

Chris


On 25/02/2014 18:14, Simon King wrote:


I've never used two-phase commit, but can you get away with just a
single session, like the example at:


http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#enabling-two-phase-commit

Simon

On Tue, Feb 25, 2014 at 6:07 PM, Chris Withers ch...@simplistix.co.uk
wrote:


No takers? :'(


On 19/02/2014 18:43, Chris Withers wrote:



Hi All,

My other usual question now ;-)

I have multiple databases that I'm connecting to from my application. I
need to commit or rollback a single transaction across all of them.

So, two phase commit, right?

Okay, but how do I tie the sessions together? What and how do I call
commit?

Is there anything better than zope.transaction? (which does address this
need...)

Chris



--
Simplistix - Content Management, Batch Processing  Python Consulting
  - http://www.simplistix.co.uk

--
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.





--
Simplistix - Content Management, Batch Processing  Python Consulting
 - http://www.simplistix.co.uk

--
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.




--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] tying multiple sessions together with one transaction

[Chris Withers]

Hi Jonathan,

On 03/03/2014 17:22, Jonathan Vanasco wrote:



Yep.  You can look at the internals of the 'Transaction' package and
cross reference to the zope.sqlachemy to see exactly how it works.


Sorry, not sure if I'm following you, but I'm talking about Simon's 
suggestion only to use a sqlalchemy session and deliberately *not* using 
zope's transaction package or zope.sqlalchemy.


cheers,

Chris



IIRC, the implementation of the two phase commit basically has everyone
flush on Phase1, then report back as a vote.  If there are any
negative votes, the transaction manager instructs everyone to fail and
rollback as Phase2.   If you had a flush that caused an integrity error
before trying to commit, that would still trigger an issue.

--
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.

__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__


--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] tying multiple sessions together with one transaction

[Chris Withers]

Hi Simon,

The problem is that I want to use multiple sessions, I think.

How would I have connections to multiple databases with differing 
schemas using a single Session?


cheers,

Chris

On 25/02/2014 18:14, Simon King wrote:

I've never used two-phase commit, but can you get away with just a
single session, like the example at:


http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#enabling-two-phase-commit

Simon

On Tue, Feb 25, 2014 at 6:07 PM, Chris Withers ch...@simplistix.co.uk wrote:

No takers? :'(


On 19/02/2014 18:43, Chris Withers wrote:


Hi All,

My other usual question now ;-)

I have multiple databases that I'm connecting to from my application. I
need to commit or rollback a single transaction across all of them.

So, two phase commit, right?

Okay, but how do I tie the sessions together? What and how do I call
commit?

Is there anything better than zope.transaction? (which does address this
need...)

Chris



--
Simplistix - Content Management, Batch Processing  Python Consulting
 - http://www.simplistix.co.uk

--
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.




--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] tying multiple sessions together with one transaction

[Chris Withers]

No takers? :'(

On 19/02/2014 18:43, Chris Withers wrote:

Hi All,

My other usual question now ;-)

I have multiple databases that I'm connecting to from my application. I
need to commit or rollback a single transaction across all of them.

So, two phase commit, right?

Okay, but how do I tie the sessions together? What and how do I call
commit?

Is there anything better than zope.transaction? (which does address this
need...)

Chris



--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] TypeError: Range objects cannot be ordered in flush

[Chris Withers]

On 24/12/2013 21:21, Ryan Kelly wrote:


IMO psycopg2's implementation should be patched, since they basically
just didn't implement ordering. PostgreSQL itself has no problem
ordering range types (though the ordering is somewhat arbitrary):
http://www.postgresql.org/docs/9.2/static/rangetypes.html#RANGETYPES-GIST


Got there in the end:

https://github.com/psycopg/psycopg2/commit/283dbccf56d1c3fb14eadc66bdd4e2a4bb56974e

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
   - http://www.simplistix.co.uk

--
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] tying multiple sessions together with one transaction

[Chris Withers]

Hi All,

My other usual question now ;-)

I have multiple databases that I'm connecting to from my application. I 
need to commit or rollback a single transaction across all of them.


So, two phase commit, right?

Okay, but how do I tie the sessions together? What and how do I call commit?

Is there anything better than zope.transaction? (which does address this 
need...)


Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] declarative polymorphic inheritance abstraction problem

[Chris Withers]

Hi All,

I'm trying to be efficient with my code, but it seems to tripping 
SQLAlchemy up.


So, basically I want to have a schema with two tables, content and 
project, to represent two three types of object:


- article has a set of fields as found in the 'content' table
- project has fields that are the set union of the 'content' and 
'project' tables' columns
- content is a plain content type, like article, and will likely never 
be instantiated, but just in case...
- in future, I may want to add more tables/content types where the 
fields are like project; a set union of the 'content' and 'project' 
tables' columns


So, I tried to be clever and start off with the following content 
class/table:


class Content(Base):

content_type = Column(String(20))

@declared_attr
def id(cls):
if cls.__name__=='Content':
return Column(Integer, primary_key=True)
else:
return Column(Integer, ForeignKey('content.id'), 
primary_key=True)


@declared_attr
def __tablename__(cls):
return cls.__name__.lower()

@declared_attr
def __mapper_args__(cls):
args = dict(polymorphic_identity = cls.__name__.lower())
if cls.__name__=='Content':
args['polymorphic_on'] = cls.content_type
return args

...with the following two classes associated with it:

class Article(Content):

@declared_attr
def __tablename__(cls):
return None

class Project(Content):

currency  = Column(String(3))
target = Column(Integer)
current = Column(Integer)

...but I get the following on import:

project.py, line 5, in module
class Project(Content):
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/ext/declarative/api.py, 
line 53, in __init__

_as_declarative(cls, classname, cls.__dict__)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/ext/declarative/base.py, 
line 322, in _as_declarative

mt.map()
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/ext/declarative/base.py, 
line 405, in map

**mapper_args
  File string, line 2, in mapper
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/mapper.py, 
line 593, in __init__

self._configure_inheritance()
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/mapper.py, 
line 900, in _configure_inheritance

self.local_table)
  File string, line 2, in join_condition
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/sql/selectable.py, 
line 651, in _join_condition

between '%s' and '%s'.%s % (a.description, b.description, hint))
sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key 
relationships between 'content' and 'project'.


If I change the id method to be decorated by classproperty rather than 
declared_attr, the error changes to:


article.py, line 3, in module
from .content import Content
content.py, line 7, in module
class Content(Base):
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/ext/declarative/api.py, 
line 53, in __init__

_as_declarative(cls, classname, cls.__dict__)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/ext/declarative/base.py, 
line 322, in _as_declarative

mt.map()
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/ext/declarative/base.py, 
line 405, in map

**mapper_args
  File string, line 2, in mapper
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/mapper.py, 
line 599, in __init__

self._configure_pks()
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.9.1-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/mapper.py, 
line 1189, in _configure_pks

(self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|Content|content could not 
assemble any primary key columns for mapped table 'content'


What's going wrong here?

cheers,

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] TypeError: Range objects cannot be ordered in flush

[Chris Withers]

Hi All,

I feel like I've asked this before but apologies, I cannot find the 
previous thread.


So, when using the support for psycopg2's range types I added, I 
sometimes see the following during a flush:


  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/session.py, 
line 1818, in flush

self._flush(objects)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/session.py, 
line 1936, in _flush

transaction.rollback(_capture_exception=True)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/util/langhelpers.py, 
line 58, in __exit__

compat.reraise(exc_type, exc_value, exc_tb)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/session.py, 
line 1900, in _flush

flush_context.execute()
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/unitofwork.py, 
line 372, in execute

rec.execute(self)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/unitofwork.py, 
line 525, in execute

uow
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py, 
line 45, in save_obj

uowtransaction)
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py, 
line 140, in _organize_states_for_save

states):
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py, 
line 767, in _connections_for_states

for state in _sort_states(states):
  File 
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py, 
line 792, in _sort_states

sorted(persistent, key=lambda q: q.key[1])
  File 
/Users/chris/buildout-eggs/psycopg2-2.5.1-py2.7-macosx-10.5-x86_64.egg/psycopg2/_range.py, 
line 138, in __lt__

'Range objects cannot be ordered; please refer to the PostgreSQL'

What's going on here? Is it SQLAlchemy, psycopg2 or my code that's at fault?

cheers,

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] full outer join?

[Chris Withers]

I have a self join (same one I've been asking about recently) and turns 
out that there should be a one to one mapping for the aggregated rows on 
one side to the aggregated rows on the other side.


If that's not true, then the returned rows are rubbish. In all my 
current use cases it *should* be true but I'm looking for a join that 
will show me when something has changed by having nulls in either the 
left or right side of the join.


If you can suggest a way that doesn't involve a full outer join, I'm all 
ears :-)


On 01/12/2013 22:20, Michael Bayer wrote:

I have a similar question - why ?


On Dec 1, 2013, at 5:08 PM, Chris Withers ch...@simplistix.co.uk wrote:


This feels like a newbie question, but how would I do a full outer join in 
SQLAlchemy?

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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.




--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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] creating a cte from a string and some params

[Chris Withers]

Cool :-)

When's 0.9 due for release?

Chris

On 29/11/2013 20:17, Michael Bayer wrote:

here you go, give it a spin:

http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#new-text-capabilities



On Nov 29, 2013, at 12:13 PM, Michael Bayer mike...@zzzcomputing.com wrote:


here’s a patch:

http://www.sqlalchemy.org/trac/ticket/2877

it’s quite short.   I just don’t like the current contract of SelectBase, which 
has many methods that don’t apply here, so might want to rework the base 
classes.




On Nov 22, 2013, at 5:06 AM, Chris Withers ch...@simplistix.co.uk wrote:


On 21/11/2013 19:25, Michael Bayer wrote:


hoping you’ve already figured it out but otherwise I’m not entirely following 
what the larger query you’re looking for would look like.I doubt you need 
text() for anything.


Okay, pseudo code that doesn't work:

positions = something(
select instrument_id, sum(quantity) as quantity
   from transaction where
   account_id = :account_id and
   timestamp  :dt and
   group by instrument_id
   having sum(quantity) != 0
, dict(dt=date(2013, 1, 1), account_id='foo')).cte('positions')

print session.query(Instrument, positions.c.quantity)\
  .select_from(Instrument)\
  .join(positions, Instrument.id=positions.instrument_id)\
  .all()

(this latter query will likely have more stuff in it, not sure I used 
.select_from(...).join(...) correctly either ;-)

Hope that illustrates what I'm after...

Chris

--
Simplistix - Content Management, Batch Processing  Python Consulting
   - http://www.simplistix.co.uk

--
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.






--
Simplistix - Content Management, Batch Processing  Python Consulting
- http://www.simplistix.co.uk

--
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.