What is the recommended python module for SQL database access?

[Chris Angelico]

On Sun, Feb 9, 2014 at 11:47 PM, Asaf Las <roegltd at gmail.com> wrote:
> 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.

The threading locks aren't doing anything, because you don't have
multiple threads here; what you need is SQLite locks, which you'll
already have.

I don't know why it wouldn't work. Unfortunately I wasn't able to test
your code directly - SQLite complained that the table didn't exist,
despite my having created it (at least, so I thought) in interactive
Python. So probably I mucked something up there. Someone who actually
knows SQLite might be better able to explain.

A few points, though.

>         c.execute("UPDATE msgid SET val={0} WHERE id = 0".format(tint))

Don't do this; instead, let c.execute() do the interpolation, by
giving it extra args rather than using .format(). It makes no
difference when you're working with integers, but with strings, it
makes the difference between safe and not-safe (or easy and
unnecessarily fiddly, if you actually take the time to get your
escaping right).

Also, you're connecting and disconnecting repeatedly... oh, I see why
it didn't work when I tried. You're also using two completely
different database names: 'messageid.db' which is named in a constant
and in the default argument, and 'example.db' which is what you
actually use. Should have a single source of truth, otherwise you
confuse the people who might otherwise be able to test your code :)
Anyway. This code is extremely inefficient:

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

Much more common would be to retain a connection and repeatedly
perform queries. Then you won't need to open it EXCLUSIVE, and you can
simply query, update, and then commit (all your locks should be
released at the commit). Do that, and you should see at least
reasonable performance, plus everything should work correctly.

ChrisA