Displaying Data
Loading data into a web page
Displaying a list of messages
Our home page is going to be a list
of all messages posted by users.
Write a query to get all messages
@website.route('/')
def index():
query_string = 'SELECT content FROM messages'
return render_template('index.html')
Find your home page route and create
a variable describing a query string.
Query the database
@website.route('/')
def index():
query_string = 'SELECT content FROM messages'
query_results = datamanager.query_db(query_string, [], one=False)
return render_template('index.html')
Use the data manager to send the query
to the database and fetch the results.
Print the query results to check them
@website.route('/')
def index():
query_string = 'SELECT content FROM messages'
query_results = datamanager.query_db(query_string, [], one=False)
for result in query_results:
print(result)
return render_template('index.html')
You can optionally print the results
of the query to the shell.
Pass the query results to the template
@website.route('/')
def index():
query_string = 'SELECT content FROM messages'
query_results = datamanager.query_db(query_string, [], one=False)
return render_template('index.html', messages=query_results)
Pass the query results into the template
under the variable name messages.
Loop over messages
<ol>
{% for message in messages %}
<li>{{ message['content'] }}</li>
{% endfor %}
</ol>
In your template, create a loop in the list
to create a list item for each message.
Check that it worked
View the home page in your browser
and check the messages are showing!
Combine user data
Our messages should also display the
username of the user who posted the message.
Modify the query
def index():
query_string = 'SELECT content, username FROM messages INNER JOIN users USING (user_id)'
query_results = datamanager.query_db(query_string, [], one=False)
for result in query_results:
print(result)
return render_template('index.html', messages=query_results)
To get the username for each message,
we need to join data from two tables.
Tidy up long queries
query_string = (
'SELECT content, username '
'FROM messages INNER JOIN users '
'USING (user_id)'
)
query_results = datamanager.query_db(query_string, [], one=False)
Once queries get longer than about 5 or 6 words,
you should tidy them by splitting over multiple lines.
Display username by message
{% for message in messages %}
<li>{{ message['username'] }}: {{ message['content'] }}</li>
{% endfor %}
Modify the index template message loop
to display the username beside the message.
Challenge: Display the message time
Modify the query to get the time the message
was posted, and modify the template to display it.
Order and optimise the results
Recent messages should be displayed first,
and only the last hundred messages should load.
Order messages by time posted
query_string = (
'SELECT content, username, time_created '
'FROM messages INNER JOIN users '
'USING (user_id) '
'ORDER BY time_created DESC'
)
Modify the query to order the results
based on the time_created value.
Limit to the last hundred messages
query_string = (
'SELECT content, username, time_created '
'FROM messages INNER JOIN users '
'USING (user_id) '
'ORDER BY time_created DESC '
'LIMIT 100'
)
We should limit the number of messages
to reduce strain on the website.
