Trying to save a sqlite table inside another table using python-CodePudding

The problem now is that I can only enter one record. No errors are recorded. It just takes the first record from one database and puts in the other database. I am trying to create a machine usable database from the user interface database. I will try to transfer around 100 records once it is working. I would appreciate in comments or suggestions. Thank you!

import sqlite3

sql = 'INSERT INTO heavenStream (scene, cascade, enclosure, sensor, streamer, dither) VALUES (?, ?, ?, ?, ?, ?)'

def dropTable(crs,conn):
    crs.execute("DROP TABLE IF EXISTS heavenStream")

def createTable(crs,conn):
    sql ='''CREATE TABLE heavenStream(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    scene TEXT,
    cascade TEXT,
    enclosure TEXT,
    sensor TEXT,
    streamer TEXT,
    dither TEXT,
    timeStream TEXT,
    streamTime TEXT
    )'''
    crs.execute(sql)
    print("Table created successfully........")

def insert_one(conn, crs):
    crs.execute("SELECT * FROM animalStream")

 for row in crs:
        scene = row[1] 
        cascade = row[2] 
        enclosure = row[3]
        sensor = row[4]
        streamer = row[5]
        dither = row[6]
        print(f"{row[1]} {row[2]} {row[3]} {row[4]} {row[5]} {row[6]}")
        try:
           crs.execute(sql, (scene, cascade, enclosure, 
           sensor,streamer,dither))
        except sqlite3.IntegrityError as err:
           print('sqlite error: ', err.args[0]) # column name is 
           not unique
        conn.commit()       

def main():
    conn = sqlite3.connect("/home/harry/interface/wildlife.db")
    crs = conn.cursor()
    dropTable(crs,conn)
    createTable(crs,conn)
    insert_one(conn, crs)
#   conn.commit()
    conn.close()
    print('done')

main()

The user interface database has had records deleted. There is one record with an id of 64 and the rest are in the 90's.

CodePudding user response：

The cursor (crs) changes here

crs.execute(sql, (scene, cascade, enclosure,sensor,streamer,dither))

after the first insert. Therefore, there are "no more rows to fetch" in the orginal crs.

One solution would be to instantiate another cursor for the insert. Another solution would be to fetchall() the rows into a variable and iterate over that variable as with:

    rows = crs.execute("SELECT * FROM animalStream").fetchall()

    for row in rows: