Database newbie - Sound Software Engineering practice for DB

[Justin Shaw]

It looks like you are on the right track.  Keep asking yourself these
questions.  I have interjected my thoughts throughout.
Justin

"Otto Tronarp" <otttr440 at student.liu.se> wrote in message
news:mailman.1043194598.32555.python-list at python.org...
> 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?)

Use the minimal number of tables such that no data is duplicated.   Filters
are easier to write than joins.  You have it right here.

<...snip...>

>
> 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

I prefer to have the cursor passed in as an argument.  That way you can let
the database handle all of the permissions.  The users get assigned a
connection when they log in and you never have to worry about tracking them.

>
> 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?

Make the common thing easy and the uncommon possible.  I do this by writing
the common querries as you have with a couple of extra defaulted arguments:
fields='*' and where='1'.  Then your query becomes something like

sql = 'select %s from TABLE where %s' % (fields, where)

I am working on a python/MySQL spelling application currently that three
data layers and a GUI layer.  The data layers are:
1. database later (MySQL)
2. DB module -- class reprensentation of the database tables
3. SmartRow module -- class represention of the rows and relationships.

The application only interacts with the SmartRow module.  You can browse the
csv on our sourceforge project if you want to have a look.
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/spellquest/spellquest/spellqu
est/

>... Or
> am I completely off track and should use raw SQL queries all the time?

I think you are on the right track.  Try to keep the SQL isolated to a
module or two.

> Or is there another way?

I've seen a few projects that are supposed to automatically write the
interface to your db but I haven't tried the.  Check out the daily python
url (linked on python.org), I think I saw them there.

Good luck and let me know if you have any good ideas for SpellQuest