[Tutor] sqlite3 making a spurious duplicate?

[Marilyn Davis]

Thank you Alan, Steven and Peter,

So, this call:

connection.execute("SELECT * FROM BRIGHTEST")

returns a <sqlite3.Cursor object at 0x101bda260>, not a regular python
sequence.  I did not know that.  And, the connection must still be alive
when you iterate it.

That is a very important tidbit of info.

The fix is to listify the Cursor object, or iterate while still in the
context.  Interesting.  Closing the context and opening a new one fixes it
too:

    with sqlite3.connect("stars.db") as connection:

        connection.executescript("""
            CREATE TABLE brightest(
            name,
            constellation,
            apparent_magnitude,
            absolute_magnitude,
            distance);
            INSERT INTO brightest VALUES("Canopus", "Carina", -0.72, -2.5,
74);
""")

        connection.executemany("INSERT INTO brightest VALUES(?, ?, ?, ?, ?)",
            [("Arcturus", "Bootes", -0.04, 0.2, 34),])

    with sqlite3.connect("stars.db") as connection:
        stored_stars = connection.execute("SELECT * FROM BRIGHTEST")

    for star in stored_stars:
        print(star)

---
But starting a new context, instead, before the executemany does not fix
it!  We still get the duplicate Canopus in the iterable, even though it
was inserted into the data a different context.

I suspect that this is not an intended behavior.

Another question: is it a good principle to try to only put SQL in the
context, and then regular python outside that context?  It suits my
instinct  but maybe it is just my superstition?

Thank you so much.

Marilyn

p.s.  Thank you, Steven, for liking the post.  You guys prove what I try
to pound into students: pretty code that does not work is much better than
hard-to-read code, even if it works -- because with pretty code, people
are happy to help you, and with working bad code, when it must be
modified, you have a mess.