Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.xs4all.nl!newsfeed4a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.008 X-Spam-Evidence: '*H*': 0.98; '*S*': 0.00; 'installed.': 0.07; 'mysql,': 0.07; 'postgresql': 0.07; 'table.': 0.07; 'rows': 0.09; 'sql,': 0.09; 'subject:module': 0.09; 'cc:addr:python-list': 0.11; 'python': 0.11; '9:20': 0.16; 'concurrency': 0.16; 'database).': 0.16; 'db2,': 0.16; 'downside': 0.16; 'for,': 0.16; 'grep': 0.16; 'sqlite': 0.16; 'subject:access': 0.16; 'such,': 0.16; 'tables,': 0.16; 'threw': 0.16; 'subject:python': 0.16; 'files.': 0.16; 'wrote:': 0.18; 'looked': 0.18; 'bit': 0.19; 'app': 0.19; 'basically': 0.19; 'seems': 0.21; 'feb': 0.22; 'email addr:gmail.com>': 0.22; 'handles': 0.22; 'cc:addr:python.org': 0.22; 'closely': 0.24; 'days,': 0.24; 'instance,': 0.24; 'integrate': 0.24; 'issue,': 0.24; 'fairly': 0.24; "haven't": 0.24; '(or': 0.24; 'cc:2**0': 0.24; '>': 0.26; 'query': 0.26; 'gets': 0.27; 'header:In-Reply-To:1': 0.27; 'installed': 0.27; 'point': 0.28; 'specifically': 0.29; 'chris': 0.29; 'external': 0.29; 'ideal': 0.29; 'words': 0.29; 'involving': 0.30; 'said,': 0.30; 'message-id:@mail.gmail.com': 0.30; 'url:mailman': 0.30; 'work.': 0.31; '(which': 0.31; 'included': 0.31; 'apparently': 0.31; 'once,': 0.31; 'option.': 0.31; 'overhead': 0.31; 'probably': 0.32; 'checked': 0.32; 'stuff': 0.32; 'interface': 0.32; 'option': 0.32; 'url:python': 0.33; 'everyone': 0.33; 'sources': 0.33; 'subject:the': 0.34; 'could': 0.34; 'common': 0.35; 'created': 0.35; 'transaction': 0.35; 'case,': 0.35; 'no,': 0.35; 'but': 0.35; 'received:google.com': 0.35; 'really': 0.36; 'consistent': 0.36; 'library.': 0.36; 'url:listinfo': 0.36; 'thanks': 0.36; 'subject:?': 0.36; 'url:org': 0.36; 'being': 0.38; 'massive': 0.38; 'on-line': 0.38; 'pm,': 0.38; 'does': 0.39; 'called': 0.40; 'url:mail': 0.40; 'referred': 0.60; 'simple,': 0.60; 'full': 0.61; 'entire': 0.61; 'simple': 0.61; "you're": 0.61; 'back': 0.62; "you'll": 0.62; "you've": 0.63; 'more': 0.64; 'situation': 0.65; 'to:addr:gmail.com': 0.65; 'details': 0.65; 'biggest': 0.67; 'optimized': 0.68; 'readers': 0.68; '1990s,': 0.84; 'confusion.': 0.84; 'desired?': 0.84; 'piping': 0.84; 'subject:SQL': 0.84; 'subject:recommended': 0.84; 'absolutely': 0.87; '9:00': 0.91; 'cutting': 0.91; 'thoroughly': 0.91 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=VBN0m6HZuYO7QZ6lifh8pD0IiVJSMv9ZRma4h7aaI8g=; b=F8LvtnnwoPzNZj5GyZgVg1znC6ocIlg5wkU4iwNaE00rSwzJwBwa5nuPVtuorKQBHB FznqkCnZx0MXPWTkvXXG3q0U7i/XJVj5By8GC3PYlwlocCNwphSWgBd7nnGMsw0XWJdF /a2nUTXO82b0AuLiMIO3lTvzLgSu69+Lsie0f+3sQgtF+IHaR344co4bUeuvTFaoEQIT aXut+Mk2HDNuX4aDbUa2j6yQWg0vQ0k7Dtx0KGKd07fWbXZ3l1ok5+wOQexY0FqLFZOm QGA4i3sfrykQ85pyqepgp3Lg+ZLWUH11a7j/KYGsQxeT+/BLhRP6FSQUAIzEMhHek11R qCUg== MIME-Version: 1.0 X-Received: by 10.60.123.75 with SMTP id ly11mr21932827oeb.1.1391947480430; Sun, 09 Feb 2014 04:04:40 -0800 (PST) In-Reply-To: References: Date: Sun, 9 Feb 2014 10:04:40 -0200 Subject: Re: What is the recommended python module for SQL database access? From: Marcel Rodrigues To: Chris Angelico Content-Type: multipart/alternative; boundary=047d7b5d25e41dfe1304f1f80756 Cc: python-list X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 139 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1391947482 news.xs4all.nl 2960 [2001:888:2000:d::a6]:44486 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.python:65744 --047d7b5d25e41dfe1304f1f80756 Content-Type: text/plain; charset=UTF-8 I just checked in the Python sources and apparently you're right about SQLite3. The Python distribution includes pysqlite which seems to be a self-contained SQLite engine. No external dependencies. Sorry for the confusion. 2014-02-09 9:00 GMT-02:00 Chris Angelico : > On Sun, Feb 9, 2014 at 9:20 PM, Marcel Rodrigues > wrote: > > As Chris said, if your needs are simple, use SQLite back-end. It's > probably > > already installed on your computer and Python has a nice interface to it > in > > its standard library. > > Already installed? I thought the point of SQLite3 being in the Python > stdlib was that Python actually included the entire engine (that's why > there's no, for instance, PostgreSQL client in the stdlib - because > there's no server; I disagree with the reasoning, but it is consistent > and valid), so you don't need _anything_ externally installed. > > In any case, SQLite is ideal for really simple databasing. Back in the > 1990s, I had DB2, DB2, and DB2, for all my database work. I wanted a > way to query a dictionary of English words using SQL, so I created a > DB2 database and threw ~60K rows into a table. Massive overkill for a > one-column table. These days, I could use SQLite (or more likely, just > use grep on /usr/share/dict/words - grep does everything that I wanted > SQL for, if you include piping from one grep into another), cutting > the overhead down enormously. > > The biggest downside of SQLite3 is concurrency. I haven't dug into the > exact details of the pager system and such, but it seems to be fairly > coarse in its locking. Also, stuff gets a bit complicated when you do > a single transaction involving multiple files. So if you have lots of > processes writing to the same set of SQLite tables, you'll see pretty > poor performance. PostgreSQL handles that situation far better, but > has a lot more overhead, so it's a poor choice for a single simple > application. MySQL's locking/concurrency system is specifically > optimized for a model that's common for web applications: a huge > number of readers and a tiny number of writers (sometimes referred to > as Data Warehousing, because you basically stuff a warehouse full of > data and then everyone comes looking for it). For the write-heavy > model (sometimes called OLTP or On-Line Transaction Processing), > PostgreSQL will hugely outperform MySQL, thanks to its MVCC model. > > Broad recommendation: Single application, tiny workload, concurrency > not an issue, simplicity desired? Go SQLite. Big complex job, need > performance, lots of things reading and writing at once, want > networked access? Go PGSQL. And don't go MySQL if PG is an option. > > And definitely don't go for a non-free option (MS-SQL, DB2, etc) > unless you've looked into it really closely and you are absolutely > thoroughly *sure* that you need that system (which probably means you > need your app to integrate with someone else's, and that other app > demands one particular database). > > ChrisA > -- > https://mail.python.org/mailman/listinfo/python-list > --047d7b5d25e41dfe1304f1f80756 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I just checked in the Python sources and apparently you= 9;re right about SQLite3. The Python distribution includes pysqlite which s= eems to be a self-contained SQLite engine. No external dependencies. Sorry = for the confusion.


2014-02= -09 9:00 GMT-02:00 Chris Angelico <rosuav@gmail.com>:
On Sun, Feb 9, 2014 at 9:20 PM, Marcel Rodrigues <marcelgmr@gmail.com> wrote:
> As Chris said, if your needs are simple, use SQLite back-end. It's= probably
> already installed on your computer and Python has a nice interface to = it in
> its standard library.

Already installed? I thought the point of SQLite3 being in the Python=
stdlib was that Python actually included the entire engine (that's why<= br> there's no, for instance, PostgreSQL client in the stdlib - because
there's no server; I disagree with the reasoning, but it is consistent<= br> and valid), so you don't need _anything_ externally installed.

In any case, SQLite is ideal for really simple databasing. Back in the
1990s, I had DB2, DB2, and DB2, for all my database work. I wanted a
way to query a dictionary of English words using SQL, so I created a
DB2 database and threw ~60K rows into a table. Massive overkill for a
one-column table. These days, I could use SQLite (or more likely, just
use grep on /usr/share/dict/words - grep does everything that I wanted
SQL for, if you include piping from one grep into another), cutting
the overhead down enormously.

The biggest downside of SQLite3 is concurrency. I haven't dug into the<= br> exact details of the pager system and such, but it seems to be fairly
coarse in its locking. Also, stuff gets a bit complicated when you do
a single transaction involving multiple files. So if you have lots of
processes writing to the same set of SQLite tables, you'll see pretty poor performance. PostgreSQL handles that situation far better, but
has a lot more overhead, so it's a poor choice for a single simple
application. MySQL's locking/concurrency system is specifically
optimized for a model that's common for web applications: a huge
number of readers and a tiny number of writers (sometimes referred to
as Data Warehousing, because you basically stuff a warehouse full of
data and then everyone comes looking for it). For the write-heavy
model (sometimes called OLTP or On-Line Transaction Processing),
PostgreSQL will hugely outperform MySQL, thanks to its MVCC model.

Broad recommendation: Single application, tiny workload, concurrency
not an issue, simplicity desired? Go SQLite. Big complex job, need
performance, lots of things reading and writing at once, want
networked access? Go PGSQL. And don't go MySQL if PG is an option.

And definitely don't go for a non-free option (MS-SQL, DB2, etc)
unless you've looked into it really closely and you are absolutely
thoroughly *sure* that you need that system (which probably means you
need your app to integrate with someone else's, and that other app
demands one particular database).

ChrisA
--
https://mail.python.org/mailman/listinfo/python-list

--047d7b5d25e41dfe1304f1f80756--