perl mysql - INSERT INTO, 157 columns

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.

Comment on perl mysql - INSERT INTO, 157 columns Download Code

Replies are listed 'Best First'.
Re: perl mysql - INSERT INTO, 157 columns by poj (Abbot) 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);` [download] poj	[reply] [d/l]
Re^2: perl mysql - INSERT INTO, 157 columns by ravi45722 (Pilgrim) on Jul 31, 2015 at 06:44 UTC
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(); }` [download] How can i edit as per you suggest. I am very beginner to this. Please edit this program as per your sysntax.	[reply] [d/l]
Re^3: perl mysql - INSERT INTO, 157 columns by Corion (Patriarch) on Jul 31, 2015 at 07:06 UTC
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(); }` [download] To help us help you better, please post the exact error message you get, and also the relevant input data that causes this.	[reply] [d/l]
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, '' );` [download] (updated to anonymize the table name)	[reply] [d/l]
Re^2: perl mysql - INSERT INTO, 157 columns by Tux (Canon) on May 02, 2014 at 12:35 UTC
`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]})"); };` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^3: perl mysql - INSERT INTO, 157 columns by graff (Chancellor) on May 02, 2014 at 16:52 UTC
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 $")	[reply]
Re^4: perl mysql - INSERT INTO, 157 columns by choroba (Cardinal) on May 02, 2014 at 17:03 UTC
Re^4: perl mysql - INSERT INTO, 157 columns by gsiems (Deacon) on May 02, 2014 at 18:17 UTC
Re^3: perl mysql - INSERT INTO, 157 columns by erix (Prior) on May 02, 2014 at 12:53 UTC
my $sth = $dbh->prepare ("select from foo");* Surely that should be `my $sth = $dbh->prepare ("select * from foo where 1=0 ");` [download] Or something like that (limit, top, or whatever to not have the db think deeper than necessary...).	[reply] [d/l]
Re^4: perl mysql - INSERT INTO, 157 columns by Tux (Canon) on May 02, 2014 at 14:57 UTC
Re^5: perl mysql - INSERT INTO, 157 columns by erix (Prior) on May 02, 2014 at 19:25 UTC
Some notes below your chosen depth have not been shown here
Re: perl mysql - INSERT INTO, 157 columns by kcott (Archbishop) 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) . ')';` [download] 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) };` [download] 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	[reply] [d/l] [select]
Re: perl mysql - INSERT INTO, 157 columns by Anonymous Monk on May 01, 2014 at 20:47 UTC
Google "perl insert many columns": Stack Overflow: insert 1 row with a lot of columns using an array in Perl DBI	[reply]
Re^2: perl mysql - INSERT INTO, 157 columns by ler224 (Acolyte) on May 01, 2014 at 21:05 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);` [download]	[reply] [d/l]
Re^3: perl mysql - INSERT INTO, 157 columns by Anonymous Monk on May 01, 2014 at 21:13 UTC
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.	[reply]
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	[reply]
Re: perl mysql - INSERT INTO, 157 columns by kschwab (Vicar) 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.	[reply]
Re: perl mysql - INSERT INTO, 157 columns by karlgoethebier (Abbot) 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�	[reply]
Re^2: perl mysql - INSERT INTO, 157 columns by ler224 (Acolyte) on May 01, 2014 at 22:05 UTC
157 is the number of columns* in the csv document with different metrics.	[reply]
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.	[reply]
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�	[reply]
Re^2: perl mysql - INSERT INTO, 157 columns by SirClive (Scribe) on May 02, 2014 at 09:50 UTC
<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	[reply]
Re^3: perl mysql - INSERT INTO, 157 columns by sundialsvc4 (Abbot) on May 02, 2014 at 11:42 UTC
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.

Back to Seekers of Perl Wisdom