2

I need to do some kind of incremental import again and again from a SQLite 3 database to a MySQL database. Column names are the same in both databases. Is there a good way to do this by a script (therefore is there a good way to get the column values of one row as some kind of array/list, so I can really work with them?) or am I better off with a small C program?

Update: OK, there is the -line option, giving one column value per line, result rows are then separated by an empty line. Still need to find a really simple way to parse this ;-)

  • 2
    Should be quite easy with a high level script language like perl, python or ruby. – Johan Sep 9 '12 at 15:12
  • 1
    this question is more suitable for stack overflow – amphibient Nov 1 '12 at 14:21
1
#!/bin/bash

sqlite test.db  "select * from tablename" > /tmp/dump.sql

mysql -uroot -ppassword  databasename <<EOF
load data infile '/tmp/dump.sql'
into table tablename
fields terminated by '|'
escaped by '\\\'
lines terminated by '\n';
EOF
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.