Django: copy data from one database to another

Question

I have two sqlite.db files. I'd like to copy the contents of one column in a table of on db file to another.

for example:

I have the model Information in db file called new.db:

class Information(models.Model):
        info_id = models.AutoField(primary_key = True)
        info_name = models.CharField( max_length = 50)

and the following information model in db file called old.db:

class Information(models.Model):
            info_id = models.AutoField(primary_key = True)
            info_type = models.CharField(max_length = 50)
            info_name = models.CharField( max_length = 50)

I'd like to copy all the data in column info_id and info_name from old.db to info_id and info_name in new.db.

I was thinking something like:

manage.py dbshell

then

INSERT INTO "new.Information" ("info_id", "info_name")
SELECT "info_id", "info_name"
FROM "old.Information";

This doesn't seem to be working. It says new.Information table does not exist... any ideas?

Solution 1

You'd need to switch your database URL in your settings file to db2 and run syncdb to create the new tables. After that the easiest thing to do imo would be to switch back to db1 and run ./manage.py dumpdata myapp > data.json, followed by another switch to db2 where you can run ./manage.py loaddata data.json.

Afterwards, you can drop the data you don't need from db2.

Edit: Another approach would be to use the ATTACH function from sqlite. First, I recommend you do the first step above (change database settings and use syncdb to create the tables), then you can switch back and do this:

./manage.py dbshell

> ATTACH DATABASE 'new.db' AS newdb;
> INSERT INTO newdb.Information SELECT * FROM Information;

Solution 2

• The dumped file from old.db contains info_type field which is not in the new Information model. This will fail the loaddata which checks all field loaded from JSON file. You could comment out info_type line before dump from old model.

• The Attach way mentioned by Alex is easier and great, which needs a tiny tweak

  INSERT INTO newdb.Information SELECT * FROM Information;

  note the missing parentheses around the SELECT, sqlite does not accept them. Refs http://sqlite.org/lang_insert.html

• If you are performing migration, have you tried South

Comments

[JohnnyCash]

interesting.. let me give that a try and get back to you! :)

[Alex Vidal]

On the dumping? Do you have data in the model? Maybe try using the second approach, which I added after your first comment.

[JohnnyCash]

not on the dumping.. on the loading :S.. I also tried the second approach but it still says "Error: no such table: newdb.Information"..

[JohnnyCash]

and i definitely ran syncdb and tables are there (i can see them in admin)

[Alex Vidal]

Ah right, sorry. The loaddata just takes the fixture because the application name is in the fixture already. ./manage.py loaddata data.json

[Alex Vidal]

Thanks for the parens tip. I wrote the query by looking at the INSERT grammar on sqlite's website and failed to notice the (lack of) parens.