Lesson 4 of 6
Run the Query from Python and Prepare Results
You modify your Python script to run the SQL query, fetch the results, and format them as a list of item names and revenue amounts.
9 minData Analysis
After this lesson, you will be able to run a SQL query from a Python script, fetch the results, and format them as a list of item names and revenue amounts ready for charting.
Run the SQL query from Python
You already have a Python script that loads sales.csv into sales.db. Now you will add code to run the same revenue query you used in the SQLite shell, but from Python. This lets you combine data loading and analysis in one place.
Create a new file called query_data.py. Start by importing sqlite3 and connecting to the database:
import sqlite3
conn = sqlite3.connect('sales.db')Next, write the SQL query as a Python string. Use the query from lesson 3 that calculates total revenue per item, ordered from highest to lowest:
query = '''
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY item
ORDER BY total_revenue DESC
'''The triple quotes let you write the SQL over several lines, just as you did in the shell. Now run the query and fetch all rows:
results = conn.execute(query).fetchall()fetchall() returns a list of tuples. Each tuple is one row from the result, with the item name first and the revenue second. Print the raw results to see what Python gives you:
print(results)Close the connection when you are done:
conn.close()The complete query_data.py looks like this:
import sqlite3
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()
print(results)
conn.close()Run the script from your terminal:
python query_data.pyYou will see a list of tuples, for example [('notebook', 10.5), ('apple', 6.0), ('pen', 3.2)]. The order depends on your data, but the item with the highest revenue should be first.
Format the results as two lists
The raw list of tuples is not convenient for charting. In the next lesson you will create a bar chart, and it will be easier if you have one list of item names and one list of revenue amounts.
Add code to split the tuples into two separate lists. First create empty lists:
items = []
revenues = []Then loop over the results and append each part to the right list:
for row in results:
items.append(row[0])
revenues.append(row[1])Now print both lists to check them:
print(items)
print(revenues)The complete script after this change is:
import sqlite3
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()
items = []
revenues = []
for row in results:
items.append(row[0])
revenues.append(row[1])
print(items)
print(revenues)
conn.close()Run it again. You should see two lists, for example ['notebook', 'apple', 'pen'] and [10.5, 6.0, 3.2]. The first item in items matches the first revenue in revenues, and so on.
Round the revenue values
The revenue values may have many decimal places, such as 10.500000000000002. For a clear chart, round each value to two decimal places. You can do this as you build the revenues list:
revenues.append(round(row[1], 2))The round function takes the number and the number of decimal places. Update the loop in your script:
for row in results:
items.append(row[0])
revenues.append(round(row[1], 2))The final query_data.py is:
import sqlite3
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()
items = []
revenues = []
for row in results:
items.append(row[0])
revenues.append(round(row[1], 2))
print(items)
print(revenues)
conn.close()Run the script one more time. The revenue list should now show clean numbers like [10.5, 6.0, 3.2].
Use the formatted lists later
You now have a Python script that runs the SQL query and produces two lists: items and revenues. These lists are ready to be passed to the charting code you will write in the next lesson. Keep query_data.py as it is, because the next lesson will build on it.
Key takeaways
- You can run a SQL query from Python using
conn.execute(query).fetchall(). - The result is a list of tuples, one tuple per row.
- Loop over the tuples to split them into separate lists of item names and revenue amounts.
- Round revenue values to two decimal places for cleaner output.
- The lists
itemsandrevenuesare ready for charting in the next lesson.
Try it
Modify query_data.py so that it also prints the total revenue across all items. Add a line that sums the values in the revenues list and prints the result with a label, for example Total revenue: 19.7. Run the script and check that the total matches the sum of the individual revenues you see. A good result is a script that prints the item list, the revenue list, and the total revenue, with the total rounded to two decimal places.
Official documentation
Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.