Convert an SQL insert into a Perl data structure

bliako has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks

I am trying to parse a (My)SQL INSERT statement's VALUES section and convert it to a Perl Data Structure. Ideally into an array of hashes, each hash being a table row. My data is produced by mysqldump --no-tablespaces.

The order of data in each of the VALUES row (within a pair of brackets) is the same as the order of columns specified in the CREATE just above. So I parse the CREATE (lamely) in order to find the column names. Then I must parse, edit: and convert into a Perl array, the VALUES section which looks like a Perl array, (1,2,3),(4,5,6), and so, lazily, I do an eval which relieves me from dealing with what's inside the VALUES quoted strings (which can be anything, html etc.)

So, my question is what is the best way to parse (My)SQL and create an array of hashes for each table's data+structure. Ideally I would prefer a CPAN module (I tried SQL::Parser but it failed to read the whole SQL dump, complained about "---"). But my eval works with my dump (so far).

Here is a gist of what I do in a large scale:

my $tablename = 'mytable';

my $sqlcontent = <<'EOS';
/* create statement before the insert */
CREATE TABLE `mytable` (
  `id` int,
  `content` varchar(1000)
)  ENGINE=InnoDB;

INSERT INTO `mytable` VALUES
  (1,'abc\"xyz\"'),(2,'blah')
;
UNLOCK TABLES;
EOS

my ($statements, $values)
  = $sqlcontent
  =~ /CREATE\s+TABLE\s+`${tablename}`\s+(\(.+\))\s+ENGINE=.+?;\s+
      .+?INSERT\s+INTO\s+`${tablename}`\s+VALUES\s+(.+?);\s+    
      .*?UNLOCK\s+TABLES;
     /sx
;

# Below is problematic but works for restricted cases
my @values = eval $values;
die $@ if $@;
print join("\n", @values)."\n";
[download]

This small issue is part of my efforts to upgrade a DB schema (by adding or removing table columns), processing the database data and inserting it back into the DB

Comment on Convert an SQL insert into a Perl data structure Select or Download Code

Replies are listed 'Best First'.
Re: Convert an SQL insert into a Perl data structure by hippo (Archbishop) on Apr 05, 2023 at 11:46 UTC
I tried SQL::Parser but it failed to read the whole SQL dump, complained about "---" If that means the lines beginning with double-dashes then you can just strip those out - they are only notes from mysqldump and can be ignored. You can avoid them in the first place by using `mysqldump --skip-comments` instead. This small issue is part of my efforts to upgrade a DB schema (by adding or removing table columns), processing the database data and inserting it back into the DB There will be some reason you are not just doing this entirely inside the DB but I can't imagine what that reason might be. 🦛	[reply] [d/l]
Re^2: Convert an SQL insert into a Perl data structure by bliako (Abbot) on Apr 05, 2023 at 12:01 UTC
I thought if it fails on that then it will be problematic with the rest. As for my situation, I have just given explanation above Re^2: Convert an SQL insert into a Perl data structure	[reply]
Re: Convert an SQL insert into a Perl data structure by LanX (Saint) on Apr 05, 2023 at 11:24 UTC
Maybe a case of tl;dr but you are aware of the possibilities to (meta) query the MySQL schemas as such, without needing to parse the CREATE statements with regex? ° IIRC was DBI.pm also (additionally) offering methods for this Edit See meta query MySQL for examples °) E.G. look out for the DB/Schema INFORMATION_SCHEMA inside MySQL, but IIRC TIMTOWTDI. Cheers Rolf _{(addicted to the 𐍀𐌴𐍂𐌻 Programming Language :) Wikisyntax for the Monastery}	[reply]
Re^2: Convert an SQL insert into a Perl data structure by bliako (Abbot) on Apr 05, 2023 at 11:57 UTC
Hmm, at first I thought that's a great idea. But then reality hit: in this way, I would need somehow to juggle two different database versions existing at the same time while I read data from the old DB (ODB), process it and add it to the new DB (NDB). I guess I would need to rename one database and that could blow. Whereas what my situation is: ODB exists and is running. An update is coming and a NDB must be created based on ODB's data plus some added columns for certain tables. I dump the ODB to file. I erase ODB. I create NDB with no data. I read the ODB's dumpfile, process it (to add some values for the new columns) and insert it into NDB. So, perhaps I can exploit your idea by creating my version of `mysqldump` based on DBI which is friendlier to my aims. Earlier answer: Yep, that's a good way: SHOW COLUMNS and then ask DBI to SELECT and get back all results as Perl data structure. Actually no need for SHOW COLUMNS, just a SELECT will contain a hash with column names/values. After your suggestion I saw DBI, add fields to existing table? which shows how to add columns with ALTER TABLE. bw, bliako	[reply] [d/l]

Edit