Updating or Inserting a database from a txt file

[Nik]

This is the best i can do with my games.pl script to load the game, description and times downloaded from a .txt file to database. Can you suggest something better?

my $dbh = DBI->connect('DBI:mysql:nikos_db', 'root', 'censored') or {R
+aiseError=>1};

#=====================================================================
+==========

my @row;
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc, ga
+mecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=? where gamenam
+e=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        my ($gamename, $gamedesc) = split /\t/;
        $insert->execute( $gamename, $gamedesc, 0 );
        if ($DBI::err)
        {
            print "Error on INSERT $gamename: $DBI::errstr\n";
            $update->execute( $gamedesc, $gamename );

            if($DBI::err)
            {
                print "Unable to UPDATE $gamename: $DBI::errstr\n";
            }
        }
    }
close (FILE);

I just need some way to update the mysql table if a gamename exists in the mayswl table games or insert a new one if it does not.

[ropey]

Firstly - I would suggest you remove the password on your post, secondly this is more a sql question, have you looked at using REPLACE instead of INSERT and UPDATE ?, this does depend if you have all the info you need. If not I personally would do a select on the table, then do a replace...FYI Replace will create a new record if one doesnt exist or replace the contents with what your passing if now

[cowboy]

I agree, REPLACE would be better.

If you want to use the fact the execute command failed to detect duplicate entries, you should capture the actual error code, rather than blindly trying an update on any error.

if ($DBI::err) {
  if ($DBI::err eq '1062') { # taken from mysqld_error.h
    # duplicate.. try an update
    unless ($update->execute( $gamedesc, $gamename )) {
      die sprintf('UPDATE failed: %s %s', $gamename, $DBI::errstr);
    }
  } 
  else {
    die sprintf('INSERT failed: %s %s', $gamename, $DBI::errstr);
  }
}

[mlh2003]

As ropey mentioned, remove the password in your post.

Also, the RaiseError attribute should be set in the connect statement (not after the ||):


my $dbh = DBI->connect(
    'DBI:mysql:nikos_db',
    'username',
    'password',
    {RaiseError=>1}
) || die $DBI::err;

Then if the connect fails, it will die with the error reported from the $DBI::err variable.

[Adrade]

Somewhat scared to enter this heated conversation, I'll try not to leave too large a footprint.


Read more... (1146 Bytes)
  -Adam

[Nik]

I wated to etst your script which seem very slick but i cant create a primary key.
i am tryign this:


$dbh->do( "CREATE TABLE games (gamename text, gamedesc text, gamecount
+er int, primary key(gamename))" );

Also can you please tell me what went wrong with my aproach even though i dont use a primary key? Here is the code!

my (@row, $gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT * FROM games WHERE gamename=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc, ga
+mecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+
+1 where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        ($gamename, $gamedesc) = split /\t/;
        $select->execute( $gamename );

        if ($select->rows) {
           $update->execute( $gamedesc, $gamecount, $gamename );
        }
        else {
           $insert->execute( $gamename, $gamedesc, 0 );
        }
    }
close (FILE);

[Adrade]

Hey Nik,

Sorry for taking so long to reply.

To create a table, try doing this:

$dbh->do("CREATE TABLE `games` (`gamename` TEXT, `gamedesc` TEXT, `gam
+ecount` TEXT );");

Then, create a unique index on the table - the 200 is a length that you can modify if you want.

$dbh->do("ALTER TABLE `games` ADD UNIQUE `UNQINDX` ( `gamename` ( 200 
+) )");


You should now successfully be able to use REPLACE on your table.

Taking a look at your code...

my (@row, $gamename, $gamedesc, $gamecount);

# selecting count(*) will run faster
my $select = $dbh->prepare( "SELECT count(*) FROM `games` WHERE `gamen
+ame`=?" );

# column `gamecount` was labeled as `gamecounter`
my $insert = $dbh->prepare( "INSERT INTO `games` (`gamename`, `gamedes
+c`, `gamecount`) VALUES (?, ?, ?)" );

my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=? 
+where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;

    while (<FILE>) {
        chomp;
        ($gamename, $gamedesc) = split /\t/;
        $select->execute( $gamename );

        if ($select->fetchrow_array) {

           # WARNING: $gamecount isn't defined!
           $update->execute( $gamedesc, $gamecount, $gamename );
        }
        else {
           $insert->execute( $gamename, $gamedesc, 0 );
        }
    }

close (FILE);

I'm a bit tired, but I hope this helps! Note my comments within the code.

The error in your message below indicates that the execute method isn't receiving the correct number of variables. Check to see that the file is tabbed correctly.

Best,
  -Adam

[Nik]

I had some small errors. Now the script is like this:

my ($gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT count(*) FROM games WHERE gamename
+=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc, ga
+mecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+
+1 where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        ($gamename, $gamedesc) = split /\t/;

        if ( $select->execute($gamename) ) {
           $update->execute( $gamedesc, $gamecount, $gamename );
        }
        else {
           $insert->execute( $gamename, $gamedesc, 0 );
        }
    }
close (FILE);

When it comes to updating it alwasy about the last game of the txt file and never the rest of them?
I cant see why!

[Animator]

Usless post: read at your own risk.

I should have know this node would get up-voted and front-paged...

Noone cares that this is the third time that the OP asks the same question, right?

Even when in the previous nodes working code (and good code) was suggested, clearly the OP decides not to use it and use yet another approach... Why is the biggest question I guess...

[DaWolf]

Actually the OP's previous posts seem different from this one. Previously he was looking for answers and now he's looking for improvements.

Have I missed something?

Nik, please be polite. Rudeness will only make things worse.

Hope you find your answers.

Regards,

[Nik]

Sorry man, i got carried away there but i become mad with the both of them and especially Animator on irc.
Yes iam trying to improve the script as you correctly noticed.

I changed it again. Here it is now:

my (@row, $gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT * FROM games WHERE gamename=$gamen
+ame" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc, ga
+mecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+
+1 where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        ($gamename, $gamedesc) = split /\t/;
        $select->execute( $gamename );

        if ($select->rows) {
           $update->execute( $gamedesc, $gamecount, $gamename );
        }
        else {
           $insert->execute( $gamename, $gamedesc, 0 );
        }
    }
close (FILE);

the descriptions.txt contains a blank line after each line of info. I dont want it to be loaded and also every time games.pl is runnign it gets doubled and doubles each time!

Anyone can see why?

[mlh2003]

I didn't see the previous post :(
Otherwise I probably would have said something similar...

[kwaping]

Check out DBD::AnyData to see if that would help.