Pyro and sqlite3 problem

Discussion in 'Python' started by Sébastien Ramage, Dec 1, 2007.

  1. Hi !

    I'm trying to build an client/server app based on Pyro and sqlite3.
    But I have a problem using sqlite3 on the server

    I got this error :

    sqlite3.ProgrammingError: ('SQLite objects created in a thread can
    only be used
    in that same thread.The object was created in thread id 240 and this
    is thread i
    d 4068', 'This error occured remotely (Pyro). Remote traceback is
    available.')

    what can I do to avoid this ?

    I'm using Python 2.5.1,Pyro 3.7 under Windows

    Sébastien
    Sébastien Ramage, Dec 1, 2007
    #1
    1. Advertising

  2. On Sat, 1 Dec 2007 01:29:32 -0800 (PST), "Sébastien Ramage"
    <> declaimed the following in
    comp.lang.python:

    >
    > sqlite3.ProgrammingError: ('SQLite objects created in a thread can
    > only be used
    > in that same thread.The object was created in thread id 240 and this
    > is thread i
    > d 4068', 'This error occured remotely (Pyro). Remote traceback is
    > available.')
    >
    > what can I do to avoid this ?
    >

    Off hand -- ensure that each remote access runs the entire sequence
    of "connect, cursor, execute, fetch, cursor-close, connection-close"
    rather than trying to, say, create a cursor in one access and then use
    that cursor on a second access...

    Or maybe create one long-running thread to handle the database
    access and use queue objects to transfer the remote access parameters to
    the database thread, then retrieve from a return queue.
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Dec 1, 2007
    #2
    1. Advertising


  3. > Off hand -- ensure that each remote access runs the entire sequence
    > of "connect, cursor, execute, fetch, cursor-close, connection-close"
    > rather than trying to, say, create a cursor in one access and then use
    > that cursor on a second access...
    >
    > Or maybe create one long-running thread to handle the database
    > access and use queue objects to transfer the remote access parameters to
    > the database thread, then retrieve from a return queue.
    > --


    thanks you for answer.
    Do you think the first solution will be slow? I think I must use
    locking to avoid problem.

    The second solution seems better and looks like what I wanted to build
    at first time, but maybe an ever opened sqlite database isn't a good
    thing ?
    Sébastien Ramage, Dec 2, 2007
    #3
  4. I'm trying to build a client/server app
    the server use a sqlite3 database to store differents things
    the client ask the server to get value or store new entries in the
    database.
    The problem is that Pyro create a new thread for each client

    at first time I create the connection to database at server start and
    I would re use the connection for transaction but I get the error.
    Maybe I can open/close database for each transaction but maybe it
    would be slow ?

    Seb


    2007/12/1, Sergio Correia <>:

    If you are creating a cursor/connection object, you need to use it
    in
    the same thread that created it. Otherwise, some weird errors can
    happen (as many of this list have said).

    If you give us more info about the type of the problem (what are
    you
    trying to do?) we can help you build a safer app.

    Best,
    Sergio

    PS: When faced with the same error, I just recycled the objects
    used
    in the I/O operations and the problem was solved. However, that
    was
    done locally, without using Pyro, so your milleage may vary.

    On Dec 1, 2007 4:29 AM, Sébastien Ramage
    <> wrote:
    >
    > Hi !
    >
    > I'm trying to build an client/server app based on Pyro and

    sqlite3.
    > But I have a problem using sqlite3 on the server
    >
    > I got this error :
    >
    > sqlite3.ProgrammingError: ('SQLite objects created in a thread

    can
    > only be used
    > in that same thread.The object was created in thread id 240 and

    this
    > is thread i
    > d 4068', 'This error occured remotely (Pyro). Remote traceback

    is
    > available.')
    >
    > what can I do to avoid this ?
    >
    > I'm using Python 2.5.1,Pyro 3.7 under Windows
    >
    > Sébastien
    > --
    > http://mail.python.org/mailman/listinfo/python-list
    >
    Sébastien Ramage, Dec 2, 2007
    #4
  5. On Sun, 2 Dec 2007 01:46:05 -0800 (PST), "Sébastien Ramage"
    <> declaimed the following in
    comp.lang.python:

    >
    > thanks you for answer.
    > Do you think the first solution will be slow? I think I must use
    > locking to avoid problem.
    >

    May not need your own level of locking since SQLite has a fairly
    complex internal set of locks -- but you /would/ have to program with
    recovery code if one thread, say, attempts to commit() and gets an error
    message that the DB is locked. SQLite allows many reader threads in
    parallel, but the first one that attempts to update the DB will: 1)
    block any NEW reader thread and, 2) block, itself, until all other
    reader threads have exited -- only then will it propagate upwards to
    actually writing the DB changes.

    So it is best to encapsulate any updates into a short fast,
    self-contained, sequence. If you have to do something like: read data,
    present to use, get user changes, submit update... You will likely need
    to do it in two transactions: read data/commit (unlocking DB), present
    to user, get user changes (hopefully only one record or group of related
    records), submit an update where you specify a match of the old data
    values (this is to allow you to detect if some other transaction slipped
    in an update), commit.

    It IS lot of overhead, but I suspect even a full client/server
    database [the DBMS controls the data files and clients send operations]
    (rather than SQLite file-server model [each client opens/controls the
    same shared files -- which is what M$ JET/Access does]) will require
    something similar as you'd need to maintain the open connection/cursor
    between operations.

    > The second solution seems better and looks like what I wanted to build
    > at first time, but maybe an ever opened sqlite database isn't a good
    > thing ?


    It would have been open continuously in your original attempt to
    share across threads. The DB access thread would only have to open
    (connect) once, and create one cursor. Updates will still have to follow
    the fast short mode, in which a read/display is a separate transaction
    from an update.

    The data model may become more complex... (pseudocode)

    DBRequestQ = Queue.Queue()

    class Transaction(object):
    def __init__(self):
    self.myQ = Queue.Queue()
    self.ops = []
    def addSelect(self, SQL, parms=None):
    self.ops.append(("SELECT", SQL, parms))
    def addUpdate(self, SQL, parms=None):
    self.ops.append(("UPDATE", SQL, parms))
    def addInsert(self, SQL, parms=None):
    self.ops.append(("INSERT", SQL, parms))
    def addShutdown(self):
    self.ops.append(("SHUTDOWN", None, None))
    def submit(self):
    DBRequestQ.put(self)
    def retrieve(self):
    myData = []
    for (op, status, data) in self.myQ.get():
    myData.append((op, status, data))
    if op == "COMMIT": return myData

    def DBThread():
    con = sqlite.connect()
    cur = con.cursor()
    run = True
    for transaction in DBRequestQ.get():
    #begin transaction
    for (op, SQL, parms) in transaction.ops:
    if op == "SHUTDOWN":
    run = False
    break
    if parms:
    cur.execute(SQL, parms)
    else:
    cur.execute(SQL)
    if op == "SELECT":
    data = cur.fetchall()
    else:
    data = None
    #get operation status code (somehow?)
    #ie, number of records updated/inserted, or error
    transaction.myQ.put((op, status, data))
    con.commit()
    #get commit status
    transaction.myQ.put(("COMMIT", status, None))
    if not run: exit #exit DB thread
    cur.close()
    con.close()

    ....

    A request sequence would then look something like:

    aTrans = Transaction()
    aTrans.addSelect("select ...", (itm1, ... itmN))
    aTrans.add*(..., ...)
    ....
    aTrans.submit()
    results = aTrans.retrieve()
    del aTrans

    Please note that I did not hack in any time-out or error recovery
    logic...


    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Dec 2, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John French

    [Pyro] Newbie Question Regarding Pyro

    John French, Jan 7, 2005, in forum: Python
    Replies:
    0
    Views:
    265
    John French
    Jan 7, 2005
  2. writeson
    Replies:
    0
    Views:
    292
    writeson
    Mar 20, 2008
  3. Irmen de Jong
    Replies:
    0
    Views:
    252
    Irmen de Jong
    Oct 30, 2011
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    471
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    249
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page