CS105 LAB 8 - Python 4

1. Download me! Place mydata.db in the folder e.g z:\db\

>>> import sqlite3

>>> db = sqlite3.connect(name of database file)

EXAMPLE :
>>> db = sqlite3.connect('z:\db\mydata.db')
db is the database handle

>>> cursor = db.cursor()
this is the cursor ............

>>> cursor.execute(command) # replace with SQL command string

>>> yr = '2002'

 >>> query = '''SELECT M.name,M.year 
           	FROM Movie M, Person P 
	   	WHERE M.id=P.id AND year = ?;''' 

The above query is a parameterised query and can be executed as follows:

>>> cursor.execute(query, [yr])

>>> cursor.fetchone()

for tuple in cursor:
     #code to process tuple goes here

>>> db.rollback()

>>> db.commit()

>>> db.close()

<file-handle>.close()

Now, practice writing in a file:

>>> fh = open('z:/db/temp.txt', 'w')
>>> fh.write("Files are easy.\n")
>>> fh.write("I am a brand new file!\n")
>>> fh.close()

Check out the new file!!! Why is the second line of the file second and not first?

1. Download me! Place mydata.db in the folder e.g z:\db\

2. Open a new module file called getMovies.py and begin with the following lines:

>>> import sqlite3

>>> db = sqlite3.connect('z:\db\mydata.db')

3. Initialize a cursor object

>>> cursor = db.cursor()

You are connected and you have a cursor object!! You are ready to perform queries!

4. Now, we perform the necessary actions to print one by one the attributes in the result of the query. Write a query to find the name and duration of all Steven Spielberg films ?

5. Execute it:

>>> cursor.execute(query)

6. Print results on the screen:

Result:

Jaws ... 124
Raiders of the Lost Ark ... 115
E.T. the Extra-Terrestrial ... 115
Indiana Jones and the Temple of Doom ... 118
Indiana Jones and the Last Crusade ... 127
Jurassic Park ... 127
Schindler's List ... 197
Lost World: Jurassic Park, The ... 129
Saving Private Ryan ... 170
Minority Report ... 145
Catch Me If You Can ... 141
Indiana Jones and the Kingdom of the Crystal Skull ... 122
War of the Worlds ... 116

How does this work??

7. Now let's modify the file to allow the user to enter the name of the director. Use a parameterized query!

8. We will now write the result to a file! Let 's also modify it to print a message if the director is not found.

>>> fh = open('z:\db\spielberg.txt', 'w')

9. Write using a for loop!

Hint: First create the line you want to write (i.e. line = str(tuple[0]) +...+...+"\n" and then use fh.write to write it!)

10. Close the file handle!!!!!!!! (otherwise nothing will be written in the file!)