[Tutor] Query on sqlite3 module

[Dennis Lee Bieber]

On Sun, 8 Aug 2021 10:22:55 +0530, Manprit Singh
<manpritsinghece at gmail.com> declaimed the following:


>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":
>

	You appear to have an unusual Use Case there. The database indicates
that it is a time-ordered list of market transactions. A change in stock
price normally does not get applied retroactively -- the price in the
transaction is presumed to be the price that was paid AT THE TIME OF THE
TRANSACTION.  If there is a new price, it would be entered as part of a new
transaction, on a new date.

	The only justification I can see for modifying historical prices would
be if a data entry clerk made the mistake when entering the day's
transactions.

>Is the following the correct  way to do it  ?
>
>cur.execute('UPDATE stocks set price=55.62 where symbol= ?', ("RHAT",))
>where cur is cursor.

	As has already been mentioned, price should also be a parameter...

>cur.execute('UPDATE stocks set price=? where symbol= ?', (price, "RHAT"))
>
>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,))

	Given the sample database, I'd suggest adding an ORDER BY (RDBMs are
not required -- by definition of a relation -- to return results in any
particular order.

>cur.execute("select * from stocks where price = ? ORDER BY date", (55.62,))

>Kindly guide

https://en.wikipedia.org/wiki/SQL_syntax
https://en.wikipedia.org/wiki/Database_normalization
https://www.sqlite.org/docs.html
https://www.sqlitetutorial.net/


-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed at ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/