Tuesday, October 28, 2014

Database from Ch 19 of Teach Yourself Python

Teach Yourself Python is a really good introduction to Python--my favorite out of the three introductory books I own. Chapter 19, on using databases, references a video game database. I couldn't find the database online, so below is the code I used to make it.

# -*- coding: utf-8 -*-
'''Code to construct database from Chapter 19 of 
Teach Yourself Python in 24 Hours by Katie Cunningham'''
import sqlite3

error = None
conn = sqlite3.connect('videoGames.db')
cursor = conn.cursor()
  
#create games table
sqlCreate = '''CREATE TABLE games 
               (title text, rating text, system text, year int)'''
try:
    cursor.execute(sqlCreate)
except sqlite3.OperationalError as e: 
    error = e

#add data, if database doesn't already exist
if not error:
    print "Successfully created database...populating table"
    gameDataAll=[('Tales of the Abyss', 'T', '3DS', 2011),
              ('Adventure Time', 'E10+', '3DS', 2012),
              ('Hollywood Crimes', 'T', '3DS', 2011),
              ('Forza Motorsport 4', 'E', '360', 2011),
              ('Sonic Generations', 'E', '360', 2011),
              ('Forza Horizon', 'T', '360', 2012),
              ('ZhuZhu Pets', 'E', 'Wii', 2012)];
              
    for gameData in gameDataAll:
        print gameData 
        sqlAdd = '''INSERT INTO games (title, rating, system, year)
                    VALUES (:title, :rating, :system, :year)'''
        cursor.execute(sqlAdd, {'title': gameData[0], 'rating': gameData[1],
                                'system': gameData[2], 'year':gameData[3]})   
    conn.commit()  #commit changes otherwise they will not be saved
else:
    print "Didn't create database because", error
    sqlShow = '''SELECT * FROM games'''
    selectResults = cursor.execute(sqlShow)
    allGames = selectResults.fetchall()
    print "\nThe games table contains the following rows:"
    for game in allGames:
        print game
          
#close shop
cursor.close()
conn.close()
Code highlighting done at highlight.me.