[Tutor] sqlite3 making a spurious duplicate?

[Steven D'Aprano]

I made one more diagnostic change to your script, changing the 
FillWithStars function to this:


def FillWithStars():
    with sqlite3.connect("stars.db") as connection:
        connection.executescript("""
            CREATE TABLE brightest(
            name,
            constellation,
            apparent_magnitude,
            absolute_magnitude,
            distance);
            INSERT INTO brightest VALUES("Arcturus", "Bootes", -0.04, 0.2, 34);""")

        stored_stars = connection.execute("SELECT * FROM BRIGHTEST")
        for star in stored_stars:
            print(star)
        del stored_stars
        print('-'*40)

        connection.executemany("INSERT INTO brightest VALUES(?, ?, ?, ?, ?)",
            [("Canopus", "Carina", -0.72, -2.5, 74),])

        stored_stars = connection.execute("SELECT * FROM BRIGHTEST")
        print("stars as reported inside the with block")
        for star in stored_stars:
            print(star)
        stored_stars = connection.execute("SELECT * FROM BRIGHTEST")

    print("stars as reported outside the with block")
    for star in stored_stars:
        print(star)




As you can see, this now prints the stars from inside the with 
block, while the database connection is still open, and then a second 
time, when the database connection is closed.

The first prints the expected information, the second does not:

[steve at ando ~]$ python3 why5.py
('Arcturus', 'Bootes', -0.04, 0.2, 34)
----------------------------------------
stars as reported inside the with block
('Arcturus', 'Bootes', -0.04, 0.2, 34)
('Canopus', 'Carina', -0.72, -2.5, 74)
stars as reported outside the with block
('Arcturus', 'Bootes', -0.04, 0.2, 34)
('Arcturus', 'Bootes', -0.04, 0.2, 34)
('Canopus', 'Carina', -0.72, -2.5, 74)



So I now expect that this is a misuse of the stored_stars cursor object. 
(Or, possibly, a bug in the cursor object.) If you want to use the 
cursor object, it appears that the connection to the database must be 
open. If you want to use it after closing the database, I think you need 
to extract the data into a list first:

# untested
stored_stars = list(connection.execute("SELECT * FROM BRIGHTEST")



Last but not least, I tried looking at the sqlite database directly:

[steve at ando ~]$ sqlite3 stars.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/steve/stars.db
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE brightest(
            name,
            constellation,
            apparent_magnitude,
            absolute_magnitude,
            distance);
INSERT INTO "brightest" VALUES('Arcturus', 'Bootes', -0.04, 0.2, 34);
INSERT INTO "brightest" VALUES('Canopus', 'Carina', -0.72, -2.5, 74);
COMMIT;




So it looks to me that the right data is stored in the database itself, 
it is just a problem with (mis)using a cursor object after the 
connection is closed.

-- 
Steve