Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #65755

Re: What is the recommended python module for SQL database access?

Show all headers | View raw

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

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

What is the recommended python module for SQL database access? Sam <lightaiyee@gmail.com> - 2014-02-08 00:55 -0800
  Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-08 20:04 +1100
  Re: What is the recommended python module for SQL database access? Marcel Rodrigues <marcelgmr@gmail.com> - 2014-02-09 08:20 -0200
  Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-09 22:00 +1100
    Re: What is the recommended python module for SQL database access? Asaf Las <roegltd@gmail.com> - 2014-02-09 04:47 -0800
      Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-10 00:14 +1100
        Re: What is the recommended python module for SQL database access? Asaf Las <roegltd@gmail.com> - 2014-02-09 05:27 -0800
          Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-10 00:36 +1100
    Re: What is the recommended python module for SQL database access? Walter Hurry <walterhurry@gmail.com> - 2014-02-11 02:57 +0000
      Re: What is the recommended python module for SQL database access? Asaf Las <roegltd@gmail.com> - 2014-02-10 19:02 -0800
        Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-11 14:31 +1100
          Re: What is the recommended python module for SQL database access? Asaf Las <roegltd@gmail.com> - 2014-02-10 19:45 -0800
      Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-11 14:18 +1100
  Re: What is the recommended python module for SQL database access? Marcel Rodrigues <marcelgmr@gmail.com> - 2014-02-09 10:04 -0200
  Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-09 23:13 +1100
  Re: What is the recommended python module for SQL database access? Tim Chase <python.list@tim.thechases.com> - 2014-02-09 08:31 -0600
  Re: What is the recommended python module for SQL database access? Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-02-09 10:40 -0500
  Re: What is the recommended python module for SQL database access? Chris Angelico <rosuav@gmail.com> - 2014-02-10 09:03 +1100
  Re: What is the recommended python module for SQL database access? Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-02-09 19:27 -0500

csiph-web