Merge "parts" of two databases?

[Ernest Jillson]

I imported years of data from wunderground into my weewx db. There was a period of very bad rain data due to a malfunctioning tipping bucket. Also had a day where a small grasshopper managed to get into the little hole, then proceeded to rack up about 15 inches of rain for me that day while trying to escape.

 

I stopped weewx, made a copy of my db, then used sqlite browser to remove all of the bad rain data, day by day, week by week. When I was satisfied, I restarted weewx and life was good.

 

It wasn't until a month later, while flipping through some of my past data that I realized I had somehow managed to remove much more data than I wanted to. I mean, we're talking like a year's worth of rain data.

 

I don't want to import those days all over again, but I can if that's the only way. I'd much rather like to find a way to "bring over" the rain from the saved database for a range of dates, then start the fixing process again. When I brought the data over from wunderground, there were lots of dew point/humidity and temperature anomolies I had to fix as well.

 

My question:  Is there a way (perhaps using the sqlite browser) to import a column (rain) from one database (the saved one) to the new database for just a range of dates?

 

Thanks in advance!

[p q]

Have you tried googling for inserting into a sqlite db from another? I'm not trying to be an ass, but if I had the problem you describe thats what I'd do. I'm sure there's sql you could use but I don't do it all the time and would have to look it up. 

--
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/CAC5oUmN6qQVrx0YTCLHhkKRdxJAg7%3DEjtcwDkudBO8bzJm956A%40mail.gmail.com.

[Ernest Jillson]

I actually expected an answer like this. I'm not trying to be an ass, but if you don't have the answer to my dilemma, you could just scroll on down to the next post. But yes, I did use Google. Found some things about "joining" databases. I'm just worried about compromising the integrity of the database by doing that. Of course, I'd make a backup first. I didn't find anything specific about copying certain time ranges.

 

I figured someone here would have already been in the same boat I am and have already gone through the process.

 

Thanks anyway.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAA1SM22%2BL2zPhtr205-aO5RmZ%3DSr-LheKcSsw6vP%2Bm3gw-uOnA%40mail.gmail.com.

[wfs...@gmail.com]

Here's an ONLY PARTIALLY TESTED suggestion using the sqlite3 utility program.  Like you said, you should only try this on copies

of both databases.  Then when you're satisfied, you can replace your live database with the fixed copy.

Start sqlite3, attach both the "good" and the "bad" databases, then execute an UPDATE statement for a range of dates.  In this example, db1 is the

database that gets fixed, and db2 has the correct rain values.

.attach database 'weewx.sdb' as db1

.attach database 'weewx2.sdb' as db2

update db1.archive set rain = (select rain from db2.archive where db1.archive.datetime == db2.archive.datetime)

 where date(db1.archive.datetime, 'unixepoch', 'localtime') between '2021-01-01' and '2021-05-01';

.exit

Then I'd start sqlite3 again and do some select queries to validate that all is good in db1.

Walt

[Les Niles]

Here’s an outline of what I would do.  I use mysql and know nothing about sqlite, but assume the basic export and delete operations are easy.

1. Export the data you want from the old sqlite database into a CSV file.  Export the full archive records, not just the missing field(s).  

2. Edit the CSV file if needed, to remove some records or whatever.

3. Use sqlite to delete from the current database the records corresponding to those in the CSV file.  (backup copy of db, like you said!)

4. Use wee_import to import the CSV file into the current database.  

5. Use wee_database to drop and rebuilt the daily summaries in weewx.

I used something like this to resurrect a bunch of historical data after a severe case of user error earlier this year. 

  -Les

[wfs...@gmail.com]

I made a goof in my previous post.  ATTACH is an SQL statement, not a dot command.  It should be:

attach database 'weewx.sdb' as db1;

attach database 'weewx2.sdb' as db2;

update db1.archive set rain = (select rain from db2.archive where db1.archive.datetime == db2.archive.datetime)

 where date(db1.archive.datetime, 'unixepoch', 'localtime') between '2021-01-01' and '2021-05-01';

.exit

Walt

[Ernest Jillson]

Thank you Walt and Les!

 

Both of these suggestions are great and greatly appreciated!  I will make a backup of my database and try one or both of these today, time permitting.

 

Ernie

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/9c58d701-884a-475b-9b90-9426241249a3n%40googlegroups.com.