[sqlalchemy] new tests show a connection/transaction anti-pattern, unsure how to address this.
A new suite of unittests shed light on what appears to be anti-pattern in
an application. I'm not sure how to address this one.
The general flow of a particular web request is this:
Phase 1- load some database objects for general verification
Phase 2- loop through a handful of routines to create some items. each
routine is an isolated transaction - not a subtransaction
so it looks like this...
# Phase1
foo = dbSession.query(FOO).all()
# Phase2
for i in (a, b, c):
bar = BAR()
dbSession.add(bar)
dbSession.commit()
The problem occurs sporadically in Phase2, when the code attempts to
address a lazyloaded attribute of the `foo` loaded in Phase1, and I get the
error
exc.ResourceClosedError("This Connection is closed")
Digging into the events API and tracking everything, it appears my
connection is returned to the pool and closed on every `commit`.
I'm roughly seeing the events happen like this (the order of where the
'action' happens may be shifted up or down a line):
Phase 1
connect
checkin
engine_connect
Phase 2 Action
commit
reset
checkout
checkin
engine_connect
Phase 2 Action
commit
reset
checkout
checkin
engine_connect
Phase 2 Action
EXCEPTION
Is there a way to ensure the session doesn't close/checkin the connection
on certain commits or is there a better strategy to deal with this
anti-pattern -- perhaps figuring out a way to update the object
sessions/connections ?
I only have a handful of situations where there are multiple transactions
like this - maybe 1% of the potential views.
It does look like I have two problems here too:
1. The symptom/problem of not being able to load this data from a previous
transaction.
2. The underlying problem of losing a connection in a request, when I know
I want to keep it for immediate use again.
--
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] Re: SQLAlchemy - Postgres Connection Issue
your database logs may indicate why it is closing the connection or what underlying error happened. there are dozens, if not hundreds, of potential reasons why an error like this may be happening. this could be from anything, including having too many connections, to an issue on your database app, or even an issue on your server or connectivity, etc. -- 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] Re: SQLAlchemy - Postgres Connection Issue
There are a few strategies to handling disconnects that are outlined in the docs: https://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects -- 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] Re: Okta authenticated credentials with sqlalchemy
you should ask snowflake/ snowflake-sqlalchemy developers. snowflake is a commercial product and they maintain their own sqlalchemy product. the chance of somewhere here being able to answer this question is very low, and you're already paying snowflake for support. https://github.com/snowflakedb/snowflake-sqlalchemy note the message at the bottom: Support Feel free to file an issue or submit a PR here for general cases. For official support, contact Snowflake support at: https://support.snowflake.net/s/snowflake-support -- 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] event overhead question
On Thursday, October 11, 2018 at 11:26:57 PM UTC-4, Mike Bayer wrote: > > > the overhead of using @event.listens on that setter will be almost > undetectable.of course it also depends on what you're doing once > youre in the event handler. > > Great! Thanks. I'm just setting a bunch of attributes on the object to None. -- 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] event overhead question
On Thursday, October 11, 2018 at 7:29:32 PM UTC-4, Mike Bayer wrote: > > > overhead is fairly minimal, but also, a "set" is something you're > doing at very specific times, so, how much is this "set" being called > typically? > The app is a read-heavy CMS. This "set" happens on the Article's body via create or edit. These two write operations are maybe 1/1000th the volume of total writes, and writes are maybe 1 per 100k reads. Using the event would be a small convenience in some ways and a good a backup plan, but the system can easily use a custom setter for the object. -- 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] event overhead question
I try to stay away from Events in production. I now need to catch modifications of a particular column in order to expire some data cached onto the object. Is there a measurable overhead for catching a single column? e.g. @event.listens_for(ObjectClass.column, 'set') The alternative is to use a setter function - which I am fine with. This is in an app that needs to be a bit more performance oriented, so I figured I would ask before doing a benchmark. -- 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] Re: question about `association_proxy` interface
On Wednesday, October 3, 2018 at 9:40:37 AM UTC-4, Mike Bayer wrote: > > > those are not going to change within 1.2 or 1.3 but it's not ideal to > be relying on them. For query._entities, you can use the public > accessor query.column_descriptions. for _with_options I'm not sure > what it is you want to do. > Thanks. This is good to know. I'll try adjusting with `column_descriptions`. The 'with_options' is used by some code that attempts to determine if a 'contains_eager' or joinedload/subqueryload was made. In response to your other suggestion, I never thought of dynamically generating the proxies. I don't think that code meeds my requirements as-is, but it definitely points me in the right direction and I can slightly alter it. Thank you so much. This is wonderful. -- 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] Re: question about `association_proxy` interface
And a quick followup to Michael:
I didn't want to pollute the comments
in
https://bitbucket.org/zzzeek/sqlalchemy/issues/3225/query-heuristic-inspection-system
as there may not have been any substantial changes and I'm just reviewing
my old notes wrong...
How long do you think it is safe to inspect query attributes like:
* `query._with_options`
* `query._entities`
Reviewing some of my old notes, it looks like these used to work:
* `query.__dict__.get('with_options')`
* `query.__dict__.get('entities')`
but they've been replaced at some point with a leading underscore, and a
corresponding attribute.
--
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] question about `association_proxy` interface
I have a common design in my database in which the heavy write/update
columns exist in their own 'metacontent' table. An `association_proxy` is
used to link them:
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
item_description = association_proxy('item_metacontent',
'item_description')
item_metacontent = relationship("ItemMetacontent",
primaryjoin=
"Item.id==ItemMetacontent.id",
uselist=False,
back_populates = 'item',
)
class ItemMetacontent(Base):
__tablename__ = 'item_metacontent'
id = Column(Integer, ForeignKey("item.id"), nullable=False, primary_key=
True)
item_description = Column(UnicodeText, nullable=True)
item = relationship("Item",
primaryjoin="ItemMetacontent.id==Item.id",
uselist=False,
back_populates = 'item_metacontent',
)
The issue I've run into is in the creation of new objects.
The required code looks like this...
_item = Item()
_item.id = 1
_item.item_metacontent = ItemMetacontent() # generate a new
ItemMetacontent object
_item.item_description = 'example description'
While metacontent does offer a constructor hook...
item_description = association_proxy('item_metacontent',
'item_description',
creator=lambda desc:
ItemMetacontent(item_description= desc))
It is not optimal to implement in my situation for two reasons:
* the order of imports. SqlAlchemy's string syntax is preferable or
required in some cases.
* the number of association_proxy columns. some tables have over a dozen
proxied columns.
It would be ideal, at least in a 1:1 relationship, to automatically create
the object of an AssociationProxy target when it does not exist.
Does anyone know if that is possible in the current codebase?
--
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] efficient strategies for partial loads of large relationships ?
like, OMFG this is brilliant. -- 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] efficient strategies for partial loads of large relationships ?
Mike, This is absolutely brilliant! thank you! -- 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] efficient strategies for partial loads of large relationships ?
I have a handful of relationships where there could be 100s of matching rows, but in most views I only want to see a subset of them. perhaps the most recent 5 items of 500 candidates.. to handle this so far, i've been constructing a join with correlated subquery. it works, but this doesn't seem efficient to write or execute. does anyone have an idea for a better approach? It feels wonky to reimplement similar queries so much in my model definition. i don't want to use `dynamic` loading, because that relationships strategy returns a query object and exposes a slightly different usage pattern.. for various compatibility reasons, I need the relationship attribute to represent a fully loaded collection. it can be view-only collection - but must be limited to a certain number of elements. -- 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] Confusion over session.dirty, query, and flush
On Wednesday, September 26, 2018 at 10:08:43 PM UTC-4, jens.t...@gmail.com wrote: > > > Suppose I get the “new”, “dirty”, and “deleted” sets as per discussion > below, and I’m especially interested in the “dirty” set: is there a way to > find out which properties of an object were modified, or only that the > object was modified? > You want the `inspect` API https://docs.sqlalchemy.org/en/latest/core/inspection.html use `inspect` to get at the InstanceState for the object (https://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState) then use `attrs` on the InstanceState to view the `attrs` which has an `AttributeState` with a `history` (https://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.AttributeState) if you search for 'inspect' in this forum, Michael has provided many examples on this topic. -- 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] Re: converting row object to dict
here are 2 methods you can add to your base class:
> def columns_as_dict(self):
> """
> Beware: this function will trigger a load of attributes if they have not
> been loaded yet.
> """
> return dict((col.name, getattr(self, col.name))
> for col
> in sa_class_mapper(self.__class__).mapped_table.c
> )
> def loaded_columns_as_dict(self):
> """
> This function will only return the loaded columns as a dict.
> """
> _dict = self.__dict__
> return {col.name: _dict[col.name]
> for col in sa_class_mapper(self.__class__).mapped_table.c
> if col.name in _dict
> }
>
--
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] How to ignore primary key errors on insert
There's also the strategy of doing something within a nested transaction, which will allow you to rollback on an integrity error. such as... try: with s.begin_nested(): # do stuff s.flush() # this will trigger an integrity error, unless the fkey checks are deferred except exceptions.IntegrityError: pass -- 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] Modifying results of a KeyedTuple query?
On Friday, August 10, 2018 at 2:43:51 PM UTC-4, Mike Bayer wrote: > > You need to copy the keyedtuples into some other data structure, like a > dictionary, modify it, then send that data back into updates. Your best > bet is to use the bulk update stuff once you have those dictionaries, see > http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=bulk#sqlalchemy.orm.session.Session.bulk_update_mappings > > . > A quick background on Mike's short answer... Tuples are immutable lists in Python, and "KeyedTuple" should indicate that you can't change the values. They're just a handy result storage object, not an ORM object mapped to a table row. -- 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] Checking for a unique constraint violation before inserting new records, is it recommended?
On Sunday, August 5, 2018 at 5:15:39 PM UTC-4, Mike Bayer wrote: > Not on my end ! The openstack code I referred towards is regex based but > works very well. I just limit what can trigger the exception and call a flush. it's not the most performant code if you are changing many fields, but it's fine if you're just updating a username (which is what I use this for too!) e.g. something like this try: foo.username = 'foo' session.flush() except sqlalchemy.exc.IntegrityError as e: raise UsernameTaken() -- 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] Approval of changes to records
On Monday, July 16, 2018 at 4:53:18 AM UTC-4, Marc wrote: > > Thank you both Jonathan and Kirk for your helpful comments. I am leaning > more and more towards using a versioning/history solution. If I can figure > out a way to incorporate a approved flag into the history tables produced > by something like SQLAlchemy-Continuum, it should meet all the requirements > and provide additional functionality for 'free' > There is a recipe for 'global filters' in the sqlalchemy docs. that may help. -- 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] Re: Approval of changes to records
On Saturday, July 14, 2018 at 5:45:29 AM UTC-4, Marc wrote: > > Surely this is quite a common requirement? Ideally I would like it to be > as simple as transparent as possible to the front-end. Is there any > add-ons/plugins for SQLAlchemy that would enable this kind of > functionality? I have searched for quite a long time and can not find > anything, but perhaps I am using the wrong search key words. > This is not a common requirement. Many people implement similar design patterns, but a lot of the details in stuff like this are very much oriented to the "business logic" which changes wildly across projects. I don't think you're going to find a SqlAlchemy project that covers this need, as it's really firmly in the "application logic" realm. HOWEVER you may find a Flask/Pyramid/etc project that uses SqlAlchemy under the hood and implements a pattern like this for you. -- 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] Serialization / De-serialization for SQLAlchemy Declarative ORM
this looks great, and omfg the docs! -- 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] Re: ORA-03135 and SqlAlchemy... Is there a design pattern to get around this?
http://docs.sqlalchemy.org/en/latest/core/pooling.html specifically: http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic and http://docs.sqlalchemy.org/en/latest/core/pooling.html#custom-legacy-pessimistic-ping There is also: http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-optimistic -- 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] Bug? Query / Execute Mismatch When Given Duplicate Columns
On Tuesday, July 3, 2018 at 9:37:04 AM UTC-4, Mike Bayer wrote: > > This architecture has been liberalized but this assumption still remains > within the Core and it's possible the ORM may or may not have some > remaining reliance on this assumption as well. > I assumed the RowProxy also requires this as well, since it works much like a namedtuple. -- 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] Debugging why Flask-SQLAlchemy won't update database
Adding to Mike's response... I think you're using two sessions... you select this: user = Users.query.filter_by(username='foo').first() and save this db.session.add(user) when you select, i think that's using some flask-sqlalchemy syntactic sugar to select the session. a 'raw' sqlalchemy approach would typically be: db.session.query(Users).filter_by(username='foo').first() Your error when saving made this stick out: sqlalchemy.exc.InvalidRequestError: Object '' is already attached to session '1' (this is '2') If that's the case, these aren't being issued against the session you queried with db.session.flush() db.session.commit() so when you do this... print(user.first_name) this never hit sql. you're just printing the object state that has not been flushed or committed. All that being said, You should repost this on the Flask community list/slack channel. The above code is *generally correct* and your issue is most likely in the integration layer of flask-sqlalchemy -- and not your usage of sqlalchemy. They can probably solve it faster there, as most people on this group don't really know flask/flask-sqlalchemy (but everyone here would love to know your solution). -- 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] Re: from_statement NOW()
the difference is possibly because this is calculated in Python, each time it is executed: datetime.datetime.now() this is calculated in Postgres, and refers to the beginning of the transaction; it does not change across the transaction. NOW() -- 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] Multiple sessions same thread - How to?
On Monday, June 25, 2018 at 11:31:07 AM UTC-4, HP3 wrote: > > I'm confused about what you said about the underlined connection: I am > creating 2 different engines. Why would both share the same connection? > > That wasn't clear from the above, however.. looking at the code you've shared, it seems you're creating two identical engines. @MikeBayer - is it possible that the connection pool is detecting this and using the same connections across databases? @HP3 just to test this, i would try adding a slightly different connection string or argument to the celery connection. e.g. create a different user, or toss in a config argument that doesn't affect your code. if the error stops, that's most-likely the reason why. -- 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] Multiple sessions same thread - How to?
Can you share/show how/where they engines and connections are created? This is odd. FWIW, with the forking bug- the issue isn't in the transaction/session but in the underlying database connections. The SqlAlchemy connection pool isn't threadsafe, so all the commits/rollbacks/etc in different sessions/transactions made in different sessions end up happening on the same connection. On Friday, June 22, 2018 at 8:18:51 PM UTC-4, HP3 wrote: > > No dice! > > I verified that engines and sessions are created after fork. > > By hijacking celery logging, verified each worker had its own transaction > and session. > > The SQL logs I described above are indeed accurate and belong to the same > worker. > -- 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] Multiple sessions same thread - How to?
On Friday, June 22, 2018 at 1:32:15 PM UTC-4, HP3 wrote:... but I'll 2x check! > (I recall that task-inheritance in celery makes certain things happen > before and others after the fork - I am using prefork) > i don't use pyramid_celery, but my own pyramid and celery integration... looking at my code, i use an event decorator to catch the fork and issue a dispose @worker_process_init.connect def mycelery_atfork(signal=None, sender=None, **named): getengine().dispose() i have NO idea if this will work for you. My code hits the database before the fork, so I needed to do this. -- 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] Re: Duplicate `self`, without affecting the original instance in memory…
FWIW, i use these methods in my base class to copy the object into a dict.
def columns_as_dict(self):
"""
Beware: this function will trigger a load of attributes if they
have not been loaded yet.
"""
return dict((col.name, getattr(self, col.name))
for col
in sqlalchemy.orm.class_mapper(self.__class__).
mapped_table.c
)
def loaded_columns_as_dict(self):
"""
This function will only return the loaded columns as a dict.
"""
_dict = self.__dict__
return {col.name: _dict[col.name]
for col in sqlalchemy.orm.class_mapper(self.__class__).
mapped_table.c
if col.name in _dict
}
--
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] Multiple sessions same thread - How to?
Is there a chance there is a query/connection being made between the initialization and worker process? If so, that could screw up the connection pool. To address that, you can try adding an `engine.dispose()` before celery forks. I'll take a look at the code later. I was home sick this morning and need to catch0up first. -- 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] Multiple sessions same thread - How to?
On Thursday, June 21, 2018 at 10:02:27 PM UTC-4, HP3 wrote: > > What's the correct way to create a session that is not automatically bound > to any transaction or that can be explicitly bound to an isolated > transaction that can be committed whenever? > That's what `Session()` does by default Usually arguments to the Engine/Session configuration or framework plugins are used to bind sessions to active transactions (such as zope.sqlalchemy). You probably have code that is doing that, somewhere. -- 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] Re: really-really slow query running from Oracle, with joins and filters
First: `join` allows you to specify a join condition. You can often use that to bypass tables or automat selects query(A).join(B, A.id == B.id_a) But: what you really need to do is check the generated SQL to see what is going on, and tweak that to eliminate the joins/fields you don't need. Then you can run the raw SQL against EXPLAIN in an Oracle client to see what is slow and why. Based on that, you can tweak what SqlAlchemy does. -- 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] Re: newbie question: best way to factor and group data when only some columns change
personally, I'd do 3 tables: -- matrix of all the potential tests and their results table testcondition id int primary key var_1 int var_2 int expected_result STRING -- one record per testrun table testrun id int primary key test_time timestamp -- one record per test per testrun table testresult id int primary key testrun_id int references testrun(id) testcondition_id int references testcondition(id) test_pass boolean default NULL test_failed_with STRING that would allow you to easily query tests and figure out when something broke or got fixed. -- 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] Re: SQLAlchemy keeps dropping sessions
In theory what you're claiming should be fine... but there's always a chance that what you *think* you're doing isn't really what you're doing. I suggest creating a tiny one-file flask app that mimics your behavior and reproduces the result -- then share it here. Your example on StackOverflow doesn't show how the sessions/request/app is setup or anything is invoked. There's a good chance you're doing everything right and this is a weird RDS specific thing that Mike hasn't seen before. But there's also a decent chance you've done something wrong or have an anti-pattern somewhere that is exacerbating this issue. Over the years a handful of people have posted random problems, and it just ends up being a small misuse in a configuration or setup creating a cascade of hard-to-diagnose errors down the line. -- 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] SQLAlchemy keeps dropping sessions
On Sunday, June 3, 2018 at 10:48:12 PM UTC-4, Mike Bayer wrote: it seems like your source of connectivity has an idle connection > timeout going on. What version of Postgresql is this, or is this > redshift? are you using a proxy like pgbouncer or haproxy? > Adding a few other things to this: 1. Are you making any connections during the Flask application setup, before request processing? That can create issues with connection pools if `dispose` is not called. 2. Does a typical request have many blocks of code that execute like this? In my experience, doing everything within a miniature sessions or transactions like that can create a lot of overhead that makes connections and servers work less than optimal. I can't remember what happened when I improperly deployed SqlAlchemy in a threaded async app years ago, but I think I ended up with some issues like you experienced (as well as lots of odd data integrity issues). -- 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] design question for sqlite backed app
On Friday, May 18, 2018 at 5:17:00 PM UTC-4, Mike Bayer wrote: > > > Well, SQLite locks the whole file during writes so that could be > problematic.if you have a write transaction open, it could cause > problems.I haven't stayed up to date on the latest SQLite changes, > current behavior is here: http://sqlite.org/lockingv3.html I'd note > that the pysqlite driver is pretty conservative about when it locks > the file as it's trying to allow for as much concurrency as it can. > Thanks. I was looking mostly at the threading docs, and was worried about this stuff. I'm running the Postgres approach on sqlite, and it seems to be working. I'll try stress testing it and hope for the best. I really don't want to refactor this app... though I may just drop transactions across the board and make it all autocommit. -- 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] design question for sqlite backed app
We use a custom SqlAlchemy+Pyramid backed client for requesting and managing LetsEncrypt SSL certificates. It centrally stores/manages the certificates, which can then be deployed to various servers on a network, with support built-in for PostgreSQL and SqlIte data storage. I'm working on an update right now to integrate rate limit awareness and hitting a conceptual roadblock for Sqlite. While the main work runs within the scope of a single transaction, I need to independently read/write to the database for some logging work. With PostgreSQL, I would just create a secondary connection - but I'm not sure about the safety of that in sqlite. A good example of what I'm trying to deal with is a certificate request The transaction scoped work looks like this: [Begin] -> [Auth Domain 1] [Auth Domain 2] [Auth Domain 3] [Sign Certificate] [Commit] The transactionless autocommit stuff looks like this: Auth Domain 1: Log requesting an auth Log validation request Log validation result (retry, pass, fail) Auth Domain 2 (repeat above) Auth Domain 3 (repeat above) Sign Cert Log requesting a cert, update with valid/not Does a secondary autocommit session seem ok for this sort of sqlite usage? -- 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] is it possible to make load_only local to an object only and/or avoid the identity map and/or not available via `get`?
On Sunday, May 13, 2018 at 10:27:16 PM UTC-4, Mike Bayer wrote: > > > that's not true, you can use the same database connection on a second > Session. Just say s2 = Session(bind=s1.connection()). > Thanks, mike. I didn't know this was doable - it seems like the right approach. The `populate_existing` approach isn't very desirable, as it could potentially effect a few hundred queries. I'd need to introduce a global filter that is conditionally triggered, and it's just likely to be messy. -- 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] is it possible to make load_only local to an object only and/or avoid the identity map and/or not available via `get`?
A better way of conveying my problem might be: `get()` returns any object which is in the identity map; I am having issues after the identity map is now populated with some objects which are 'partials' that were created via 'load_only' parameters. The benefits of using `get` to minimize a database query are outnumbered by dozens of selects to the database. I'll try to re-explain everything with a more details below. On Sunday, May 13, 2018 at 11:41:56 AM UTC-4, Mike Bayer wrote: > > > Phase one is a query with lots of joinedload, and *also*, a > load_only() (Is that right? your previous email shows a joinedload + > load_only, this email OTOH says "phase 1, lots of eagerloading and > joinedloading", is that the one with the "load_only" ?) > > Phase 1 can have a load_only, usually not. The problem with the load_only is in the stuff that now happens before phase 1. Then, you say, the get() in phase 2 (which we assume is the get() you > refer towards in your previous email) hits objects from this > joinedload + loadonly 99% of the time, so 1% of the time they are from > a cache, which you haven't told me how objects get into that or what > state they are in. > That 1% are direct queries against the Database. The cache I'm talking about is SqlAlchemy's identity map. Phase-1: - SqlAlchemy queries PostgreSQL for the primary keys of objects under several criteria. - The app aggregates all the primary keys by object type, then selects the objects by the primary key, making extensive use of the joined/eager loading. - The generated SqlAlchemy objects are inspected and analyzed for foreign keys that are of interest, which are then loaded via the aggregated gets. - Batching selects like this enormously improved PostgreSQL performance by influencing how it loads/unloads and caches data. The queries only involve indexes, and then tables only get 1 (sometimes 2) operations with the primary index. Phase-2: - The objects are interacted with. Their attributes and relationships are accessed. - Sometimes this code needs to use a specific object that was not-necessarily loaded in Phase-1. SqlAlchemy's `get(primary_key)` is used to leverage the identity map, which would either return an existing object by primary key, or hit the database. This almost always results in a "cache hit" from the identity map, so no sql is emitted. Occasionally the object was not loaded, so sql must be emitted to select it. The current problem was created when a new "Phase-0" was introduced. In Phase-0, SqlAlchemy queries a number of objects with `load_only` specifying a small subset of their columns. If these objects are queried in Phase-2 via `get()`, the identity map usually returns the previously selected object with a small subset of the the columns; this causes sql to be emitted and data to load every time a previously untouched attributed or relationship is accessed for the bulk of the objects' columns and relationships. This results in dozens of sql selects. What I'd like to accomplish: not return any objects which were loaded in Phase-0 via the `get` queries in Phase-2. I'd use a second session to handle the Phase-0 objects (which are readonly), except that means two database connections. Loading the 'full' objects isn't a good option either - some of these objects span a half dozen database tables and are mapped back into a single object via association_proxy. -- 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] is it possible to make load_only local to an object only and/or avoid the identity map and/or not available via `get`?
On Saturday, May 12, 2018 at 9:49:34 AM UTC-4, Mike Bayer wrote: > > > The former would be a bug. The latter, I'm not sure what you would expect > it to do. Do you want the unloaded attributes to raise attribute error? > Or did you want the get() to fully refresh what was not loaded (that is > doable). > The latter. The closest thing I've been able to get the behavior I need with is a separate session. This need came due to application growth over a long period. The application previously had two phases: * phase 1: lots of eagerloading and joinedloading * phase 2: leverage get(), which hits items loaded in phase1 99% of the time. i just finished up some changes to the authorization system, which is now using a dogpile cache with a small subset of keys on short timeout. when there is a cache miss, this component executes sql before "phase 1" – i'll call this "phase 0". a handful of items accessed in "phase 2" are now loaded into the identity map during "phase 0", instead of being a miss. instead of saving a Sql query, i'm now 20+ queries per object. -- 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] is it possible to make load_only local to an object only and/or avoid the identity map and/or not available via `get`?
I tracked down a pattern that has been causing some bottleneck issues in an
app. I'm hoping there may be a way to handle this, but for once I don't
expect there to be!
The problem arises when I fetch a specific "light" query with joined
objects (however toplevel objects have the same issue).
foo =
s.query(Foo).options(sqlalchemy.orm.joinedload('bar').load_only('name')).filter(Foo.id==2)
Later on, I might request the same bar I had loaded using `get`
bar = s.query(Bar).get(id=22)
If I happen to grab a Bar that was loaded off a load_only, every time i
touch an attribute I talk to the database - which is what causes my slowup.
I don't necessarily want to call expunge_all() or expunge, because I might
still access that object.
--
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] Inconsistent flushing preventing "get_history" from working
On Monday, May 7, 2018 at 10:27:03 PM UTC-4, Mike Bayer wrote: > > can you perhaps place a "pdb.set_trace()" inside of session._flush()? > using the debugger you can see the source of every flush() call. > Generally, it occurs each time a query is about to emit SQL. > > Building off what Mike said... it's going to emit sql + flush if you are accessing any attributes or relationships that haven't been loaded already. So if the object only had a few columns loaded (via load_only or deferred) or didn't load all the relationships, your code is iterating over the columns and relationships so will trigger a load. It may make sense to turn autoflush off and manually call flush as needed. -- 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] Re: Poll of interest: add @properties to mapped objects or use functions instead?
we do both... a @property on the ORM just invokes the helper method. most sections of a traffic heavy app are backed by a cache of dicts built off sqlalchemy objects. when a cached object is pulled out of storage, the same helper methods are used by it's model/api. -- 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] turning only loaded columns into a dict
On Wednesday, May 2, 2018 at 5:53:56 PM UTC-4, Mike Bayer wrote: > > if you only care about things that are loaded, like before, look in > inspect(instance).dict , that's what's loaded > Thanks. I'll migrate my proof-of-concept to use `inspect`. -- 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] turning only loaded columns into a dict
I have a mixin that helps convert object to JSON using a `columns_as_dict`
method.
it looks like this:
from sqlalchemy.orm import class_mapper as sa_class_mapper
class Mixin(object):
def columns_as_dict(self):
_cls = self.__class__
return dict((col.name, getattr(self, col.name)) for col in
sa_class_mapper(_cls).mapped_table.c)
I pinpointed a performance issue where the db was getting hit when
`load_only` was used on the objects.
The simplest fix I could think of, is fetching column values from the
object's dict instead of via getattr . Is there a more appropriate way?
from sqlalchemy.orm import class_mapper as sa_class_mapper
class Mixin(object):
def columns_as_dict(self):
_cls = self.__class__
return {col.name: self.__dict__[col.name]
for col in sa_class_mapper(_cls).mapped_table.c
if col.name in self.__dict__
}
--
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] best ways to preserve and analyze detached/unbound items
Thanks for all this help, Mike! On Tuesday, May 1, 2018 at 8:56:35 PM UTC-4, Mike Bayer wrote: > > at what "moment in time"?I thought you might mean when they are > expired from the Session, easy enough use the expire event > > http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=expire%20event#sqlalchemy.orm.events.InstanceEvents.expire > > but then you are saying "detached by their nature", as though they are > created that way. > This event looks like what I need. I'll add it into my debugger's code tomorrow! one of the `persistent_to_` events might be better... more below. > inspect(instance).expired_attributes > The inspector would have been a better idea. I got lazy and was just going through the object in pdb! > but when you say "dead weakref" I think we are again getting at this > "moment in time" you refer towards > ... > preserve it fromwhen ? when is it there? why did it go away ? > The debugging tool essentially decorates a web request with a middleware-like context-wrapper like flow, then allows it to be inspected in another browser window. The particular functionality I'm working with stashes some ORM objects onto the request record, and a custom debugging panel is used to audit/inspect the objects that were stashed on the request (the last 20 requests are stored in-memory and available to the debugging tool). 'By their inherent nature' meant everything in the debugger tool occurred in a session that had previously ended with an explicit `close()`. The flow looks like this: * request made * debugger wrapper starts ** sqlalchemy session starts *** activity ** sqlalchemy session close() * debugger wrapper cleanup * debugger wrapper ends So I'm basically trying to access the object's state in `*** activity` prior to `** close` The events look right. If I can't hack something with them, I'll just stash a dict of the object if there is a flag for the dev environment present. -- 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] best ways to preserve and analyze detached/unbound items
I have a debugging toolbar on a web panel that occasionally receives SqlAlchemy objects, which are unbound/detached by their nature. Is there a way I can preserve their contents as a dict (at that moment in time) which can be iterated/inspected? Looking at `ormInstance.__dict__['_sa_instance_state'].__dict__`: * expired = True (expected) * expired_attributes are the columns I want * _instance_dict is a dead weakref I think if I could preserve the _instance_dict, that would be fine. I don't want to bind/merge this to a new session, because I'm concerned with the info at that moment in time. -- 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] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)
On Thursday, April 26, 2018 at 1:59:02 PM UTC-4, Jeremy Flowers wrote: > > >> But I now have a stmt variable at the end.. > How do I iterate over that in the form: > for row in results: >print(row) > results = query.all() for row in results: print row http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.all If you want to see what it compiles to, this will give you a general idea, but the SQL won't be compiled to the active database... print stmt you can compile the query's statement with a specific dialect for your database like this: from sqlalchemy.dialects import oracleprint str(stmt.statement.compile(dialect=oracle.dialect())) -- 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] Re: WISHLIST: Dialect for InterSystems Caché Database
Since this is a commercial database, you should ask InterSystems support to build or sponsor development of this. -- 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] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)
almost everything in `func` is just standard sql that is executed in the database. `unnest` is a sql function that is used to turn arrays into tabular data. a search for CTE should surface 'suffix_with' which can be used to augment a query http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.CTE.suffix_with there are examples of this being used for depth-first in old tickets on the issue tracker -- 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] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)
In the example you pointed to, `c` are the columns of an object created by `alias()` `secondary` was created as an `alias()`, and the query has 2 columns that were queried and named via `label()` "id" and "ancestor_id" sqlalchemy has a lot of relevant docs if you search via the keyword "CTE". there are a handful of posts in the archive , and possibly stackoverflow, on doing this in Postgresql. -- 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] Re: unable to do nested select while using query object
On Wednesday, April 18, 2018 at 11:20:39 AM UTC-4, su-sa wrote: > > > But if I am not mistaken, the from clause of the query is generated by > SQLAlchemy and the database driver or the dialect has no influence on this > from clause generation of SQLAlchemy. > As an aside from everything Simon is saying: SqlAlchemy generates the textual SQL provided to the DBAPI, but it is influenced-by or overridden-in the dialect. It sort of looks like this: [your code] -> [complied by sqlalchemy WITH AND FOR dialect] -> [dbapi] -> [database] -> [dbapi] -> [sqlalchemy turns into objects] -> [your code] I assume you are probably using this dialect: https://github.com/SAP/sqlalchemy-hana When there is an issue with a dialect in a certain situation * very often: the dialects are capable of generating the SQL for a certain situation, and they must fix it. * very rare: sqlalchemy doesn't offer the dialect appropriate hooks to accomplish something; the dialect maintainers propose a way to for SqlAlchemy to offer the needed functionality for their dialect to generate a specific query -- 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] Re: Is there any way do select top 100 results from a query in SQLAlchemy?
On Tuesday, April 17, 2018 at 5:58:32 PM UTC-4, Jonathan Vanasco wrote: > > > So .limit() is .top() > Clarified: `limit()` is essentially the same as if there were a `top()`, because it will emit `TOP` for the query. -- 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] Re: Is there any way do select top 100 results from a query in SQLAlchemy?
SqlAlchemy doesn't natively support `TOP` because it's not part of standard sql. IIRC, only mssql uses it -- and uses it instead of 'limit'. The SqlAlchemy dialect for mssql will adapt .limit() to emit `TOP` instead of `LIMIT`. http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#limit-offset-support So .limit() is .top() -- 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] question on making a `text` element usable by the ORM?
I have a complicated recursive CTE that exists as text()
_complex_sql_ = sqlalchemy.text("""WITH RECURSIVE _foos AS (
SELECT id
FROM foo
WHERE (id = :id_start AND ...)
UNION
SELECT f.id
FROM foo f
INNER JOIN _foos _f ON _f.id = f.id
)
SELECT DISTINCT id FROM _foos""")
I'd like to make this a selectable element that can be joined to ORM
classes.
The only way I can seem to move forward is with soemthing like this...
stmt =
sqlalchemy.text(_complex_sql_).bindparams(id_start=1).columns(sqlalchemy.sql.column('id',
sqlalchemy.Integer))
that wraps it in "SELECT id AS id FROM (_complex_sql_)" and fails because
the subquery needs an alias
stmt =
sqlalchemy.text(_complex_sql_).bindparams(id_start=1).columns(sqlalchemy.sql.column('id',
sqlalchemy.Integer)).alias(wrapped)
that wraps it in "SELECT id AS id FROM (_complex_sql_) AS wrapped" and
works
is there any way to 'unwrap' that without the `SELECT id AS id FROM` stuff
and just run the raw query?
that form is throwing off one of the settings in the query planner, and
it's a real pain to work on two versions of this query at once.
--
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] Re: standardizing sqlalchemy usage: session.query + session.execute
On Monday, April 9, 2018 at 7:35:14 PM UTC-4, Mike Bayer wrote: > > fetchall() gives you a list, how about that? the RowProxy acts just > like a tuple. > Thanks! That could work. I'll try some tests. The issue I ran into earlier is that some encoders inspect the `type`, and that throws off some encoders. `_collections.result` inherits from `tuple`, so is treated as one. I can write in RowProxy/ResultProxy support via encoders I know of... but this app doesn't have full coverage and I'd like to risk likely issues. -- 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] Re: standardizing sqlalchemy usage: session.query + session.execute
I forgot to add, the current fix is this: # consume and coerce `ResultProxy[RowProxy,...]` into a list for compatibility with other functions results = [list(i) for i in results] this feels like I'm doing something wrong. -- 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] standardizing sqlalchemy usage: session.query + session.execute
I have a handful of functions within an application's "library api" that
share a similar intent in functionality and return values, but query for
discrete types of objects. I'm working on standardizing them right now and
could use some feedback.
case 1 is simple:
results = dbSession.query(Foo.id, case(..)).all()
results is a `list`
results[0] is an instance of `sqlalchemy.util._collections.result`
case 2 is where i'm stuck - some queries are a bit complex and still in raw
SQL..
results = dbSession.execute(text("SELECT id, id_bar FROM foo WHERE
...")).all()
results is a `sqlalchemy.engine.result.ResultProxy` instance
if I consume all rows as `results = [i for i in results]`
then `results[0]` is a `sqlalchemy.engine.result.RowProxy`
I need to ensure the latter form is a list of tuples, like the former, so
it can be used by various encoders and libraries that look for a list/tuple
in that objects MRO.
Right now I'm manually doing this and documenting the rationale. But is
there anything in the sqlalchemy api that would streamline turning the
second form into something where a tuple/list is involved - perhaps
bypassing the RowProxy step? I couldn't find anything in the docs.
--
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] is this intentional or a bug with subquery join
On Wednesday, April 4, 2018 at 4:49:16 PM UTC-4, Mike Bayer wrote: > > I've not seen any context for what you're doing so is using > "query.select_from()" part of how you want to handle this? > My personal fix was just to flip the order of entities submitted to `.query()` - but I could have used `.select_from()`. It was already handled when I first posted - I was just wondering "why". Now I know - the order of entities is deterministic to the generated sql. The behavior wasn't documented in the API under 'query' or 'join', but was in the tutorial. I was refactoring an expensive query yesterday, and a few columns got deleted or moved around from the query's components. I never experienced this in... 10+ years of SqlAlchemy. -- 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] is this intentional or a bug with subquery join
I built a simplified test-case, then tried to simplify it more into a second form -- and got a different error. The second error message, and sqlalchemy source code lines, confirmed my initial belief this is an intentional behavior, which is not reflected in the `query` or `join` API docs. They also let me track down a callout box in the ORM tutorial noting this behavior though: when multiple entities are queried, SqlAlchemy builds the join off the first entity. -- 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] is this intentional or a bug with subquery join
On Wednesday, April 4, 2018 at 4:31:36 AM UTC-4, Simon King wrote: > Where is the traceback raised? On the evaluation of the > "subquery.c.event_timestamp" expression? That wouldn't really make any > sense. Or is it when the query itself is evaluated? We need to see at > least the traceback, or better a script to reproduce. > I'm reading this as "possibly not intentional behavior", so I will write a script to reproduce it. -- 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] is this intentional or a bug with subquery join
On Tuesday, April 3, 2018 at 10:34:03 PM UTC-4, Mike Bayer wrote: > > "does not work" ? wow, i am an awful person. sorry. that is the least helpful description of what happens i can think of. second try: if the subquery is the 1st element, the select compiles correctly and gives me the correct data. if the subquery is 2nd element, sqlalchemy raises an exception that event_timestamp is not a column in subquery.c -- 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] is this intentional or a bug with subquery join
I couldn't find docs on this behavior, so wanted to ask before filing a
ticket with a test-case. If this isn't the expected behavior, I'll
generate a SSCCE. I think it might be expected though.
I have a form of a query that uses an ORM object joined against a subquery
It works when the subquery is the first element
query = dbSession.query(sqlalchemy.func.max(subquery.c.event_timestamp).
label('event_timestamp'),
model.Foo.id.label('foo_id'),
)...
but it does not work when the subquery is the second element
query = dbSession.query(model.Foo.id.label('foo_id'),
sqlalchemy.func.max(subquery.c.event_timestamp).
label('event_timestamp'),
)...
--
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] dispose/close question
On Tuesday, April 3, 2018 at 11:41:43 AM UTC-4, Mike Bayer wrote: > > right, the dispose() will emit "close connection" commands on the > socket which will leak into the parent process. > But only if `close()` has been called -- right? > they're not ! :) that's one of the "features" of QueuePool. only > when they get checked in again does the QP have any idea who they are. > > I wrote an alternative pool some months ago that works identically to > QueuePool but does the more traditional approach of having a fixed > "slot" for every possible connection.That would be a nice pool to > use someday, but unfortunately doesn't have the ten years of > production use by thousands of applications behind it. > Makes perfect sense. I'm surprised I couldn't find a debugging version of the Queue Pool that just notes the object ids as text (this way there's no reference). Like most things SqlAlchemy, It's probably in there but I missed it. -- 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] dispose/close question
looking at the current `engine.dispose()` (which I should have done sooner), that's pretty much what it's doing -- right? https://bitbucket.org/zzzeek/sqlalchemy/src/55371f4cffa730f65f1b687e9f6287d2ac189227/lib/sqlalchemy/engine/base.py?at=master=file-view-default#base.py-1899:1923 self.pool.dispose()self.pool = self.pool.recreate() self.dispatch.engine_disposed(self) or are you thinking of something that would not call the dispose(), this way the main process can keep that connection? something like... original_pool = dbSession.connection().engine.detach() def *detach(self): """replaces the pool"""* old_pool = self.pool self.pool = self.pool.recreate() return old_pool Are the checked-out connections stored anywhere? The 'Pool._all_connections' attribute seems to be more like '_idle_connections' or '_available_connections' -- 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] dispose/close question
On Monday, April 2, 2018 at 1:40:06 PM UTC-4, Mike Bayer wrote: > > now we can probably do better than that but the dispose() case is not > supposed to be that common or critical, it's usually for when you just > did a fork(), and you don't want to reference any of the filehandles > in the parent process (but to that extent, dispose() is not ideal > because it will step on those connections that might be *used* by the > parentso...more bugs :) ) > One person's uncommon is another person's everyday ;) Someone was running a pyramid app with SqlAlchemy connection issues. They were running in a gunicorn container, and connected to the database pre-fork to pull out some configuration settings - at the very least they needed to call `Engine.dispose()`. There may be some other things going on too, but that's the guaranteed way to screw up database connections running in gunicorn, uwsgi and a few other servers. -- 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] dispose/close question
clarifying: I had run tests, and the checked-out connections are still open after dispose(), and then garbage collected out. -- 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] dispose/close question
On Monday, April 2, 2018 at 1:21:26 PM UTC-4, Mike Bayer wrote: > > Connections that are checked out stay open, they > still refer to that dereferenced pool, and I think at the moment they > don't get closed until they are garbage collected along with that pool > object. > Thanks. That's what I thought was happening, but wanted to make sure. -- 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] dispose/close question
a question came up in another list, and I just wanted to confirm a behavior it looks like `engine.dispose()` doesn't explicitly `close()` any open connections, but they inherently get closed by the method's behavior. is that right? -- 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] Custom utcnow function and session.execute yields a constant timestamp
in postgres returns the start of the current transaction: NOW() CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP LOCALTIME LOCALTIMESTAMP returns the actual time: clock_timestamp() timeofday() -- 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] Two application versions, different back ends
On Monday, March 26, 2018 at 9:41:00 AM UTC-4, Rich wrote: > > >I'm working now on the single-user, SQLite3 version and will return to > this issue when that's functioning. Pay close attention to which database functions you use, and how you use datetime fields. Those are two things were SQLite tends to differ from the other common databases. You can handle any of those differences using dialect specific custom compilers in SqlAlchemy (http://docs.sqlalchemy.org/en/latest/core/compiler.html) There's no need to do anything now, but it's helpful to know what is likely to break when switching the model's datastore to postgresql. -- 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] Creating a dynamic/new column using Text selects?
On Thursday, March 22, 2018 at 4:27:18 PM UTC-4, Mike Bayer wrote:
>
>
> probably just make a fake column:column('counted') see what that
> does
>
>
Bingo. Thanks!
counted_column = sqlalchemy.Column('counted')
stmt = sqlalchemy.text('''SELECT...''')
stmt = stmt.columns(Foo.id, Foo.name, counted_column)
results = session.query(Foo, counted_column).from_statement(stmt).all()
--
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] Creating a dynamic/new column using Text selects?
I have a large and complex analytics query that I'd like to keep in raw
SQL. It generally looks like this:
SELECT foo.id,
foo.name,
tally.counted
FROM(...
...) tally
JOIN foo ON tally.foo_id = foo.id
WHERE tally.counted > 1
ORDER BY tally.counted DESC
Is it possible to use the `columns` approach from the textual sql docs to
pull in the `counted` value ? Something like this:
stmt = sqlalchemy.text('''SELECT...''')
stmt = stmt.columns(Foo.id, Foo.name, ???)
results = session.query(Foo, ???).from_statement(stmt).all()
--
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] Re: Differentiate what raised IntegrityError (fkey not found vs. duplicate pkey)
Mike will probably chime in with a more correct answer, but... You should be able to figure that out by catching the `sqlalchemy.exc.IntegrityError` error and inspecting the attributes (original error or the text message). http://docs.sqlalchemy.org/en/latest/core/exceptions.html#sqlalchemy.exc.IntegrityError The DBAPI only defines IntegrityError, and IIRC not all backends provide a differentiation between the types of integrity error. On Tuesday, March 20, 2018 at 2:11:44 PM UTC-4, Peter Lai wrote: > > I'm implementing a recursive upsert operation for an object whose primary > key also contains a foreignkey, and I'd like to get some more info from > IntegrityError, namely whether integrity was violated because the > foreignkey didn't exist (yet) or I am trying to insert a duplicate pkey. In > the former case, I'd go and insert the parent, in the latter, I'd just > ignore it and rollback (since the state is already end state) > > Is there a way to fish this out from IntegrityError or do I need to catch > DBAPI exceptions instead? > > -- 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] overriding/ignoring specific database records
On Tuesday, March 20, 2018 at 11:29:18 AM UTC-4, Mike Bayer wrote:
>
>
> if you just need a different name, rename it:
>
> my_attr = Column('id_foo__context_a', Integer, ...)
>
>
>
SqlAlchemy does everything I need. Thanks Mike!
--
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] overriding/ignoring specific database records
this is silly - I realized I can just use a flag to run a different
`orm.relationship` on the Admin and Public views:
if ADMIN:
records = orm.relationship(a.id=b.id)
else:
records = orm.relationship(and(a.id=b.id, b.id<1000))
Is there any magical SqlAlchemy feature that would let me define
setters/getters for a table's python column, while still keeping the
underlying name of the column?
to explain this poorly worded question...
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
id_foo__context_a = Column(Integer, ForeignKey("foo.id"))
id_foo__context_b = Column(Integer, ForeignKey("foo.id"))
i'd like to replace `id_foo__context_a` with a property or setter/getter,
which can still access/manipulate a stored value for a database field named
`id_foo__context_a`. usually i rename the column or field to handle this,
but in this case I can't.
Perhaps there is an undocumented way to alter the backing db column of a
sqlalchemy column, something like this...
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
_id_foo__context_a = Column(Integer, ForeignKey("foo.id"),
DatabaseColumn='id_foo__context_a')
id_foo__context_b = Column(Integer, ForeignKey("foo.id"))
@property
def id_foo__context_a(self):
return self._id_foo__context_a
--
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] overriding/ignoring specific database records
I was wondering if something were doable with SqlAlchemy in a decently performant manner that can be maintained cleanly. I'm open to any strategies that people can suggest. We have a handful of reserved internal "system" or "control" records in our database. Depending on the table, anywhere from 10k to 1MM ids are reserved (in practice, only 10 are actually used). I'd like to exclude these items from showing up in relationships (and possibly even from appearing on a record as an id field). They are only needed in our Admin interface (runs as a separate app, so it can be configured differently), but are a hindrance on the Public app. Here's a typical use case: One of our tables represents a content-graph of spidered web pages. During the course of development, we realized certain data wasn't being attributed to the right pages due to issues with redirect detection in a specific scenario. Consequently, a chunk of records created before we addressed this case now list their 'redirected_from' id as the control record for "Untracked Redirect Chain". (Due to the size of the DB and several other constraints, adding in a new field to track errors/data wasn't an option). That leaves me needing to hide this connection in two places on our clientside app: * don't load it into a sqlalchemy.orm.relationship * discard it when looking at the raw column ids (when populating the read-through cache) To handle this, I'm doing the latter manually and using a 'display_' prefixed property to wrap the real relationship - but this is ugly. I'm hoping for some suggestions from the larger community. -- 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] is it possible to automatically create child from parent when there is a one-to-one relationship?
I'm refactoring a large table and moving some write-heavy columns into their own table Is there a way to automatically create the child when the parent is generated, or must this be done manually? I couldn't find anything in the docs. This is almost a variant of inherited tables, but there are actually 3 different child tables with 1:1 relationships. -- 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] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?
On Tuesday, March 6, 2018 at 3:23:42 PM UTC-5, KCY wrote: > > I recall coming upon a section about this in the SQLAlchemy docs, although > I can't remember where exactly. It's not the problem (if you can call it > that) that I'm describing here. I should double check to make sure the > design doesn't expect to have concurrent edits on the same objects. > I'm not suggesting it is the problem. What I am suggesting is that you're very likely going to have a concurrency/race problem when there is a first Session that lasts the scope of web requests, and a second session which operates on a GUI (you're likely to have this on a web requests only variant too). -- 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] Re: not announcing minor releases on the mailing list or twitter for now
I can possibly help with the Twitter/blog stuff, I know that sort of stuff well. I'll absolutely try to help, I'm just not sure I can do what you exactly envision in a convenient timeframe. -- 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] standalone asc/desc function vs ColumnElement asc/desc function
On Thursday, March 1, 2018 at 9:31:26 PM UTC-5, Mike Bayer wrote: not that I'm aware of. it's a matter of whichever is more convenient > given the kind of expression that is applied with DESC (e.g. a more > complex expression might look more natural enclosed in desc()). > I've had some advanced queries with joins/unions in the past where a `.desc()` didn't work and they needed to be wrapped as `desc(foo)`. I think those edge cases have all been resolved in the past few years. as a style convention, our code always uses `column.desc()` unless it can't. -- 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] Re: Performance of ORDER BY vs. list.sort() vs. sorted()
This is going to wildly depend on how many things are being sorted, and what those things are. this topic usually a premature optimization or "you're doing it wrong". Imagine this query in Postgres: SELECT * FROM records WHERE ORDER BY timestamp_desc; If there are 1,000 items in the database, whether you sort in Python or Postgres is irrelevant – the speed will be negligible. If there are 1,000,000 items in the database, then database sorting is probably faster, as you won't have to do the operations in Python. But consider this slightly different query where a limit is added, which would happen before the sort: SELECT * FROM records WHERE ORDER BY timestamp_desc LIMIT 100; You need to sort in the database, so re-sorting in python is largely irrelevant – but if you do that, it will likely be negligible. -- 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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.
On Wednesday, February 14, 2018 at 6:49:47 AM UTC-5, Simon King wrote: > For what it's worth, I would do it something like this: > that's elegant. I'd rather do that now too. -- 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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.
On Sunday, February 11, 2018 at 8:07:23 PM UTC-5, Jeremy Flowers wrote: > > I'm afraid it just doesn't work. I would think you have to import String > too.. > Please run the entirety of what I pasted above as it's own file and share the error. That is a self-contained example that runs as a file under Python 2 and 3. String is imported at the top section under 'standard imports' In the example above, I use two forms (SELECT and INSERT), and iterate over a string / number / None, and strings containing the characters you strip. -- 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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.
The following works for me on Python 2&3
how are you generating an error ?
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
#
# Use this file to build your own SSCCE
# SSCCE = Short, Self Contained, Correct (Compatible) Example
# see http://sscce.org/
#
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# Standard imports
import sqlalchemy
import sqlalchemy.orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean, Integer, Column, Unicode, ForeignKey, String
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# You probably don't need to overwrite this
Base = declarative_base()
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# Define some models that inherit from Base
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
name = Column(Unicode)
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# set the engine
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# do a simple query to trigger the mapper error
sessionFactory = sqlalchemy.orm.sessionmaker(bind=engine)
s = sessionFactory()
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# test
from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
class stripctrl(expression.FunctionElement):
type = String()
name = 'stripctrl'
@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):
args = list(element.clauses)
return "REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13),
'')" % (compiler.process(args[0]))
@compiles(stripctrl, 'sqlite')
def stripctrl_sqlite(element, compiler, **kw):
args = list(element.clauses)
return "replace(replace(replace(%s, char(9), ''), char(10), ''),
char(13), '')" % (compiler.process(args[0]))
cases = (None, "Aaaa", 123, 'A\ta', 'B\rb', 'C\nc,')
for idx, case in enumerate(cases):
f = Foo()
f.id = idx
f.name = case
s.add(f)
s.commit()
print("--SELECT")
q = s.query(stripctrl(Foo.name)).all()
print (q)
print ("-- INSERT")
for idx, case in enumerate(cases):
f = Foo()
f.id = idx + 100
f.name = stripctrl(case)
s.add(f)
s.commit()
--
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.
that won't work as you expect. You're applying the python character replacements to the arguments, and then emitting that in sql. You'll end up with the same InstrumentedAttribute issue, because you're trying to operate on the Python column instance in the orm defintion. the string returned by the custom compiler should be a sql function, something like: """REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), '') % compiler.process(args[0]) That would create sql from your example that reads: SELECT REPLACE(REPLACE(REPLACE(, CHR(9), ''), CHR(10), ''), CHR(13), '') LIMIT 1; If you just want to insert data, you could just use a python function that does that string cleanup. -- 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] Query a query in SQLAlchemy
ColdFusion's "queryable results cache" is pretty unique. I don't know of any database layers or ORMs that support that functionality out-of-the-box. They basically replace the upstream database with the results as a "materialized view", and act as the database server for that view. There is an official recipe/example for caching results with relations on dogpile (https://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.dogpile_caching), but it doesn't really do what you want. There may be a 3rd party library that does what you want. -- 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] Re: Help- Building Join in Graph Structure.
actually, you'd be constraining the join against the edge elements since the base is a node. (edge is the more common name for your usage/link) if you're searching for node2, then you'd constrain the query by joining the edge items like this: result = session.query(node_2)\ .join(edge_1, node_2.id == edge_1.second_id)\ .join(edge_2, node_2.id == edge_2.first_id)\ you don't have a `relationship` on the Node table, so you can define that on the edge via `backref` kwarg (http://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.params.backref) or create symmetric relationships on edge and node and reference one another with the `back_populates` kwarg (http://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.params.back_populates). Once you have those relationships defined, you can just eagerload node1 and node3 off the edge_1 and edge_2 objects respectively (that's covered in the backref tutorial, there's a link to it in the backref docs above). efficiency depends on your database, dataset, database indexes and what your queries are expected to do. -- 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] Re: Help- Building Join in Graph Structure.
I do a lot with graphs in SqlAlchemy, and I think you'd have a easier time
writing these queries if you flipped your search so that you're querying a
single identified object.
For example, on use-case-1, you can query for the "Usage", and join the
nodes.
> result = session.query(Usage)\
> .join(node_1, Usage.first_id == node_1.id)\
> .join(node_2, Usage.second_id, node_2.id)\
> .options(sqlalchemy.orm.containseager('orl1', aliased=node_1),
> sqlalchemy.orm.containseager('orl2', aliased=node_2),
> )\
> .first()
>
>
Using that approach, on use-case-2, you can query for the "Node2 " object,
and constrain the selection with joins to node1 and node3.
You can definitely use your current approach, but a lot of "complicated"
joins in SqlAlchemy are **much** simpler when the primary thing you query
is not what you would naturally think to query of in raw sql - but
something more convenient for how the necessary joins and relationships are
constructed.
--
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] check for None with == , within join, raises argument error
On Monday, February 5, 2018 at 3:19:40 AM UTC-5, Rajesh Rolo wrote: > > > Thank you. .op() seems to have done the trick. I'm going to go with it as > of now. > >> Please make a test-case of your mistake though, it's important to get this figured out and patched if there is a bug. -- 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] check for None with == , within join, raises argument error
On Sunday, February 4, 2018 at 11:07:49 AM UTC-5, Mike Bayer wrote:
>
> You test for NULL with == None. I have no idea what your issue is can you
> please share complete mappings, complete stack trace and a reproducible
> example? Thanks
>
This definitely needs a reproduction testcase to find the correct error as
Mike noted, but if you're stuck in a bind for a temporary fix you can try:
User.flag.op('IS')(None)
using `.op()` has gotten me out of a lot of problems.
you should also check the sql emitted, because you may need to
'.self_group()' the or_ elements.
or_(User.flag == None, User.flag != "dnd").self_group()
i haven't been able to create a reliable test case for the above, but there
are a handful of times in postgres queries where my or's haven't been
grouped as expected, leading to a bad query. i check everything now.
>
--
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] Re: join relationship
at the very least, you are not using a "join" on the tables. i suggest first going through the tutorial again, and paying attention to the section on "join". http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-joins -- 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] Usage of Pool.dispose() to close connections using SQLAlchemy 0.8
On Wednesday, January 17, 2018 at 9:40:23 AM UTC-5, Mike Bayer wrote: > > if you create your engine in your parent process, and then let that > engine travel into a fork(), then yes, you need to call dispose() just > once when the fork starts, so that you get new connections in your > forked process. I mentioned that above. that's what we do! sorry, I read things wrong and thought you said we shouldn't do that. > however, there's a neater way that we do this in openstack which I also > refer to above in the pooling docs, that is to use an event to track which process a > particular connection was created within, and invalidate it if the two > don't match.yet another thing that could be just added to the pool > :) create_engine(..., protect_across_forks=True) that's much neater, and I'll add that as a backup! i still need to use fork events because some other libraries depend on it (the crypto library i use on a few deployments isn't forksafe, and there's another library on a legacy project that I can't remember) -- 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] Usage of Pool.dispose() to close connections using SQLAlchemy 0.8
On Tuesday, January 16, 2018 at 6:26:34 PM UTC-5, Mike Bayer wrote: dispose() is not needed inside of a web context. however, the kind > of web container you're using as well as if you are using something > like gevent or eventlet may be significant in why you are getting idle > transactions. > Mike- I thought `dispose()` was necessary with forking webservers if connections are made *before* the process forked, because the servers (typically) implement fork() behind the scenes. uwsgi offers a @postfork decorator (and an explicit hook) that I use to call the dispose(), and gunicorn has a `post_fork` hook. i don't recall the specifics of other servers. -- 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] Usage of Pool.dispose() to close connections using SQLAlchemy 0.8
On Tuesday, January 16, 2018 at 12:33:42 PM UTC-5, Joe Biggert wrote: > > we've got a wrapper around our requests that basically looks like this: > On Tuesday, January 16, 2018 at 2:34:25 PM UTC-5, Mike Bayer wrote: > > the dispose at the end is completely wrong, sorry. > ... > or if you are starting up a new process using either os.fork() or > multiprocessing. > Since this appears to be in a web context, I believe `.dispose()` is also required if you're using a forked application server (e.g. uwsgi, mod_wsgi in many contexts, etc). if that is the case and your code is connecting to the database at all before the fork, you should be able to define an "at fork" routine that calls `dispose()` for whatever deployment framework you're using. -- 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] Re: SQLAlchemy does not properly create temporary table for subqueries in MariaDB
I can't speak to the internals of this being a bug or not, or how this should be done... but I think you could do a short-term (and cross platform) fix using an alias via `sqlalchemy.orm.aliased` for one (or more) of the inner subqueries. That should result in a unique discriminator being generated for the table. It's usually used for joining a table against itself, but I've used it to get more control over the generated sql like this a few times. http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=alias#sqlalchemy.orm.aliased http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.alias something like this... Test1 = sqlalchemy.orm.aliased(Test, name='test1') protected_items_ids = session.query(Test1.id).join( protected_items, Test1.id == protected_items.c.id)\ .subquery(name='subquery2') -- 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] Re: load_only change in 1.2.0?
On Wednesday, January 10, 2018 at 2:00:25 PM UTC-5, Matt Schmidt wrote: > > The version I upgraded from was 1.1.9, and I originally started the > project on 1.1.1. > > And you wrote that above and I totally missed that line - sorry. I'll pipe down for someone else to chime in now. -- 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] Re: load_only change in 1.2.0?
what version did you update from? If this is the issue I am thinking about, that feature was added a long time ago... https://bitbucket.org/zzzeek/sqlalchemy/issues/3080 the issue in the ticket was the load_only added in the primary keys on joinedload but not subqueryload -- and the orm needs the primary key in order to pull the correct items. so michael's fix was to include it in subqueryloads automatically. it looks to be 0.9.5 in 2014 http://docs.sqlalchemy.org/en/latest/changelog/changelog_09.html#change-25bfed359ea9ffac545b3582739da0c4 and it has been in every release since 1.0b1 if you upgraded from something after 0.9.5, then this may be something else. if you upgraded from before 0.9.5, this is expected. -- 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] sqlalchemy.func.lower in a primaryjoin
I'm using declarative. I need to map a viewonly relationship that uses sqlalchemy.func.lower in the primaryjoin. `func` doesn't seem available in `ext/declarative/clsregistry.py` and can't be used in the string context. is that correct? i have a workaround of declaring the class, then mapping the relationship via python code. i'd like to make sure I'm doing this right and not missing another bit of the docs (with 99.9% of my issues have been). just to illustrate the above: class Table1(base): # this won't work" rel1 = sqlalchemy.orm.relationship( "Table2", primaryjoin="""sqlalchemy.func.lower(Table1.name)==foreign( sqlalchemy.func.lower(Table2.name))""", viewonly=True, ) # this will work: Table1.rel1 = sqlalchemy.orm.relationship( Table2, primaryjoin=sqlalchemy.func.lower(Table1.name)== sqlalchemy .orm.foreign(sqlalchemy.func.lower(Table2.name)), viewonly=True, ) -- 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] Re: Join on filter for chained loads
use `sqlalchemy.orm.aliased` to create an alias of A for your join
condition...
A_alt = sqlalchemy.orm.aliased(A, name='a_alt')
then use that to join and specify your join conditions
the `contains_eager` needs to specify the alias though.
options(sqlalchemy.orm.contains_eager('a.c.a', alias= A_alt)
i didn't know about that last bit, and just had an issue 2 days ago in
this thread, which has some sample code showing how an aliased join works.
https://groups.google.com/forum/#!topic/sqlalchemy/VLStmyJazVM
--
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.
