Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Updating or Inserting a database from a txt file

by Nik
on May 10, 2005 at 13:21 UTC ( #455559=perlquestion: print w/replies, xml ) Need Help??

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

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.

Replies are listed 'Best First'.
Re: Updating or Inserting a database from a txt file
by ropey (Hermit) on May 10, 2005 at 13:38 UTC

    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

      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); } }
Re: Updating or Inserting a database from a txt file
by mlh2003 (Scribe) on May 10, 2005 at 13:50 UTC
    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.
    _______
    Code is untested unless explicitly stated
    mlh2003
Re: Updating or Inserting a database from a txt file
by Adrade (Pilgrim) on May 11, 2005 at 00:37 UTC
    Somewhat scared to enter this heated conversation, I'll try not to leave too large a footprint.

      -Adam
      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);
        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
        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!
Re: Updating or Inserting a database from a txt file
by Animator (Hermit) on May 10, 2005 at 13:50 UTC

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

      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,
        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?
      I didn't see the previous post :(
      Otherwise I probably would have said something similar...
      _______
      Code is untested unless explicitly stated
      mlh2003
    A reply falls below the community's threshold of quality. You may see it by logging in.
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Updating or Inserting a database from a txt file
by kwaping (Priest) on May 10, 2005 at 19:42 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://455559]
Approved by fauria
Front-paged by fauria
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2021-03-08 06:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (123 votes). Check out past polls.

    Notices?