COMMAND LINE AND ZIP FILE

• (Unix only) Use pwd to see the "present working directory"
• Use ls (Mac) or dir (Windows) to see a listing of the files and directories in this directory
• Use cd to move into a directory you see listed in the present working directory (for example cd Downloads)
• Use cd .. to move into the "parent" directory of the one you are in (this will take you back to the parent of Downlaods
• Use cd /Users/[yourhomedir]/Desktop to move into the Desktop directory
• Use cd ../Downloads to move from the Desktop directory to the Downloads directory
• Use cd (Mac) or cd %HOMEPATH% (Windows) to move back into the home directory

SQLITE COMMANDS

If you unzipped your project directory in the Downloads folder, the commands are:

cd
cd Downloads
cd python_data
cd session_02_files

cd %HOMEPATH%
cd Downloads
cd python_data
cd session_02_files

If any command returns an error, check the spelling of your command. If the 3rd command returns an error, check that the session_02_files folder was unzipped to your Downloads directory (and that you've spelled it correctly).

sqlite3

sqlite>

sqlite>  .quit

sqlite> .open session_2.db
sqlite> .tables
ad_buys              revenue              students
ad_companies         student_status       user
companyrev           student_status_orig  user_classes

If you don't see any tables listed, then you have either misspelled the name of the .db file, or you're in the wrong directory. First, delete the empty file that you created so you don't get confused.

sqlite> .shell cd

sqlite> .shell pwd

You can use .shell commands to move into the directory where the .db file can be found.
Please note that SQL queries end in a semicolon (;) and SQLite3 commands such as .tables and .schema do not: adding a semicolon to a "dot" command (e.g. .schema;) causes SQLite to ignore the command; omitting a semicolon from a query causes SQLite to allow "multi-line" queries, which means it will wait until it sees the semicolon.

sqlite> .header on
sqlite> .mode column

sqlite> .schema revenue

SQL COMMANDS

sqlite> SELECT * FROM revenue;

Please note that SQL queries end in a semicolon (;) and SQLite3 commands such as .tables and .schema do not: adding a semicolon to a "dot" command (e.g. .schema; causes SQLite to ignore the command; omitting a semicolon from a query causes SQLite to allow "multi-line" queries, which means it will wait until it sees the semicolon. If you see a continuation line indicator ( ...> ) this means that you have started an SQL client and the client is waiting for more SQL (queries can be expressed in more than one line, so the client needs the semicolon to understand the end of the query). If you're done, simply type the semicolon and hit [Enter].

sqlite> CREATE TABLE test (name TEXT, years INT, revenue REAL);
sqlite> .schema test
CREATE TABLE test (name TEXT, years INT, revenue REAL);

sqlite> DROP TABLE test;

sqlite> CREATE TABLE test (name TEXT, years INT, revenue REAL);

sqlite> INSERT INTO test VALUES ('Joe', 23, 23.95);

Note that TEXT column values must have quotes around them and INT and REAL (floating-point) column values must not.

sqlite> SELECT * FROM test;

sqlite> DELETE FROM test;

sqlite> DROP TABLE test;

Next, use the up arrow to recover and re-execute the CREATE TABLE and follow it up imediately with the DROP TABLE IF EXISTS -- to see how easy it is to create and drop tables.

sqlite> DROP TABLE IF EXISTS test;
sqlite> CREATE TABLE test (name TEXT, years INT, revenue REAL);
sqlite> DROP TABLE IF EXISTS test;
sqlite> CREATE TABLE test (name TEXT, years INT, revenue REAL);

Remember, you don't have to retype an old command! Just use up arrow and down arrow to recall previously executed commands.

Please KEEP YOUR SQLite SESSION OPEN FOR THE FOLLOWING Python exercises.

LAB 1

Note: if this is a new session, please follow the directions at the start of these exercises and the first few exercises to travel to the directory where session_2.db can be found, to verify that it is in your current location, and to use .open to open the file. (Don't forget that if you are in the wrong location or misspell the filename you will create a new, empty database file.)

.mode column
.headers on

.tables

ad_buys         companyrev      student_status  user
ad_companies    revenue         students        user_classes

(please note that your table listing may be different than shown above, but should at least show several tables rather than no tables) If there are no tables listed, use .quit to leave sqlite3, use ls or dir to see that you have created a new, empty .db file (you can check the size in your Windows Explorer or Mac Finder view of the file), and then delete this file. Then return to the start of these exercises and follow directions to find the existing session_2.db file.

planet (a TEXT field)
mass (a REAL (or floating-point) field)
distance (an INT field)

When done, issue a .schema query to see the query and structure of the table reflected back to you.

Mercury   .33    58
Venus    4.87   108
Earth    5.98   150

The sqlite prompt should return without error after each INSERT query.

Select all rows and columns from the planets table.

planet      mass        distance
----------  ----------  ----------
Mercury     0.33        58
Venus       4.87        108
Earth       5.98        150

Select the planet and and distance columns for all planets further than 100 (km) from the sun.

planet      distance
----------  ----------
Venus       108
Earth       150

Please keep this sqlite session open, or make note of where your session_2.db file is located so we can access the data you created in the next lab.

DATABASE ACCESS FROM PYTHON

NOTE THAT when opening data files from the exercise files or notebooks, any filename referenced from those exercises should be preceded with ../. (If you create a script in the same directory as the data file, you would not include this.) NOTE ALSO THAT if you are using Jupyter Notebook, if you wish to call exit() from a notebook, please use sys.exit() (requires import sys); if a strange error occurs, it may be because Jupyter retains variables from all executed cells. To reset the notebook, click 'Restart Kernel' (the circular arrow) -- this will not undo any changes made.

Close the database connection with conn.close() when finished.

We're using a LIMIT 1 to pull just one row from the table, which will have all columns.

Make sure to call conn.commit() (where conn is the connection object) to see results. Also remember to close the database connection when done. After running, return to your SQLite3 session and perform a SELECT * query so you can see the new row.

If you don't see the change, make sure you executed conn.commit() after executing the insert.

CSV: review

LAB 2

Remember that from exercise files, any data file will be located in the parent directory. So the correct location for this week's database file is session_2.db.

SELECT name FROM sqlite_master WHERE type= "table"

('ad_companies',)
('ad_buys',)
('students',)
('revenue',)
('student_status',)
('user',)
('user_classes',)
('companyrev',)
('planets',)

Select all columns and rows from the 'planets' table (created in the previous lab), then use 'for' to loop over and print each row of results.

('Mercury', '0.33', '58')
('Venus', '4.87', '108')
('Earth', '5.98', '150')

Select all columns from the user table where user_id is equal to 2. Use .fetchone() to retreieve the row.

(2, 'joe', 'pass', 'Joe', 'Wilson')

Select all columns from the students table where state is equal to NJ. Use .fetchall() to retreieve the rows.

('jb23', '115 Karas Dr.', 'Jersey City', 'NJ', '07127')
('jb29', '119 Xylon Dr.', 'Jersey Cit', 'NJ', '07127')

After doing a select * query on the students table, use the cursor .description attribute to retrive a tuple of tuples with tables names:

( ('id', None, None, None, None, None, None),
  ('address', None, None, None, None, None, None),
  ('city', None, None, None, None, None, None),
  ('state', None, None, None, None, None, None),
  ('zip', None, None, None, None, None, None)     )

Next, use a list comprehension to retrieve a list of names:

['id', 'address', 'city', 'state', 'zip']

Insert a new row into the planets table with the following data:

Mars    64.20    228

After committing the insert with conn.commit(), issue a SELECT query to confirm that the row has been inserted.

Select all rows from the planets table and insert them into a new file ../planets.csv file that you open for writing and write row-by row using a csv.writer object. (Make sure to include the newline='' argument when opening the file, and to close the write file at the end.)

Mercury,0.33,58
Venus,4.87,108
Earth,5.98,150
Mars,64.2,228

JSON / MULTIDIMENSIONAL STRUCTURES

Now open this file in Python and load the JSON into a Python object with json.load(). Loop through the list of dicts and print each dict.

Loop through each dict in the list of dicts and print just the 'date' column.

Now open this file in Python and load the JSON into a Python object with json.load(). Loop through the dict of dicts and print each key and associated value in the dict.

Loop through each key and value in the dict of dicts and from each dict value print the 'mean_temp' value.

Now open the file in your Python code and load it into an object with json.load(). See if you can reach the repeating dict of dicts using a single statement of chained dict and/or list subscripts. Print this 'inner' structure (dict of dicts) using json.dumps() with the indent=4 parameter argument.

Now build a dict from the header row and first data row with keys that match the header line. Tip: use dict(zip(headers, data_row)) to build the dict: zip() takes two lists and "zips" them together like a zipper to pair up the first items in each list, the second items in each list, etc. zip(headers, data_row) will pair up the column heads with data from the row, and passing this to dict() will transform to a dictionary.

Now initialize a new empty list before the loop begins. As you build each dict, append the dict to the list. Print the resulting structure once completed. Finally, use print(json.dumps(lod, indent=4)) to print the structure in a friendly format.

Now initialize a new empty dict before the loop begins. As you build each dict, add the "inner" dict to the "outer" dict: make the company name the key in the "outer" dict, and the value the "inner" dict. Print the resulting structure once completed. Finally, use print(json.dumps(dod, indent=4)) to print the structure in a friendly ("pretty") format.

LAB 3

Access the value 10.6 and print it.

10.6

Using a loop, print the state names one after the other.

Indiana
Georgia
Alabama

Using a loop, print the years one after the other.

1816
1788
1819

In a single statement, print 1819.

1819

Using a loop, print the populations, one after the other.

6.7
10.6
4.9

Print the value 9.7 in the below structure:

9.7

Using a loop, print the values 1816, 1788 and 1819 from the below structure:

1816
1788
1819

Using a loop nested within another 'for' loop, print each state, print and all 3 population values. For clarity, try indenting each population value, and printing an extra blank line after each state.

Indiana
  6.7
  6.5
  6.1

Georgia
  10.6
  9.7
  8.2

Alabama
  4.9
  4.8
  4.4