# Retrieve data from SQLite queries with Python
Since we already know how to connect to SQLite and how to execute queries, all that's left is making use of that cursor that we get back.
When retrieving data we don't need to use the context manager because we're not going to commit or rollback anything. So we'll just use the connection to execute the query:
cursor = connection.execute("SELECT * FROM entries;")
connection.execute returns the cursor that it created implicitly to run the query. It is the same thing as doing this:
cursor = connection.cursor()
cursor.execute("SELECT * FROM entries;")
Just a little bit shorter!
Now that our cursor has executed the query, it is pointing at the very first row of the results returned by the database.
We just need to use those results. We can:
- Fetch one row;
- Fetch all rows;
- Iterate over the cursor as if it were a list (using a for loop).
So with the cursor we can do:
cursor.fetchone()- gives you a tuple with the contents of the first row in the results.cursor.fetchall()- gives you a list of tuples with all the contents of the results.- Something like this:
for row in cursor:
print(row) # tuple of one row
# Creating our data retrieval function
Let's put this code into a function so that we can use it from within app.py. At the moment, we have this function that uses the Python list as a data store:
def get_entries():
return entries
We're going to change it to this:
def get_entries():
return connection.execute("SELECT * FROM entries;")
This will return the cursor, and then we can iterate over the cursor inside app.py. This is the code we have in app.py at the moment:
def view_entries(entries):
for entry in entries:
print(f"{entry['date']}\n{entry['content']}\n\n")
But the cursor will use indexes instead of keys to access values. We must change the function to this:
def view_entries(entries):
for entry in entries:
print(f"{entry[1]}\n{entry[0]}\n\n")
That is because the column 0 is the content, and column 1 is the date. Make sure to swap these numbers around if your CREATE TABLE statement has a different column order.
# Making SQLite give us named results
Especially if you're working with tables that have many columns, being able to access row values by name (e.g. entry['date'] instead of entry[1]) can be super helpful.
SQLite allows us to do this, and we only have to tell it that we want to do that.
Whenever SQLite executes a query and builds a result set, it puts each resultant row of data into the cursor as a tuple. But we can tell it to create an object instead.
If we tell it to create an object, we can make that object allow us to access each column by its name instead of by index.
Fortunately, we don't have to code these objects ourselves. SQLite comes with one that does it all for us. To tell SQLite to use it when fetching results, all we have to do is this[1]:
connection = sqlite3.connect("data.db")
connection.row_factory = sqlite3.Row
What that'll do is for each row of data, SQLite will put the data into a sqlite3.Row object that allows us to access the data by the name of the column. Now we can go back to app.py and revert the change we made just a moment ago:
def view_entries(entries):
for entry in entries:
print(f"{entry['date']}\n{entry['content']}\n\n")
You can still access the data by index if you prefer, but using sqlite3.Row you can also access the data by name. It's up to you which one to use, but there's no point to using sqlite3.Row if you always access data by index. Just something to remember!