Flask_Table manipulating the content of specific cells

[hackRussiaVKA]

Is there any way to execute sql queries in flask_table columns? Some of the values in my database are raw SQL queries saved as a string; when I manually create an html table I could force jinja to execute those queries using {{ }} instead of browsing them as a text, but with flask_table I can't edit the content of specific columns. Is this correct?

[plumdog]

It's true that you can't use the built-in flask table column types to do things like execute SQL custom queries.

However, there's some options:

1. Hide the SQL execution in a property or method on the model you're iterating over.
2. Write a custom column type that runs the queries you want.
3. Make it so the initial query does a join so you don't have to execute a query for each row in your table.

Option 3 would be the most efficient as it avoids the "n+1 queries" problem, and certainly the right one if you have lots of rows or performance is a concern. Options 1 and 2 are essentially the same, but I might prefer option 1 because option 2 combines presentation logic with business logic.

Hopefully that points you in the right direction. I can post some code snippets if that might be useful.

[hackRussiaVKA]

Thanks a lot, your options are much better that the way I invented to implement this. If you have some free time I would be very grateful for code snippets. Thx, for your work it's very useful and convenient!

[hackRussiaVKA]

I'm sorry for being intrusive but I have another question about using sortable tables. I've looked through the example (sortable.py) and it works well, but i couldn't make the same with a DB data. In example we have an Item object and some @classmethods inside it. Where should I define this @classmethods in case of using DB data in flask_table tables ? Thx for your attention

[hackRussiaVKA]

I feel so ashamed. With sorting db data in table I figured out. But with executing sql queries I still have problems:(

[hackRussiaVKA]

in case you ever come here, I still really would like to look at code examples :)

[plumdog]

Exactly how my suggested code works will depend on some things - namely what you are using to perform your SQL queries. If you're not already, you should probably use http://flask-sqlalchemy.pocoo.org/2.3/ for interacting with any database records.

---

1. Hide the SQL execution in a property or method on the model you're iterating over.

How this looks depends a lot on how you're getting things from the database. If you're using sqlalchemy as suggested above, then you already have model classes, so you can declare methods or properties on that:

class MyModel(Model):
    ...
    def do_a_query(self):
        return run_an_sql_query(...)

    @property
    def or_can_do_query_with_property(self):
        return run_an_sql_query(...)

then do_a_query and or_can_do_query_with_property can be used as properties in your table like normal.

If you're doing raw SQL queries, then this way becomes much more fiddly. (See the bit at the bottom about why you probably shouldn't be doing raw SQL queries.)

2. Write a custom column type that runs the queries you want.

The docs and examples go into this quite a bit - see:

• https://github.com/plumdog/flask_table#subclassing-col
• https://github.com/plumdog/flask_table/blob/master/examples/subclassing.py
• https://github.com/plumdog/flask_table/blob/master/examples/subclassing2.py
• https://github.com/plumdog/flask_table/blob/master/examples/subclassing3.py

3. Make it so the initial query does a join so you don't have to execute a query for each row in your table.

You might well be familiar enough with SQL that you already understand this one, and it's not really a flask-table thing, but I'll explain it anyway.

Suppose we have a blogging application. We have a table of users and a table of posts. Every post has an author. (Exactly what the columns look like doesn't really matter here - point is there is a foreign key from posts to users.) Suppose we want to show a table of blog posts including the name of each author.

Bad way: do a query get a list of posts, iterate over it, for each row get the user id of the author and do a query to get their username.

Why is this bad? Well as the list of blog posts gets bigger, our application has more work to do, as every row in triggers another query. This is the "N+1" queries problem, because we do 1 to get the initial list of posts, but then "N" because we do another query for each row.

Good way: do a join. By joining the tables using the foreign key, we can do just one query to get everything, and doing joins and doing them fast is what makes SQL databases great (and other things, but joins are wonderful).

How you might go about making your database do a join depends on what you are using to run queries (sqlalchemy or some other ORM or raw SQL queries.)

---

So that is roughly how to go each of these ways, and option 3 is definitely the best one. If you're not using sqlalchemy or some other ORM/data-mapper then you absolutely should (it hugely reduces your risk of SQL-injection for one thing) and sqlalchemy is very good, and is widely used with Flask. The docs are great too, and there's lots of examples for getting started with it.

If more specific help would be useful, perhaps you could post some of the code that you have, and explain what it is that you would like it to do.

[hackRussiaVKA]

Hellow, Plum ) so, everything is ok, i used one of your solutions and it works. I think we can close this issue, thanks for your help. It's an honor for me to have a chat with such a skillfull and responsive specialist. But I still have some questions, pls, answer them befor closing this.

1. When I use LinkCol, can I make several links with different endpoint functions? Look:

class Main(Table):
    doc_number = Col('№')
    doc_name = Col('Name')
    timestamp = Col('Date')
    worker_name = Col('Worker')
    sources = SqlCol('Sources',allow_sort=False)
    a_link = LinkCol('Open','a_link', url_kwargs=dict(link='link'), allow_sort=False)
    edit = LinkCol('+', 'edit', url_kwargs=dict(id='doc_number'), allow_sort=False)
    allow_sort = True

    def sort_url(self, col_key, reverse=False):
        if reverse:
            direction = 'desc'
        else:
            direction = 'asc'
        return url_for('table', sort=col_key, direction=direction)

and

@app.route('/<link>', methods=['GET', 'POST'])
def a_link(link):
    if link == '':
        flash('Такие документы не хранятся')
        return redirect(url_for('table'))
    else:
        qry = db.session.query(Doc).filter(Doc.link == link)
        doc = qry.first()
        return send_from_directory(directory=r'C:\Users\User\PycharmProjects\oio_db\app\files', filename=link)

@app.route('/<id>', methods=['GET', 'POST'])
def edit(id):
    qry = db.session.query(Doc).filter(Doc.doc_number == id)
    doc = qry.first()
    lst = []
    str_1 = ''
    str_2 = ''
    if doc:
        for smth in doc.sources:
            str_1 = smth.source_type
            str_2 = smth.source_name
            new_str = str_1 + ' ' + str_2
            lst.append(new_str)
        form = addDocForm(obj=doc)
        form.source_name.data=lst
        if request.method == 'POST' and form.validate():
            # save edits
            #save_changes(album, form)
            flash('Album updated successfully!')
            return redirect('/')
        return render_template('edit_doc.html', form=form)
    else:
        return 'sorry'

finally, I have a table with multiple columns and two of them are links ('open' and '+'). I want to make different things when I click on different links, but now it's look like that for every link work the endpoint function that described in code first. Now it is 'a_link', if I swap 'a_link' and 'edit' it will be trying to process every link with 'edit' function. But I need different functions for different links. What am I doing wrong?
2. Where (and how :D) i can specify sorting function ? currently it uses '>' to compare things, but i need more complex condition.

[plumdog]

1. While LinkCol doesn't do this, if you subclass LinkCol I think you'll be able to do this. In particular, the url method is probably what you want to override.

Take a look at #112.

2. The actual sorting of items is not done by flask-table at all. It does prepare the URLs for the headers, but it is up to the view to take the column and sort direction and arrange the items accordingly.

You won't want to implement any classmethods for the sorting - in the sortable.py example, the Item class is just a stand-in for a database/ORM/datastore of some sort.

Take a look at #113.

[hackRussiaVKA]

These functions would not work if there were no LinkDeciderCol ?

@app.route('/item_a/<int:id>')
def single_item_a(id):
    element = Item.get_element_by_id(id)
    return '<h1>A: {}</h1><hr><small>id: {}</small>'.format(element.name, element.id)

@app.route('/item_b/<int:id>')
def single_item_b(id):
    element = Item.get_element_by_id(id)
    return '<h1>B: {}</h1><hr><small>id: {}</small>'.format(element.name, element.id)

It works exactly like I need, when you click 'Link A' we do some stuff with data, when 'Link B' another stuff. And i understand what is going on when we click DeciderCol - depending on data (item.name) it choose the endpoint function using usual conditions. But are they interrelated ?

[hackRussiaVKA]

I mean, it's normal ? it should work so ? I did the same things as you but in my case it works like every item.name is processed with 'single_item_a(b)' depending on what is written first in my routes.py. It just ignore another function ( but in your example it works.. And it seems to me that LinkDeciderCol is not the question.

[plumdog]

There's nothing special at all about those two functions - try running the example in #112 but remove the link_decider = LinkDeciderCol(...) from the table. Everything still works, they're just flask endpoints.

All that LinkDeciderCol does is pick which endpoint's URL should be rendered in the HTML. Remember that all Flask is doing here is returning some HTML.

As far as I can tell, what you're after is indeed #112 but without needing any of LinkDeciderCol. You should check to see if the HTML you're creating looks correct - specifically that it has the URLs you expect in it. Try making your own really simple version, make sure it works as you expect, then add in your specific code.

[hackRussiaVKA]

I did exactly what you just said, and find out that my URLs were bad. I'm idiot ) thanks, that's all, you really help me.