Skip to content

Lesson 6 of 6

Assemble the Analysis into a Small Report

You combine the question, SQL query, result table, and chart into a single short document or script output that answers your question.

7 minData Analysis

After this lesson, you will be able to combine your question, SQL query, result table, and chart into one short report that answers your question clearly.

Start from the pieces you already have

You have built several pieces in the earlier lessons. You have a question in definitions.txt, a database in sales.db, a query in query_data.py, and a chart saved as chart.html. Now you will bring them together into one report.

A report is a short document that shows your question, how you answered it, and the answer. It helps someone else see what you did and trust your result. You will write a Python script that reads the pieces and writes a single HTML file. HTML is the language used to make web pages. Your browser can open an HTML file and show the report with headings, text, and the chart.

Write the report script

Create a new file called make_report.py. Start by importing the modules you need. You will use sqlite3 to get the data, and csv to read the definitions file.

import sqlite3
import csv

Next, read the question and definitions from definitions.txt. This file has one term and its definition per line, separated by a colon. For example, a line might be item: the thing sold. You will read each line and split it at the first colon.

with open('definitions.txt', 'r') as f:
    definitions = []
    for line in f:
        line = line.strip()
        if line:
            term, definition = line.split(':', 1)
            definitions.append((term.strip(), definition.strip()))

Now get the data from SQLite. Use the same query as in query_data.py. Fetch all rows and store the item names and rounded revenues in lists.

conn = sqlite3.connect('sales.db')
query = '''
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY item
ORDER BY total_revenue DESC
'''
results = conn.execute(query).fetchall()
conn.close()

items = []
revenues = []
for row in results:
    items.append(row[0])
    revenues.append(round(row[1], 2))

You also need the chart. The chart is saved as chart.html from the previous lesson. Read its contents into a string so you can include it in the report.

with open('chart.html', 'r') as f:
    chart_svg = f.read()

Now build the report as an HTML string. Use a simple structure: a heading, the question, the definitions, the SQL query, the result table, and the chart. You will write the SQL query as text inside a <pre> tag, which keeps spaces and line breaks. The result table uses <table>, <tr> for rows, <th> for header cells, and <td> for data cells.

html_parts = []
html_parts.append('<html><head><title>Sales Analysis Report</title></head><body>')
html_parts.append('<h1>Sales Analysis Report</h1>')
html_parts.append('<h2>Question</h2>')
html_parts.append('<p>Which item has the highest total revenue?</p>')
html_parts.append('<h2>Definitions</h2>')
html_parts.append('<ul>')
for term, definition in definitions:
    html_parts.append(f'<li><strong>{term}</strong>: {definition}</li>')
html_parts.append('</ul>')
html_parts.append('<h2>SQL Query</h2>')
html_parts.append(f'<pre>{query}</pre>')
html_parts.append('<h2>Results</h2>')
html_parts.append('<table border="1">')
html_parts.append('<tr><th>Item</th><th>Total Revenue</th></tr>')
for i in range(len(items)):
    html_parts.append(f'<tr><td>{items[i]}</td><td>{revenues[i]}</td></tr>')
html_parts.append('</table>')
html_parts.append('<h2>Chart</h2>')
html_parts.append(chart_svg)
html_parts.append('</body></html>')

report_html = '\n'.join(html_parts)

Finally, save the report to a file called report.html and print a message.

with open('report.html', 'w') as f:
    f.write(report_html)

print('Report saved to report.html')

The complete script looks like this.

import sqlite3
import csv

with open('definitions.txt', 'r') as f:
    definitions = []
    for line in f:
        line = line.strip()
        if line:
            term, definition = line.split(':', 1)
            definitions.append((term.strip(), definition.strip()))

conn = sqlite3.connect('sales.db')
query = '''
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY item
ORDER BY total_revenue DESC
'''
results = conn.execute(query).fetchall()
conn.close()

items = []
revenues = []
for row in results:
    items.append(row[0])
    revenues.append(round(row[1], 2))

with open('chart.html', 'r') as f:
    chart_svg = f.read()

html_parts = []
html_parts.append('<html><head><title>Sales Analysis Report</title></head><body>')
html_parts.append('<h1>Sales Analysis Report</h1>')
html_parts.append('<h2>Question</h2>')
html_parts.append('<p>Which item has the highest total revenue?</p>')
html_parts.append('<h2>Definitions</h2>')
html_parts.append('<ul>')
for term, definition in definitions:
    html_parts.append(f'<li><strong>{term}</strong>: {definition}</li>')
html_parts.append('</ul>')
html_parts.append('<h2>SQL Query</h2>')
html_parts.append(f'<pre>{query}</pre>')
html_parts.append('<h2>Results</h2>')
html_parts.append('<table border="1">')
html_parts.append('<tr><th>Item</th><th>Total Revenue</th></tr>')
for i in range(len(items)):
    html_parts.append(f'<tr><td>{items[i]}</td><td>{revenues[i]}</td></tr>')
html_parts.append('</table>')
html_parts.append('<h2>Chart</h2>')
html_parts.append(chart_svg)
html_parts.append('</body></html>')

report_html = '\n'.join(html_parts)

with open('report.html', 'w') as f:
    f.write(report_html)

print('Report saved to report.html')

Run the script from your terminal.

python make_report.py

Open report.html in your browser. You should see the question, definitions, SQL query, a table of items and revenues, and the bar chart. Check that the item with the highest revenue appears first in the table and has the longest bar in the chart.

Make the report answer your question

The report you just made uses a fixed question: "Which item has the highest total revenue?" But your own question may be different. You can change the question text in the script to match what you wrote in definitions.txt. The SQL query and chart already show the data needed to answer that question. If your question asks about a different measure, such as total quantity, you would need to change the query and the chart code accordingly. For this lesson, keep the revenue question so the pieces work together.

The report is a single file that contains everything. You can share it with someone else, and they can see your question, your method, and your answer without needing to run any code.

Key takeaways

  • A report combines your question, method, and answer in one place.
  • You can use Python to read your data and chart, then write an HTML file that shows them together.
  • The SQL query in the report should be the exact query that produced the results.
  • Check that the table and chart agree: the highest revenue item should be first in the table and have the longest bar.
  • Your report is a single HTML file you can open in a browser and share.

Try it

Modify make_report.py so that the report also includes the total revenue across all items, printed as a sentence below the table. Add a line that sums the revenues list and writes a paragraph like Total revenue: 19.7. Run the script and open report.html in your browser. Check that the total shown matches the sum of the values in the table. A good result is a report that shows the question, definitions, query, table, chart, and a correct total revenue sentence.

Official documentation

Ready to use this in a job?

Rolebound applies to matching roles for you.

Start now

Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.

    Cookies on Rolebound

    We use necessary cookies to sign you in and keep the site working — those cannot be turned off. We would also like to set analytics and advertising cookies so we can see which pages help people get interviews and which adverts brought them here. Nothing optional is set unless you say yes, and you can change your mind at any time from “Cookie settings” in the footer. Read our Privacy Policy.

    Assemble the Analysis into a Small Report | Ask and Answer a Data Question with SQL and Python | Rolebound