Building MVP with React Flask Full Stack – Day 9 – Sorting with ORM

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 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.

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 list is not ordered.

It means filter function works, but I pass wrong parameters.

I change parameters according to some article I found on internet.

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

Another error 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. 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.

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 tag with closing tag.

{% endif %}

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

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.

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 connection config then.

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

It was not helpful. I got an 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 few things to make it work.

  1. Delete all() function from ORM query. Pandas expects query object, not 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.

How to do sticky footer with Bootstrap 4?

Sometimes, when your page content does not fill out 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 following HTML code.

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

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

Unfortunately, it won’t work on 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 sustainable way if I ever want to finish my MVP.

Thanks for reading and see you in the next post.

Share it!


Want to support me in my journey 🚀? You can send me a donation below 👇.

Donate with Paypal

Leave a Reply

avatar

Want to support me in my journey 🚀? You can send me a donation below 👇.

Donate with Paypal

Made with 🔥 by Viet Phan 2018