|
Re: Mysql and Perl Module
by tospo (Hermit) on Jun 14, 2012 at 08:27 UTC
|
First of all: take the whole setting up out of the subroutine and into the main body of that script. That includes the DBI->connect stuff and preparing a statement handle.
This will make your script much faster because you don't keep reconnecting for every single row you want to insert. Same for the preparation of the statement handle. Prepare it once and pass the handle around (into the subroutine).
The next thing you should look up is how to use placeholders in your statement (questionmarks in the statement that are bound to values in the execute method.
Then there is the question of what it means if you have less than the full set of values in one row of your csv file. I guess those are "NULL" in your database, so you justneed to ensure that those values are "undef", which will insert them as NULLs.
| [reply] |
|
|
Hello Tospo,
the problem is it should be an module we can use for all of our customer scripts. The point is, that we need a module where I can only give a few arguments to it. Is it better to make another sub for the connection ?
| [reply] |
|
|
sub IntoDbImportCSV {
my(
$dbiconn, # dbi connection string
$colnames, # column names
$csvfile, # path to csv file
$csvargs, # Text::CSV->new options hash
) = @_;
my $dbh = DBI->connect(
$dbiconn,
undef, undef, { RaiseError => 1, PrintError => 1, },
);
open my($infh), '<', $csvfile ...
my $csv_in = ...
$dbh->begin_work;
my $sth = $dbh->prepare($sql);
while ( my $row = $csv_in->getline( $infh ) ) {
$sth->execute( @{ $row } );
}
$dbh->commit;
$dbh->disconnect;
}
IntoDbImportCSV(
'dbi:SQLite:dbname=temp.test.sqlite',
'/path/to/foo.cvs',
['ro', 'sham', 'bo' ],
{
quote_char => '"',
sep_char => ',',
allow_loose_escapes => 1,
empty_is_undef => 1,
binary => 1,
auto_diag => 1,
},
);
| [reply]
[d/l] |
|
|
The ideal situation for that scenario is that your module takes some sort of config file with the DSN for the databse connection so that you can ship it with a tailor-made config file for each of your customers.
Look at something like Config::General to handle such a config.
Alternatively, you could make the DSN an argument that needs to be supplied by the user when running this script.
If you have a lot of stuff to do with that database then it would also be worth exploring something like DBIx::Class to handle your database in a much more abstract way.
| [reply] |
Re: Mysql and Perl Module
by Anonymous Monk on Jun 14, 2012 at 08:58 UTC
|
my @Columns = qw/ ro sham bo /;
my $sql = sprintf "insert into (%s) values (%s) on duplicate key updat
+e ",
join( ',', map { $dbh->quote_identifier($_) } @Columns ),
join( ',', ( '?' ) x @Columns );
my $sth = $dbh->prepare($sql);
while ( my $row = $csv_in->getline( $infh ) ) {
$sth->execute( @{ $row } );
}
Where $csv_in is a Text::CSV object and $infh is a filehandle of your csv file
You could also read the file using using DBD::AnyData or DBD::CSV
| [reply]
[d/l] |
|
|
Hello,
one questins again. The $infh is only the path to my file? For example $infh = "/data1/konv/perl/test.csv";
| [reply] |
|
|
Hello, one questins again. The $infh is only the path to my file? For example $infh = "/data1/konv/perl/test.csv";
No, I said and $infh is a filehandle of your csv file
I gave an example in Re^3: Mysql and Perl Module, and Text::CSV shows an example in synopsis ($fh)
| [reply] |
|
|
Now I found my problem. I get an error with the "getline" command. "Can't call method "getline" on an undefined value"
D
| [reply] |
|
|
| [reply]
[d/l] |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Hello,
I don't understand the meaning of %s ? Is this a hash ? I'm getting my information from an array. My problem is I don't know how many columns I'm getting.
| [reply] |
|
|
Hello, I don't understand the meaning of %s ? Is this a hash ?
No, its a string, a format string
If you use perl -MO=B::Deparse,-p myfile.pl to see how perl parses my code, you can see how that format string is the first argument to the sprintf function
I'm getting my information from an array. My problem is I don't know how many columns I'm getting.
You don't need to know, the array/code I posted knows that information, and does the right thing -- I linked a runnable example ( Re: Open multiple file handles? ) a few minutes ago in Re^3: Mysql and Perl Module
| [reply] |
|
|
|
|
in sprintf, %s is a placeholder for a string, as in:
$a = "world";
sprintf "hello %s", $a; # results in "hello world"
| [reply]
[d/l] |
Re: Mysql and Perl Module
by tinita (Parson) on Jun 14, 2012 at 08:43 UTC
|
| [reply] |
|
|
Sorry for the cross post.
| [reply] |
|
|
| [reply] |
