Transfer from sqlite to MySQL  

This article is about moving data from sqlite to MySQL. For information on moving the other direction, see its companion article,[[http://sourceforge.net/p/weewx/wiki/Transfer%20from%20MySQL%20to%20sqlite|Transfer from MySQL to sqlite]].

There are two general approaches to this problem:

  *Use the command line utility sqlite3 to dump the contents of the sqlite database to a file, then massage it, then restore it on MySQL using the utility mysql; or
  *Use the experimental weewx utility transfer_db.py

The former is more general and can be done standalone. The latter is simpler, but requires access to the rest of weewx.


----
**Dumping to SQL**

For approach #1, first dump the sqlite database using the utility sqlite3. Adjust the path to the sqlite3 database, weewx.sdb, as necessary. Note: you may have to install sqlite3 first.

<code oobas>
$ echo ".dump archive" | sqlite3 weewx.sdb > weewx.sql
</code>
Now recreate in MySQL. Start by creating the database:

<code oobas>
$ echo "CREATE DATABASE weewx" | mysql -u root -p
</code>
Now restore using the contents of the file weewx.sql, massaged a bit:

<code oobas>
$ sed -e '/PRAGMA/d' -e's/BEGIN/START/' -e 's/"archive"/archive/' < weewx.sql | mysql -u root -p --database=weewx
</code>
This will result in a database "weewx" in MySQL, with a single table, "archive," holding the contents of your sqlite archive file.


----
**Using transfer_db.py**

For the second method, download the program transfer_db.py from the code repository on SourceForge:

<code oobas>
$ wget -O transfer_db.py http://sourceforge.net/p/weewx/code/HEAD/tree/trunk/experimental/transfer_db.py?format=raw
</code>
Open up the file in a text editor. At the top, you'll see Python dictionaries controlling the source and destination databases. The default setup is to transfer from sqlite to MySQL, which is what you want. Double check to make sure the source and destinations are to your liking. In particular, check the path to your sqlite file, weewx.sdb. Yours may be in a different location.

<code oobas>
old_archive_dict = {'driver'        : 'weedb.sqlite',
                    'database_name' : '/home/weewx/archive/weewx.sdb'}

new_archive_dict = {'driver'        : 'weedb.mysql',
                    'database_name' : 'weewx',
                    'host'          : 'localhost',
                    'user'          : 'weewx',
                    'password'      : 'weewx'}
</code>
In order to run the program, you'll have to supply a path to the weewx executables as the variable PYTHONPATH. For a setup.py installation, this would be:

<code oobas>
$ PYTHONPATH=/home/weewx/bin python transfer_db.py
</code>
For a Debian install

<code oobas>
$ PYTHONPATH=/usr/share/weewx/bin python transfer_db.py
</code>

----
**Related**

[[http://sourceforge.net/p/weewx/wiki/Transfer%20from%20MySQL%20to%20sqlite/|Wiki: Transfer from MySQL to sqlite]]
[[http://sourceforge.net/p/weewx/wiki/Weewx%20Wiki/|Wiki: Weewx Wiki]]