are there pros or contras, keeping a connection to a (sqlite) database?

Discussion in 'Python' started by Stef Mientki, Sep 8, 2010.

  1. Stef Mientki

    Stef Mientki Guest

    hello,

    I wrap my database in some class, and on creation of the instance, a connection to the database is
    created,
    and will stay connected until the program exists, something like this:

    self.conn = sqlite3.connect ( self.filename )

    Now I wonder if there are pros or contras to keep the connection to the database continuously "open" ?

    thanks,
    Stef Mientki
    Stef Mientki, Sep 8, 2010
    #1
    1. Advertising

  2. Stef Mientki

    John Nagle Guest

    Re: are there pros or contras, keeping a connection to a (sqlite)database ?

    On 9/8/2010 10:09 AM, Stef Mientki wrote:
    > hello,
    >
    > I wrap my database in some class, and on creation of the instance, a connection to the database is
    > created,
    > and will stay connected until the program exists, something like this:
    >
    > self.conn = sqlite3.connect ( self.filename )
    >
    > Now I wonder if there are pros or contras to keep the connection to the database continuously "open" ?
    >
    > thanks,
    > Stef Mientki


    Open is OK. Open is good, because the database system
    gets to cache some data. Open with an uncommitted transaction
    may leave the file locked, preventing access by other processes.
    So make sure you commit before you go idle.

    John Nagle
    John Nagle, Sep 8, 2010
    #2
    1. Advertising

  3. Stef Mientki

    CM Guest

    Re: are there pros or contras, keeping a connection to a (sqlite)database ?

    On Sep 8, 1:09 pm, Stef Mientki <> wrote:
    >  hello,
    >
    > I wrap my database in some class, and on creation of the instance, a connection to the database is
    > created,
    > and will stay connected until the program exists, something like this:
    >
    >     self.conn = sqlite3.connect ( self.filename )
    >
    > Now I wonder if there are pros or contras to keep the connection to the database continuously  "open" ?
    >
    > thanks,
    > Stef Mientki


    I do the same thing--good to hear from John that keeping it open is
    OK.

    But another question that this provokes, at least for me is: what
    happens when you call .connect() on the same database multiple times
    from within different parts of the same app? Is that bad? And is it
    that there now multiple connections to the database, or one connection
    that has multiple names in different namespaces within the app?

    I'm not even sure what a "connection" really is; I assumed it was
    nothing more than a rule that says to write to the database with the
    file named in the parentheses.

    Further elaboration from the community would be helpful.

    Thanks,
    Che
    CM, Sep 8, 2010
    #3
  4. Stef Mientki

    News123 Guest

    Re: are there pros or contras, keeping a connection to a (sqlite)database ?

    On 09/09/2010 12:29 AM, CM wrote:
    > On Sep 8, 1:09 pm, Stef Mientki <> wrote:
    >> hello,
    >>
    >> I wrap my database in some class, and on creation of the instance, a connection to the database is
    >> created,
    >> and will stay connected until the program exists, something like this:
    >>
    >> self.conn = sqlite3.connect ( self.filename )
    >>
    >> Now I wonder if there are pros or contras to keep the connection to the database continuously "open" ?
    >>
    >> thanks,
    >> Stef Mientki

    >
    > I do the same thing--good to hear from John that keeping it open is
    > OK.
    >
    > But another question that this provokes, at least for me is: what
    > happens when you call .connect() on the same database multiple times
    > from within different parts of the same app? Is that bad? And is it
    > that there now multiple connections to the database, or one connection
    > that has multiple names in different namespaces within the app?

    CM,

    Do you talk about a multithreaded environment?
    or only about an environment with logically separate blocks (or with
    generators),
    and multiple objects each keeping their own connection.

    As far as I know sqlite can be compiled to be thread safe, but is not
    necessarily be default.
    No idea about the library used b python.


    I personally just started sqlite in one of my apps with multithrading
    and in order to be safe I went for the conservative approach
    connect, perform transactions, commit and close.
    However this is probably overkill and later in time I might also ty to
    keep connections open in order to increse performance.


    > I'm not even sure what a "connection" really is; I assumed it was
    > nothing more than a rule that says to write to the database with the
    > file named in the parentheses.
    >
    > Further elaboration from the community would be helpful.
    >
    > Thanks,
    > Che
    News123, Sep 9, 2010
    #4
  5. Re: are there pros or contras,keeping a connection to a (sqlite) database ?

    On Thu, 09 Sep 2010 10:41:50 +0200, News123 <> declaimed
    the following in gmane.comp.python.general:

    >
    > As far as I know sqlite can be compiled to be thread safe, but is not
    > necessarily be default.
    > No idea about the library used b python.
    >


    >>> import sqlite3 as db
    >>> db.threadsafety

    1
    >>>


    And from the specification of DB-API compliance (PEP 249):
    -=-=-=-=-
    threadsafety

    Integer constant stating the level of thread safety the
    interface supports. Possible values are:

    0 Threads may not share the module.
    1 Threads may share the module, but not connections.
    2 Threads may share the module and connections.
    3 Threads may share the module, connections and
    cursors.

    Sharing in the above context means that two threads may
    use a resource without wrapping it using a mutex semaphore
    to implement resource locking. Note that you cannot always
    make external resources thread safe by managing access
    using a mutex: the resource may rely on global variables
    or other external sources that are beyond your control.
    -=-=-=-=-
    --
    Wulfraed Dennis Lee Bieber AF6VN
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Sep 9, 2010
    #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. J.S.
    Replies:
    10
    Views:
    6,070
    shawpnendu
    May 20, 2009
  2. exhuma.twn
    Replies:
    5
    Views:
    843
    exhuma.twn
    Sep 19, 2007
  3. Robert Kern
    Replies:
    0
    Views:
    495
    Robert Kern
    Sep 11, 2010
  4. Carl Youngblood
    Replies:
    1
    Views:
    222
    Carl Youngblood
    Apr 9, 2005
  5. Replies:
    4
    Views:
    344
Loading...

Share This Page