[Tutor] A toy example to have clarity on Sqlite3 module

[Alan Gauld]

On 16/11/2021 16:33, Manprit Singh wrote:
> Dear Sir ,
> 
> I have written an example of  sqlite 3 module, without using a cursor
> object . and with exception handling as given below:
> 

> try:
>     con = sqlite3.connect("example.db")
>     con.execute("create table lang(name, first_appeared)")
>     con.executemany("insert into lang values (?, ?)", lang_list)
> except sqlite3.OperationalError:   # It can handle if table already exists
>     print("Error! You may be trying to make a new table with same name")
> else:
>    for row in con.execute("select * from lang"):
>        print(row)
> finally:
>     con.close()


> Just need to know, is this the correct implementation ? any suggestions

As you say it's a toy example and therefore sufficient for it's purpose.

It is not good database programming style however. You should create
a cursor for anything non-trivial. There are several reasons for
this but one is that if you need to execute multiple different
queries during your app you will need a cursor for each, otherwise
each new query will wipe out the result of the previous one.

If there is not much data being returned you can copy the results
into local variables but on a real-world database that isn't
always practical.

Using "select * from table" is also not recommended since your
code will probably break if the structure of the table is
modified - eg. columns are added. So it is always better
to specify exactly which columns you expect to get back.

Also creating a database table without primary keys (and
usually an ID field) leads to problems in coding (how to
handle duplicates etc)

Using a catch-all try/except is also not usually recommended since
you want to know what part caused the error, so you would need
to introduce code to examine the error in detail.

Closing in a finally clause, however, is good practice. In particular
you should close all open resources including cursors and
connection(s) - there may be more than one!

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