Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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 ;-)

share|improve this question
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
add comment

2 Answers

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)

share|improve this answer
add comment
#!/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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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