Recently I was building a simple web blog using Flask and PostgreSQL and I stumbled upon something which seems to me like a weird behavior.
@app.route('/')
def index():
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM posts")
posts = cur.fetchall()
print(posts, flush=True)
conn.close()
return render_template('index.html', posts=posts)
In this function I'm trying to get all the entries from my database using Psycopg2. In the documentation, there is written that fetchall() method should return the list of tuples:
Fetch all (remaining) rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch.
However, what I'm getting back is a list of lists. The print() function is giving me an output like this:
[[1, datetime.datetime(2022, 1, 24, 23, 25, 39, 559908, tzinfo=datetime.timezone.utc), 'Hello', 'First post.']] for this schema:
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
created TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
content TEXT NOT NULL
);
Regarding the second part of my question; I don't understand how Jinja handles this iterable when I pass it to the render_template() function. What I particularly mean is the fact, that when it iterates through this data in a for cycle it is able to assess specific attribute by using its name like if it was a Python Dictionary. Let me demonstrate what I mean:
{% for post in posts %}
<a href="{{ url_for('post', post_id=post['id']) }}">
<h2>{{ post['title'] }}</h2>
</a>
<span class="badge badge-primary">{{ post['created'] }}</span>
<hr>
{% endfor %}
In these two {{ post['title'] }} and {{ post['created'] }} blocks it is able to access the data by the name of their attributes in DB, which doesn't make any sense if I pass it just the list of lists.
Can somebody please explain to me what is going on in the background and therefore make my confusion go away?
Thank you.
