Database newbie - Sound Software Engineering practice for DB

[Otto Tronarp]

Hi,

I'm a complete database newbie, the little I know I learned the last
couple of hours when browsing the web, and have a couple of questions on
what sound software engineering practice is when it comes to using
databases.

Say that I want to make an app that calculates some statistics of soccer
matches in several different leagues. So I have the following SQL table:
CREATE TABLE matches (date DATE,
                      home_team VARCHAR(20), 
                      away_team VARCHAR(20), 
                      home_score INT, 
                      away_score INT, 
                      league_id VARCHAR(5));

(BTW: Is it smart to have have all the matches from all leagues in the
same table or should I have one matches table per league?)

So if I want all the matches where arsenal plays at home I could do
(with MySQLdb) something like this (untested code):

con = MySQLdb.connect()
cur = con.cursor()
cur.execute("""USE soccer""")
cur.execute("""SELECT * FROM matches WHERE home_team="Arsenal" """)
result = cur.fetchall()

But it doesn't look to good with all those "raw" SQL statements
cluttered everywhere in the code, if I change the structure of the table
I might need to change the code in a lot of places. So, I thought it was
a good idea to hide it in a class, maybe something like this.

class LeagueDB:
    def __init__ (self, con, dbname):
        self.con = con
        self.dbname = dbname
    def _newCursor(self):
        cur = self.con.cursor()
        #with MySQLdb I could use db.select_db but this
        #doesn't look like a part of the DB2 API so....
        cur.execute("""USE %s""" % self.dbname)
        return cur

    def getHomeMatches(self, team):
        cur = self._newCursor()
        cur.execute("""SELECT * from matches WHERE home_team="%s" """,
team)
	return cur


But often I want different constraints on the query, like only matches
before a specific date or in a specific time period, etc. I could do a
getHomeMatchesBeforeDate(self, team, date),
getHomeMatchesForTimePeriod(self, team, start_date, end_date) but it is
pretty clumsy so I want something like getHomeMatches(self, team,
constraints), any ideas how I could implement that in a smooth way? Or
am I completely off track and should use raw SQL queries all the time?
Or is there another way?

Further, returning the cursor and operate on the rows directly exposes
how the table is structured so I thought something like this would be
appropriate  

class LeagueDBResult:
    def __init__(self, cur):
        self.cur = cur
    
    def __iter__(self):
        return self
   
    def next(self):
        row = cur.fetchone()
        if not row:
            #maybe close cursor?
            raise StopIteration
            m = Match(row) #Constructs a match object from a row
        return m

and change the return in getHomeMatches to return LeagueDBResult(cur).

Am I on track here and this is "The Right Thing" to do or should I go
back to the drawing board?

Does anyone have any suggestion on a smal app that uses databases in The
Right Way that I could study and learn from? Or any other pointers
suggestions?


Regards,
Otto