Writing databases
In this practical, we'll have a quick go at writing and reading a SQLite database.
Make yourself a new directory for the practical. We'll assume m:\GEOG5790M\practcial5. In there, make a createdb.py file and open it in Spyder.
We'll now write our connection code, make our database table, and fill it with information.
Using your lecture notes, write the code to do the following steps:
1) Import the sqlite3 library.
2) Connect to a database called "resultsdb.sqlite" (run this code, and look in your directory to see what it creates. If for any reason you ever need to delete the database manually, just delete the file).
3) Get a cursor from the connection to interact with the database.
4) Execute the following SQL to create a table: CREATE TABLE Results (address text, burglaries integer). Note that
you can only run this once – so if you want to test this works and keep running this script, you'll need to either
delete the database file or comment out this line.
5) Run the following SQL to insert data: INSERT INTO Results VALUES ('Queen Vic',2)
6) Commit the changes.
7) Close the connection.
If you run this you should see nothing if it works (other than the file being created) – it should only come back if there are problems. You'll find that once the table is created, running it again will produce an error, as the table can't be created again. You can always comment out that line after it has run once if you want to just insert data.
This is all very well and good, but it would be nice to check the magic has actually worked. Go on to Part Two where we'll write a script to check this.
- This page
- Reading databases <-- next