----PYTHON ONLY----

sqlite3: Creating and Using a Database

In this lab task you will create a simple working example of a relational database using sqlite3. Operating from the Python3 command line prompt you will execute sqlite statements against the database created.

Task Goals

Write a program that creates a database using sqlite3. Your program will create a table and insert data. Following a successful database creation, a series of sqlite statements against the database created will be executed.

Database Creation:

For a working example, create a database named yourlastname.db. For example, Ann SanMateo would create a database named sanmateo.db.

Create a table with 2 columns. The first column contains the name of a region and the second column contains the population of the region resulting in a row of the table representing a region and its population. Name this two column table PopByRegion that will store region names as strings in the Region column and population as integers in the Population column.

Region TEXT

Population INTEGER

Data Entry:

The database created will contain the following data.

Region	Population
Central Africa	330,993
Southeastern Africa	743,112
Japan	100,562

Statement Session:

>>> import sqlite3

>>> con = sqlite3.connect('yourlastname.db')

>>> cur = con.cursor()

>>> cur.execute('SELECT Region, Population FROM PopByRegion')

>>> cur.fetchall()

>>> cur.execute('SELECT Region, Population FROM PopByRegion ORDER by Region')

>>> cur.fetchall()

>>> cur.execute('SELECT Region FROM PopByRegion')

>>> cur.fetchall()

>>> cur.execute ('SELECT Region FROM PopbyRegion WHERE Population > 400000')

>>> cur.fetchall()

>>> cur.execute('SELECT * FROM PopByRegion WHERE Region = "Japan"')

>>> cur.fetchone()

>>> cur.execute('''UPDATE PopByRegion SET Population = 100600 WHERE Region = "Japan"''')

>>> cur.execute('SELECT * FROM PopByRegion WHERE Region = "Japan"')

>>> cur.fetchone()

>>> cur.execute('DELETE FROM PopByRegion WHERE Region < "S"')

>>> cur.execute('SELECT * FROM PopByRegion')

>>> cur.fetchall()

>>> cur.close()

>>> con.close()

Deliverable: yournameLab10.py Your source code solution and a copy of the command line sqlite statement session pasted into your source submission file. Be sure to comment out your run so that your .py file will still run in the grader test bed.

Test Run Requirements:

Here are some other tips and requirements:

1. Be sure to name the database that you create yourlastname.db for your lastname.

2. Run the sqlite3 statements against the database you create in the order presented in the session sequence shown above.

Here is a sample run:

>>> import sqlite3

>>> con = sqlite3.connect('sanmateo.db')

>>> cur = con.cursor()

>>> cur.execute('SELECT Region, Population FROM PopByRegion')

>>> cur.fetchall()

[('Central Africa', 330993), ('Southeastern Africa', 743112), ('Japan', 100562)]

...