A recent task

• I needed to create a database for DSCI 201
  • I had a series of CSV files containing data.
  • These were generated in excel, but I could have done so with python too.
    • CardTable.csv

      • ID,Name,HP,Attack,Defense,Sp. Atk,Sp. Def,Speed,Generation,Legendary
        1,Bulbasaur,45,49,49,65,65,45,1,FALSE
        2,Ivysaur,60,62,63,80,80,60,1,FALSE
        

    • TypeTable.csv

      • TypeID, TypeName
        1,Bug
        2,Dark
        3,Dragon
        

    • CardTypeTable.csv

      • CardID,TypeID
        14,1
        15,1
        16,1
        17,1
        

  • I wanted to build a script that would completely rebuild the database upon need.
  • So I created the following.
• A file called build.sql
  • This built the database and the tables.
  • It is in SQL so there is no real need to review this here.
  • The key is to run it you need to
  • This needs to be run when the database is rebuilt.
• Next, I needed to convert the CSV files to SQL to insert them into the db.
  • There is a chance, pretty good one actually that MySQL can read csv files.
  • But I don't know how
  • And I want an example.
  • So I built a few simple python scripts to do this.

  • #!/usr/bin/python
    
    # need to use the correct database
    print "USE PokemonDB;"
    
    # SQL command to insert the data
    print "INSERT INTO TypeTable (TypeID, TypeName) VALUES"
    
    dbfile = open("TypeTable.csv");
    
    # I need this so 
    #    I can skip the first line 
    #    I can print comma after all but last line.
    lineNo = 0;
    for line in dbfile:
       if lineNo == 0:
          lineNo +=1;
       else: 
          # add a comma after a line has been printed, not the first line
          if lineNo > 1:
               print ","
          (tid,name) = line.split(',');
          print '('+tid+',"'+name.strip()+'")',
          lineNo +=1;
    
    # print a semicolon after the last line
    print ";"
    

  • The other two are like this, they just open different fields
  • These are not clean, but they are a quick and dirty solution.
  • This produces

  • USE PokemonDB;
    INSERT INTO TypeTable (TypeID, TypeName) VALUES
    (0,"") ,
    (1,"Bug") ,
    (2,"Dark") ,
    ...
    (19,"Water") ;
    

• I'm lucky that mysql supports the script command
  • So PokemonDB.sql is simply

  • source build.sql
    source cards.sql
    source types.sql
    source cardType.sql
    

• Finally, I need to tie it all together.
  • Bash is the answer here.
  • I need to run several different programs.
  • I could do this with make, but again, I need an example

  • #!/bin/bash
    
    python cardMaker.py > cards.sql
    python cardTypeMaker.py > cardType.sql
    python typeMaker.py > types.sql
    
    mysql -u dsciAdmin -p -s < PokymonDB.sql
    

  • There is a security piece to this, but it is a sql script and I don't really want to share it here.