8

I want to start using Core Date on iPhone with pre-existing MySQL databases. What's the easiest way to transfer a MySQL database to SQLite?

I've tried using SQLite Migrator, but I don't know where to find the ODBC drivers for Mac (Snow Leopard). I found http://www.ch-werner.de/sqliteodbc/ which seems to have drivers, but they are for Power PC.

If someone could give me a walkthrough, or tell me what the best tools for this are, I'd be grateful.

Thanks.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
nevan king
  • 112,709
  • 45
  • 203
  • 241

7 Answers7

6

Perhaps the simplest would be to use mysqldump to dump the raw SQL from your MySQL database into a text file and then use the sqlite3_exec() function to execute that SQL in order to populate the SQLite database.

Mike Willekes
  • 5,960
  • 10
  • 33
  • 33
  • 1
    it's a slightly different dialect of SQL however, particularly in regard to table creation schemas. – Alister Bulman Nov 05 '09 at 22:54
  • 2
    Good point. I suppose if the schema rarely changed you could dump just the data from the MySql database and have a separate script/sql file that constructed the sqlite database. You would also need to specify --complete-insert to mysqldump. Sqlite can't handle compound inserts (see question 1609637). – Mike Willekes Nov 05 '09 at 23:03
  • +1 for mysqldump. Although there are some differences between MySQL and SQLite syntax, you can easily do a find and replace with your favorite software or script since the differences are minimal. For example, AUTOINCREMENT is different. If this is something you will be doing regularly, you can write your own script or find one that someone else wrote to make your mysqldump compatible with sqlite. – styfle May 14 '11 at 07:12
2

Have you looked at this Perl script? I haven't used it - just did a quick search for mysql to sqlite migration and it popped right up.

Edit (after you replied to my comment):

The reverse direction is dealt with here.

If you are going to do it repeatedly and if data structure changes are to happen, maybe you would be better off using something like Django (albeit in a very hackish way). With it I would:

# This three lines are done once
django-admin.py startproject mymigrationproject
cd mymigrationproject
./manage.py startapp migration

# The following lines you repeat each time you want to migrate the data
edit settings.py and make the changes to connect to MySQL
./manage.py inspectdb > ./migration/models.py
edit ./migration/models.py to reorder tables (tables in which other tables depend on top)
mkdir fixtures
./manage.py dumpdata migration > ./fixtures/data.json
edit settings.py and make the changes to connect to SQLite
./manage.py syncdb
./manage.py loaddata ./fixtures.data.json
Community
  • 1
  • 1
cethegeek
  • 6,286
  • 35
  • 42
  • Thanks for the answer. I think I want something with minimal learning and set up though, since I'm not going to be doing it that often. – nevan king Nov 03 '09 at 09:57
1

Here is a list of converters:

An alternative method that would work nicely but is rarely mentioned is: use a ORM class that abstracts the specific database differences away for you. e.g. you get these in PHP (RedBean), Python (Django's ORM layer, Storm, SqlAlchemy), Ruby on Rails ( ActiveRecord), Cocoa (CoreData)

i.e. you could do this:

  1. Load data from source database using the ORM class.
  2. Store data in memory or serialize to disk.
  3. Store data into source database using the ORM class.
David d C e Freitas
  • 7,481
  • 4
  • 58
  • 67
0

You can use a trial from http://www.sqlmaestro.com/products/sqlite/datawizard/

It is completely functional for 30 days.

Pavlo Svirin
  • 508
  • 4
  • 8
0

You can get ODBC drivers for Mac OS X from Actual Technologies.

http://www.actualtech.com/

To connect to MySQL you need their ODBC Driver for Open Source Databases:

http://www.actualtech.com/product_opensourcedatabases.php

(Disclaimer: I am the author of SQLite Migrator)

Paul Lefebvre
  • 6,253
  • 3
  • 28
  • 36
-1

To do my conversions, I ended up using an ODBC from Actual Access. I think I used it in combination with SQLite Migrator. I never liked this way though it was always clunky. Expensive too, it ended up costing about $80 for those two pieces of software.

If I had to do this again, I'd buy SQLiteConverter by SQLabs. I use their SQLite Manager, and although it has a lot of interface problems, for database software it's not bad.

http://www.sqlabs.net/sqliteconverter.php

nevan king
  • 112,709
  • 45
  • 203
  • 241
-1

There is a free ETL product that can be used to migrate data from one db to another. Have a look: http://www.talend.com/index.php

Good luck!

Greg
  • 313
  • 1
  • 4