[Tutor] Proper SQLite cursor handling?

[boB Stepp]

On Mon, Jul 5, 2021 at 3:28 AM Cameron Simpson <cs at cskk.id.au> wrote:
>
> On 04Jul2021 20:33, boB Stepp <robertvstepp at gmail.com> wrote:
> >3)  Am I handling the database stuff properly so far?
>
> Just to this, there's some stuff to criticise here.
>
> >        (
> >            self.game_id,
> >            _,  # Already have game_name value.
> >            self.strategy,
> >            self.num_reserve_cards,
> >            self.num_foundation_cards,
> >            self.reserve_card_value,
> >            self.foundation_card_value,
> >            self.total_score,
> >        ) = self._get_game_data()
>
> This is quite fragile...

I definitely concur as this bothered me before I even composed my
post.  But I wanted to get out a question as to my overall approach
before I wasted too much time going down the wrong rabbit holes.
However, I had hopes that someone would have a better way than the
above...

> ... If the columns in your game table change, even in
> their order, this will assign the wrong things to the various
> attributes. You can have sqlite3 return a Row object instead of a bare
> tuple of values for each row. There's an example in the module docs in
> the "Row Objects" section. Adapting it, after you connect to the
> database, add:
>
>     self.con.row_factory = sqlite3.Row
>
> Then the row fetch methods return Row insteance, which still act like
> tuples but also have attributes named after the columns. So the
> assignment above would become:

This definitely is useful and the way to go.  I had seen this in the
docs and meant to read about it, but I was/am currently worrying about
SQL usage.

> Also, very importantly, this is the wrong way to insert values into an
> SQL query.  The format field {self.game_name} will just get the bare
> game name inserted raw into the SQL, which will likely be a syntax error
> or be misread as a column name. See this document:
>
>     https://xkcd.com/327/

I am aware of this, but game_name is supposed to be fully vetted by
this point and would map str in Python to TEXT in that column.  Can
there still be issues with this getting inserted into the table?  But
I suppose many bad things can happen during code development which
might alter the safeness of that value, so I take your point and will
adopt this as a generally used technique.

> The execute method accepts a dict for things to fill in in the SQL.
> There's an example in the docs:
>
>     cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})
>
> That will place syntacticly correct values at the ":who" and ":age"
> placeholders in the SQL string, using the values from the dict. Do that
> instead.
>
> >        game_data = cur.fetchall()[0]
>
> You ccould just fetchone().

In retrospect I don't know why I didn't use fetchone() as there will
always only be one row returned as game_name must be unique in that
table.

> >        if not game_data:
>
> This test is too late. game_data won't be empty or false, you'll get an
> exception during the fetch. You could go:

I beg to differ here.  This test is meant to cover the "new game" case
where there is not an entry yet for that game.  I tested this in the
interpreter and as I originally wrote it returns an empty list.

Thanks, Cameron!
boB Stepp