combining old and new databases

[Lynda Howell]

I needed to upgrade Raspbian from Stretch to Buster, Python from 2 to 3, and weewx from 3 to 4.  So it seemed to make more sense to install everything fresh rather than upgrade anything in place. That all worked great.

But then I got distracted and let the new system run for several weeks before discovering that I hadn't copied my old weewx.sdb over.  So now I have several weeks' of data in a database with the new expanded structure, and 7 years' worth in another sdb with the old structure.  And some of the new data have aged off of my TE923, so I can't just re-download them.

So I'm trying to get everything into one database with the expanded structure.

I used sqlite3 to dump the new records into recent.sql.  Then I backed up weewx.sdb and replaced it with my old one.  Following https://github.com/weewx/weewx/wiki/Switching-to-the-new-wview_extended-schema, I ran wee_database weewx.conf --reconfigure and renamed weewx.sdb_new to weewx.sdb.  Then I ran wee_database weewx.conf --rebuild-daily and renamed weewx.sdb_new to weewx.sdb.  

And now I'm trying to import the new records from recent.sql.

When I just did sqlite3 weewx.sdb < recent.sql, I got errors because it tried to create tables that already existed.  So I edited recent.sql to just include the INSERT statements.  And now sqlite3 weewx.sdb < recent.sql gives errors about tables that don't exist.  

I can put the CREATE TABLE statements back in for the missing tables, but they have names like archive_day_appTemp, archive_day_appTemp1, and archive_day_barometer, so they look like things the --rebuild-daily should have created.  So now I'm afraid I missed something.

What am I doing wrong?

If it'd be better to try to import the new data into the old-format database, I can go back and do that.  I just figured since I needed to do something I might as well get the new format and be ready for any future changes.

Or is it possible to use two databases at once?  7 years of data in one sdb makes for long backups and big dump files -- I've been caught by cross-architecture problems with sqlite files in the past, so I prefer to save my data in text files for preservation.  If I could use the old sdb for historical reports and save new data to a new file, my active file would be a whole lot smaller.  Is that possible, and can they use different schemas?

Thanks!

[vince]

I think you're overthinking things.....but your basic steps look good.

To import the old data into the new db, you would need to convert your old db to the new/bigger schema (if your new db uses the big schema), do the dump, and restore just the INSERT lines (like you did) and rebuild_daily again. 

[Tom Keffer]

Besides Vince's suggestion, here's another one. There's a utility called transfer_db.py in the examples subdirectory that does something very close to what you seek. Modify it slightly, and you'd end up with (NOT TESTED):

from __future__ import with_statement
from weewx.manager import Manager

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

new_archive_dict = {'driver'        : 'weedb.sqlite',
                    'database_name' : '/home/weewx/archive/new.sdb'}

with Manager.open(old_archive_dict) as old_archive:
    with Manager.open(new_archive_dict) as new_archive:

        # This is very fast because it is done in a single transaction context:
        new_archive.addRecord(old_archive.genBatchRecords())

This will merge the contents of the "old database" into the "new database". Modify paths for database_name for the old and new databases as necessary. 

Then rebuild the daily summaries of the new database.

-tk

On Fri, Oct 23, 2020 at 10:47 AM vince <vince...@gmail.com> wrote:

I think you're overthinking things.....but your basic steps look good.

To import the old data into the new db, you would need to convert your old db to the new/bigger schema (if your new db uses the big schema), do the dump, and restore just the INSERT lines (like you did) and rebuild_daily again. 

--
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/23d29e1a-7fef-4973-b183-70b032136a58o%40googlegroups.com.

[howe...@gmail.com]

Vince,

So you're suggesting dump/insert the converted old records into the smaller new file, rather than the new records into the converted old file?  I can try that.  Thanks!

Lynda.

[howe...@gmail.com]

Hi Tom,

I saved that code as merge.py and edited the database paths (I'd done a Debian package install).  But now I'm not sure where to run it.  In /usr/share/weewx (which is where wee_database is), I get "ImportError: No module named configobj".  Anywhere else, I get "ImportError: No module named weewx.Manager. 

That same configobj error showed up the first time I tried to run wee_database, but I solved that by doing cd /usr/share/weewx first.  But that's not helping this time.  I'm using sudo in all cases (and not doing so gives me permission denied errors).  

My Python skills are only a few steps above Hello World, so I may be missing something obvious.  

Thanks for your help.

Lynda.

[vince]

On Friday, October 23, 2020 at 2:31:44 PM UTC-7, howe...@gmail.com wrote:

So you're suggesting dump/insert the converted old records into the smaller new file, rather than the new records into the converted old file?  I can try that.  Thanks!

It doesn't matter if 1+4 = 5 or 4+1 = 5 as long as your total is 5 :-)

 

[wes...@gmail.com]

i will point out that these kind of operations are way easier if you're using mysql as opposed to sqlite.  not too helpful at this point but you may consider changing to mysql.

[Tom Keffer]

Respectively disagree. For the average user, who has not taken a database admin course, MySQL is much more complicated. It's great if you know what you're doing, but the average user does not. Stick with SQLite.

My take on SQLite vs MySQL. Am I missing something? 

-tk

On Fri, Oct 23, 2020 at 4:37 PM wes...@gmail.com <wes...@gmail.com> wrote:

i will point out that these kind of operations are way easier if you're using mysql as opposed to sqlite.  not too helpful at this point but you may consider changing to mysql.

--
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/07ce14a4-9c31-4846-a241-184668627786n%40googlegroups.com.

[howe...@gmail.com]

That's what I would have thought, but old + new is what wasn't working because of the missing tables.  That's why I was confirming that you were just suggesting new + old instead.

But thanks to your advice about PYTHONPATH I was able to get the python approach to run, and we'll see if that fixes it.

Lynda.

[howe...@gmail.com]

It worked!  Thanks so much for all your help.

Lynda

[paul.ba...@gmail.com]

Just an extra note about SQLite3 and MySQL: I am running many small dB using both SQLite3 and MySQL and find both useful and equally easy to use. MySQL is definitely more complex to start with, but provides also much more flexibility than SQLite3. Administration of MySQL is essentially zero.

One small but important difference in dump files is that SQLite3 starts with creating the database, while MySQL erase it before creating the new one.

So if you were running MySQL, you must first edit manually the file, removing both DROP and CREATE lines before reloading the "old' file, while with SQLite3 just remove the CREATE line as you did.

Enjoy and take care!     Paul