Re: perl with mysql which takes a file as a input that contains t he info to create the tables
- From: chas.owens@xxxxxxxxx (Chas. Owens)
- Date: Fri, 16 Nov 2007 09:08:42 -0500
On Nov 16, 2007 1:52 AM, Pradeep Kumar <prady184u@xxxxxxxxx> wrote:
snip
NOW I WANTED TO KNOW THE PROCEDURE FOR TAKING THE CODE AS INPUT FROM Asnip
TEXT FILE & CREATE THE SPECIFIED TABLE
Don't yell.
Warning, this is untested code and may blow up in your face. Second
warning, this code is very inefficient due to its use of AutoCommit,
this should be set to 0 and you should be committing after every
create table and after every thousand or so inserts (I was just too
lazy to put that in). Third warning, I have not scanned the code for
stupidity (I already see at least one: I don't have to use $1, $2, I
could do the assignment in the if).
#!/usr/bin/perl
use warnings;
use strict;
my $dbh = DBI->connect(
'dbi:mysql:dbname=foo',
'',
'',
{
AutoCommit => 1,
ChopBlanks => 1,
RaiseError => 1,
PrintError => 0,
}
) or die DBI->errstr;
my @cols;
my $insert;
while (<DATA>) {
chomp;
if (/^(\w+)>(.*)/) {
my ($tabname, $tabbody) = ($1, $2);
$dbh->do("CREATE TABLE $tabname ( $tabbody )");
@cols = map { (split " ")[0] } split ",", $tabbody;
$insert = $dbh->prepare("insert into $tabname (" .
(join ", ", @cols) . ") values (" .
(join ", ", (('?') x @cols)) . ")");
next;
}
die "corrupt file" unless @cols;
$insert->execute(split ",");
}
$dbh->disconnect;
__DATA__
Details> name VARCHAR(12), id INTEGER, area VARCHAR(32)
prady,2039,india
sandy,2398,india
sam,1234,aussie
Rob,2345,Eng
extraDetails>name1 VARCHAR(12), name2 VARCHAR(12)
prady,sandy
sandy,Rob
Rob,sam
sam,prady
.
- References:
- Prev by Date: DBD::mysql::st execute failed: Column count doesn't match value count at row 1 a
- Next by Date: RE: :mysql::st execute failed: Column count doesn't match value count at row 1 a
- Previous by thread: perl with mysql which takes a file as a input that contains t he info to create the tables
- Next by thread: perl with mysql which takes a file as a input that contains t he info to create the tables
- Index(es):
Relevant Pages
|
