Please note: notations (as used in the Introduction to Python course) are not required. IMPORTANT note on using the sqlite client The sqlite client is convenient for viewing table structure and other tasks, but it is not required. When using it, please keep in mind that your Terminal / Command Prompt session must be in the same directory as your database file, or you will create a new, empty file of the same name as the one you attempted to open. Please see the Challenge Exercises for steps needed to move your command line session to the proper data folder for these projects.

Use the cursor .description attribute to retrieve the column names and build that into the first row of the CSV file. The result should be a .csv file with the same column heads and data as the database table. We will use session_2.db as database and read from a table named revenue, and write to revenue_from_db.csv. Please make sure to close the filehandle as well as the database connection.

It shouldn't be necessary to develop a list of lists for writing with csv.writerows(); you can simply write to the CSV as you are iterating through the database rows. In fact, since database result sets are tuple rows and a tuple works in the same way as a list, you can write each row to the csv file with .writerow()

connect to db
create cursor object
execute sql DROP TABLE IF EXISTS query
execute sql CREATE TABLE query
commit the changes to the database

print all tables in the database (this is for testing only -- see code below)

Please note that the "precip" column has a "T" value instead of a numeric value in some rows. Please account for this by inserting the Python value None (DO NOT USE STRING 'None' BUT RATHER THE VALUE None (no quotes) instead of the T. Of course this is the SQL to CSV assignment in reverse. Start by copying in the DROP TABLE IF EXISTS and CREATE TABLE code from the prior assignment, and test it to see that it works (you'll be able to tell that it worked if the script runs without error - if there was any problem you'd see an exception). Next, compose your INSERT query string and include parameter arguments (i.e., with ? tokens in place of values). You'll execute this query over and over but with different values inserted, so the query string should be assigned before the loop over the CSV data begins. Then open the CSV file using the csv module, next() over the header and loop through the CSV data rows, executing the INSERT query with each row's data. Don't forget to commit() at the end in order to see the rows written to the table.

connect to db
create cursor object
execute sql DROP TABLE IF EXISTS query
execute sql CREATE TABLE query

initialize an INSERT query (use ? parameter arguments)

open CSV file
pass filehandle to csv reader
next() over the header row

'for' loop over the reader
    assign the 4 desired column values to variables:
    date, temp, precip, events
    if the precip value is 'T', reassign as None (no quotes)
    execute the INSERT query with this row's data

commit to finalize the inserts

Finally, you'll want to issue a SELECT query and loop over the results to confirm that the rows have been inserted. Ideally, you would use the sqlite3 client to view the rows, since this gives independent confirmation. You must confirm that you have replaced T 'precip' values with None. The first row with a 'precip' value of T is on 1/9/16. . At that row in the table, the 'precip' value should be empty or NULL. (In the sqlite3 client it will show as an empty cell; selecting using Python should show None.)

Start with the same DROP TABLE / CREATE TABLE code you used in the previous assignment to drop and then create the weather_newyork table. Also as in the previous assignment, initialize an INSERT query with parameter arguments. Open weather_newyork_dod.json and use the json module to load the file as a Python dict of dicts. Loop through the dict of dicts (where the key is a string and the value is an "inner" dict of values), and insert each "inner" dict as a row in the database by executing the INSERT query with the "inner" dict's values. Use subscripts to grab each dict value for assembly into the table. Please note that the "precip" key sometimes has a "T" value instead of a numeric value. Please account for this by inserting a None (DO NOT USE STRING 'None' BUT RATHER THE VALUE None (no quotes) Python value instead of the T. Don't forget to commit() the database connectino at the end in order to see the rows written to the table.

connect to db
create cursor object
execute sql DROP TABLE IF EXISTS query
execute sql CREATE TABLE query

initialize an INSERT query (use ? parameter arguments)

open JSON file
load JSON file as a Python object (a dict of dicts)

'for' loop over the dict keys
    use dict key to get "inner" dict associated with that key
    retrieve values from "inner" dict and assemble into correct order for SQL table
    execute INSERT query with reordered values

commit to finalize the inserts

A dict of dicts must start with an empty dict, initialized before the loop begins. We will call this the "outer" dict, because we will be adding dicts to this dict. The source data is the database, so we will be SELECTing and looping through the rows of data. For each row of data we will want to build a complete "inner" dictionary that matches the row. For example, here is a sample "inner" dictionary from the first row of the data:

Understand however that the above dict will be built from row data inside the database result set loop. In other words, as we loop through the database results, we build a dict based on the row values inside the loop, and then we add it to the "outer" dictionary using the company name as the key and the "inner" dict as the value.

(The above assumes your dict of dicts variable is named outer_dict and your open file for writing is a variable named wfh.)

connect to db
create cursor object

initialize an empty "outer" dict (will be built as a "dict of dicts")

execute the query to select all columns from the revenue table

'for' loop over database rows
    assign each column value to a variable:  name, state, cost

    build a new "inner" dict with key/value pairs
    matching the database row values

    add a new key/value pair to the "outer" dict:
    name as key and "inner" dict as value

open a new file for writing:  revenue.json

use the json.dump() method to write the dict of dicts to
revenue.json (use the indent=5 parameter of
json.dump() to write the structure in a human-readable format; otherwise it will
be written as a block of text without syntactic spaces)

How many keys are in the 'routes' dict of dicts, and In the 'routes' dict of dicts, what are the ip addresses that have a 'routeType' of 'iBGP'? Looking down to where the ip addresses start, and scrolling down, you'll see first that the 'routes' key points to a dict of dicts in which each key is an ip address, and the value for each key is a dict containing information in key/value pairs. Note that each ip dict has a 'routeType' key and that the value is usually 'eBGP'. There are 3 ip addresses that are associated with dicts where this value is 'iBGP'. First, we need to 'drill down' to the dict of dicts associated with 'routes'. As you can see, this is a dict nested in a series of other dicts (and one list). You should use multiple subscripts to get to this dict. As your eye travels down the structure toward 'routes', ask the question "what key or index do I need to get to the next level?"

(where obj is the object returned from json.load()).

Continue on from there until you have retrieved the 'routes' dict. If you put this dict into len() you should find that it has 1346 keys. Next, we need to loop through the key/value pairs in the dict associated with 'routes'. Each key is an ip address, and each value is a dict. Now where the dict has a 'routeType' key with value 'iBGP', print the ip. You should see 3 of them.
Besides 'routes', what are the keys in the dict associated with 'default', and what are the values associated with those keys? As above, you'll 'drill down', this time not quite as far, to the dict associated with 'default'. Loop through this dict and print out the key and value for each, EXCEPT for 'routes'. Of course, if you print the value associated with 'routs', you'll get a lot of output! So we'll omit this key/value pair from the output.

sql2csv(query, conn)

(The \r\n above represents a newline - csv by default writes in the "Excel" dialect, which uses Windows-style newlines - however, these will also work well on Mac and Linux systems.)

sql2json(query, conn, format='lod', primary_key=None)

This function may write to a dict of dicts (if the argument format='lod') or a list of dicts (if format='dod'). In either case, an "inner" dict must be constructed where the dict's keys are the names of the column heads, and the dict's values are the values for each row.

As you loop through the result set, you'll have a tuple of values for each row. Here is where you will build the "inner" dict for adding to the dict of dicts or list of dicts.

The above assumes that headers is your result set column heads retrieved from the cursor.description attribute, and result_row is a tuple of values retrieved in each iteration of 'for' looping over the result set. Please study the above example before using it - it's an extremely useful idiom for "marrying" lists of keys to lists of values in a dictionary, and it will serve you in the the future as well.

csv2sql(filename, conn, table)

This more straightforward assignment asks you to simply loop through a CSV file row-by-row, and for each row of data in the CSV file execute an INSERT query to the database and table.

Note this form of INSERT INTO does not use column names. This will work as long as values are inserted in the same order as found in the file.

', '.join('??') treats the '??' string as a sequence of characters, "joining" them with ', '. The net effect is to insert the ', ' string between each character, in the same way that .join() inserts a string in between each list item in the usual usage. Don't forget to conn.commit() your inserts or they won't show up in the database immediately or at all.

json2sql(filename, conn, table)

Note this form of INSERT INTO does not use column names. This will work as long as values are inserted in the same order as found in the file.

', '.join('??') treats the '??' string as a sequence of characters, "joining" them with ', '. The net effect is to insert the ', ' string between each character, in the same way that .join() inserts a string in between each list item in the usual usage. In order to figure out how many keys are in an "inner" dict, I suggest you access the first dict by index rather than by looping.

Where dod is your dict of dicts read from json.

If each "inner" dict has 3 keys, the above code builds the string shown. (See the discussion of .join() above to understand the above code.)

The above assumes inner_dict is each "inner" dict in the dict of dicts. Don't forget to conn.commit() your inserts or they won't show up in the database immediately or at all.