[Tutor] Query on sqlite3 module

[Cameron Simpson]

On 08Aug2021 10:22, Manprit Singh <manpritsinghece at gmail.com> wrote:
>Consider a table "stocks" being made in a sqlite3 database, whose 
>entries
>are as follows :
>date                  trans  symbol   qty    price
>
>('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
>('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
>('2006-04-08', 'BUY', 'CPQ', 160, 40.56)
>('2006-03-27', 'BUY', 'IBM ', 120, 45.34)
>
>1) I have to update price = 55.62 where symbol is "RHAT":
>
>Is the following the correct  way to do it  ?
>
>cur.execute('UPDATE stocks set price=55.62 where symbol= ?', ("RHAT",))
>where cur is cursor.

That looks ok to me. I would make 55.62 also a parameter though - almost 
every value you use in an SQL statement should be a parameter so that 
the driver can correctly pass it to the database. Whenever you embed a 
value (particularly strings) in SQL directly there is a risk a mistake 
can lead to injection, where what you intended to be a simple scalar 
value was misused by the SQL because it's actually SQL syntax.

>2) I have to print all matching rows where price is 55.62:
>Is the following the correct  way to do it  ?
>
>cur.execute("select * from stocks where price = ?", (55.62,))
>print(cur.fetchall())

That will work just fine. Did you try it?

I'd probably write a little loop to print each row, because we usually 
expect listings to have one row per line.

Cheers,
Cameron Simpson <cs at cskk.id.au>