Import single rows from SQLite to MySQL?

Your problem is not exactly simple, so I don't think there is a really simple solution.

Most probably you will need to modify this, but in the hope that it can help you get started here's a simple perl implementation of converting the output of sqlite3 -line to insert statements:

chomp($_);
if ($_) { 
    m/^\s*(\w+) = (.*)/; 
    $map{$1} = $2; 
} else { 
    my $keys = join(", ", keys(%map));
    my $values = join(", ", map { s/'/\\'/g; "'$_'"; } values(%map));
    print sprintf("insert into TABLE (%s) values (%s);\n", $keys, $values);
}

Save the above perl script in a file named parse.pl, and pipe the results of sqlite3 -line output to it like this:

echo 'select * from TABLE;' | sqlite3 -line sqlite3.db | perl -n parse.pl

The result is sql insert statements which can be piped to mysql, after you have tested carefully that they look correct. The script creates the column names and values dynamically, you may want to adjust that, especially the values, to not quote non-string values.

WARNING! Do not try to use this script "as is"! Automating inserts from one database to another is NOT a trivial task, and if not done carefully there can be all kinds of side effects depending on the type of data you have. For example I haven't tested the above script with multi-line data or binary valued columns.

Another solution is to save the output of sqlite3 in a file, and import that into mysql using the load data command. Look up the syntax and the details in the documentation, it has many interesting options. If I remember correctly it has an option to replace data, which I have used on a few occasions similar to your need, for incremental imports.

I've written such a thing in PHP using adapters, one thing we did was create a sys_parsed flag to mark what has been imported (or failed). Basically keep track so you don't end up trying an import that doesn't work forever.

It parses thousands of sqlite files and imports this into a singl mysql db with a table per customer. But it's definitely not a 15 liner. There's a lot more to it if you want reliable imports. It's quite performant too, not a lot of load.

If it interests you, I could take a look and see if I can share it (written for customer)