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

Hello everyone,

How can I make my perl script upload a tab delimited file into a mysql table within the database? This table will be replaced entirely, everyday, by a new file. For example;

$foo = /home/foo.txt
$foo_table is in the $foo_database
Everyday $foo needs to replace $foo_table with fresh data.

Please note that my webhost will not grant my mysql user the *file* privelege access (saying it is too much of a security risk), so I cannot use 'mysqlimport' (gives me an access denied error).

If you need more info, please let me know.
Thank you.

Replies are listed 'Best First'.
Re: Perl and Mysql
by George_Sherston (Vicar) on Aug 10, 2001 at 03:43 UTC
    If your webhost permissions will let you use it, then the mysql LOAD DATA INFILE syntax might cut some corners for you (of course, you may already know this!). Once you've deleted the table (as above), I think you would need do no more than: 
    $dbs=$dbh->prepare("LOAD DATA INFILE 'foo.txt' INTO TABLE foo");
$dbs->execute;

    [download]
    Although I acknowledge that this isn't really a perl solution. But there is, as we are taught, more than one way to do it.

    § George Sherston
[reply]
[d/l]
      I believe that the LOAD DATA INFILE also needs the *file* privlege for the user.
[reply]
Re: Perl and Mysql
by aquacade (Scribe) on Aug 10, 2001 at 08:03 UTC
    Hope this complete example using DBI will assist you. I think this is enough framework for you to extend it to your specific needs. This is one of many simple ways to use DBI.

    To use this approach your webhost must have the DBI and DBD-MySQL Perl modules installed. You can write a test program to see if Perl can "find" these modules or call their customer service. 
    use strict;
use DBI;
print "$0\t\tStart: ",scalar localtime,"\n";
my $driver = "mysql";
my $database = "enter_dbname_here";
my $hostname = "enter_hostname_here";
my $user = "root";
my $password = "";
my $dsn = "DBI:$driver:$database:$hostname";
my $dbh = DBI->connect($dsn,$user,$password) or die "Error $DBI::errst
+r connecting to $dsn\n";
$dbh->do('DELETE FROM YOURFILE');

open(FILEIN,'c:\input.txt') or die "Can't open FILEIN file: $!\n";
my $sql=q/INSERT INTO YOURFILE
            (YOURFIELD_1, YOURFIELD_2, YOURFIELD_3)
            values (?,?,?)/;
my $sth=$dbh->prepare( $sql ) or die "Cannot prepare SQL: $DBI::errstr
+ \n";

my $line=<FILEIN>;    # Ignore Header if you have one
while ($line=<FILEIN>)
{
    chomp($line);
    my ($f1,$f2,$f3) = (split /\t/, $line);
    $f1 =~ s/"//g;    # Remove double quotes from input if needed
    $f1 =~ s/,//g;    # Remove commas from input if needed
    # tr/// may work better for your situation?
    # etc ...
    
    # These match the values (?,?,?) in the INSERT above
    $sth->bind_param(1,$f1);
    $sth->bind_param(2,$f2);
    $sth->bind_param(3,$f3);

    # Write to mySQL database
    $sth->execute or die "Cannot execute SQL: $DBI::errstr \n";
}

close(FILEIN);
$dbh->disconnect;
print "$0\t\t  End: ",scalar localtime,"\n";
__END__

    [download]

    ..:::::: aquacade ::::::..

[reply]
[d/l]
Re: Perl and Mysql
by ralphie (Friar) on Aug 10, 2001 at 03:27 UTC
    maybe a more illustrious monk had a different perspective, but you should probably open the external file like this ...
    open (FOO,'/home/foo.txt');
    it should be fairly straightforward simply to delete the current records in the table and repopulate it ... i.e.,
    $dbs=$dbh->prepare("delete from foo");
    $dbs->execute;

    and then simply loop through the <FOO> file splitting the line and inserting the new records, like

    while <FOO> {
  $line=<FOO>;
  @values=split $line;

    [download]

    (then do whatever your favorite flavor of record insertion is, such as..)
    ($val1,$val2,$val3 ...) = @values;
    $dbs=$dbh->prepare("insert into foo set val1=$val1,val2=$val2 ...);
    $dbs->execute;
    }
    you may want to take a look at my post on "mysql and postgresql tricks and gotchas", and the resultant thread, and do a search for mysql for perspectives. there are fancier ways of doing this, but this is one of the canonical approaches.
[reply]
[d/l]
Re: Perl and Mysql
by ralphie (Friar) on Aug 15, 2001 at 23:31 UTC
    i've just realized there is a much slicker way to repopulate the table each day given that the data is in the same format each time ... the sql statement

    "load data infile $foo into $foo_table fields terminated by '/t'"

    should be able to do the job without stepping through the input file.

    as a result, this should be as simple as preparing and executing the delete statement and then preparing and executing the statement above. i've never had occasion to execute it in this fashion from within perl, but it is one of the standard way to restore a data backup in mysql and works very smoothly from within the mysql client.

[reply]
[d/l]

Back to Seekers of Perl Wisdom