[Tutor] sqlite3 making a spurious duplicate?

[Alan Gauld]

On 19/04/17 03:21, Marilyn Davis wrote:

> connection.execute("SELECT * FROM BRIGHTEST")
> 
> returns a <sqlite3.Cursor object at 0x101bda260>, not a regular python
> sequence.  

Pretty much everything inn  SQL land uses cursor objects.
It's why best practice creates an explicit cursor for
executing statements rather than the impl8icit one on
the connection. Also of course because you can then
have more than one query active at a time.

A connection based query is fine where you just want
to jump in and pull out some data, in that case a
single, implicit, cursor doesn't cause any issues.

> 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.

Can you show what you mean there?
Provided you use the cursor inside the context
it should still work.

> 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?

No, you often need to put the python that manipulates the
data inside the context, especially if the database is
large. You could (should?) of course put the manipulation
into a function that is defined outside the context and
called from within, but what you probably should not do
is try to pull all your data into memory within the context
then manipulate it outside. This would be particularly
dangerous in a multi-user database context, where the
data may be changing independently of your program.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos