perlinacan has asked for the wisdom of the Perl Monks concerning the following question:

In the following code I want do a score update 3 times each weekend. At the moment I end up with 3 copies of the info in the database, I need it to overwrite whats there or insert new stuff, can't get it to do it, am I better using update or replace ? I am using mySQL btw 
#!/usr/bin/perl -w
use strict;
use DBI;

#my fledgling attempt at a Fantasy AFL comp
#this file reads the downloaded weekly scores and inserts them
#into a datbase

#open the source file for weekly statistics
my $weekly_file = shift || 'weekly'; # you can specify a team file on 
+the command
                                     # line or take the default


#set variables for the database
my $dsn="dbi:mysql:database=fflnew";
my $user="root";
my $pass="";


my $dbh = DBI->connect($dsn, $user, $pass) ||
   die "Could not connect: $DBI::errstr\n";

   open WEEKLY, "< $weekly_file" or die "Cannot open $weekly_file for 
+reading:$!";
   while (<WEEKLY>) {


   my ($round,$playername,$kick,$handpass,$possess,$mark,$hitout,$tack
+le,$freefor,$freeagainst,$goal,$behind) = ("","","","","","","","",""
+,"","","");
      ($round,$playername,$kick,$handpass,$possess,$mark,$hitout,$tack
+le,$freefor,$freeagainst,$goal,$behind)= split( /,/ );


   my $sth = $dbh->prepare("INSERT into ffl_weekly (score_ID,round,pla
+yername,kick,handpass,posession,mark,hitout,tackle,freefor,freeagains
+t,goal,behind,total) values (NULL,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");   
+                                                                     
+                                                $sth->execute($round,
+$playername,$kick,$handpass,$possess,$mark,$hitout,$tackle,$freefor,$
+freeagainst,$goal,$behind);

#close and commit changes
}
close WEEKLY|| die "Cannot close $weekly_file, $!";
$dbh->disconnect();

[download]

Replies are listed 'Best First'.
Re: checking to do a inser or update
by MrCromeDome (Deacon) on Jun 07, 2002 at 04:05 UTC
    Well, as they always say, TIMTOWTDI ;) Personally, if you don't want to keep old scores lying around, I would always do a delete statement, then an insert to add in the new stores. IMHO, it's a bit less work (for you and the database). I mean, you could check to see if the update processed successfully, and if not, do an insert, but this requires a lot less thought ;)

    You might also wish to explicitly issue a commit at the end of your script. While I haven't brushed up on the DBI documentation lately, assuming that the disconnect will do a commit for you just strikes me as a bad idea (that next rollback could be brutal. . . ). For safety sake, you might wish to add a $dbh->commit() before the disconnect.

    And (just a writeup tip) I hope the connection information for MySQL is bogus. If not, I hope you change the username and password for database access before exposing your database to the world ;) Kinda related to that though, you might wish to check out How (Not) To Ask A Question by jeffa for that (not giving us your real username and password) and other good writeup tips.

    Hope this was of some help :) Good luck!
    MrCromeDome

[reply]

      With regards to commit, by default DBI defaults to AutoCommit on, which means that DBI will commit after each statement.

      See the DBI docs, in particular here.

      If you've turned AutoCommit off, it is a bad idea to rely on disconnect to commit for you, the docs say the transaction behaviour of disconnect is undefined...

[reply]
      Well, he isn't checking the return value of the execute, nor does he have RaiseError to be true (and by default, AutoCommit is set to true *shiver*). So I guess he doesn't really care about his data and what happens if something fails.

      Abigail

[reply]
Re: checking to do a insert or update
by graff (Chancellor) on Jun 07, 2002 at 04:48 UTC
    If you can identify a "primary key" value in the data that you're putting into the table -- e.g. if the combination of "round" and "playername" should never repeat in the table -- then you could start with a query that pulls out all the existing "round,playername" tuples in the table, and store those as keys to a hash (values assigned to those keys won't matter, just so long as the keys are made to exist).

    Then, use the "prepare" method in DBI to create two SQL statement handles -- one for update, and one for insert.

    Now, as you go through the "weekly_file", check each "round,playername" value to see if it exists as a hash key from that initial query -- if it's there, use the update statement handle, otherwise, use the insert.

    I wrote a wrapper for DBI that makes this sort of thing fairly simple to code in Perl (the pod even gives an example a bit like this) -- I'm not claiming it's the "best" or "optimal" solution, but it works for me, and saves me a lot of time when writing Perl code for SQL operations like this.

[reply]
      Another similar variation, which I typically use, is to check whether the record exists (either using a hash, as described, or by doing a SELECT if there's a reasonable possibility of the database having been changed), then call a stock INSERT to create a dummy record if it's not already there, and finally run a standard UPDATE regardless of the initial search's result.

      Less efficient in execution, but easier to work with since I only need to keep a single UPDATE statement current rather than both an INSERT and an UPDATE.

[reply]

Back to Seekers of Perl Wisdom