Merge 2 weewx SQLite databases

[B Major]

I would like to merge 2 weewx SQLite databases. I am currently planning to use the wee_import function. Is there a better way to do this?

The back story here is that I recently got a new desktop. I installed the latest version of weewx and had it running in short order. I have my old weewx SQLite database. I'd like to keep all that data. As noted, I was going to just use wee_import to the new database after extracting the records from the old one. But given that  it appears that these are of the same format is there an easier way to do this?  I've been running the new system for a couple of weeks so swapping out databases may be an option but I'd like to keep those 2 weeks of data. These files are Linux based.

Any suggestions or ideas for ease of effort would be appreciated.

[Tom Keffer]

I seem to recall there was a thread on the forum about this not too long ago.

The simplest would be to dump one file, using the tools sqlite3, then import into the other, again using sqlite3. Something like (NOT TESTED):

echo ".dump archive" | sqlite3 file1.sdb | grep -v 'CREATE TABLE' >file1.sql

sqlite3 file2.sdb < file1.sql

where file1.sdb is the sqlite database you want to add to file2.sdb.

The first line dumps file1.sdb, removing the 'CREATE TABLE' statement (you don't want it because you already have the table in file2.sdb). It saves the results to file1.sql, which will be full of SQL "INSERT" statements.

The second line then uses that file to insert the new data into file2.sdb.

Needless to say, make backups first.

-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.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/f227d9ab-78ed-41b0-a7ee-75a1114dcb8e%40googlegroups.com.

[B Major]

First off, let me thank you for your assistance and direction here. You pointed me to the sqlite utilities which is what I was wondering; can I use them here. I am taking the time to learn more about sqlite database.

Second off, thank you for kindly answering when I clearly didn't use the group search function effectively. There is a lot of useful information on this group. 

Thanks for sharing the nice weewx application too.

Brian

To unsubscribe from this group and stop receiving emails from it, send an email to weewx...@googlegroups.com.

[Ken Waters]

Tom,

Thanks so much for posting this information.  I was looking for a way to merge all these orphan .sdb files that I have due to the frequent date reset issue that I have and posted about earlier.  The result was several sdb files that had 2-3 weeks of data in them.

I just tested this solution and I am happy to report that it worked perfectly. I first stopped the service so the current .sdb file would not be modified.  Then I made copies of the files in another directory and did the two commands as my regular user so I didn't have to 'sudo'.  Finally I cp'd over the new .sdb overwriting the weewx.sdb file and then restarted the service.  Lastly, I verified in the new images that the time frame of my database suddenly went back an additional 2 weeks.  I'm not sure about the error but I kind of suspect maybe there was a duplicate entry in the database.  But it did not prevent the actual merge from working.

Thanks so much for this solution

Ken

Mesa AZ

Below is some of the relevant output:

 

ken@WeewxWeatherServer:~/w/try$ ll
total 13872
-rw-r--r-- 1 ken ken 3923968 Apr 24 11:35 weewx.sdb
-rw-r--r-- 1 ken ken 2289664 Apr 24 11:31 weewx.sdb_202004090905

ken@WeewxWeatherServer:~/w/try$ echo ".dump archive" | sqlite3 weewx.sdb_202004090905 | grep -v 'CREATE TABLE' > file1.sql

ken@WeewxWeatherServer:~/w/try$ sqlite3 weewx.sdb < file1.sql
Error: near line 11483: UNIQUE constraint failed: archive.dateTime

ken@WeewxWeatherServer:~/w/try$ ll
-rw-rw-r-- 1 ken ken 5210741 Apr 24 11:36 file1.sql
-rw-r--r-- 1 ken ken 5791744 Apr 24 11:36 weewx.sdb
-rw-r--r-- 1 ken ken 2289664 Apr 24 11:31 weewx.sdb_202004090905
ken@WeewxWeatherServer:~/w/try$

ken@WeewxWeatherServer:~/w$ sudo ./start
[ ok ] Starting weewx (via systemctl): weewx.service.
Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: engine: Debug is 1
Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: engine: Initializing engine
Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: engine: Loading station type Vantage (weewx.drivers.vantage)
Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: vantage: Driver version is 3.1.1
Apr 24 11:39:26 WeewxWeatherServer systemd[1]: Started LSB: weewx weather system.
Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: vantage: Opened up serial port /dev/ttyS0; baud 19200; timeout 4.00
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Retry  #0 failed
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Gentle wake up of console successful
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Hardware type is 16
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: ISS ID is 1
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Hardware name: Vantage Pro2
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdTimeSynch
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdTimeSynch
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdConvert
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: StdConvert target unit is 0x1
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdConvert
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdCalibrate
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdCalibrate
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdQC
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdQC
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.wxservices.StdWXCalculate
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.wxservices.StdWXCalculate
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdArchive
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Archive will use data binding wx_binding
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: The archive interval in the configuration file (300) does not match the station hardware interval (60).
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Using archive interval of 60 seconds (specified by hardware)
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Using binding 'wx_binding' to database 'weewx.sdb'
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: manager: Starting backfill of daily summaries
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Starting up weewx version 3.9.2
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Gentle wake up of console successful
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Clock error is 1.44 seconds (positive is fast)
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Getting archive packets since 2020-04-24 11:28:00 MST (1587752880)
Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Gentle wake up of console successful

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/e75a8e17-b0bc-46a5-9d4e-7236921e2bd0%40googlegroups.com.