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/sqlalchemy>.
>>         For more options, visit https://groups.google.com/d/optout
>>         <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
>>     <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/uUhcft4S7-E/unsubscribe
>>     <https://groups.google.com/d/topic/sqlalchemy/uUhcft4S7-E/unsubscribe
>> >.
>>     To unsubscribe from this group and all its topics, send an email to
>>     sqlalchemy+unsubscr...@googlegroups.com
>>     <mailto:sqlalchemy%2bunsubscr...@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
>>     <https://groups.google.com/group/sqlalchemy>.
>>     For more options, visit https://groups.google.com/d/optout
>>     <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
>> <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.

Reply via email to