Reading databases
In this part, we'll write a separate script to read our SQLite database.
We want to show that our data is persistent between Python sessions, so we'll make a separate script to read the data from our database. In our directory, make a readdb.py file and open it in Spyder.
We'll now write our connection code, read our database table, and print it to the screen.
Using your lecture notes, write the code to do the following steps:
1) Import the sqlite3 library.
2) Connect to our "resultsdb.sqlite" database.
3) Get a cursor from the connection to interact with the database.
4) Execute the following Python to read the table rows and print the first value in each row:
for row in c.execute('SELECT * FROM Results ORDER BY burglaries'):.
print(row[0])
Test this works. You should see it returns the first value. Can you get it to print all the data? Can you get it to print it nicely?
One option for nice printing is to use the old-style string formating. Try this, which replaces the curly brackets/braces with the variables in the parentheses:
print(u'{0}, {1}'.format(row[0], row[1]))
Play around with reversing the numbers and see the effect. You can also remove the
numbers from the braces. Try it and see what happens. Try replacing the string with
alternative formating like '{1} burglaries have happened at {0}'.
Once you've done that, have a play with the other elements of SQLite (language guide; Python DB-API) and see what works.