Merging Tables from 2 Different Databases into One with Same Scheme-CodePudding

I'm attempting to merge two databases into one with both databases containing the same scheme, but unique information. I've written a code to merge them together, but I get an error depending on whether I include 'id' (the primary key in both tables) or not. I've read that when merging tables together I should only maintain the the primary key in one of the tables and have it as an auto increment to set the primary key in the next table. When I've done this I get an error saying I've supplied one less column of value than the table requires, but when I include 'id' I get an error for unique id's (because the primary key's in both programs are the exact same). If someone can tell me why my primary key isn't incrementing for this I would really appreciate it. If I described anything poorly or didn't present enough information let me know and I'll supplement where needed.

#Table being imported's table structure

def build_case_study_1_table():
    with sqlite3.connect('albums1.db') as db:
        db.execute(
            "CREATE TABLE IF NOT EXISTS albums(" \
            "id INTEGER PRIMARY KEY NOT NULL," \
            "nr INTEGER NOT NULL," \
            "band TEXT NOT NULL," \
            "song TEXT NOT NULL," \
            "album TEXT NOT NULL," \
            "duration TEXT NOT NULL);")

#Table being merged into, my main table

conn = sqlite3.connect('albums.db')
c = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS albums (
    id INTEGER PRIMARY KEY NOT NULL,
    nr INTERGER NOT NULL,
    band TEXT NOT NULL,
    song TEXT NOT NULL,
    album TEXT NOT NULL,
    duration TEXT NOT NULL
    )""")

#Code to merge the two

def merge_databases():
    
    db_main = sqlite3.connect('albums.db')
    db_import = sqlite3.connect('albums1.db')

    import_cursor = db_import.cursor()
    import_cursor.execute('SELECT * FROM albums')
    output = import_cursor.fetchall()
    
    sql = "INSERT INTO albums (nr, band, song, album, duration) VALUES (:nr, :band, :song, :album, :duration)"
    main_cursor = db_main.cursor()
    for row in output:
        main_cursor.execute(sql, row)
        
    db_main.commit()
    import_cursor.close()
    main_cursor.close()

CodePudding user response：

You can pass :id null which will evaluate as null for the column id and it will get the appropriate value since it is defined as INTEGER PRIMARY KEY:

sql = "INSERT INTO albums VALUES (:id   null, :nr, :band, :song, :album, :duration)"
main_cursor = db_main.cursor()
for row in output:
    main_cursor.execute(sql, row)

Or, with executemany() to avoid the for loop:

sql = "INSERT INTO albums  VALUES (:id   null, :nr, :band, :song, :album, :duration)"
main_cursor = db_main.cursor()
main_cursor.executemany(sql, output)