SQLite or files?

Discussion in 'Python' started by vlad, Sep 17, 2009.

  1. vlad

    vlad Guest

    Hello,

    I'm a novice in Python and got one question related to the information
    storage for my application.

    I'm currently working on rewriting my own the program that stores
    everyday information sitting in the system tray. It was written in
    Delphi some time ago and proved to be durable and fast enough. All the
    info was stored in the simple RTF files.

    However now I'd like to rewrite this program in Python (using PyQt) as
    I want to make it cross-platform and add/remove some features. Now I'm
    thinking about where to store my information. Would it be better to
    use files as I used to do or use the database, SQLite in particular?
    What will be faster and more flexible in the long run? This
    application will be in the memory most of the time so I'm also
    concerned about memory usage.

    If anyone has any experience of comparison these two approaches please
    help.

    Thanks,
    Vlad
    vlad, Sep 17, 2009
    #1
    1. Advertising

  2. vlad

    Tim Chase Guest

    > info was stored in the simple RTF files.
    >
    > However now I'd like to rewrite this program in Python (using PyQt) as
    > I want to make it cross-platform and add/remove some features. Now I'm
    > thinking about where to store my information. Would it be better to
    > use files as I used to do or use the database, SQLite in particular?
    > What will be faster and more flexible in the long run? This
    > application will be in the memory most of the time so I'm also
    > concerned about memory usage.


    Not knowing what you do with the files, nor what sort of data
    they contain makes it a bit difficult to make suggestions in
    light of the context. However:

    - sqlite will let you perform arbitrary queries against your
    data, so if you want to aggregate or perform complex conditional
    tests, it's just SQL.

    - I don't know if you're currently keeping the RTF in memory the
    whole time, or if you repeatedly reload (whether in one go, or
    streaming it) and reparse the file. This sounds memory and/or
    processor intensive. Using sqlite, the processing is done at the
    C-module level, the data is kept on disk and only brought into
    memory as-requested, being released when you're done with it.

    - concurrently sharing a sqlite database should have minimal
    issues. Sharing RTF files concurrently means locking/contention
    issues. May not be an issue for you.

    - sqlite comes built-in with Python2.5+ while RTF processing is
    not batteries-included from what I can tell[1]

    So in the general case, I see sqlite being a notable win over
    RTF. Depending on your data, if it's just key/value pairs of
    strings, you might look into the anydbm module[2] which has an
    even simpler interface.

    -tkc

    [1]
    http://pyrtf.sf.net

    [2]
    http://docs.python.org/library/anydbm.html
    Tim Chase, Sep 17, 2009
    #2
    1. Advertising

  3. vlad

    ici Guest

    I like shelve for saving small amounts of data, user preferences,
    recent files etc.
    http://docs.python.org/library/shelve.html

    For Qt use QtCore.QCoreApplication.setOrganizationName,
    QtCore.QCoreApplication.setApplicationName than setValue, value from
    QtCore.QSettings.
    ici, Sep 17, 2009
    #3
  4. ici <> writes:

    > I like shelve for saving small amounts of data, user preferences,
    > recent files etc.
    > http://docs.python.org/library/shelve.html


    I like it too, but I hear the great powers that be are going to
    deprecate it.

    >
    > For Qt use QtCore.QCoreApplication.setOrganizationName,
    > QtCore.QCoreApplication.setApplicationName than setValue, value from
    > QtCore.QSettings.
    J Kenneth King, Sep 17, 2009
    #4
  5. En Thu, 17 Sep 2009 11:10:34 -0300, J Kenneth King <>
    escribió:
    > ici <> writes:
    >
    >> I like shelve for saving small amounts of data, user preferences,
    >> recent files etc.
    >> http://docs.python.org/library/shelve.html

    >
    > I like it too, but I hear the great powers that be are going to
    > deprecate it.


    Why?
    Even if it were to be deprecated, that could only happen in 2.7, the
    module would still be there in 2.8, and could only disappear in 2.9 (or
    3.4) (see PEP 4: Deprecation of Standard Modules). And even after it's
    gone, being a pure Python module it's easy to keep the previous version
    around.
    So, I wouldn't worry about that.

    --
    Gabriel Genellina
    Gabriel Genellina, Sep 18, 2009
    #5
  6. vlad

    TerryP Guest

    Gabriel Genellina wrote:
    > And even after it's
    > gone, being a pure Python module it's easy to keep the previous version
    > around.
    > So, I wouldn't worry about that.


    Yeah, I'm sure that is the same kind of thinking that caused 16-bit MS-
    DOS applications to remain a part of Windows NT so long.

    --
    Good bye edit and edlin, you would be missed, if it wasn't for vim!
    TerryP, Sep 18, 2009
    #6
  7. vlad

    alex23 Guest

    TerryP <> wrote:
    > Yeah, I'm sure that is the same kind of thinking that caused 16-bit MS-
    > DOS applications to remain a part of Windows NT so long.


    So what part of the standard library do you recommend using instead?
    Or was there no time for advice between snarkiness?
    alex23, Sep 18, 2009
    #7
  8. vlad

    Aahz Guest

    In article <>,
    Tim Chase <> wrote:
    >
    >- I don't know if you're currently keeping the RTF in memory the
    >whole time, or if you repeatedly reload (whether in one go, or
    >streaming it) and reparse the file. This sounds memory and/or
    >processor intensive. Using sqlite, the processing is done at the
    >C-module level, the data is kept on disk and only brought into
    >memory as-requested, being released when you're done with it.


    You can also make a SQLite database be in-memory, giving you the
    performance benefits of skipping the disk.
    --
    Aahz () <*> http://www.pythoncraft.com/

    "I won't accept a model of the universe in which free will, omniscient
    gods, and atheism are simultaneously true." --M
    Aahz, Sep 18, 2009
    #8
  9. vlad

    TerryP Guest

    alex23 wrote:
    > So what part of the standard library do you recommend using instead?
    > Or was there no time for advice between snarkiness?


    As a matter of technique, I believe in fitting the storage to the
    particulars of the problem at hand.

    In my own projects, I will often employ simple text based formats
    (unix-rc, ini, or xml) whenever possible, and then roll the
    application specifics to suit it -- for any data that I expect that
    gaining good compression rates on later, will be favourable.
    Personally from what I've read in this thread, I would suggest using
    sqlite3 or an xml parser, depending on exactly what the OP wants.
    SQLite3 is fairly stable for routine use, and assuming that the OP has
    half a clue of figuring it out, would probably suit'em perfectly with
    much less bother then the standard xml brews.

    Over the years I have seen virtually everything tried for storing
    information, down to writing dictionaries out to a file for later
    slupin' & eval() recovery, which is a method that I have occasionally
    thrown my hands up at.... I don't even want to mention some of the
    commercial products I've bumped into!

    --
    TerryP.
    TerryP, Sep 18, 2009
    #9
  10. vlad

    Tim Chase Guest

    > You can also make a SQLite database be in-memory, giving you
    > the performance benefits of skipping the disk.



    Yes, I love the in-memory database -- especially for my automated
    testing in Django. However, the OP said that memory footprint
    was a concern (granted, I don't know how much data they were
    talking about -- a couple dozen rows in 1-2 tables might make
    this an attractive option).

    -tkc
    Tim Chase, Sep 18, 2009
    #10
  11. vlad

    AggieDan04 Guest

    On Sep 17, 9:10 am, J Kenneth King <> wrote:
    > ici <> writes:
    > > I likeshelvefor saving small amounts of data, user preferences,
    > > recent files etc.
    > >http://docs.python.org/library/shelve.html

    >
    > I like it too, but I hear the great powers that be are going to
    > deprecate it.


    If you want the convenience of shelve without the limitations of dbm,
    you can do:


    """Implementation of Python shelves using SQLite."""

    from __future__ import division

    import UserDict
    import pickle
    import sqlite3

    def to_db_type(value):
    """
    If value's type is supported natively in SQLite, return value.
    Otherwise, return a pickled representation.
    """
    if value is None or isinstance(value, (int, long, float,
    basestring)):
    return value
    else:
    return buffer(pickle.dumps(value))

    def from_db_type(value):
    """
    Converts a value from the database to a Python object.
    """
    if isinstance(value, buffer):
    return pickle.loads(value)
    else:
    return value

    class SQLiteShelf(UserDict.DictMixin):
    """
    Shelf implementation using an SQLite3 database.
    """
    def __init__(self, filename):
    self._database = sqlite3.connect(filename)
    self._database.execute("CREATE TABLE IF NOT EXISTS Shelf "
    "(Key TEXT PRIMARY KEY NOT NULL, Value
    BLOB)")
    self._open = True
    def __del__(self):
    self.close()
    def __getitem__(self, key):
    row = self._database.execute("SELECT Value FROM Shelf WHERE
    Key=?",
    [key]).fetchone()
    if row:
    return from_db_type(row[0])
    else:
    raise KeyError(key)
    def __setitem__(self, key, value):
    self._database.execute("INSERT OR REPLACE INTO Shelf VALUES
    (?, ?)",
    [key, to_db_type(value)])
    def __delitem__(self, key):
    self._database.execute("DELETE FROM Shelf WHERE Key=?", [key])
    def keys(self):
    """Return a list of keys in the shelf."""
    return [row[0] for row in
    self._database.execute("SELECT Key FROM Shelf")]
    def close(self):
    """Commit changes and close the file."""
    if self._database is not None:
    self._database.commit()
    self._database.close()
    self._database = None
    AggieDan04, Oct 6, 2009
    #11
  12. En Mon, 05 Oct 2009 23:08:59 -0300, AggieDan04 <>
    escribió:
    > On Sep 17, 9:10 am, J Kenneth King <> wrote:
    >> ici <> writes:


    >> > I likeshelvefor saving small amounts of data, user preferences,
    >> > recent files etc.
    >> >http://docs.python.org/library/shelve.html

    >>
    >> I like it too, but I hear the great powers that be are going to
    >> deprecate it.

    >
    > If you want the convenience of shelve without the limitations of dbm,
    > you can do:
    >
    > """Implementation of Python shelves using SQLite."""


    See also http://bugs.python.org/issue3783 "dbm.sqlite proof of concept"

    --
    Gabriel Genellina
    Gabriel Genellina, Oct 6, 2009
    #12
    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. crazyprakash
    Replies:
    4
    Views:
    3,362
    adrian
    Oct 30, 2005
  2. Replies:
    4
    Views:
    936
    M.E.Farmer
    Feb 13, 2005
  3. Helmut Jarausch
    Replies:
    4
    Views:
    6,412
    Ethan Furman
    Jul 15, 2009
  4. Carl Youngblood
    Replies:
    1
    Views:
    212
    Carl Youngblood
    Apr 9, 2005
  5. Replies:
    4
    Views:
    334
Loading...

Share This Page