Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

perl mysql - INSERT INTO, 157 columns

by ler224 (Acolyte)
on May 01, 2014 at 20:23 UTC ( #1084683=perlquestion: print w/replies, xml ) Need Help??
ler224 has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to insert csv data into mysql from perl using DBI. My data is currently 157 columns.

my $query = 'INSERT INTO table (col1,col2,...,col157) VALUES (?,?,...,?157)';

Is there anyway to simplify the INSERT INTO syntax or do I need to list all 157 columns, and add 157 question marks. I already imported most of the data rows to a table using phpmyadmin, but now I am trying to add additional rows to the table.

Replies are listed 'Best First'.
Re: perl mysql - INSERT INTO, 157 columns
by poj (Monsignor) on May 01, 2014 at 21:17 UTC
    my $sql = q!LOAD DATA INFILE 'c:/temp/data.csv' INTO TABLE test3 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'!; $dbh->do($sql);
      My file is presented in /root/Download/backup.csv
      open (DATA,"<database.csv"); while(my $line = <DATA>) { chomp $line; @array=split(',',$line); my $sth = $dbh->prepare("insert into copy values ('$array[0]', +$array[1],'$array[2]','$array[3]','$array[4]',$array[5],'$array[6]')" +); $sth->execute() or die $DBI::errstr; $sth->finish(); }
      How can i edit as per you suggest. I am very beginner to this. Please edit this program as per your sysntax.

        Please use SQL placeholders. It's the only sensible way to construct an SQL statement for arbitrary data and frees you from worrying about contained quotes, newlines and other potentially problematic data. Also consider using Text::CSV_XS for reading in your CSV data instead of manually splitting the data. This frees you up from worrying about quotes, newlines, commas and other problematic input in your data.

        open (DATA,"<database.csv"); while(my $line = <DATA>) { chomp $line; @array=split(',',$line); my $sth = $dbh->prepare("insert into copy values (?,?,?,?,?,?,?)") +; $sth->execute(@array) or die $DBI::errstr; $sth->finish(); }

        To help us help you better, please post the exact error message you get, and also the relevant input data that causes this.

Re: perl mysql - INSERT INTO, 157 columns
by graff (Chancellor) on May 02, 2014 at 01:52 UTC
    If you created (or altered) the table in such a way that the columns in the table are ordered the same as the columns in the input data file, then you can build your insert sql statement like this:
    my $sth = $db->prepare( "describe my_big_table" ); $sth->execute; my $rows = $sth->fetchall_arrayref; $sth->finish; my @column_names = map { $$_[0] } @$rows; my $insert_sql = join( '', 'insert into my_big_table (', join( ',', @column_names ), ') values (', join( ',', ( '?') x scalar @column_names ), ')' ); # check your work, if you like: print join( "\n", "=== columns:", @column_names, '=== sql:', $insert_s +ql, '' );
    (updated to anonymize the table name)

      describe is not present in all databases :(

      More portable would be:

      my $sth = $dbh->prepare ("select * from foo"); $sth->execute; my @fld = @{$sth->{NAME}}; $sth->finish; $sth = do { local $" = ","; $dbh->prepare ("insert into foo (@fld) values (@{[('?') x scalar @ +fld]})"); };

      Enjoy, Have FUN! H.Merijn
        Given that the OP specifically mentioned mysql, I would not have posted the approach using "describe" if it didn't work in mysql.

        As for your alternative (for cases where "describe" doesn't work), I would hope that any type of database covered by DBD would at least support "select * from foo limit 1", and this would be a prudent usage for the task at hand, whether or not you actually need to execute the query in order to get field names.

        (BTW, thanks for the reminder about $")

        my $sth = $dbh->prepare ("select * from foo");

        Surely that should be

        my $sth = $dbh->prepare ("select * from foo where 1=0 ");

        Or something like that (limit, top, or whatever to not have the db think deeper than necessary...).

Re: perl mysql - INSERT INTO, 157 columns
by kcott (Chancellor) on May 02, 2014 at 09:47 UTC

    G'day ler224,

    Don't hand-code all 1,333 characters of that string!

    Think of the maintainance nightmare you're creating. Are you, or the next poor sod who takes over from you, going to physically count 157 '?' characters to make sure there's the right number? How were you intending to check there were exactly 157 colN elements and that N represents an unbroken sequence of unique integers from 1 to 157?

    You already have a tool in front of you to assign the correct string. Use it.

    my $query = 'INSERT INTO table (' . join(',' => map { "col$_" } 1 .. $ +cols) . ') VALUES (' . join(',' => ('?') x $cols) . ')';

    Also, dynamically generate the value of $cols so that you don't have to edit the code if the number of columns changes. You said "My data is currently 157 columns.": this suggests to me that previously it was some other value or there's a potential for that number to change in the future. You don't show enough code for me to say how that might be best implemented; perhaps something like:

    my $cols = @{ $csv->getline($fh) };

    Finally, your column names (i.e. "col1" .. "col157") are a very poor choice. How are you planning to reference them? Are you planning to rewrite all your SQL (or, at least, review all your SQL to see whether rewrites are required) whenever columns are added/removed to/from your CSV file? I don't know enough about your data to advise you further; however, if your CSV data (or its source) has column headings, that would be a good place to start.

    -- Ken

Re: perl mysql - INSERT INTO, 157 columns
by Anonymous Monk on May 01, 2014 at 20:47 UTC
      Would I still need to list all of the column names?
      my $placeholders = join ", ", ("?") x @array; $sth->prepare("insert into bigtable (col...) values ($placeholders)"); $sth->execute(@array);

        From here:

        If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement.
Re: perl mysql - INSERT INTO, 157 columns
by GotToBTru (Prior) on May 01, 2014 at 20:42 UTC

    Other than making sure your VALUES are in the correct order so you can skip the field list, no. Otherwise, you want to find a bulk loader. It wouldn't surprise me if such a thing existed, mysql being popular, but I am not personally familiar with the database. We made good use of SQL*Loader when I was working with Oracle several years and jobs ago.

    1 Peter 4:10
Re: perl mysql - INSERT INTO, 157 columns
by kschwab (Priest) on May 02, 2014 at 16:59 UTC

    Other answers here cover your main question well. I thought, though, that it might be helpful to point out that MySql has a hardcoded limit of 65,336 bytes per row.

    The limit doesn't apply to TEXT and BLOB columns in the row, but with 157 columns, it wouldn't take too many large varchars to blow the limit.

    You might want to consider some normalization for this, and other reasons.

Re: perl mysql - INSERT INTO, 157 columns
by karlgoethebier (Monsignor) on May 01, 2014 at 21:32 UTC
    ...157 columns

    It's just curiosity: Why do you want to have 157 columns in one table?

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

      157 is the number of columns* in the csv document with different metrics.
Re: perl mysql - INSERT INTO, 157 columns
by chacham (Prior) on May 02, 2014 at 13:06 UTC

    If you're open to alternatives, a stored procedure might be helpful. The IN parameter could be a comma-delimited string that the SP expands.

Re: perl mysql - INSERT INTO, 157 columns
by Anonymous Monk on May 02, 2014 at 06:11 UTC
    ...157 columns

    It's just curiosity: Why do you want to have 157 columns in one table?

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

      <zero perl content> 157 columns in a table is not good database design - it makes maintenance / reporting / analysis very difficult no matter whether you are using perl or anything else. I would normally be concerned with any more than 30 columns in a table - then I would look to see if I could split into two or more tables. You may possibly lose a very small amount of performance speed having to join the tables when you retrieve the data but it does make things infinitely more maintainable

        Spreadsheets often have a very-ridiculous number of columns because it is easiest for a macro to work with one row of the sheet at a time, without resorting to VLOOKUP() and other such rot.   Even though the sheet contents are often the product of a fairly substantial database JOIN, you often do not have access to the original database from when the spreadsheet ultimately came.   What I usually suggest, in cases like this, is that you should try to avoid writing programs just to get your data into the thing, and that if possible you should do further manipulations by stored-procedure within the database environment.   Something equivalent to LOAD DATA INFILE usually exists somewhere, and the table which this loads into ought to basically be just a “holding tank.”   Then, additional queries can be run to, in effect, “un-join” the data and use it to update the permanent tables.   You may or may not use Perl, as the case may be, to do this.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1084683]
Front-paged by GotToBTru
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (7)
As of 2018-06-21 10:44 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (118 votes). Check out past polls.