Re: [sqlalchemy] When to set "cache_ok" to True?

[Jinghui Niu]

Thank you. Could you please also give some hint on a case where we must set
this attribute to `False`?

On Wed, May 26, 2021 at 5:54 AM Mike Bayer  wrote:

>
>
> On Wed, May 26, 2021, at 3:07 AM, niuji...@gmail.com wrote:
>
> I have consistently receiving the warning:
> will not produce a cache key because the ``cache_ok`` flag is not set to
> True.  Set this flag to True if this type object's state is safe to use in
> a cache key, or False to disable this warning.
>
>
> After reading the documentation, I learned that the "cache_ok" class-level
> attribute can be set to either True or False. But the documentation is very
> abstract on when to use it? What is cache key? Is it for loading and
> caching a set of objects from the database? Or caching the query itself?
>
>
> the caching system is used to cache the generated SQL and related
> parameter and result-handling structures for a statement object and is
> described at:
>
>
> https://docs.sqlalchemy.org/en/14/core/connections.html#sql-compilation-caching
>
> it is always safe to set cache_ok=False on your TypeDecorator class,  you
> will however lose the benefit of SQL statement caching for statements which
> include use of this datatype.
>
>
>
> If my TypeDecorator class doesn't even have a __init__method, just
> "process_bind_param" and "process_result_value" two methods, do I even need
> to bother with this "cache_ok" setting at all?
>
>
> If your TypeDecorator is stateless, then it's fine to set cache_ok=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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/abe578f0-fd11-468f-857d-dee6fd77ebc5n%40googlegroups.com
> 
> .
>
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/zCPZKTxM6b0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f3640072-8d16-4f59-bc67-f468c78878cb%40www.fastmail.com
> 
> .
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOQAhNf94F7XHBZ28LEKe5TmC7Q4vKRbiNSZX48waASnGgYJPQ%40mail.gmail.com.

Re: [sqlalchemy] conditionals inside column_property

[Jinghui Niu]

Thanks. One thing to clarify, I noticed that here you used `case` without
using in a context of `select`. Is this considered a shorthand within
sqlalchemy?

On Fri, Dec 11, 2020 at 2:16 AM Simon King  wrote:

> You can do it, but you need to use an SQL conditional rather than a
> python one. In this case that would probably be a CASE expression:
>
>
> https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.case
>
> I think it would look something like this:
>
> from sqlalchemy.sql import case
>
> aggr = column_property(
> case([(attr_a == 'go', attr_b + attr_c)],
>  else_=attr_b - attr_c)
> )
>
> Hope that helps,
>
> Simon
>
> On Fri, Dec 11, 2020 at 9:13 AM niuji...@gmail.com 
> wrote:
> >
> > I have a mapped class:
> >
> > class Model(sqlalchemy.declarative_base()):
> > attr_a = Column(String)
> > attr_b = Column(Integer)
> > attr_c = Column(Integer)
> >
> > aggr = column_property(attr_b + attr_c IF attr_a=='go' ELSE attr_b -
> attr_c)
> >
> > Last line is pseoudo code that requires some conditional logic. Is such
> logic allowed inside column_property? Thanks a lot!
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com.
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/736f0ded-e39a-47fd-a0db-8ed33057d2a3n%40googlegroups.com
> .
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/a_-Bqhh5wY0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeO2qgWeKeWPN-WH9LD8_Zch4jQRYeiB-WNbZrcMBrZFQ%40mail.gmail.com
> .
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOQAhNeTVkH5m-XXotNyHV_wBrb3Ev9SJnJpTYF2sRuJvxmOFA%40mail.gmail.com.

Re: [sqlalchemy] Why is this pattern discouraged in SQLAlchemy?

[Jinghui Niu]

Thanks very much for the informative reply Mike!

On Sun, Aug 6, 2017 at 9:38 PM, Mike Bayer <clas...@zzzcomputing.com> wrote:

>
>
> On Aug 6, 2017 8:11 PM, "Jinghui Niu" <niujing...@gmail.com> wrote:
>
> When reading the official SQLAlchemy documentation, I found the example
> below:
>
> ### this is the **wrong way to do it** ###
>
> class ThingOne(object):
> def go(self):
> session = Session()
> try:
> session.query(FooBar).update({"x": 5})
> session.commit()
> except:
> session.rollback()
> raise
>
> class ThingTwo(object):
> def go(self):
> session = Session()
> try:
> session.query(Widget).update({"q": 18})
> session.commit()
> except:
> session.rollback()
> raise
>
> def run_my_program():
> ThingOne().go()
> ThingTwo().go()
>
> I don't really understand the drawbacks for this pattern. Actually I can
> think of one major ADVANTAGE to this: in a multi-threading context, this
> pattern can ensure that each session instance is a local variable to the
> function that actually uses it.
>
> Could someone enlighten me by giving some potential drawbacks for the
> example above? Thanks.
>
>
> The drawbacks aren't just potential, they are actual.   I have spent
> several years extricating openstack from their use of this antipattern
> everywhere.
>
> When you hardcode the transaction logic with a small query operation, now
> you can't use that method in other context besides all by itself.  If some
> other function wants to do some database work in a transaction, and wants
> to call upon your function too to also do some database work, you end up
> using two separate transactions simultaneously, as well as two database
> connections simultaneously, when you should be using just one.   The two
> transactions are isolated from each other, and if one is dependent on the
> rows of the other, now you have to also use a low isolation level so that
> this works.
>
> In practice, developers know none of this, they string the functions
> together, and then you get one simple web service method that runs four
> transactions holding onto two connections.   Then under load the app
> deadlocks, uses too many connections, and when an operation fails, it
> leaves garbage committed in the database.   Because the application was not
> correctly designed to properly separate the concerns of running
> transactions at the start and end of a logical sequence of operations, from
> that of individual database query and DML operations such that database
> operations are composable.
>
> Also this issue has nothing to do with threading as long as you aren't
> using a Session as a global variable, which you also should never do within
> any non-trivial application.  The session is passed between functions so
> would not find itself crossing thread boundaries unless you explicitly
> placed it on some kind of channel that other threads are consuming (which
> you should not do).
>
> Also, things like handling what happens during an exception, how the
> rollback and close occurs, how the exception is rethrown is *absolutely*
> something that should all be in one place and one place alone, and not
> repeated all over the place.   Handling failure modes should be done as
> consistently as possible.
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/W_Rn-EwKvZo/unsubscribe.
> To uns

[sqlalchemy] Why is this pattern discouraged in SQLAlchemy?

[Jinghui Niu]

When reading the official SQLAlchemy documentation, I found the example 
below:

### this is the **wrong way to do it** ###

class ThingOne(object):
def go(self):
session = Session()
try:
session.query(FooBar).update({"x": 5})
session.commit()
except:
session.rollback()
raise

class ThingTwo(object):
def go(self):
session = Session()
try:
session.query(Widget).update({"q": 18})
session.commit()
except:
session.rollback()
raise

def run_my_program():
ThingOne().go()
ThingTwo().go()

I don't really understand the drawbacks for this pattern. Actually I can 
think of one major ADVANTAGE to this: in a multi-threading context, this 
pattern can ensure that each session instance is a local variable to the 
function that actually uses it.

Could someone enlighten me by giving some potential drawbacks for the 
example above? Thanks.

-- 
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] Do I need to explicitly close each Session instance in a scoped_session context after use?

[Jinghui Niu]

I'm using Sqlite as backend, which doesn't use any connection pools. In my 
case, I don't know if there is any negative consequences to just let Python 
garbage collect each Session instance after use? Especially under a 
multi-threading context. Could someone point out any risks not closing them 
manually? Thanks.

-- 
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 get a Engine or Connection from Session?

[Jinghui Niu]

That's it! Thanks!

On Saturday, August 5, 2017 at 3:15:28 PM UTC-7, Jonathan Vanasco wrote:
>
>
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.connection
>
>
>

-- 
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: How to get a Engine or Connection from Session?

[Jinghui Niu]

But it's not for returning a connection object?

On Aug 5, 2017 1:51 PM, "Ruben Di Battista" <rubendibatti...@gmail.com>
wrote:

> session.bind?
>
> On Saturday, August 5, 2017 at 2:36:53 PM UTC+2, Jinghui Niu wrote:
>>
>> Is there a way to get the currently binding Engine or Connection from a
>> Session object? The backend database is Sqlite. Thanks.
>>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/e8jwPo7ALAw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] How to get a Engine or Connection from Session?

[Jinghui Niu]

Is there a way to get the currently binding Engine or Connection from a 
Session object? The backend database is Sqlite. Thanks.

-- 
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 there a way to configure session.commit() so that it automatically return the committed instance's class.__name__ + primary id?

[Jinghui Niu]

Thanks Simon. Your answers always help immensely getting to know SA better.
I now know the overview.

On Tue, Jul 25, 2017 at 1:45 AM, Simon King <si...@simonking.org.uk> wrote:

> On Tue, Jul 25, 2017 at 2:35 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> > I was wondering if there is a way to configure Session.commit() so on
> each
> > successful commit, it will return the committed/updated/deleted
> instance's
> > class.__name__ + row.id. Is this possible? Thanks.
>
> No, there's no way to make the commit() method do that. In general the
> request doesn't really make sense as a commit may cover multiple
> instances, not just one. Also, objects are inserted/updated during a
> flush(), which may happen separately from the commit().
>
> If you wanted to build this yourself, you could use ORM events
> (http://docs.sqlalchemy.org/en/latest/orm/events.html) to be notifed
> when an instance is inserted/updated/deleted.
>
> Hope that helps,
>
> Simon
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/B8zpOVkCzv4/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Is there a way to configure session.commit() so that it automatically return the committed instance's class.__name__ + primary id?

[Jinghui Niu]

I was wondering if there is a way to configure Session.commit() so on each 
successful commit, it will return the committed/updated/deleted instance's 
class.__name__ + row.id. Is this possible? Thanks.

-- 
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 better understand `remote_side` in sqlalchemy?

[Jinghui Niu]

Now I see, thanks Mike!

On Jul 20, 2017 6:51 PM, "Mike Bayer" <mike...@zzzcomputing.com> wrote:

> On Thu, Jul 20, 2017 at 8:10 PM, Jinghui Niu <niujing...@gmail.com> wrote:
> > I see. But still I'm struggling to see the real difference between:
> >
> > Node.parent_id = Node.id
> > vs.
> > Node.id = Node.parent_id
> >
> > Aren't we just switching around sides here?
>
> those two conditions are equivalent.  but that's not how you would
> join a table to itself self-referentially.  you always need at least
> one of the tables to be *aliased*, so that it represents a distinct
> set of rows:
>
>  SELECT * FROM node AS n_left JOIN node AS n_right ON
> n_left.parent_id = n_right.id
>
> can you see the difference?
>
> >
> >
> >
> >
> > On Thu, Jul 20, 2017 at 4:47 PM, Mike Bayer <mike...@zzzcomputing.com>
> > wrote:
> >>
> >> On Thu, Jul 20, 2017 at 1:34 PM, Jinghui Niu <niujing...@gmail.com>
> wrote:
> >> > I've been studying sqlalchemy's self referential table. I've read the
> >> > documentation many times and still have difficulties understanding the
> >> > concept of remote_side. Could someone please draw a diagram or use an
> >> > analogy to help explain this concept? I think visualizing is a better
> >> > way
> >> > but anything helps would be appreciated. Thanks.
> >> >
> >> >
> >> > By the way, in my opinion the word remote is a little vague, as it can
> >> > be
> >> > interpreted from different angles. Like the word left and rigt, it
> >> > really
> >> > depends on which direction you are facing, my right could be your
> left.
> >>
> >>
> >> it's more specific than "left" and "right", because if you have:
> >>
> >> Node.relationship(remote_side=something) -> Node
> >>
> >> the "remote_side" refers specifically to the columns that are not
> >> local to the Node that has the .relationship.
> >>
> >> That is:
> >>
> >> Node.col1
> >> Node.col2
> >> Node.relationship:
> >>   name='foo'
> >>   local_side=Node.col1
> >>   remote_side=Node.col2
> >>
> >>
> >> You then know that:
> >>
> >> query(Node).join(Node.foo, aliased=True)
> >>
> >> will emit the equivalent of:
> >>
> >> SELECT * FROM node JOIN node AS node_remote ON node.col1 =
> >> node_remote.col2
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> I'm
> >> > not really confident in this but I would guess chaing the name from
> >> > remote_side to many_side may help? Correct me if I'm wrong here.
> >> >
> >> > --
> >> > SQLAlchemy -
> >> > The Python SQL Toolkit and Object Relational Mapper
> >> >
> >> > http://www.sqlalchemy.org/
> >> >
> >> > To post example code, please provide an MCVE: Minimal, Complete, and
> >> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> >> > description.
> >> > ---
> >> > You received this message because you are subscribed to the Google
> >> > Groups
> >> > "sqlalchemy" group.
> >> > To unsubscribe from this group and stop receiving emails from it, send
> >> > an
> >> > email to sqlalchemy+unsubscr...@googlegroups.com.
> >> > To post to this group, send email to sqlalchemy@googlegroups.com.
> >> > Visit this group at https://groups.google.com/group/sqlalchemy.
> >> > For more options, visit https://groups.google.com/d/optout.
> >>
> >> --
> >> SQLAlchemy -
> >> The Python SQL Toolkit and Object Relational Mapper
> >>
> >> http://www.sqlalchemy.org/
> >>
> >> To post example code, please provide an MCVE: Minimal, Complete, and
> >> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> >> description.
> >> ---
> >> You received this message because you are subscribed to a topic in the
> >> Google Groups "sqlalchemy" group.
> >> To unsubscribe from this topic, visit
> >> https://groups.google.com/d/topic/sqlalchemy/e68eFOoG3LQ/unsubscribe.
> >> To unsubscribe from this group and all its topics, send an email to
> >> sqlalchemy+unsubscr...@googlegroups.com.
&

Re: [sqlalchemy] How to better understand `remote_side` in sqlalchemy?

[Jinghui Niu]

I see. But still I'm struggling to see the real difference between:

Node.parent_id = Node.id
vs.
Node.id = Node.parent_id

Aren't we just switching around sides here?




On Thu, Jul 20, 2017 at 4:47 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:

> On Thu, Jul 20, 2017 at 1:34 PM, Jinghui Niu <niujing...@gmail.com> wrote:
> > I've been studying sqlalchemy's self referential table. I've read the
> > documentation many times and still have difficulties understanding the
> > concept of remote_side. Could someone please draw a diagram or use an
> > analogy to help explain this concept? I think visualizing is a better way
> > but anything helps would be appreciated. Thanks.
> >
> >
> > By the way, in my opinion the word remote is a little vague, as it can be
> > interpreted from different angles. Like the word left and rigt, it really
> > depends on which direction you are facing, my right could be your left.
>
>
> it's more specific than "left" and "right", because if you have:
>
> Node.relationship(remote_side=something) -> Node
>
> the "remote_side" refers specifically to the columns that are not
> local to the Node that has the .relationship.
>
> That is:
>
> Node.col1
> Node.col2
> Node.relationship:
>   name='foo'
>   local_side=Node.col1
>   remote_side=Node.col2
>
>
> You then know that:
>
> query(Node).join(Node.foo, aliased=True)
>
> will emit the equivalent of:
>
> SELECT * FROM node JOIN node AS node_remote ON node.col1 = node_remote.col2
>
>
>
>
>
>
>
> I'm
> > not really confident in this but I would guess chaing the name from
> > remote_side to many_side may help? Correct me if I'm wrong here.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send an
> > email to sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/e68eFOoG3LQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] How to better understand `remote_side` in sqlalchemy?

[Jinghui Niu]

I've been studying sqlalchemy's self referential table. I've read the 
documentation many times and still have difficulties understanding the 
concept of remote_side. Could someone please draw a diagram or use an 
analogy to help explain this concept? I think visualizing is a better way 
but anything helps would be appreciated. Thanks.


By the way, in my opinion the word remote is a little vague, as it can be 
interpreted from different angles. Like the word left and rigt, it really 
depends on which direction you are facing, my right could be your left. I'm 
not really confident in this but I would guess chaing the name from 
remote_side to many_side may help? Correct me if I'm wrong here.

-- 
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: What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

I just tried a revised version of the cache consumer method as follows:


def Record_entries_count(self):
# import pdb; pdb.set_trace()
db = cherrypy.request.db_session
query_subset = db.query(MyClass).merge_result(self.
search_result_cache)
result = query_subset.count()
return result

However, this gives me the following error:

AttributeError: 'list_iterator' object has no attribute 'count'


Why does `merge_result` return a list_iterator instead of the Query object 
itself? How can I perform a .count() method in this case? Thanks.



On Thursday, July 13, 2017 at 4:38:19 PM UTC-7, Jinghui Niu wrote:
>
> I have a web application served by cherrypy (, which is multi-threaded. ) 
>
> I'm trying to cache a set of rows queried from database using 
> `self.search_result_cache` variable on the GUI_Server object. On my 
> front-end, the web first request `list_entries` to prepare the rows and 
> stores them on `self.search_result_cache`. After that, on user's mouse 
> click the front-end initiats another request calling 
> `Record_entries_count`, which is expected to revive the Query from 
> `self.search_result_cache` and continue on to do some data refining, e.g. 
> summing up the count in this case.
>
> class GUI_Server:
>
>
> def __init__(self):
> self.search_result_cache = None
>
>
> @cherrypy.expose
> def list_entries(self, **criteriaDICT):
> # always store the result to self cache
> 
> ...
>
>
> db = cherrypy.request.db_session
>
>
> filter_func = getattr(self, 'filterCriteria_' + classmodel_obj.
> __name__)
> queryOBJ = filter_func(criteriaDICT, queryOBJ)
> self.search_result_cache = queryOBJ
> db.expunge_all()
>
> 
>
> def Record_entries_count(self):
> db = cherrypy.request.db_session
> query_subset = self.search_result_cache
> result = query_subset.count()
> return result
>
>
> But this doesn't work. It always give me an error:
>
> sqlite3.ProgrammingError: SQLite objects created in a thread can only be used 
> in that same thread.The object was created in thread id 139937752020736 and 
> this is thread id 139938238535424 
>
> I am already using `scoped_session` for each request session. I don't 
> understand why I got this error.
>
>
> What is the best pratice to cache queried result across different request 
> sessions like this? Thanks a lot.
>
>

-- 
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] What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

Hi Mike, I've read the example of dogpile caching. For my case
dogpile.cache seems to be a overkill, could you please provide a thinner
example of using Query.merge_result without involving another library?
Thanks.

On Thu, Jul 13, 2017 at 8:07 PM, Jinghui Niu <niujing...@gmail.com> wrote:

> Thanks Mike. Just to clarify, so instead of caching a Query object, I
> should cache all those queried instance in my `self.search_result_cache`,
> is this the idea? Is there a way to just relay Query object from one method
> to another? Which seems a little simpler to me.
>
> On Thu, Jul 13, 2017 at 7:20 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
>
>> you need to use Session.merge and/or Query.merge_result so that a
>> *copy* of the detached object is placed into the Session.In
>> particular, Query.merge_result was created for the use case of caching
>> result sets.
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html?highligh
>> t=merge_result#sqlalchemy.orm.query.Query.merge_result
>>
>>
>> this links to the dogpile cache example at
>> http://docs.sqlalchemy.org/en/latest/orm/examples.html#modul
>> e-examples.dogpile_caching
>> illustrates a method that I've used in production successfully.
>>
>>
>>
>>
>>
>> On Thu, Jul 13, 2017 at 7:38 PM, Jinghui Niu <niujing...@gmail.com>
>> wrote:
>> > I have a web application served by cherrypy (, which is multi-threaded.
>> )
>> >
>> > I'm trying to cache a set of rows queried from database using
>> > `self.search_result_cache` variable on the GUI_Server object. On my
>> > front-end, the web first request `list_entries` to prepare the rows and
>> > stores them on `self.search_result_cache`. After that, on user's mouse
>> click
>> > the front-end initiats another request calling `Record_entries_count`,
>> which
>> > is expected to revive the Query from `self.search_result_cache` and
>> continue
>> > on to do some data refining, e.g. summing up the count in this case.
>> >
>> > class GUI_Server:
>> >
>> >
>> > def __init__(self):
>> > self.search_result_cache = None
>> >
>> >
>> > @cherrypy.expose
>> > def list_entries(self, **criteriaDICT):
>> > # always store the result to self cache
>> >
>> > ...
>> >
>> >
>> > db = cherrypy.request.db_session
>> >
>> >
>> > filter_func = getattr(self, 'filterCriteria_' +
>> > classmodel_obj.__name__)
>> > queryOBJ = filter_func(criteriaDICT, queryOBJ)
>> > self.search_result_cache = queryOBJ
>> > db.expunge_all()
>> >
>> > 
>> >
>> > def Record_entries_count(self):
>> > db = cherrypy.request.db_session
>> > query_subset = self.search_result_cache
>> > result = query_subset.count()
>> > return result
>> >
>> >
>> > But this doesn't work. It always give me an error:
>> >
>> > sqlite3.ProgrammingError: SQLite objects created in a thread can only be
>> > used in that same thread.The object was created in thread id
>> 139937752020736
>> > and this is thread id 139938238535424
>> >
>> >
>> > I am already using `scoped_session` for each request session. I don't
>> > understand why I got this error.
>> >
>> >
>> > What is the best pratice to cache queried result across different
>> request
>> > sessions like this? Thanks a lot.
>> >
>> > --
>> > SQLAlchemy -
>> > The Python SQL Toolkit and Object Relational Mapper
>> >
>> > http://www.sqlalchemy.org/
>> >
>> > To post example code, please provide an MCVE: Minimal, Complete, and
>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> > description.
>> > ---
>> > You received this message because you are subscribed to the Google
>> Groups
>> > "sqlalchemy" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> an
>> > email to sqlalchemy+unsubscr...@googlegroups.com.
>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Objec

Re: [sqlalchemy] What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

Thanks Mike. Just to clarify, so instead of caching a Query object, I
should cache all those queried instance in my `self.search_result_cache`,
is this the idea? Is there a way to just relay Query object from one method
to another? Which seems a little simpler to me.

On Thu, Jul 13, 2017 at 7:20 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:

> you need to use Session.merge and/or Query.merge_result so that a
> *copy* of the detached object is placed into the Session.In
> particular, Query.merge_result was created for the use case of caching
> result sets.
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html?
> highlight=merge_result#sqlalchemy.orm.query.Query.merge_result
>
>
> this links to the dogpile cache example at
> http://docs.sqlalchemy.org/en/latest/orm/examples.html#
> module-examples.dogpile_caching
> illustrates a method that I've used in production successfully.
>
>
>
>
>
> On Thu, Jul 13, 2017 at 7:38 PM, Jinghui Niu <niujing...@gmail.com> wrote:
> > I have a web application served by cherrypy (, which is multi-threaded. )
> >
> > I'm trying to cache a set of rows queried from database using
> > `self.search_result_cache` variable on the GUI_Server object. On my
> > front-end, the web first request `list_entries` to prepare the rows and
> > stores them on `self.search_result_cache`. After that, on user's mouse
> click
> > the front-end initiats another request calling `Record_entries_count`,
> which
> > is expected to revive the Query from `self.search_result_cache` and
> continue
> > on to do some data refining, e.g. summing up the count in this case.
> >
> > class GUI_Server:
> >
> >
> > def __init__(self):
> > self.search_result_cache = None
> >
> >
> > @cherrypy.expose
> > def list_entries(self, **criteriaDICT):
> > # always store the result to self cache
> >
> > ...
> >
> >
> > db = cherrypy.request.db_session
> >
> >
> > filter_func = getattr(self, 'filterCriteria_' +
> > classmodel_obj.__name__)
> > queryOBJ = filter_func(criteriaDICT, queryOBJ)
> > self.search_result_cache = queryOBJ
> > db.expunge_all()
> >
> > 
> >
> > def Record_entries_count(self):
> > db = cherrypy.request.db_session
> > query_subset = self.search_result_cache
> > result = query_subset.count()
> > return result
> >
> >
> > But this doesn't work. It always give me an error:
> >
> > sqlite3.ProgrammingError: SQLite objects created in a thread can only be
> > used in that same thread.The object was created in thread id
> 139937752020736
> > and this is thread id 139938238535424
> >
> >
> > I am already using `scoped_session` for each request session. I don't
> > understand why I got this error.
> >
> >
> > What is the best pratice to cache queried result across different request
> > sessions like this? Thanks a lot.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send an
> > email to sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/CWvLr8GPra4/unsubscribe.
> To unsubscribe from this group and all its topics, 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://gr

[sqlalchemy] What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

I have a web application served by cherrypy (, which is multi-threaded. ) 

I'm trying to cache a set of rows queried from database using 
`self.search_result_cache` variable on the GUI_Server object. On my 
front-end, the web first request `list_entries` to prepare the rows and 
stores them on `self.search_result_cache`. After that, on user's mouse 
click the front-end initiats another request calling 
`Record_entries_count`, which is expected to revive the Query from 
`self.search_result_cache` and continue on to do some data refining, e.g. 
summing up the count in this case.

class GUI_Server:


def __init__(self):
self.search_result_cache = None


@cherrypy.expose
def list_entries(self, **criteriaDICT):
# always store the result to self cache

...


db = cherrypy.request.db_session


filter_func = getattr(self, 'filterCriteria_' + classmodel_obj.
__name__)
queryOBJ = filter_func(criteriaDICT, queryOBJ)
self.search_result_cache = queryOBJ
db.expunge_all()



def Record_entries_count(self):
db = cherrypy.request.db_session
query_subset = self.search_result_cache
result = query_subset.count()
return result


But this doesn't work. It always give me an error:

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used 
in that same thread.The object was created in thread id 139937752020736 and 
this is thread id 139938238535424 

I am already using `scoped_session` for each request session. I don't 
understand why I got this error.


What is the best pratice to cache queried result across different request 
sessions like this? Thanks a lot.

-- 
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 query an optional column on a self referential adjacency list table?

[Jinghui Niu]

Do I need to turn on or do some configuration with SQLAlchemy before using
CTE? Like foreign keys

On Sun, Oct 16, 2016 at 10:41 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:

>
> oh I was wrong, SQLite has it:
>
> https://www.sqlite.org/lang_with.html
>
>
> that leaves just MySQL out.
>
> On 10/16/2016 01:21 PM, Jinghui Niu wrote:
>
>> Thanks Mike.
>>
>> On Sun, Oct 16, 2016 at 7:14 AM, Mike Bayer <mike...@zzzcomputing.com
>> <mailto:mike...@zzzcomputing.com>> wrote:
>>
>>
>>
>> On 10/14/2016 06:08 PM, Jinghui Niu wrote:
>>
>> I have the following Table model representing a timeline.
>>
>> |
>> classTimeRange(Base):
>>
>>
>> __tablename__ ="time_line"
>>
>>
>> record_id =Column(Integer,primary_key=True)
>> level =Column(String,nullable=False)# e.g. "Point", "Range"
>> content =Column(String,nullable=False)
>> language_marker =Column(String)# this one column is optional
>> and
>> needs to be queried
>> immediate_parent_id
>> =Column(Integer,ForeignKey('time_line.record_id'))
>> child_timelines
>> =relationship('TimeRange',backref=backref('parent_timeline',
>> remote_side=[record_id]))
>> |
>>
>>
>> The language_marker Column is the one that needs to be queried in
>> a
>> recursive manner. Not all records have such an attribute, and the
>> business logic is: along the hierarchy lineage from the root
>> down to the
>> child timelines, at least one level of the TimeRange instance
>> carries
>> such an attribute, and the one in the lowest level should be
>> returned.
>> This works a little like cascading style sheet, where if the
>> TimeRange
>> object itself doesn't have such an attribute, just look further
>> up one
>> level above, util found one, and the latest defined style wins.
>>
>> What is the technical direction I should look into to implement
>> such
>> queries? I'm using SQLAlchemy and the backend is SQLite. Thanks.
>>
>>
>> Someone else can probably work out the details on this one, but the
>> general technique on the SQL side is to use a recursive query.  With
>> Postgresql / SQL Server this is a CTE using WITH RECURSIVE, with
>> Oracle I *think* they support this syntax also now though
>> historically it's been "CONNECT BY", and then with any other DB like
>> MySQL / SQlite it's basically nothing.
>>
>> The other technique, which I tend to prefer if it can be made
>> feasible, is that if I'm working with overall a limited number of
>> rows in the first place, such as all of these records where
>> language_marker may be significant all belong to some common
>> "document id" or something where there are only a few hundred or a
>> few thousand rows that would matter for the whole operation I'm
>> doing, I pull it into memory and assemble it into a tree hierarchy
>> right there.
>>
>>
>>
>> --
>> 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
>> <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
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>
>> <mailto:sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>>.
>> Visit this group at https://groups.google.com/group/sqlalchemy
>> <https://groups.google.com/group/

Re: [sqlalchemy] How to query an optional column on a self referential adjacency list table?

[Jinghui Niu]

Thanks Mike.

On Sun, Oct 16, 2016 at 7:14 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:

>
>
> On 10/14/2016 06:08 PM, Jinghui Niu wrote:
>
>> I have the following Table model representing a timeline.
>>
>> |
>> classTimeRange(Base):
>>
>>
>> __tablename__ ="time_line"
>>
>>
>> record_id =Column(Integer,primary_key=True)
>> level =Column(String,nullable=False)# e.g. "Point", "Range"
>> content =Column(String,nullable=False)
>> language_marker =Column(String)# this one column is optional and
>> needs to be queried
>> immediate_parent_id =Column(Integer,ForeignKey('ti
>> me_line.record_id'))
>> child_timelines
>> =relationship('TimeRange',backref=backref('parent_timeline',
>> remote_side=[record_id]))
>> |
>>
>>
>> The language_marker Column is the one that needs to be queried in a
>> recursive manner. Not all records have such an attribute, and the
>> business logic is: along the hierarchy lineage from the root down to the
>> child timelines, at least one level of the TimeRange instance carries
>> such an attribute, and the one in the lowest level should be returned.
>> This works a little like cascading style sheet, where if the TimeRange
>> object itself doesn't have such an attribute, just look further up one
>> level above, util found one, and the latest defined style wins.
>>
>> What is the technical direction I should look into to implement such
>> queries? I'm using SQLAlchemy and the backend is SQLite. Thanks.
>>
>
> Someone else can probably work out the details on this one, but the
> general technique on the SQL side is to use a recursive query.  With
> Postgresql / SQL Server this is a CTE using WITH RECURSIVE, with Oracle I
> *think* they support this syntax also now though historically it's been
> "CONNECT BY", and then with any other DB like MySQL / SQlite it's basically
> nothing.
>
> The other technique, which I tend to prefer if it can be made feasible, is
> that if I'm working with overall a limited number of rows in the first
> place, such as all of these records where language_marker may be
> significant all belong to some common "document id" or something where
> there are only a few hundred or a few thousand rows that would matter for
> the whole operation I'm doing, I pull it into memory and assemble it into a
> tree hierarchy right there.
>
>
>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> description.
> --- You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/to
> pic/sqlalchemy/uUhcft4S7-E/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to query an optional column on a self referential adjacency list table?

[Jinghui Niu]

I'm thinking of JOIN with the parents, but failed. Because joining 
indefinite levels seems to be very hard to implement. You don't know how 
many levels you need to look up until you find a parent with a non Null 
value for that optional column. How to approach this problem?

On Friday, October 14, 2016 at 3:08:32 PM UTC-7, Jinghui Niu wrote:
>
> I have the following Table model representing a timeline.
>
> class TimeRange(Base):
>
>
> __tablename__ = "time_line"
>
>
> record_id = Column(Integer, primary_key=True)
> level = Column(String, nullable=False) # e.g. "Point", "Range"
> content = Column(String, nullable=False)
> language_marker = Column(String) # this one column is optional and 
> needs to be queried
> immediate_parent_id = Column(Integer, ForeignKey('time_line.record_id'
> ))
> child_timelines = relationship('TimeRange', backref=backref(
> 'parent_timeline', remote_side=[record_id]))
>
>
> The language_marker Column is the one that needs to be queried in a 
> recursive manner. Not all records have such an attribute, and the business 
> logic is: along the hierarchy lineage from the root down to the child 
> timelines, at least one level of the TimeRange instance carries such an 
> attribute, and the one in the lowest level should be returned. This works a 
> little like cascading style sheet, where if the TimeRange object itself 
> doesn't have such an attribute, just look further up one level above, util 
> found one, and the latest defined style wins.
>
> What is the technical direction I should look into to implement such 
> queries? I'm using SQLAlchemy and the backend is SQLite. Thanks.
>
>

-- 
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] How to query an optional column on a self referential adjacency list table?

[Jinghui Niu]

I have the following Table model representing a timeline.

class TimeRange(Base):


__tablename__ = "time_line"


record_id = Column(Integer, primary_key=True)
level = Column(String, nullable=False) # e.g. "Point", "Range"
content = Column(String, nullable=False)
language_marker = Column(String) # this one column is optional and 
needs to be queried
immediate_parent_id = Column(Integer, ForeignKey('time_line.record_id'))
child_timelines = relationship('TimeRange', backref=backref(
'parent_timeline', remote_side=[record_id]))


The language_marker Column is the one that needs to be queried in a 
recursive manner. Not all records have such an attribute, and the business 
logic is: along the hierarchy lineage from the root down to the child 
timelines, at least one level of the TimeRange instance carries such an 
attribute, and the one in the lowest level should be returned. This works a 
little like cascading style sheet, where if the TimeRange object itself 
doesn't have such an attribute, just look further up one level above, util 
found one, and the latest defined style wins.

What is the technical direction I should look into to implement such 
queries? I'm using SQLAlchemy and the backend is SQLite. Thanks.

-- 
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 implement SQL level expression for this hybrid property?

[Jinghui Niu]

This really helps. Thank you Simon! I still have a couple of smaller
questions.

When you access .fullname, the "self" parameter is now the
> *class*, so self.firstname and self.lastname are SQLAlchemy column
> properties.


Here by *column properties* do you mean the object returned by
column_property() function? Are they used interchangeably with
*InstrumentedAttribute object*?


but you'll need to accept that you can't simply use your
> currency_exchange_rate_lookup
> dictionary as it is.


I have a dream, that one day SQL side and Python side can truly mingle in
such a way that when you query SQL you can directly refer to variables
defined in the Python model class:-)



I will read on the sql expression part on the docs. I've finished reading
Essential Sqlalchemy 2nd edition, but there seems to be a big gap between
beginner level and pro level in terms of available reading materials.
Really looking forward to some new books releasing soon.

Thanks Simon, cheers!

On Mon, Oct 3, 2016 at 2:32 AM, Simon King <si...@simonking.org.uk> wrote:

> The first example from the docs is illustrating the most simple case,
> where the function happens to work at both the instance and class
> level. Here's the example:
>
> class User(Base):
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
> firstname = Column(String(50))
> lastname = Column(String(50))
>
> @hybrid_property
> def fullname(self):
> return self.firstname + " " + self.lastname
>
> When you access .fullname, the "self" parameter refers to
> the instance, so self.firstname and self.lastname are plain python
> strings.
>
> When you access .fullname, the "self" parameter is now the
> *class*, so self.firstname and self.lastname are SQLAlchemy column
> properties. Since SA implements the "+" operator for those properties,
> the result of the expression is an SQL expression. When you write
> "User.fullname == 'Jinghui Niu", that becomes an SQL expression
> looking something like:
>
> (user.firstname || ' ' || user.lastname) == 'Jinghui Niu'
>
> ...except that it will use bind parameters for the string literals,
> and the database-appropriate string concatenation operators.
>
> It wouldn't make sense to write "User.__table__.c.firstname" in this
> example, because that wouldn't work in the *instance* case. However,
> if you split the implementations, so that you have one function for
> the instance case, and a separate function for the class case (via
> hybrid_property.expression), there's no reason you couldn't use
> User.__table__.c.firstname in the class case. You *usually* don't need
> to, since the ORM-level property User.firstname can be used in most of
> the same places as User.__table__.c.firstname.
>
> In other words, this *should* work (but I haven't tried it):
>
> @hybrid_property
> def fullname(self):
> return self.firstname + " " + self.lastname
>
> @fullname.expression
> def fullname(cls):
> return cls.__table__.c.firstname + " " + cls.__table__.c.lastname
>
> ...but it is redundant, because you can use "cls.firstname" instead of
> "cls.__table__.c.firstname", and once you've done that the, the
> implementation is exactly the same as the instance-level version and
> so you can just skip the @fullname.expression definition altogether.
>
> In answer to your other question, I don't know exactly how to
> implement the SQL expression part of your property, but you'll need to
> accept that you can't simply use your currency_exchange_rate_lookup
> dictionary as it is. Perhaps if you could give an example of a query
> you'd like to write using this property, and the sort of SQL you'd
> expect to see generated, we might be able to help with the
> implementation.
>
> Cheers,
>
> Simon
>
>
> On Mon, Oct 3, 2016 at 9:51 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> >> User.firstname is not the value from any particular row - it's the
> >> (ORM-level) column object.
> >
> >
> > It's a little abstruse here that a ORM-level instrumented column object,
> > such as User.firstname works, but a true Column object, such as
> > User.__table__.c.firstname doesn't. Maybe I misunderstood your comment
> here?
> >
> > On Mon, Oct 3, 2016 at 1:40 AM, Jinghui Niu <niujing...@gmail.com>
> wrote:
> >>
> >> Thank you Simon. Your explanation helps me understand this quite a lot.
> >> Sometimes the documentation is so terse that only when you fully
> understand
> >> the subject then you can understand it by reading it:) But still if I
> want
> &g

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

>
> User.firstname is not the value from any particular row - it's the
> (ORM-level) column object.


It's a little abstruse here that a ORM-level instrumented column object,
such as User.firstname works, but a true Column object, such as
User.__table__.c.firstname doesn't. Maybe I misunderstood your comment here?

On Mon, Oct 3, 2016 at 1:40 AM, Jinghui Niu <niujing...@gmail.com> wrote:

> Thank you Simon. Your explanation helps me understand this quite a lot.
> Sometimes the documentation is so terse that only when you fully understand
> the subject then you can understand it by reading it:) But still if I want
> to implement this hybrid property from the query level, how would you
> suggest modify my current code? Or maybe you could please point out a link
> to where I can explore further on the python to SQL transition? Thank you
> so much.
>
> Jinghui
>
> On Mon, Oct 3, 2016 at 1:27 AM, Simon King <si...@simonking.org.uk> wrote:
>
>> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu <niujing...@gmail.com> wrote:
>> > I have a ledger table and a corresponding python class. I defined the
>> model
>> > using SQLAlchemy, as follows,
>> >
>> > class Ledger(Base):
>> > __tablename__ = 'ledger'
>> >
>> > currency_exchange_rate_lookup = {('CNY', 'CAD'): 0.2}
>> >
>> > amount = Column(Numeric(10, 2), nullable=False)
>> > currency = Column(String, nullable=False)
>> > payment_method = Column(String)
>> > notes = Column(UnicodeText)
>> >
>> > @hybrid_property
>> > def amountInCAD(self):
>> > if self.currency == 'CAD':
>> > return self.amount
>> > exchange_rate = self.currency_exchange_rate_lo
>> okup[(self.currency,
>> > 'CAD')]
>> > CAD_value = self.amount * Decimal(exchange_rate)
>> > CAD_value = round(CAD_value, 2)
>> > return CAD_value
>> >
>> > @amountInCAD.expression
>> > def amountInCAD(cls):
>> > amount = cls.__table__.c.amount
>> > currency_name = cls.__table__.c.currency
>> > exchange_rate = cls.currency_exchange_rate_loo
>> kup[(currency_name,
>> > 'CAD')]
>> > return case([
>> > (cls.currency == 'CAD', amount),
>> > ], else_ = round((amount * Decimal(exchange_rate)),2))
>> >
>> > Now as you can see, I want to create a hybrid property called
>> "amountInCAD".
>> > The Python level getter seems to be working fine. However the SQL
>> expression
>> > doesn't work.
>> >
>> > Now if I run a query like this:
>> >
>> >>>>db_session.query(Ledger).filter(Ledger.amountInCAD > 1000)
>> >
>> > SQLAlchemy gives me this error:
>> >
>> >   File "ledger_db.py", line 43, in amountInCAD
>> > exchange_rate = cls.currency_exchange_rate_lookup[(currency_name,
>> > 'CAD')]
>> > KeyError: (Column('currency', String(), table=, nullable=False),
>> > 'CAD')
>> >
>> > I've researched SQLAlchemy's online documentation regarding hybrid
>> > property.http://docs.sqlalchemy.org/en/latest/orm/mapped_
>> sql_expr.html#using-a-hybrid
>> > Comparing my code to the example code, I don't understand why mine
>> doesn't
>> > work. If in the official example, cls.firstname can refer to a column of
>> > value, why in my code the cls.__table__.c.currencyonly returns a Column
>> not
>> > its value?
>>
>> Forget about using this in a query for a second. For example, open a
>> python shell, import your class, and type "Ledger.amountInCAD". This
>> will trigger the same exception. There's no way that
>> cls.__table__.c.currency can *ever* return a value from a specific
>> row, because you are accessing it from the *class*, which isn't
>> related to any specific row.
>>
>> I assume the example in the docs you are referring to is this one:
>>
>> @hybrid_property
>> def fullname(self):
>> return self.firstname + " " + self.lastname
>>
>> In this example, "User.fullname" is precisely equivalent to:
>>
>>   User.firstname + " " + User.lastname
>>
>> User.firstname is not the value from any particular row - it's the
>> (ORM-level) column object. The result of that expression is another
>> SQL expression.
>>
>> Sorry, that's probably not a very good explanation. Has it made it any
>> c

Re: [sqlalchemy] How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

Thank you Simon. Your explanation helps me understand this quite a lot.
Sometimes the documentation is so terse that only when you fully understand
the subject then you can understand it by reading it:) But still if I want
to implement this hybrid property from the query level, how would you
suggest modify my current code? Or maybe you could please point out a link
to where I can explore further on the python to SQL transition? Thank you
so much.

Jinghui

On Mon, Oct 3, 2016 at 1:27 AM, Simon King <si...@simonking.org.uk> wrote:

> On Mon, Oct 3, 2016 at 7:17 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> > I have a ledger table and a corresponding python class. I defined the
> model
> > using SQLAlchemy, as follows,
> >
> > class Ledger(Base):
> > __tablename__ = 'ledger'
> >
> > currency_exchange_rate_lookup = {('CNY', 'CAD'): 0.2}
> >
> > amount = Column(Numeric(10, 2), nullable=False)
> > currency = Column(String, nullable=False)
> > payment_method = Column(String)
> > notes = Column(UnicodeText)
> >
> > @hybrid_property
> > def amountInCAD(self):
> > if self.currency == 'CAD':
> > return self.amount
> > exchange_rate = self.currency_exchange_rate_
> lookup[(self.currency,
> > 'CAD')]
> > CAD_value = self.amount * Decimal(exchange_rate)
> > CAD_value = round(CAD_value, 2)
> > return CAD_value
> >
> > @amountInCAD.expression
> > def amountInCAD(cls):
> > amount = cls.__table__.c.amount
> > currency_name = cls.__table__.c.currency
> > exchange_rate = cls.currency_exchange_rate_
> lookup[(currency_name,
> > 'CAD')]
> > return case([
> > (cls.currency == 'CAD', amount),
> > ], else_ = round((amount * Decimal(exchange_rate)),2))
> >
> > Now as you can see, I want to create a hybrid property called
> "amountInCAD".
> > The Python level getter seems to be working fine. However the SQL
> expression
> > doesn't work.
> >
> > Now if I run a query like this:
> >
> >>>>db_session.query(Ledger).filter(Ledger.amountInCAD > 1000)
> >
> > SQLAlchemy gives me this error:
> >
> >   File "ledger_db.py", line 43, in amountInCAD
> > exchange_rate = cls.currency_exchange_rate_lookup[(currency_name,
> > 'CAD')]
> > KeyError: (Column('currency', String(), table=, nullable=False),
> > 'CAD')
> >
> > I've researched SQLAlchemy's online documentation regarding hybrid
> > property.http://docs.sqlalchemy.org/en/latest/orm/
> mapped_sql_expr.html#using-a-hybrid
> > Comparing my code to the example code, I don't understand why mine
> doesn't
> > work. If in the official example, cls.firstname can refer to a column of
> > value, why in my code the cls.__table__.c.currencyonly returns a Column
> not
> > its value?
>
> Forget about using this in a query for a second. For example, open a
> python shell, import your class, and type "Ledger.amountInCAD". This
> will trigger the same exception. There's no way that
> cls.__table__.c.currency can *ever* return a value from a specific
> row, because you are accessing it from the *class*, which isn't
> related to any specific row.
>
> I assume the example in the docs you are referring to is this one:
>
> @hybrid_property
> def fullname(self):
> return self.firstname + " " + self.lastname
>
> In this example, "User.fullname" is precisely equivalent to:
>
>   User.firstname + " " + User.lastname
>
> User.firstname is not the value from any particular row - it's the
> (ORM-level) column object. The result of that expression is another
> SQL expression.
>
> Sorry, that's probably not a very good explanation. Has it made it any
> clearer?
>
> Simon
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/7AsxiTT3Dtc/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>

-- 
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] How to implement SQL level expression for this hybrid property?

[Jinghui Niu]

I have a ledger table and a corresponding python class. I defined the model 
using SQLAlchemy, as follows,

class Ledger(Base):
__tablename__ = 'ledger'

currency_exchange_rate_lookup = {('CNY', 'CAD'): 0.2}

amount = Column(Numeric(10, 2), nullable=False)
currency = Column(String, nullable=False)
payment_method = Column(String)
notes = Column(UnicodeText)

@hybrid_property
def amountInCAD(self):
if self.currency == 'CAD':
return self.amount
exchange_rate = self.currency_exchange_rate_lookup[(self.currency, 
'CAD')]
CAD_value = self.amount * Decimal(exchange_rate)
CAD_value = round(CAD_value, 2)
return CAD_value

@amountInCAD.expression
def amountInCAD(cls):
amount = cls.__table__.c.amount
currency_name = cls.__table__.c.currency
exchange_rate = cls.currency_exchange_rate_lookup[(currency_name, 
'CAD')]
return case([
(cls.currency == 'CAD', amount),
], else_ = round((amount * Decimal(exchange_rate)),2))

Now as you can see, I want to create a hybrid property called 
"amountInCAD". The Python level getter seems to be working fine. However 
the SQL expression doesn't work.

Now if I run a query like this:

>>>db_session.query(Ledger).filter(Ledger.amountInCAD > 1000)

SQLAlchemy gives me this error:

  File "ledger_db.py", line 43, in amountInCAD
exchange_rate = cls.currency_exchange_rate_lookup[(currency_name, 'CAD')]
KeyError: (Column('currency', String(), table=, nullable=False), 'CAD')

I've researched SQLAlchemy's online documentation regarding hybrid property.
http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-a-hybrid 
Comparing 
my code to the example code, I don't understand why mine doesn't work. If 
in the official example, cls.firstname can refer to a column of value, why 
in my code the cls.__table__.c.currencyonly returns a Column not its value?

-- 
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] How can I programmatically give a hybrid_property its name?

[Jinghui Niu]

The documentation shows that hybrid_property should used as a decorator, 
like:
@hybrid_property
def my_property(self):
pass


What if I wanted to give this hybrid property a name by referring a 
variable in runtime? Is this allowed? Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] How can I duplicate mixin columns?

[Jinghui Niu]

For example, I have a Mixin as follow:

class MyNoteMixin:
note = Column(String)


Now I have a subclass that inherit from the above Mixin, but needs two 
different columns both are of a note nature. Can I do something like:

class Child(Base, MyNoteMixin as "Description", MyNoteMixin as "Quote"):
pass


So that the resolved table would contain a column named Description that is 
exactly a MyNoteMixin copy except for the name, AND also has another column 
named Quote with the same nature.

Is this possible with SQLAlchemy? Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQL expression for function (and/or method) dispatching?

[Jinghui Niu]

>
> If you still want to store it as a string, I guess you'll need to try
> parsing it as a datetime and then fall back to parsing it as a date.


Exactly! That's my intention. I'm so excited that my idea has affirmed by a
pro now:)

You haven't said what database you are using


SQLite in Python 3.

But I'm still more inclined towards storing a long string of datetime/date
into a column, plus a suffix column indicating whether it's a date, a
datetime, or in future a time range. Is there a way to do this at SQL
expression level using hybrid_property?

I'm definitely going to check out TypeDecorator. It's another new thing but
sounds very interesting. Thanks for mentioning!

On Fri, Sep 16, 2016 at 2:28 AM, Simon King <si...@simonking.org.uk> wrote:

> Another option would be to have separate columns for date and time,
> and leave the time column NULL when it's not present.
>
> If you still want to store it as a string, I guess you'll need to try
> parsing it as a datetime and then fall back to parsing it as a date.
> You haven't said what database you are using, but for MySQL it might
> be something like this:
>
> IFNULL(STR_TO_DATE(timepoint, ),
> STR_TO_DATE(timepoint, ))
>
> If you're not actually going to filter on these columns, I don't think
> I'd do any of this at all. Instead, I'd use something like a
> TypeDecorator to convert the values when loading from and saving to
> the database.
>
> Simon
>
>
> On Fri, Sep 16, 2016 at 9:53 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> > If I store the as DateTime values and with a second column to indicate
> > whether it's a date or datetime, it would look like this for a Date:
> > col1: "2016-09-16 00:00:00", col2: "date only"
> >
> > It looks so messy to me:)
> >
> > I'd prefer to have Date and DateTime distinctively written in my
> database. I
> > admit that it's one of my personal preference though:)
> >
> > On Fri, Sep 16, 2016 at 1:49 AM, Jinghui Niu <niujing...@gmail.com>
> wrote:
> >>
> >> Thanks for reply.
> >>
> >> The reason is simple. I plan in the future to accommodate datetime range
> >> into that column as well, so storing this logic as plain strings gives
> the
> >> most flexibility. This is a project that I'm learning by making. So I
> would
> >> like to try all the new features for later tasks.
> >>
> >> I'm fascinated by hybrid_property, but is stuck now when trying to
> >> dispatch functions at the SQL expression level.
> >>
> >> Jinghui
> >>
> >> On Fri, Sep 16, 2016 at 1:34 AM, Simon King <si...@simonking.org.uk>
> >> wrote:
> >>>
> >>> Ignore SQLAlchemy for the moment and describe what you are trying to
> >>> achieve. It looks like you want to store dates and times as strings in
> >>> your database (rather than the appropriate type), and yet still be
> >>> able to perform date-related operations on them qhen querying. Is that
> >>> right? Is there a reason why you can't store them as proper DateTime
> >>> values (perhaps with a second column to indicate whether or not the
> >>> time part is valid)?
> >>>
> >>> Simon
> >>>
> >>> On Fri, Sep 16, 2016 at 4:39 AM, Jinghui Niu <niujing...@gmail.com>
> >>> wrote:
> >>> > I have the following code snippet, I marked my question in a comment
> >>> > line
> >>> > inside the hybrid_property.expression part. As you can see, it is now
> >>> > not
> >>> > implemented:
> >>> >
> >>> > from sqlalchemy.ext.declarative import declared_attr
> >>> > from sqlalchemy import Column, Integer, String, Unicode, UnicodeText
> >>> > from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
> >>> > import arrow
> >>> >
> >>> >
> >>> > datetimeString_format = {
> >>> > "UTC": "%Y-%m-%d %H:%M:%S+00:00",
> >>> > "local_with_timezoneMarker": "%Y-%m-%d %H:%M:%S %Z",
> >>> > "local_without_timezoneMarker": "%Y-%m-%d %H:%M:%S"
> >>> > }
> >>> >
> >>> >
> >>> > dateString_format = "%Y-%m-%d"
> >>> >
> >>> >
> >>> > class My_TimePoint_Mixin:
> >>> > # define output formats:
> >>> > datetimeString_inUTC_format = "%Y-%m-%d %H:%M:%S+00:00"
> >>> > datetimeSt

Re: [sqlalchemy] SQL expression for function (and/or method) dispatching?

[Jinghui Niu]

If I store the as DateTime values and with a second column to indicate
whether it's a date or datetime, it would look like this for a Date:
col1: "2016-09-16 00:00:00", col2: "date only"

It looks so messy to me:)

I'd prefer to have Date and DateTime distinctively written in my database.
I admit that it's one of my personal preference though:)

On Fri, Sep 16, 2016 at 1:49 AM, Jinghui Niu <niujing...@gmail.com> wrote:

> Thanks for reply.
>
> The reason is simple. I plan in the future to accommodate datetime range
> into that column as well, so storing this logic as plain strings gives the
> most flexibility. This is a project that I'm learning by making. So I would
> like to try all the new features for later tasks.
>
> I'm fascinated by hybrid_property, but is stuck now when trying to
> dispatch functions at the SQL expression level.
>
> Jinghui
>
> On Fri, Sep 16, 2016 at 1:34 AM, Simon King <si...@simonking.org.uk>
> wrote:
>
>> Ignore SQLAlchemy for the moment and describe what you are trying to
>> achieve. It looks like you want to store dates and times as strings in
>> your database (rather than the appropriate type), and yet still be
>> able to perform date-related operations on them qhen querying. Is that
>> right? Is there a reason why you can't store them as proper DateTime
>> values (perhaps with a second column to indicate whether or not the
>> time part is valid)?
>>
>> Simon
>>
>> On Fri, Sep 16, 2016 at 4:39 AM, Jinghui Niu <niujing...@gmail.com>
>> wrote:
>> > I have the following code snippet, I marked my question in a comment
>> line
>> > inside the hybrid_property.expression part. As you can see, it is now
>> not
>> > implemented:
>> >
>> > from sqlalchemy.ext.declarative import declared_attr
>> > from sqlalchemy import Column, Integer, String, Unicode, UnicodeText
>> > from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
>> > import arrow
>> >
>> >
>> > datetimeString_format = {
>> > "UTC": "%Y-%m-%d %H:%M:%S+00:00",
>> > "local_with_timezoneMarker": "%Y-%m-%d %H:%M:%S %Z",
>> > "local_without_timezoneMarker": "%Y-%m-%d %H:%M:%S"
>> > }
>> >
>> >
>> > dateString_format = "%Y-%m-%d"
>> >
>> >
>> > class My_TimePoint_Mixin:
>> > # define output formats:
>> > datetimeString_inUTC_format = "%Y-%m-%d %H:%M:%S+00:00"
>> > datetimeString_naive_format = "%Y-%m-%d %H:%M:%S"
>> >
>> >
>> > # instrumented fields:
>> > _TimePoint_in_database = Column('timepoint', String, nullable=False)
>> > _TimePoint_in_database_suffix = Column(
>> > 'timepoint_suffix', String, nullable=False)
>> >
>> >
>> > @hybrid_property
>> > def timepoint(self):
>> > twoPossibleType_handlers = [
>> > self._report_ACCRT_DATE,
>> > self._report_ACCRT_DATETIME
>> > ]
>> > for handler in twoPossibleType_handlers:
>> > print("handler: ", handler)
>> > try:
>> > return handler(self)
>> > except (AssertionError, ValueError) as e:
>> > logging.warning("Try next handler!")
>> >
>> > @timepoint.expression
>> > def timepoint(cls):
>> > pass
>> > # How can I implement SQL expression for a equivalent function
>> > dispatch here? There seems to be no SQL equivalent structure for this?
>> >
>> >
>> > @timepoint.setter
>> > def timepoint(self, datetimepointOBJ):
>> > handlers_lookup = {
>> > datetime.datetime: self._set_ACCRT_DATETIME,
>> > datetime.date: self._set_ACCRT_DATE
>> > }
>> > this_time = type(datetimepointOBJ)
>> > this_handler = handlers_lookup[this_time]
>> > print("handler: ", this_handler)
>> > this_handler(datetimepointOBJ)
>> >
>> >
>> > def _report_ACCRT_DATE(self):
>> > """Accurate Date"""
>> > assert self._TimePoint_in_database_suffix == "ACCRT_DATE"
>> > date_string = self._TimePoint_in_database
>> > dateString_format = "%Y-%m-%d"
>> > # return a da

Re: [sqlalchemy] SQL expression for function (and/or method) dispatching?

[Jinghui Niu]

Thanks for reply.

The reason is simple. I plan in the future to accommodate datetime range
into that column as well, so storing this logic as plain strings gives the
most flexibility. This is a project that I'm learning by making. So I would
like to try all the new features for later tasks.

I'm fascinated by hybrid_property, but is stuck now when trying to dispatch
functions at the SQL expression level.

Jinghui

On Fri, Sep 16, 2016 at 1:34 AM, Simon King <si...@simonking.org.uk> wrote:

> Ignore SQLAlchemy for the moment and describe what you are trying to
> achieve. It looks like you want to store dates and times as strings in
> your database (rather than the appropriate type), and yet still be
> able to perform date-related operations on them qhen querying. Is that
> right? Is there a reason why you can't store them as proper DateTime
> values (perhaps with a second column to indicate whether or not the
> time part is valid)?
>
> Simon
>
> On Fri, Sep 16, 2016 at 4:39 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> > I have the following code snippet, I marked my question in a comment line
> > inside the hybrid_property.expression part. As you can see, it is now not
> > implemented:
> >
> > from sqlalchemy.ext.declarative import declared_attr
> > from sqlalchemy import Column, Integer, String, Unicode, UnicodeText
> > from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
> > import arrow
> >
> >
> > datetimeString_format = {
> > "UTC": "%Y-%m-%d %H:%M:%S+00:00",
> > "local_with_timezoneMarker": "%Y-%m-%d %H:%M:%S %Z",
> > "local_without_timezoneMarker": "%Y-%m-%d %H:%M:%S"
> > }
> >
> >
> > dateString_format = "%Y-%m-%d"
> >
> >
> > class My_TimePoint_Mixin:
> > # define output formats:
> > datetimeString_inUTC_format = "%Y-%m-%d %H:%M:%S+00:00"
> > datetimeString_naive_format = "%Y-%m-%d %H:%M:%S"
> >
> >
> > # instrumented fields:
> > _TimePoint_in_database = Column('timepoint', String, nullable=False)
> > _TimePoint_in_database_suffix = Column(
> > 'timepoint_suffix', String, nullable=False)
> >
> >
> > @hybrid_property
> > def timepoint(self):
> > twoPossibleType_handlers = [
> > self._report_ACCRT_DATE,
> > self._report_ACCRT_DATETIME
> > ]
> > for handler in twoPossibleType_handlers:
> > print("handler: ", handler)
> > try:
> > return handler(self)
> > except (AssertionError, ValueError) as e:
> > logging.warning("Try next handler!")
> >
> > @timepoint.expression
> > def timepoint(cls):
> > pass
> > # How can I implement SQL expression for a equivalent function
> > dispatch here? There seems to be no SQL equivalent structure for this?
> >
> >
> > @timepoint.setter
> > def timepoint(self, datetimepointOBJ):
> > handlers_lookup = {
> > datetime.datetime: self._set_ACCRT_DATETIME,
> > datetime.date: self._set_ACCRT_DATE
> > }
> > this_time = type(datetimepointOBJ)
> > this_handler = handlers_lookup[this_time]
> > print("handler: ", this_handler)
> > this_handler(datetimepointOBJ)
> >
> >
> > def _report_ACCRT_DATE(self):
> > """Accurate Date"""
> > assert self._TimePoint_in_database_suffix == "ACCRT_DATE"
> > date_string = self._TimePoint_in_database
> > dateString_format = "%Y-%m-%d"
> > # return a datetime.date
> > return datetime.datetime.strptime(date_string,
> > dateString_format).date()
> >
> >
> > def _report_ACCRT_DATETIME(self):
> > """Accurate DateTime"""
> > assert self._TimePoint_in_database_suffix in
> pytz.all_timezones_set
> > datetimeString_inUTC = self._TimePoint_in_database
> > utc_naive = datetime.datetime.strptime(
> > datetimeString_inUTC, self.datetimeString_inUTC_format)
> > utc_timepoint = arrow.get(utc_naive, "utc")
> > # localize
> > local_timepoint =
> > utc_timepoint.to(self._TimePoint_in_database_suffix)
> > # return a datetime.datetime
> > return local_timepoint.datetime
> >
> >
> >

[sqlalchemy] SQL expression for function (and/or method) dispatching?

[Jinghui Niu]

I have the following code snippet, I marked my question in a comment line 
inside the hybrid_property.expression part. As you can see, it is now not 
implemented:

from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy import Column, Integer, String, Unicode, UnicodeText
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
import arrow


datetimeString_format = {
"UTC": "%Y-%m-%d %H:%M:%S+00:00",
"local_with_timezoneMarker": "%Y-%m-%d %H:%M:%S %Z",
"local_without_timezoneMarker": "%Y-%m-%d %H:%M:%S"
}


dateString_format = "%Y-%m-%d"


class My_TimePoint_Mixin:
# define output formats:
datetimeString_inUTC_format = "%Y-%m-%d %H:%M:%S+00:00"
datetimeString_naive_format = "%Y-%m-%d %H:%M:%S"


# instrumented fields:
_TimePoint_in_database = Column('timepoint', String, nullable=False)
_TimePoint_in_database_suffix = Column(
'timepoint_suffix', String, nullable=False)


@hybrid_property
def timepoint(self):
twoPossibleType_handlers = [
self._report_ACCRT_DATE,
self._report_ACCRT_DATETIME
]
for handler in twoPossibleType_handlers:
print("handler: ", handler)
try:
return handler(self)
except (AssertionError, ValueError) as e:
logging.warning("Try next handler!")
 
@timepoint.expression
def timepoint(cls):
pass
# How can I implement SQL expression for a equivalent function 
dispatch here? There seems to be no SQL equivalent structure for this?


@timepoint.setter
def timepoint(self, datetimepointOBJ):
handlers_lookup = {
datetime.datetime: self._set_ACCRT_DATETIME,
datetime.date: self._set_ACCRT_DATE
}
this_time = type(datetimepointOBJ)
this_handler = handlers_lookup[this_time]
print("handler: ", this_handler)
this_handler(datetimepointOBJ)


def _report_ACCRT_DATE(self):
"""Accurate Date"""
assert self._TimePoint_in_database_suffix == "ACCRT_DATE"
date_string = self._TimePoint_in_database
dateString_format = "%Y-%m-%d"
# return a datetime.date
return datetime.datetime.strptime(date_string, dateString_format).
date()


def _report_ACCRT_DATETIME(self):
"""Accurate DateTime"""
assert self._TimePoint_in_database_suffix in pytz.all_timezones_set
datetimeString_inUTC = self._TimePoint_in_database
utc_naive = datetime.datetime.strptime(
datetimeString_inUTC, self.datetimeString_inUTC_format)
utc_timepoint = arrow.get(utc_naive, "utc")
# localize
local_timepoint = utc_timepoint.to(self.
_TimePoint_in_database_suffix)
# return a datetime.datetime
return local_timepoint.datetime


def _set_ACCRT_DATETIME(self, datetimeOBJ_aware):
assert isinstance(datetimeOBJ_aware, datetime.datetime), "Must be a 
valid datetime.datetime!"
assert datetimeOBJ_aware.tzinfo is not None, "Must contain tzinfo!"
utctime_aware_arrow = arrow.get(datetimeOBJ_aware).to('utc')
utctime_aware_datetime = utctime_aware_arrow.datetime
store_datetime_string = utctime_aware_datetime.strftime(
self.datetimeString_inUTC_format)
self._TimePoint_in_database = store_datetime_string


def _set_ACCRT_DATE(self, dateOBJ):
store_date_string = dateOBJ.isoformat()
self._TimePoint_in_database = store_date_string

Could someone please point out a clue as to implement the 
hybrid_property.expression part? Thanks.

I've also posted a similar issue on stackoverflow.com, for reference:

http://stackoverflow.com/questions/39485440/calling-from-the-same-class-why-is-one-treated-as-bound-method-while-the-other

-- 
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] hybrid_property vs. hybrid_method, other than the latter can take arguments?

[Jinghui Niu]

Hi, I wonder if there is any recommendation or best practice on choosing 
between 
hybrid_property

and 
hybrid_method
,
other than they hybrid_method can take arguments? If I use the 
hybrid_method only throughout, without giving it a argument more than self, 
doesn't it equal to using hybrid_property? Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] What criteria should I use to determine whether I need a distinct class-level expression for my hybrid attributes?

[Jinghui Niu]

Thanks Mike. In future is it likely to have the instance level expression
and class level expression automatically translated by ORM? It will be so
much easier!

On Mon, Aug 22, 2016 at 7:20 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:

>
>
> On 08/22/2016 03:20 AM, Jinghui Niu wrote:
>
>> I'm creating a mixin for my timestamp columns throughout my projects.
>> Internally, the mixin uses UTC strings to store timestamps, externally
>> it converts back and forth into local time using a second column that
>> stores timezone information. I'm studying the hybrid attribute section
>> in the documentation, but not sure if this use case would involve a
>> different expression between the instance level and class level.
>>
>> As a general rule, what should I look into to determine whether I need
>> to use the hybrid_property.expression()?
>>
>
> expression is needed when you A. want to use your attribute in a Query as
> part of filter() or similar and B. if the Python inside your hybrid doesn't
> work as a SQLAlchemy expression.
>
> Example 1: doesn't need expression - simple addition:
>
> @hybrid_property
> def a_plus_b(self):
> return self.a + self.b
>
>
> Example 2: does need expression - Python "if" statement needs to be CAST
> on SQL
>
> @hybrid_property
> def conditional_a(self):
> return "foo" if self.a == 'f' else "bar"
>
>
> @conditional_a.expression
> def conditional_a(cls):
> return sql.case(("foo", cls.a == 'f'), else_="bar")
>
>
>
>> Do I need to first familiarize myself with the SQLalchemy's SQL
>> functions, such as func() etc., or there is some simpler rules? Thanks.
>>
>
> if you're working with date / timezone conversions in SQL then you should
> figure out the SQL you want first, then at that point you probably would
> need to use func. to get some SQL functions out of it, it's pretty easy at
> that level.
>
>
>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/to
> pic/sqlalchemy/AHNauPgot3c/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>

-- 
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] What criteria should I use to determine whether I need a distinct class-level expression for my hybrid attributes?

[Jinghui Niu]

I'm creating a mixin for my timestamp columns throughout my projects. 
Internally, the mixin uses UTC strings to store timestamps, externally it 
converts back and forth into local time using a second column that stores 
timezone information. I'm studying the hybrid attribute section in the 
documentation, but not sure if this use case would involve a different 
expression between the instance level and class level.

As a general rule, what should I look into to determine whether I need to 
use the hybrid_property.expression()?

Do I need to first familiarize myself with the SQLalchemy's SQL functions, 
such as func() etc., or there is some simpler rules? Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] thread-safety of Session, Engine, and Connections

[Jinghui Niu]

For clarity I made a table below in my notes out of reading the 
documentation and generalize the points:

Engine: no worries, Engine object is always thread safe.
Session: Session objects are tricky, they can be quite dangerous to use in 
a multi-threading context if one is not careful. Forget about 
scoped_session(), it's only for pros. Just remember to create a Session 
object when a thread starts and close it before a thread ends. That way we 
can ensure that the said Session object is local to one particular thread.
Connection: This is the most dangerous part. You can create a Engine object 
and a Session object outside of the current thread, as long as you bind 
those two together from the current thread to get a Connection that is 
local to current thread, then throughout this thread's lifespan the entire 
system will be thread safe. Before that particular thread ends, just close 
the session object.


Can some advanced user help review it if there is any flaws in those 
points? I'm thinking to write a blog to explain these stuff to newbies, I 
found current documentation is fine but a little too technical for beginner 
users. Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

Oh by the way, I'm using SQLite as backend.
On Aug 14, 2015 2:04 AM, Jinghui Niu niujing...@gmail.com wrote:

 I have three different DBs, one is person.db, another is journal.db, yet
 another is tag.db. In the documentation it reads:

 Vertical partitioning places different kinds of objects, or different
 tables, across multiple databases:


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

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


 Session = sessionmaker(twophase=True)


 # bind User operations to engine 1, Account operations to engine 2

 Session.configure(binds={User:engine1, Account:engine2})


 session = Session()


 I noticed that this example only deals with two DBs, and the parameter is
 called twophase. I was wondering if there is any significance of two
 here? How can I fit my third DB in? Thanks.

 --
 You received this message because you are subscribed to a topic in the
 Google Groups sqlalchemy group.
 To unsubscribe from this topic, visit
 https://groups.google.com/d/topic/sqlalchemy/tRlV984I_64/unsubscribe.
 To unsubscribe from this group and all its topics, send an email to
 sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

I have three different DBs, one is person.db, another is journal.db, yet 
another is tag.db. In the documentation it reads:

Vertical partitioning places different kinds of objects, or different 
 tables, across multiple databases:


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

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


 Session = sessionmaker(twophase=True)


 # bind User operations to engine 1, Account operations to engine 2

 Session.configure(binds={User:engine1, Account:engine2})


 session = Session()


I noticed that this example only deals with two DBs, and the parameter is 
called twophase. I was wondering if there is any significance of two 
here? How can I fit my third DB in? Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

Thanks for all these helpful feedback. 

If I still want to use SQLite, and I still need to do vertical partition, 
what can I do? Am I out of luck?



On Friday, August 14, 2015 at 2:04:37 AM UTC-7, Jinghui Niu wrote:

 I have three different DBs, one is person.db, another is journal.db, yet 
 another is tag.db. In the documentation it reads:

 Vertical partitioning places different kinds of objects, or different 
 tables, across multiple databases:


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

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


 Session = sessionmaker(twophase=True)


 # bind User operations to engine 1, Account operations to engine 2

 Session.configure(binds={User:engine1, Account:engine2})


 session = Session()


 I noticed that this example only deals with two DBs, and the parameter is 
 called twophase. I was wondering if there is any significance of two 
 here? How can I fit my third DB in? Thanks.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

Thank you very much Jonathan for your very intuitive analogy!

Basically I just want to put people, journal and tag tables(each will 
potentially be very large) into different DBs, if I write that logic, how 
can I integrate it with SQLAlchemy? Could you give me a rough idea here? Or 
point some reference. I suppose such feature is relatively commonly needed 
among SQLite users isn't it?

On Friday, August 14, 2015 at 3:48:40 PM UTC-7, Jonathan Vanasco wrote:



 On Friday, August 14, 2015 at 5:16:48 PM UTC-4, Jinghui Niu wrote:


 If I still want to use SQLite, and I still need to do vertical partition, 
 what can I do? Am I out of luck?


 You can, but not with a two-phase commit. 

 Two-phase commit basically works like this:

 - round 1, everyone locks-state and votes COMMIT! or N!
 - round 2, if commit in round 1 was unanimous, it commits. otherwise 
 everyone is told to roll back.

 Since SQLlite doesn't support that, you'd need to write that logic in at 
 the application level.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

Thanks Jonathan for pointing out the direction, it is very helpful to know 
where I can find more info.

On Friday, August 14, 2015 at 5:06:09 PM UTC-7, Jonathan Vanasco wrote:

 Well, this problem doesn't really have anything to do with SqlAlchemy -- 
 you should probably ask people for advice on the Sqlite lists or Stack 
 Overflow.

 You can segment out your database into 3 files using the example above. 
  You will just run into an issue where -- because there isn't a 
 two-phase-commit available in Sqlite, you will need to decide how to handle 
 situations like (but not limited to):

 - the first and second databases committed, but the third database raised 
 an error (you need to undo in the application)
 - the first and second databases committed, but your application was quit 
 before the third database could commit (you need to undo from another 
 application)

 You will have to decide how to handle that at the application and database 
 levels, and then SqlAlchemy can be used to implement that strategy. 

 I just want to be clear -- your concern right now is on the best way to 
 use Sqlite to solve your problem -- not use Sqlalchemy.  Once you figure 
 that out, people here can be more helpful.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

Just a thought, if I don't commit those three tables together in my 
application, can I just use 3 Session objects to commit them separately, 
without having to worry about this two phase issue? I want to go simple, 
not sure if I can handle this fancy stuff:)

On Friday, August 14, 2015 at 5:20:07 PM UTC-7, Jinghui Niu wrote:

 Thanks Jonathan for pointing out the direction, it is very helpful to know 
 where I can find more info.

 On Friday, August 14, 2015 at 5:06:09 PM UTC-7, Jonathan Vanasco wrote:

 Well, this problem doesn't really have anything to do with SqlAlchemy -- 
 you should probably ask people for advice on the Sqlite lists or Stack 
 Overflow.

 You can segment out your database into 3 files using the example above. 
  You will just run into an issue where -- because there isn't a 
 two-phase-commit available in Sqlite, you will need to decide how to handle 
 situations like (but not limited to):

 - the first and second databases committed, but the third database raised 
 an error (you need to undo in the application)
 - the first and second databases committed, but your application was quit 
 before the third database could commit (you need to undo from another 
 application)

 You will have to decide how to handle that at the application and 
 database levels, and then SqlAlchemy can be used to implement that 
 strategy. 

 I just want to be clear -- your concern right now is on the best way to 
 use Sqlite to solve your problem -- not use Sqlalchemy.  Once you figure 
 that out, people here can be more helpful.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Session.merge() use case (possible) ambiguity

[Jinghui Niu]

About the Session.merge(), the documentation gives several examples of use 
cases, I think there might be a slight ambiguity in this one:

An application which reads an object structure from a file and wishes to 
 save it to the database might parse the file, build up the structure, and 
 then use merge() to save it to the database, ensuring that the data within 
 the file is used to formulate the primary key of each element of the 
 structure. Later, when the file has changed, the same process can be 
 re-run, producing a slightly different object structure, which can then be 
 merged in again, and the Session will automatically update the database to 
 reflect those changes, loading each object from the database by primary key 
 and then updating its state with the new state given.



In this case when the same process re-run, producing a slightly different 
object structure, does this newer object carry a primary key? If it's just 
been produced by parsing from the file, then I think probably it doesn't 
have a primary key. If it doesn't have a primary key, then merge() will 
create an entirely new entry in database for it instead of updating the 
older one, right? And this would defeat the updating intention, right?

I think I probably missed some point here. Could someone elaborate it a 
little bit? Thanks.

p.s. Please bear with me with so many entry-level questions. I've been 
studying thoroughly the documentation these days and trying to learn the 
ropes.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] What exactly is the rationale behind manage the life cycle of the session externally to functions that deal with specific data.?

[Jinghui Niu]

In the Documentation -- Session Basics, I read this: 

 E.g. don’t do this:


 ### this is the **wrong way to do it** ###


 class ThingOne(object):

 def go(self):

 session = Session()

 try:

 session.query(FooBar).update({x: 5})

 session.commit()

 except:

 session.rollback()

 raise


 class ThingTwo(object):

 def go(self):

 session = Session()

 try:

 session.query(Widget).update({q: 18})

 session.commit()

 except:

 session.rollback()

 raise


 def run_my_program():

 ThingOne().go()

 ThingTwo().go()\



I really can't think of any reasons/risks that the above way of doing 
things would incur any bad consequences. Could someone shed a little light 
here? Thanks a lot.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLite: How can I turn on ON DELETE CASCADE from within sqlalchemy?

[Jinghui Niu]

Hi Mike. Thanks for your reply. That's great news!

Just want to clarify on which level such constraints will be placed? 
sqlalchemy or the underlying database?

So now if I want to use passive delete, which relies solely on the 
underlying SQLite database's constraints not the sqlalchemy's, I just need 
to set the foreign key argument like this:
ForeignKey('items.record_id', ondelete='CASCADE'))
when I declare the classes. This will set the constraint on the underlying 
database. Is my understanding correct?


On Thursday, August 6, 2015 at 2:48:26 PM UTC-7, Michael Bayer wrote:



 On 8/6/15 5:31 PM, Jinghui Niu wrote:

 I know you can set this constraint if you are directly dealing with 
 sqlite3, but how can I achieve this database level setting from within 
 sqlalchemy? 

 The documentation reads:

 Note that these clauses are not supported on SQLite, and require InnoDB 
 tables when used with MySQL. They may also not be supported on other 
 databases.


 So is there a way to turn this feature on from within sqlalchemy? Thanks. 

 that documentation is probably out of date.  The ON DELETE CASCADE 
 directive will emit on SQLite like on any other backend.   You would need 
 to enable foreign key support on a per-connection basis for sqlite3 in 
 order for them to take effect: 


 http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#foreign-key-support




 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+...@googlegroups.com javascript:.
 To post to this group, send email to sqlal...@googlegroups.com 
 javascript:.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] SQLite: How can I turn on ON DELETE CASCADE from within sqlalchemy?

[Jinghui Niu]

I know you can set this constraint if you are directly dealing with 
sqlite3, but how can I achieve this database level setting from within 
sqlalchemy?

The documentation reads:

 Note that these clauses are not supported on SQLite, and require InnoDB 
 tables when used with MySQL. They may also not be supported on other 
 databases.


So is there a way to turn this feature on from within sqlalchemy? Thanks. 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Where can I find the signature definition for @validates functions?

[Jinghui Niu]

I read the documentation and encountered this:
@validates('addresses', include_backrefs=False) 
def validate_address(self, key, address): 
assert '@' in address.email 
return address


What are the key and address in the validate_address function? I can't find 
any explanation in our documentation. Do they have any meaning or expect 
any special objects? Can I use other names for those two parameters here? 
Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] searching for multiple ( unknown mumber) keywords

[Jinghui Niu]

for example, I'd like to search for keywords foo, bar and possibly but 
not necessarily more keywords in the description column of my *Item* class, 
so I build a list of such keywords:
[foo, bar],

and then use primitive looping to achieve my goal:

query_obj = session.query(Item)
for k in search_keywords:
query_obj = query_obj.filter(Item.description.like('%{0}%'.format(k)))

but this doesn't seem very elegant. I think there could be a better 
solution in sqlalchemy. Could someone share some tips other than looping 
and chaining? Thanks.

p.s. Also what is the best practice to achieve a OR search of 
multiple(indefinite number of) keywords?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: what is the way to achieve automatic conversion on each query?

[Jinghui Niu]

Thanks Mike,

I've read the documentation on both, hybrid attributes seem to be easier
and have a clearer logic in this use case. Although this is just my gut
feeling as a layman. What is the rationale behind having two competing
solutions?

Also, I dimly remember someone recommended using property method, I
didn't quite understand it back then, does it relate to the two techniques
you suggested here?

On Sun, Jul 26, 2015 at 11:06 AM, Mike Bayer mike...@zzzcomputing.com
wrote:



 On 7/25/15 6:38 PM, Jinghui Niu wrote:

 By the way, the database driver that I'm using is SQLite, which doesn't
 support native timestamp I believe.

 On Saturday, July 25, 2015 at 2:21:44 PM UTC-7, Jinghui Niu wrote:

  I'm using two columns to store my datetime records: one column stores
 utc timestamp as a string, the other column stores a timezone offset as an
 integer. Now I find myself most of the time writing ad hoc functions to
 convert those UTC times to various local times. My code base has become
 really inconsistent and repetitive.

  I'm looking to write some code with sqlalchemy, natively sqlalchemy, to
 allow me to get the converted local time on each query, automatically. I've
 heard of that feature before.
 I'm kind of lost in the documentation. Could someone point a general
 direction here? I don't expect too much, just a general direction would be
 highly appreciated. Thanks a lot.

  --


 there are multiple documented techniques for this kind of thing.  Have you
 looked into composite attributes or hybrid properties ?  Both can suit this
 use case.





  You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


  --
 You received this message because you are subscribed to a topic in the
 Google Groups sqlalchemy group.
 To unsubscribe from this topic, visit
 https://groups.google.com/d/topic/sqlalchemy/qWh8o5bGzU4/unsubscribe.
 To unsubscribe from this group and all its topics, send an email to
 sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.