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 <[email protected]> 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 <[email protected] >> <mailto:[email protected]>> 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 [email protected] >> <mailto:sqlalchemy%[email protected]> >> <mailto:[email protected] >> <mailto:sqlalchemy%[email protected]>>. >> To post to this group, send email to [email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> <mailto:[email protected]>>. >> 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 >> [email protected] >> <mailto:sqlalchemy%[email protected]>. >> To post to this group, send email to [email protected] >> <mailto:[email protected]>. >> 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 [email protected] >> <mailto:[email protected]>. >> To post to this group, send email to [email protected] >> <mailto:[email protected]>. >> 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 > [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
