Migrate Mysql Weewx database to new Sqlite install

[roelw]

I have 3 years of weather data in a Weewx Mysql database.
There are a topics that explain how to migrate from Sqlite to Mysql but I cannot find a working method to import the Mysql archive-table into a SQlite archive-table.
I've tried to export the table to a csv-file, but then I got problems with NULL values.
I've tried to use Mysqldump, but then I got the error: Error: dump.sql line 1: expected 52 columns of data but found 1

Info: The Weewx version of the MySql database is Version 2.4.0. I have not installed the dev version 3.0.1

Hope somebody can help me.

Roel

[vds]

Hard to say, but guessing you didn't get the mysqldump syntax correct. It's been a long time since I ran mysql but the command seems to be 'mysqldump -uUSERNAME -pPASSWORD DATABASENAME'. There was a long discussion about how to backup/verify databases last summer.  Look at the bottom mwall post on Jul-21 at https://groups.google.com/forum/#!topic/weewx-user/ha-mbc6zkpY for one way to do it.

There are a lot of links for converting mysql->sqlite3 if you can get a good mysql database dump completed.  One link that looks interesting is  http://ahoj.io/how-to-convert-mysql-to-sqlite

[Thomas Keffer]

This is a common problem. There are two general approaches. 

1. Use mysqldump to create a dump file, massage it, then restore using the utility sqlite3; or
2. 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.

For approach #1, first dump the MySQL database using mysqldump.

$ mysqldump -u root -p --skip-add-drop-table --skip-add-locks weewx archive > weewx.sql

Unfortunately, the resultant file includes a CREATE TABLE statement that sqlite does not understand. I could write some fancy sed script here to massage it into something sqlite3 can deal with, but the simplest is to just edit the file weewx.sql, replacing the CREATE TABLE statement with:

CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `barometer` REAL, `pressure` REAL, `altimeter` REAL, `inTemp` REAL, `outTemp` REAL, `inHumidity` REAL, `outHumidity` REAL, `windSpeed` REAL, `windDir` REAL, `windGust` REAL, `windGustDir` REAL, `rainRate` REAL, `rain` REAL, `dewpoint` REAL, `windchill` REAL, `heatindex` REAL, `ET` REAL, `radiation` REAL, `UV` REAL, `extraTemp1` REAL, `extraTemp2` REAL, `extraTemp3` REAL, `soilTemp1` REAL, `soilTemp2` REAL, `soilTemp3` REAL, `soilTemp4` REAL, `leafTemp1` REAL, `leafTemp2` REAL, `extraHumid1` REAL, `extraHumid2` REAL, `soilMoist1` REAL, `soilMoist2` REAL, `soilMoist3` REAL, `soilMoist4` REAL, `leafWet1` REAL, `leafWet2` REAL, `rxCheckPercent` REAL, `txBatteryStatus` REAL, `consBatteryVoltage` REAL, `hail` REAL, `hailRate` REAL, `heatingTemp` REAL, `heatingVoltage` REAL, `supplyVoltage` REAL, `referenceVoltage` REAL, `windBatteryStatus` REAL, `rainBatteryStatus` REAL, `outTempBatteryStatus` REAL, `inTempBatteryStatus` REAL);

Then restore using sqlite3 (which you may have to install first)

$ sqlite3 weewx.sdb < weewx.sql 

Put the resultant sqlite3 file, weewx.sdb, wherever your installation method requires.

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

$ wget -O transfer_db.py http://sourceforge.net/p/weewx/code/HEAD/tree/trunk/experimental/transfer_db.py?format=raw

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, so you'll want to swap the two dictionaries to go the other direction. When you're done, they'll look like  this:

new_archive_dict = {'driver'        : 'weedb.sqlite',

                    'database_name' : '/home/weewx/archive/weewx.sdb'}

old_archive_dict = {'driver'        : 'weedb.mysql',

                    'database_name' : 'weewx',

                    'host'          : 'localhost',

                    'user'          : 'weewx',

                    'password'      : 'weewx'}

(You may also want to adjust the path to the resultant sqlite database to something other than '/home/weewx/archive/weewx.sdb')

In order to run the program, you'll have to supply a path to the weewx executables. For a setup.py installation, this would be:

$ PYTHONPATH=/home/weewx/bin python transfer_db.py

For a Debian install

$ PYTHONPATH=/usr/share/weewx/bin python transfer_db.py

Hope this helps.

I should really put all this in a Wiki...

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[roelw]

Tom,

After a couple of days I finally got a correct sqldump file. I had to add a extra parameter to the command, namely --extended-insert=FALSE .
Without this parameter i got the error: Error: near line 87: too many terms in compound SELECT
Now my Raspberry is busy for hours importing my old data.

Nb With the second solution I got an error of a missing module Manager.
 
Thanks for your help

Roel

Op woensdag 17 december 2014 00:21:17 UTC+1 schreef Tom Keffer:

[Thomas Keffer]

Thanks, Roel. I missed that with the small dataset I tested. 

I've made the change in the Wiki.

-tk