Building MVP with Flask Day 9 – Sorting with ORM Queries

Hi everyone.

This is the last post before I go into complete silent development mode. Today I struggled a bit with ORM, again.

Since I was able to query all products into a nice list and display it with Jinja2 template tags, query results were ordered by database primary key.

It is not useful if you want to see, what products are most profitable.

Using order_by() to sort a query

So let's figure out, how to sort with SQLAlchemy ORM.

I will try this syntax first because it is how I expect it to be.

all().order_by(ProductL.term.desc())

But, this syntax throws an error.

AttributeError: 'list' object has no attribute ‘order_by'

After googling a bit, I find out about filter function. So I add filter function to my query.

.filter().order_by(ProductL.term.desc())

There is no error now, but the list is not ordered. It means filter function works, but I pass the wrong parameters.

I change parameters according to some articles I found on the internet.

.filter().order_by('term desc')

Another error is thrown out.

sqlalchemy.exc.CompileError: 
Can't resolve label reference for ORDER BY / GROUP BY. 
Textual SQL expression 'term desc' should be explicitly declared as text('term desc’)

Screw random articles on the internet, let's look into filter() documentation directly.

This should work.

.order_by(ProductL.term)

Nope. The list is still not sorted.

I am out of ideas. Maybe adding all() will help.

.order_by(ProductL.term).all()

Yeah, I think I forgot to add function, that tells SQLAlchemy the sorting direction. Let's fix it now.

.order_by(ProductLeg.term.desc()).all()

Finally, my list is sorted as expected.

[wbcr_html_snippet id="629" title="adsense-inarticle"]

Using if-else condition in Jinja2 template

Next, I want to try Jinja2 conditional tags for the first time in my life. I hope it will go smoother than my attempt to sort data.

{% if productL.max_amt == 0 %}
    <div>True</div>
{% else %}
    <div>False</div>

Not internal server error again!

jinja2.exceptions.TemplateSyntaxError: Encountered unknown tag 'endfor’. 
You probably made a nesting mistake. Jinja is expecting this tag, but currently looking for 'endif’. 
The innermost block that needs to be closed is 'if’.

Seems I forgot to close if a tag with a closing tag.

{% endif %}

If else logic works and now I can display content in the Jinja2 template conditionally.

Formatting numbers to thousands in Jinja2

Next, I want to display numbers with spaces between thousands. Right now, thousands are comma-separated.

I will achieve that with this syntax.

{{'{:,d}'.format(productL["amt"]).replace(",", " ")}}

This code will find all commas and replace them with blank space.

Pass query result to Pandas data frame

What I need to do now, is convert my SQLAlchemy query results into pandas data frames, so I can do some math magic.

Let's try to pass query result to pandas and see what happens.

df = pd.read_sql(query)

But, this will throw an error.

TypeError: read_sql() missing 1 required positional argument: ‘con'

Apparently, read_sql needs a connection string, because it looks directly into DB.

I will add the connection config then.

df = pd.read_sql(query, con = db.session.bind)

It was not helpful. I got another error.

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object

Damn, let's try another syntax for connection string.

conn = db.engine.connect().connection
df = pd.read_sql(query, con= conn)

No luck either.

pandas.io.sql.DatabaseError: Execution failed on sql:
operation parameter must be str

An hour later, after browsing Stackoverflow, I had to change a few things to make it work.

1. Delete all() function from ORM query. Pandas expects a query object, not a result object.
2. Change connection string back to my first attempt.

df = pd.read_sql(
query, con= db.session.bind
)

I am too exhausted to do any more database related development, so I do one design-related change before I end this session.

The question is.

Sticky footer in Bootstrap 4

How to do sticky footer with Bootstrap 4?

Sometimes, when your page content does not fill out the whole viewport vertically, your footer will just float in the middle of the screen. It looks ugly and I want my footer to stay at the bottom of the screen.

First I need to upgrade Bootstrap to version 4.1. And then change the HTML structure of my body content to reflect the following HTML code.

<div class="d-flex flex-column wrapper">
    <nav>
    </nav>
    <main class="flex-fill">
    </main>
    <footer>
    </footer>
</div>

Thanks to the flexbox, the main body container will stretch itself to the maximum height of your screen.

Unfortunately, it won't work on an older browser. But I don't care. Older browsers should be prohibited.

Finally, add some CSS for maximum screen height calculation.

body, .wrapper {
   min-height:100vh;
}

That's it. My footer now sticks at the bottom of my screen.

I am too exhausted to do anything else.

I think I lost the drive I had at the beginning of the project. Instead of solving architecture challenges, I am now solving design and syntax issues.

I need to take a pause and develop my MVP privately without time pressure. It is only a sustainable way if I ever want to finish my MVP.

Thanks for reading and see you in the next post.