What is the recommended python module for SQL database access?

[Asaf Las]

On Sunday, February 9, 2014 1:00:58 PM UTC+2, Chris Angelico wrote:
> The biggest downside of SQLite3 is concurrency. I haven't dug into the
> exact details of the pager system and such, but it seems to be fairly
> coarse in its locking. Also, stuff gets a bit complicated when you do
> a single transaction involving multiple files. So if you have lots of
> processes writing to the same set of SQLite tables, you'll see pretty
> poor performance.
> ChrisA

Hi Chris 

i simply tested running 2 independent processes started at same time in 
parallel towards same sqlite database and never get 20000 in that row
though used exclusive lock on DB. might be i did something wrong.

p.s. threading locks don't do anything in this example.

import sqlite3
import threading
#from threading import Lock

# Constants 
CNT_DB_NAME = "messageid.db"

class MessageId:
    ''' --------------------------------------------------------------------------
        Following must be done in advance:
        - Create DB     sqlite3 sqlite_msgd.db
        - Create table  CREATE TABLE msgid (id INTEGER PRIMARY KEY, val INTEGER);
        - Inser def     INSERT INTO msgid VALUES (0, 0);
        --------------------------------------------------------------------------'''
    def __init__(self, dflag = False, dbname = 'messageid.db'):
        #print(type(self))
        #print(id(self))
        self._debug = dflag
        self._lock = threading.Lock()
        self._dbname = dbname
        if self._debug:
            print("MessageId.__init__(dbname = {0})".format(dbname))
        
    def get_msgid(self):
        ''' --------------------------------------------------------------------------
            - Acquire lock
            - Connect to database
            - Select current value      SELECT val FROM msgid WHERE id = 0;
            - Increment current value
            - Insert a row of data      UPDATE msgid SET val = 1 WHERE id = new_val; 
            --------------------------------------------------------------------------'''
        self._lock.acquire(True, 1)
        conn = sqlite3.connect('example.db', 10.0, True, "EXCLUSIVE")
        c = conn.cursor()
        c.execute("SELECT val FROM msgid WHERE id = 0")
        tint = int(c.fetchone()[0]) + 1
        c.execute("UPDATE msgid SET val={0} WHERE id = 0".format(tint))
        conn.commit()
        conn.close()
        self._lock.release()
        if self._debug:
            print("MessageId.get_msgid() = ", tint)
        return tint

tclass = MessageId()

for k in range(10000):
    tclass.get_msgid()