[Tutor] sqlite3 making a spurious duplicate?

[Steven D'Aprano]

On Mon, Apr 17, 2017 at 04:36:50PM -0700, Marilyn Davis wrote:
> #!/usr/bin/env python3
> """
> Hello Tutors,
> 
> I can't figure out why the FillWithStars() function puts Canopus in the db
> twice.


Good question. And thank you for providing a really well-written, 
simple, clear script that we can run unchanged to demonstrate the 
problem.

I get the same results as you, and like you, I'm not too sure why. But 
I'm leading to suspect the blame lies with sqlite, not your code.

If I take your script and add the following lines between the call to 
connection.executescript(...) and connection.executemany(...):

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

I get this output:

[steve at ando ~]$ python3 why4.py
('Canopus', 'Carina', -0.72, -2.5, 74)
----------------------------------------
('Canopus', 'Carina', -0.72, -2.5, 74)
('Canopus', 'Carina', -0.72, -2.5, 74)
('Arcturus', 'Bootes', -0.04, 0.2, 34)


so it looks to me like the initial call to executescript() correctly 
adds Canopus once, and then the call to executemany() mysteriously 
duplicates it.

I tried one more iteration: swap the two stars, so that Acturus 
is added first, then Canopus:


[steve at ando ~]$ python3 why5.py
('Arcturus', 'Bootes', -0.04, 0.2, 34)
----------------------------------------
('Arcturus', 'Bootes', -0.04, 0.2, 34)
('Arcturus', 'Bootes', -0.04, 0.2, 34)
('Canopus', 'Carina', -0.72, -2.5, 74)


At this point, I'm not sure whether this is a bug in sqlite, or a 
misunderstanding that we're doing something wrong. I think this now 
needs an sqlite expert.



-- 
Steve