1

I need to migrate information that I created in SQLite3 to a MySQL database on my website. The website is on a hosted server. I have remote access to the MySQL database. I initially thought it would be easy, but I am not finding any good info on it, and everything I read seems to imply that you need to dump the SQLite3 file, convert it to a MySQL dump file using messy scripts, and then import it into the MySQL.

(example: Quick easy way to migrate SQLite3 to MySQL?)

My question: Is it not better to read it and import it straight from the script into MySQL. I haven't used MySQL at all with Python, but it would seem intuitive that it would be better to have less steps for things to be miss-read. I am also trying to save a little time by not having to understand the way that a MySQL dump file works.

If you have a script (Python if possible), tool or link that will help me out that would be great, but my main concern first of all is how to go about doing it. I can't realistically check everything (otherwise I would just do copy and paste), so I want to be sure that I am going about it the right way.

I am using Django, perhaps there is a Django specific method for it, but I haven't been able to find one.

Community
  • 1
  • 1
Vernon
  • 2,521
  • 2
  • 22
  • 28

2 Answers2

2

The reason the "messy" scripts are required is that it's generally a difficult problem to solve.

If you are lucky there won't be too many schema incompatibilities between the databases.

These may help

db_dump.py
dbpickle.py
What is your favorite solution for managing database migrations in django?

Community
  • 1
  • 1
John La Rooy
  • 263,347
  • 47
  • 334
  • 476
  • "The reason the "messy" scripts are required is that it's generally a difficult problem to solve."... is the reason why I just want to skip that altogether. If I open the sqlite3, iterate through the table and update the mysql table row by row, that doesn't seem messy, but nobody seems to do it that way. My question is why? – Vernon Feb 25 '10 at 09:57
  • @Vernon, Do what ever is easiest for you. Many people seem to migrate via the db dumps. You might find a better way than anyone before. If it doesn't work out you can always go back and try the dumps :) – John La Rooy Feb 25 '10 at 10:13
  • @Vernon: probably because you need to ensure the schema matches. Taking loosely typed data and inserting it into a more strongly-typed schema is likely to raise some problems, and each application may have a different strategy for dealing with them. – Kylotan Feb 25 '10 at 14:33
  • I see. I am getting it to work for my purposes, but I can see the issues with the schema matching already. I guess I just thought it would be very "Python like" for there to be a module already written to take care of it. – Vernon Feb 25 '10 at 19:06
2

Have you tried using django-admin's dumpdata and loaddata?

e.g. dump sqllite3 db 

manage.py dumpdata

change db to mysql, recreate tables and load

manage.py loaddata mydata.json

dumpdata by default dumps json but you can set format to xml too.

Alternatively, you can just read whole sqlite3 db using python db api or sqlalchemy or django ORM and dump it to MySQL db.

xyres
  • 16,063
  • 3
  • 42
  • 65
Anurag Uniyal
  • 77,208
  • 39
  • 164
  • 212
  • I overlooked the fact that the dumpdata put it out as a json format. That certainly makes it much easier. I am trying to avoid having to load each table as a file (I don't want to upload the whole database, only certain files) I am still not sure why not write a script that read the sqlite3 database and uploads it to mysql. Since the sqlite3 database is on my computer and the mysql is on a hosted server, if I can do it all in one go, it would be much easier. If not, I will do the tables one by one with the method you suggest. – Vernon Feb 25 '10 at 09:43
  • @vermon, yes you can write a script, if that is easy, and you have only some data to load – Anurag Uniyal Feb 25 '10 at 11:21