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

[Manprit Singh]

Dear Sir,
Kindly look at this modified example, need your comments on using
con.rollback() and con.commit() & fetchone(), fetchmany() and fetchall()
import sqlite3


lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]

try:

    # Connection object represents the database
    con = sqlite3.connect("example.db")

    # Cursor object - To execute the queries & Fetch the records
    cur = con.cursor()

    # Create the table
    cur.execute("create table lang(name, first_appeared)")

    # Fill the table
    cur.executemany("insert into lang values (?, ?)", lang_list)

except sqlite3.Error:  # base class of all exceptions of sqlite3 module

    con.rollback()    # Rolls back any changes to database
    print("Database related error is there")


else:
    con.commit()   # Commits the current transaction
    cur.execute("select name from lang")
    print(cur.fetchone())            # Fetches next row
    print("-------------------")
    print(cur.fetchmany(size=1))     # fetches next rows(size given)
    print("-------------------")
    print(cur.fetchall())            # Fetches all(remaining) rows

finally:
    cur.close()
    con.close()

Placing con.rollback() in the except block will rollback all changes in the
database if any exception occurs, that's why I have placed it in the except
block. con.commit() will commit all changes done so I have placed it in the
else block to ensure if no exception is raised all changes must be
committed..
Need your comments .

Regards
Manprit Singh

On Wed, Nov 17, 2021 at 4:07 AM Alan Gauld via Tutor <tutor at python.org>
wrote:

> 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
>
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
>