sql alchemy closure table relationship definition

  • Last Update :
  • Techknowledgy :

After posting to the SQL Alchemy Google Group and receiving some awesome help from Michael Bayer, I arrived at the following definition of the areas relationship in the DbRoute class

areas = relationship('DbArea',
   backref = backref('routes', order_by = 'DbRoute.name'),
   secondary = area_relationship_table,
   primaryjoin = area_id == area_relationship_table.c.descendent,
   secondaryjoin = DbArea.area_id == area_relationship_table.c.ancestor,
   innerjoin = True, order_by = DbArea.name,
   foreign_keys = [area_relationship_table.c.ancestor,

Suggestion : 2

#34 eon said 2018-10-16T22:08:19Z

Thanks again
for your prompt replies.
sir, i discover that the code about have some errors but i dont how to figure it out.
replies to comments only take the comment.id of the first comment.
ie.i have two comments.when i write a reply on the first i got path = 1.3.when i write reply on the second comment, instead of getting path = 2.4, i got 1.4 and it displays directly under the first comment.what could be problems
@Harvey: it 's difficult for me to say. I think you need to use a debugger to discover what the problem in your logic is. PyCharm and Visual Studio Code both have very good debuggers that work great with Flask applications.
Hi, Miguel
I am a newbie trying to create a little blog using your mega tutorial samples. i am trying to implement nested comments and i found this tutorial written by you also. after going through it and comments from others here, I put up this code into my work similar to something i found here. i will show the routes.py and the reply.html because this is where i fell the error is coming from.
@main.route('/blog//', methods=['GET', 'POST'])
def detail(slug):
    post = Post.query.filter_by(slug=slug).first_or_404()
    comment = Comment.query.filter_by(post_id=post.id).order_by(Comment.path.asc()).all()
    form1 = CommentForm()
    form2 = ReplyForm()
    if form2.validate_on_submit() and request.method == 'POST':
        for reply in comment:
            comm = Comment(body=form2.reply.data, author=current_user._get_current_object(),
            flash('Your reply has been published')
    elif form1.validate_on_submit() and request.method == 'POST':
flash('Your comment  has been published')
        return redirect(url_for('main.detail', slug=post.slug, page=-1))
    page = request.args.get('page', 1, type=int)
    if page == -1:
        page  = (post.comments.count() - 1) // current_app.config['COMMENTS_PER_PAGE'] + 1
    pagination = post.comments.order_by(Comment.timestamp.asc()).paginate(page, 
    comments = pagination.items
    return render_template('detail.html',post=post, form1=form1,form2=form2,comments=comments,comment=comment,

{% for reply in comment.replies %}

                {% if moderate or not comment.disabled %}
                    {% if reply.body_html %}
                        {{ reply.body_html | safe }}
                {% else %}
                        {{ reply.body }}
                    {% endif %}
                    {% endif %}

{% endfor %}

i wrote two comments to a post, it was ok. but when i write to reply through  the reply form under the first comment, the reply appeared under both comment one and two. 
please how do i force each  reply to sit directly under its comment.
@Mavis: the code you copied from the comments in this article is not a good model to follow, and is not endorsed by me.Creating a web page that accepts threaded comments is actually fairly difficult, you need some JavaScript in addition to the backend support.I intend one day to write a follow up article in which I cover the user interface implementation
for threaded comments, unfortunately until then I do not know any resource to recommend on this topic.
Another approach to the problem is using the logic you described in Adjacency Lists code, but executing it not with multiple requests, but with one recursive request as in this StackOverflow issue: https: //stackoverflow.com/questions/3699395/find-parent-recursively-using-query. This solution, however, requires some understanding of SQL. I can see one comment talking about recursive CTE, but the link should provide an example for those considering it.
@Andriy: recursive queries are not supported by all databases, and I 'm also unclear on the performance implications of using them. Clearly they have to perform better than issuing the recursive queries from the Python application, but is that good enough or can it still perform poorly? I do not know.

Suggestion : 3

Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable See here.,The number of returned rows affected is the sum of the rowcount attribute of sqlite3.Cursor or SQLAlchemy connectable which may not reflect the exact number of written rows as stipulated in the sqlite3 or SQLAlchemy.,This is allowed to support operations that require that the same DBAPI connection is used for the entire operation.,Specifying the datatype for columns. If a dictionary is used, the keys should be the column names and the values should be the SQLAlchemy types or strings for the sqlite3 legacy mode. If a scalar is provided, it will be applied to all columns.

>>> from sqlalchemy
import create_engine
   engine = create_engine('sqlite://', echo = False)
>>> df = pd.DataFrame({
      'name': ['User 1', 'User 2', 'User 3']
   }) >>>
0 User 1
1 User 2
2 User 3
>>> df.to_sql('users', con = engine)
   engine.execute("SELECT * FROM users").fetchall()[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]
>>> with engine.begin() as connection:
   ...df1 = pd.DataFrame({
      'name': ['User 4', 'User 5']
   ...df1.to_sql('users', con = connection, if_exists = 'append')
>>> df2 = pd.DataFrame({
      'name': ['User 6', 'User 7']
   }) >>>
   df2.to_sql('users', con = engine, if_exists = 'append')
   engine.execute("SELECT * FROM users").fetchall()[(0, 'User 1'), (1, 'User 2'), (2, 'User 3'),
      (0, 'User 4'), (1, 'User 5'), (0, 'User 6'),
      (1, 'User 7')]
>>> df2.to_sql('users', con = engine, if_exists = 'replace',
   ...index_label = 'id')
   engine.execute("SELECT * FROM users").fetchall()[(0, 'User 6'), (1, 'User 7')]

Suggestion : 4

November 22, 2014 20:52 / peewee python sqlite / 0 comments

$ git clone https: //gist.github.com/coleifer/7f3593c5c2a645913b92 closure
$ gcc - g - fPIC - shared closure.c - o closure.so
$ sqlite3
SQLite version 3.8 .7 .1 2014 - 10 - 29 13: 59: 56
Enter ".help"
for usage hints.
Connected to a transient in -memory database.
Use ".open FILENAME"
to reopen on a persistent database.

sqlite > .load. / closure
CREATE TABLE category(
   name VARCHAR(255),
   parent_id INTEGER,
   FOREIGN KEY(parent_id) REFERENCES category(id));

CREATE VIRTUAL TABLE category_closure USING transitive_closure(
   tablename = "category",
   idcolumn = "id",
   parentcolumn = "parent_id");
INSERT INTO category(name, parent_id) VALUES('Books', NULL);
INSERT INTO category(name, parent_id) VALUES('Fiction', 1);
INSERT INTO category(name, parent_id) VALUES('Sci-fi', 2);
INSERT INTO category(name, parent_id) VALUES('Western', 2);
INSERT INTO category(name, parent_id) VALUES('Non-fiction', 1);
sqlite > SELECT name FROM category WHERE id IN(
   ... > SELECT cc.id
   ... > FROM category_closure AS cc
   ... > WHERE cc.root = 1 AND cc.depth = 1);

-- -- -- -- -- -
Non - fiction

Suggestion : 5

This session describes creation of another table which is related to already existing one in our database. The customers table contains master data of customers. We now need to create invoices table which may have any number of invoices belonging to a customer. This is a case of one to many relationships.,The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship.,A second directive, known as relationship(), tells the ORM that the Invoice class should be linked to the Customer class using the attribute Invoice.customer. The relationship() uses the foreign key relationships between the two tables to determine the nature of this linkage, determining that it is many to one.,Invoices class applies ForeignKey construct on custid attribute. This directive indicates that values in this column should be constrained to be values present in id column in customers table. This is a core feature of relational databases, and is the “glue” that transforms unconnected collection of tables to have rich overlapping relationships.

Using declarative, we define this table along with its mapped class, Invoices as given below −

from sqlalchemy
import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative
import declarative_base
Base = declarative_base()
from sqlalchemy.orm
import relationship

class Customer(Base):
   __tablename__ = 'customers'

id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'

id = Column(Integer, primary_key = True)
custid = Column(Integer, ForeignKey('customers.id'))
invno = Column(Integer)
amount = Column(Integer)
customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")

This will send a CREATE TABLE query to SQLite engine as below −

CREATE TABLE invoices(
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   FOREIGN KEY(custid) REFERENCES customers(id)