Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Mysql and Perl Module

by endymion (Acolyte)
on Jun 14, 2012 at 07:36 UTC ( #976138=perlquestion: print w/replies, xml ) Need Help??
endymion has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, first at all, great job you are doing here ! And here is my problem/question: We are creating a little mysqlserver for our tables. Now I have to write a module to get information into the table. I have already done an insert, but I also need an update. The problem is I don't know the colums sometimes. For example sometimes I have 2 columns, then 3........ Here is my example module (the table is column tab1, column tab2, the data is an 1000 rows csv file 6666666:Y)
# 1. INSERT INTO TABLE # 2. SELECT FROM TABLE sub insert_tab { $db = @_[0]; $table = @_[1]; @TAB = split (/ /,@_[2]); @VALUES = split (/ /,@_[3]); @VAL = (); foreach $i (@VALUES) { push (@VAL,"\'".$i."\'"); } $fieldlist = join (",", @TAB); $fieldvalues = join (",", @VAL); $con = "DBI:mysql:$db"; $user = "root"; $passwort = "pass"; $dbh = DBI->connect("$con","$user","$passwort") || die "DB connect +ion not made: $DBI_errstr"; $sql = qq{ insert into $table ($fieldlist) values ($fieldvalues) o +n duplicate key update }; #$sql = qq{ insert into $table ($fieldlist) values ($fieldvalues) +}; $sth = $dbh->prepare($sql); $sth->execute(); $sth->finish(); $dbh->disconnect(); } return 1;
I hope you can help me.

Replies are listed 'Best First'.
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.

      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 ?

        See this example program Re: Open multiple file handles?

        You can easily create a module with a function like this

        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, }, );
        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.
Re: Mysql and Perl Module
by Anonymous Monk on Jun 14, 2012 at 08:58 UTC

    This is the idiom or recipe

    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

      Hello, one questins again. The $infh is only the path to my file? For example $infh = "/data1/konv/perl/test.csv";

        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)

      Now I found my problem. I get an error with the "getline" command. "Can't call method "getline" on an undefined value" D

        :D Yup, if you follow Re^3: Mysql and Perl Module, you'll see ... for  my $csv_in = , you'll have to fill in the missing $csvargs bits

      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.

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

        in sprintf, %s is a placeholder for a string, as in:
        $a = "world"; sprintf "hello %s", $a; # results in "hello world"
Re: Mysql and Perl Module
by tinita (Parson) on Jun 14, 2012 at 08:43 UTC
      Sorry for the cross post.

        Cross-posting is allowed , but you should post a link/url. To quote davido on cross-posting

        Please make sure when you cross-post that you identify such, so that people here (or there) don't end up working on a problem that is solved. Identifying cross-posting will promote collaborative progress too. Getting a bunch of people doing research for you in parallel without knowing about each others' progress defeats the collaboration aspect of public forums.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://976138]
Approved by kcott
Front-paged by Corion
Corion idly wonders if there is a way to produce ordered .yml files. I want to accept YAML for a configuration file format, but I also want to generate (for debugging/start) a sample configuration file from the current configuration.
[Corion]: Ideally, that configuration file would have some order of the keys, but I'm not sure whether/how YAML supports ordered output.
[Corion]: Basically I have this metadata and generating a purposeful example is much better if the output is ordered in the same way the documentation is ordered

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (9)
As of 2017-01-16 12:00 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (149 votes). Check out past polls.