Lab 9

Our second to last tutorial

Schedule

Lab 7 solution
Review
Lab 9

Lab 7 solution


						def move_cursor(f):
						    '''Given an open file f, reads past its three-line header.

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    '''
						    NUM_HEADER_LINES = 3
						    for _ in range(NUM_HEADER_LINES):
							f.readline()

						def list_of_list(f):
						    '''Reads the contents of alkaline_metals.txt and returns it in a list
						    of lists, with each inner list containing the name, atomic number, and
						    atomic weight for an element. (Hint: Use string.split.)

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint: 
							Use split() to create a list of each line
						    '''
						    move_cursor(f)
						    new_list = []
						    for line in f:
							lines = line.split()
							new_list.append(lines)
						    return new_list

						def sorted_atomic_weights(f):
						    '''Returns a list containing the three highest atomic weights(represented as
							floats), in descending order, for all metals.
						
						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint:
							Use split() to create a list of each line
							Use sort() function to sort elements of a list
							Be mindful of the types of data
							Once you assign melements to a list; you need to use float() function
							to convert str type to float type. Else, elements won't get sorted
						    '''
						    move_cursor(f)
						    atom_weight = []
						    for line in f:
							atoms = line.split()
							atom_weight.append(float(atoms[2]))
						    atom_weight.sort(reverse = True)
						    return atom_weight[0:3]  

						def create_dict(f):
						    '''Returns a dictionary where key is the name of the metal and value
						    is the atomic weight.

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint:
							Multiple ways to solve this.
							One way:
							Create a list first using list_of_list(f)
							Then assign first element as key and third element as value
						    '''
						    move_cursor(f)
						    new_list = []
						    new_dict = {}
						    for line in f:
							lines = line.split()
							new_dict[lines[0]] = lines[2]
							new_list.append(lines)    
						    '''
						    Another solution:
						    move_cursor(f)
						    new_list = []
						    new_dict = {}
						    for line in f:
							lines = line.split()
							new_list.append(lines)
						    li_item = 0
						    for li_item in new_list:
							new_dict[li_item[0]] = li_item[2]
						    '''
						    return new_dict

						if __name__ == '__main__':
						    # put this file in your directory along with this lab file
						    filename = 'alkaline_metals.txt'

						    # Call the functions and print the results here.
						    # For example, this will print all the lines of the file other than the
						    # header.
						    with open(filename) as f:
							move_cursor(f)
							for line in f:
								print(line.strip())
							print('--------------------------------')
							
						    with open(filename) as f:
							print(list_of_list(f))
							print('--------------------------------')
							
						    with open(filename) as f:
							print(sorted_atomic_weights(f))
							print('--------------------------------')
							
						    with open(filename) as f:
							print(create_dict(f))
							print('--------------------------------')

Common Mistakes


						def list_of_list(f):
						    '''Reads the contents of alkaline_metals.txt and returns it in a list
						    of lists, with each inner list containing the name, atomic number, and
						    atomic weight for an element. (Hint: Use string.split.)

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint: 
							Use split() to create a list of each line
						    '''
						    new_list = []
						    for line in f:
							lines = line.split()
							new_list.append(lines)
						    return new_list

						with open(filename) as f:
						    move_cursor(f)
						    print(list_of_list(f))
						    print('--------------------------------')

Common Mistakes


						def list_of_list(f):
						    '''Reads the contents of alkaline_metals.txt and returns it in a list
						    of lists, with each inner list containing the name, atomic number, and
						    atomic weight for an element. (Hint: Use string.split.)

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint: 
							Use split() to create a list of each line
							'''
						    move_cursor(f)
						    new_list = []
						    for line in f:
							lines = line.split()
							new_list.append(lines)
						    return new_list

						with open(filename) as f:
						    list_of_list(f)
						    for line in f:
						        print(line.strip())
						    print('--------------------------------')

Common Mistakes


						def list_of_list(f):
						    '''Reads the contents of alkaline_metals.txt and returns it in a list
						    of lists, with each inner list containing the name, atomic number, and
						    atomic weight for an element. (Hint: Use string.split.)

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint: 
							Use split() to create a list of each line
							'''
						    move_cursor(f)
						    new_list = []
						    for line in f:
							lines = line.split()
							new_list.append(lines)
						    return new_list

						with open(filename) as f:
						    list_of_list(f)
						    for line if f:
						        print(line.strip())
						    print('--------------------------------')

Common Mistakes


						def move_cursor(f):
						    '''Given an open file f, reads past its three-line header.

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    '''
						    NUM_HEADER_LINES = 3
						    for _ in range(NUM_HEADER_LINES):
							f.readline()
						
						return_value = move_cursor(f)

Common Mistakes


						def list_of_list(f):
						    '''Reads the contents of alkaline_metals.txt and returns it in a list
						    of lists, with each inner list containing the name, atomic number, and
						    atomic weight for an element. (Hint: Use string.split.)

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint: 
							Use split() to create a list of each line
							'''
						    move_cursor(f)
						    new_list = []
						    for line in f:
							lines = line.split(" ")
							new_list.append(lines)
						    return new_list


						['metal1':'2.345\n']

Common Mistakes


						def list_of_list(f):
						    '''Reads the contents of alkaline_metals.txt and returns it in a list
						    of lists, with each inner list containing the name, atomic number, and
						    atomic weight for an element. (Hint: Use string.split.)

						    Args:
							f: a freshly opened file in the format of the file alkaline_metals.txt
						    Hint: 
							Use split() to create a list of each line
							'''
							with open(filename) as f:
							    f.readline()
							    f.readline()
							    f.readline()
								
						            new_list = []
						            for line in f:
							        lines = line.split()
							        new_list.append(lines)
						            return new_list

						with open(filename) as f:
						    print(list_of_list(f))
						    print('--------------------------------')

Review

Working with database

Python
SQL

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

Library

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

Library -> connection

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

Library -> connection -> cursor

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

Library -> connection -> cursor -> execute

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

Library -> connection

How to make a connection


						import sqlite3

						con = sqlite3.connect('name_of_your_database')

						cur = con.cursor()

						cur.execute('SQL commands')

						cur.close()

						con.close()

Library

What if ...


							import sqlite3

							con = sqlite3.connect('name_of_your_database')

							cur = con.cursor()

							cur.execute('SQL commands')

							con.close()

							cur.close()

Error!


						    sqlite3.ProgrammingError: Cannot operate on a closed database.

SQLite

Modify
Query

Modifing the database


							DROP TABLE IF EXISTS db_name


							cur.execute('DROP TABLE IF EXISTS PopByRegion')


							CREATE TABLE table_name(column_name column_type, column_name column_type, ...)


							cur.execute('CREATE TABLE PopByRegion(Region TEXT, Population INTEGER)')


							INSERT INTO table_name VALUES(value, value, ...)


							cur.execute('INSERT INTO PopByRegion VALUES("Central Africa", 330993)')
							cur.execute('INSERT INTO PopByRegion VALUES (?, ?)', ("Japan", 100562))


							UPDATE table_name SET column_name = new_value WHERE some_condition


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


							DELETE FROM table_name WHERE some_condition


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

What's more


							con.commit()


								cur.close()
								con.commit()
								con.close()


								con.commit()
								cur.close()
								con.close()


								cur.close()
								con.close()
								con.commit()

Querying the database


							SELECT column_name
							FROM table_name


							cur.execute('SELECT Region FROM PopByRegion')


							SELECT column_name, column_name, ...
							FROM table_name


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


							SELECT *
							FROM table_name


							cur.execute('SELECT * FROM PopByRegion')

Querying the database


							SELECT * / column_name / column_name, column_name, ...
							FROM table_name
							ORDER BY column_name


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


							SELECT * / column_name / column_name, column_name, ...
							FROM table_name
							ORDER BY column_name ASC / DESC


							cur.execute('SELECT Region, Population FROM PopByRegion ORDER BY Region ACS')
							cur.execute('SELECT Region, Population FROM PopByRegion ORDER BY Region DESC')


							SELECT * / column_name / column_name, column_name, ...
							FROM table_name
							WHERE conditions
							ORDER BY column_name, column_name, ...


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

Querying the database


							SELECT * / column_name / column_name, column_name, ...
							FROM table_name
							WHERE condition
							ORDER BY column_name, column_name, ... none / ASC / DESC


							cur.execute('SELECT Region FROM PopByRegion WHERE Population > 1000000 AND Region < "L")

Relational operators
Logical operators


							cur.execute('SELECT ? FROM PopByRegion WHERE ? > ? AND Region < "L", ("Region", "Population", 100000))

What's more


							cur.fetchone()	# gives you a tuple
							cur.fetchall()  # gives you a list of tuples

							for row in cur:
							    print(row)

Lab 9

create_table_region(db, f)

check lab 7

create_table_country(db, f)

check lab 7

print_tables(db)

print each row line by line

run_query(db, query)

close the connection

query (3 in total)

use region table for the third query

call run_query (for each query)

make the connection and close the connection in each function
check code handouts for lecture 9
what if a line is too long?
how to open a db file?