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

Groups > comp.lang.python > #43297 > unrolled thread

python-noob - which container is appropriate for later exporting into mySql + matplotlib ?

Back to article view | Back to comp.lang.python

Contents

  python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-11 00:06 +0200
    Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Cousin Stanley <cousinstanley@gmail.com> - 2013-04-11 01:39 +0000
      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-11 10:49 +0200
        Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-11 15:38 +0200
    Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Cousin Stanley <cousinstanley@gmail.com> - 2013-04-11 17:58 +0000
      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Cousin Stanley <cousinstanley@gmail.com> - 2013-04-11 18:44 +0000
        Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-12 16:19 +0200
      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-11 21:42 +0200
      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-12 16:03 +0200
        Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Cousin Stanley <cousinstanley@gmail.com> - 2013-04-12 16:58 +0000
          Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-12 22:52 +0200
            Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Cousin Stanley <cousinstanley@gmail.com> - 2013-04-12 23:26 +0000
              Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 02:00 +0200
              Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2013-04-13 01:44 +0000
                Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 13:08 +0200
                  Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-13 21:39 +1000
                    Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 15:30 +0200
                      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-14 00:03 +1000
                        Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Roy Smith <roy@panix.com> - 2013-04-13 10:36 -0400
                          Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 21:25 +0200
                            Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Roy Smith <roy@panix.com> - 2013-04-13 17:38 -0400
                        Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 16:39 +0200
                          Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Walter Hurry <walterhurry@lavabit.com> - 2013-04-13 14:56 +0000
                            Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 21:34 +0200
                              Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Walter Hurry <walterhurry@lavabit.com> - 2013-04-13 22:22 +0000
                                Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-14 00:31 +0200
                                  Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-14 08:54 +1000
                                    Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-14 02:06 +0200
                              Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-14 08:34 +1000
                                Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-14 02:10 +0200
                          Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-14 02:15 +1000
                            Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? rusi <rustompmody@gmail.com> - 2013-04-13 10:02 -0700
                              Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 21:49 +0200
                              Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2013-04-14 07:56 +0000
                                Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? rusi <rustompmody@gmail.com> - 2013-04-14 04:17 -0700
                                  Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-14 23:22 +1000
                                    Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? rusi <rustompmody@gmail.com> - 2013-04-15 04:45 -0700
                                      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-15 22:28 +1000
                                  Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Ned Deily <nad@acm.org> - 2013-04-14 09:40 -0700
                                  Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Tim Chase <python.list@tim.thechases.com> - 2013-04-14 15:16 -0500
                                    Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Roy Smith <roy@panix.com> - 2013-04-14 17:48 -0400
                                  Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-15 07:43 +1000
                            Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 21:42 +0200
                      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-04-13 16:01 -0400
                        Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? someone <newsboost@gmail.com> - 2013-04-13 23:36 +0200
                      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Chris Angelico <rosuav@gmail.com> - 2013-04-14 08:44 +1000
                      Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-04-13 19:42 -0400
                Re: python-noob - which container is appropriate for later exporting into mySql + matplotlib ? Cousin Stanley <cousinstanley@gmail.com> - 2013-04-14 18:20 +0000

Page 2 of 3 — ← Prev page 1 [2] 3  Next page →

#43548

In article <kkcb9f$pei$1@dont-email.me>, someone <newsboost@gmail.com> 
wrote:

> > Some of the early Unix file systems were very fragile.  One of the
> > (often under-appreciated) major advances in BSD (it was certainly in
> > 4.2, not sure how much earlier) was a new filesystem which was much more
> > robust in the face of hardware failures and system crashes.  Prior to
> 
> Are you talking about (journaling?) filesystems such as ext3, ext4, JFS, 
> ReiserFS and XFS ?
> 
> http://en.wikipedia.org/wiki/Journaling_file_system

No, I'm talking about

http://en.wikipedia.org/wiki/Berkeley_Fast_File_System

Journaling came along later.

[toc] | [prev] | [next] | [standalone]

#43516

On 04/13/2013 04:03 PM, Chris Angelico wrote:
> On Sat, Apr 13, 2013 at 11:30 PM, someone <newsboost@gmail.com> wrote:
>> On 04/13/2013 01:39 PM, Chris Angelico wrote:
>>> Note that there's a caveat: You have to tell SQLite to be ACID
>>> compliant, effectively.
>>
>>
>> So, you're saying to me that by default SQLite isn't ACID compliant, if I
>> begin to use it in my own small programs?
>> ...
>> Do I understand you correct, that by "You have to tell SQLite to be ACID
>> compliant, effectively", you're saying that by default SQLite isn't ACID
>> compliant ?
>>
>
> First off: I am NOT inherently familiar with sqlite. I'm more familiar
> with PostgreSQL, DB2, and MySQL. I'm also not an expert at database
> engine design, so this discussion is from the point of view of an
> applications developer who has used databases from his apps.

Ok, would be nice to hear the opinion from an sqlite expert then...

> True ACID compliance demands support at every level:
>
> 1) The application has to operate in logical units of work, which -
> apart from with DB2 - requires an explicit "BEGIN" query, or
> single-statement transactions.
>
> 2) The database engine must employ some form of write-ahead log.
> Different databases do this somewhat differently (according to the
> page I linked to, SQLite does this in reverse, maintaining a log
> that's sufficient to *undo* the transaction, while PostgreSQL does
> this forwards, maintaining a log that's sufficient to *redo* it as
> well - more effort, but it can be used for database replication), but
> one way or another, there must be a way to detect half-done
> transactions.
>
> 3) The operating system and filesystem must support a forced file
> synchronization (fsync/fdatasync), so the database engine can wait for
> the data to be written to disk.
>
> 4) The underlying media (hard disk, SSD, USB stick, etc) must respond
> to the fsync call by actually writing the content to persistent
> storage before returning.

Ok.

> Failure at any level means the overall system is not ACID compliant.

Roger... But google says sqlite is supposed to be ACID compliant 
(although maybe not "fully" as you indicate, I'm not sure about this)...

> PostgreSQL has a huge amount of code in it to try to deal with (or at
> least recognize) a level-3 failure, but nothing in the database engine
> can deal with level 1 or 4 issues.
>
> You'd have to actually test it. The easiest way is to get two
> computers, side by side, and run the database engine on one and a
> monitor on the other. To test some SSDs at work, I knocked together a
> little program that worked somewhat thus:
>
> * Connect to the database over TCP/IP (easy, as we were doing this
> with PostgreSQL)
> * Create a table with a number of rows with an ID and a counter,
> initialized to 0
> * Repeatedly, in parallel, perform a transaction:
>    - Increment the counter on one of the rows (at random)
>    - Increment a "possible" in-memory counter for that row
>    - Commit the database transaction
>    - Increment a "confirmed" in-memory counter for that row
> * When an error of "database seems to be down" is detected, wait for
> it to come up again, then query the table. The counters must all be at
> least their corresponding "possible" value and at most the
> "confirmed".

Ok, that doesn't sound to be so simple after all...

> With that running, I simply pulled the plug on the database computer.
> With a properly-configured hard disk, every one of the counters was
> within its correct range. With a lying SSD, though, they could be
> anywhere from "pretty close" (with a low workload - simulated by
> having only a single thread doing transactions and having it sleep for
> a few ms each iteration) to "pretty appalling" (with a bunch of
> threads spinning tightly, keeping the workload high). Once the SSD
> starts doing major write reordering, its throughput soars, but at the
> cost of trustworthiness.

Ok, it would be nice to hear/read the opinion from another in here 
who've been working (a lot?) with sqlite...

>> Next question: Is it something I should worry about in my own programs (I'm
>> not sure, I'm an SQL noob)... ?
>
> Yes, it most certainly is. If you have any data that you care about,
> put together some kind of test that will allow you to literally pull
> the plug on the database, while still knowing whether or not your
> transaction was completed (so you'll most likely need some kind of
> "possible" / "confirmed" counter pair as I used above).

I'm not so rich, so I prefer to go for a free database solution rather 
than an expensive license... I've heard good things about oracle and 
that's also what they used at my previous company, but it's not 
something I am willing to pay for, from my private/own money for my 
sparetime-projects...

Maybe what you've written explains why somebody got corrupted firefox 
sqlite files... I'll just practice a bit more and remember your advice 
about testing - at least for "important" projects, I'll remember how you 
tested this with pulling out the plug and monitoring the data...

[toc] | [prev] | [next] | [standalone]

#43517

On Sat, 13 Apr 2013 16:39:12 +0200, someone wrote:

> I'm not so rich, so I prefer to go for a free database solution rather
> than an expensive license
 (<paraphrasing> but I do care about ACID compliance)

Sounds to me that PostgreSQL is your man, then.

[toc] | [prev] | [next] | [standalone]

#43540

On 04/13/2013 04:56 PM, Walter Hurry wrote:
> On Sat, 13 Apr 2013 16:39:12 +0200, someone wrote:
>
>> I'm not so rich, so I prefer to go for a free database solution rather
>> than an expensive license
>   (<paraphrasing> but I do care about ACID compliance)
>
> Sounds to me that PostgreSQL is your man, then.

Oh, ok. Thanks! BTW: I just read: "Yahoo runs a multi-petabyte modified 
PostgreSQL database that processes billions of events per day" - that's 
truely amazing, I think...

I think maybe I'll experiment a bit with both mySql (small/medium sized 
databases) and for critical/important stuff I should go with 
PostgreSQL... Glad to hear this... Then I know what to look at...

[toc] | [prev] | [next] | [standalone]

#43549

On Sat, 13 Apr 2013 21:34:38 +0200, someone wrote:

> On 04/13/2013 04:56 PM, Walter Hurry wrote:
>> On Sat, 13 Apr 2013 16:39:12 +0200, someone wrote:
>>
>>> I'm not so rich, so I prefer to go for a free database solution rather
>>> than an expensive license
>>   (<paraphrasing> but I do care about ACID compliance)
>>
>> Sounds to me that PostgreSQL is your man, then.
> 
> Oh, ok. Thanks! BTW: I just read: "Yahoo runs a multi-petabyte modified
> PostgreSQL database that processes billions of events per day" - that's
> truely amazing, I think...
> 
> I think maybe I'll experiment a bit with both mySql (small/medium sized
> databases) and for critical/important stuff I should go with
> PostgreSQL... Glad to hear this... Then I know what to look at...

If it were me I wouldn't use MySQL for anything at all. I'd use sqlite 
for little non-critical local applications, and Postgres for the rest.

Postgres is not difficult at all, provided you RTFM and follow the 
instructions (the documentation is superb). And whichever you use, you 
need to learn SQL anyway.

[toc] | [prev] | [next] | [standalone]

#43550

On 04/14/2013 12:22 AM, Walter Hurry wrote:
> On Sat, 13 Apr 2013 21:34:38 +0200, someone wrote:
>
>> On 04/13/2013 04:56 PM, Walter Hurry wrote:
>>> On Sat, 13 Apr 2013 16:39:12 +0200, someone wrote:
>>>
>>>> I'm not so rich, so I prefer to go for a free database solution rather
>>>> than an expensive license
>>>    (<paraphrasing> but I do care about ACID compliance)
>>>
>>> Sounds to me that PostgreSQL is your man, then.
>>
>> Oh, ok. Thanks! BTW: I just read: "Yahoo runs a multi-petabyte modified
>> PostgreSQL database that processes billions of events per day" - that's
>> truely amazing, I think...
>>
>> I think maybe I'll experiment a bit with both mySql (small/medium sized
>> databases) and for critical/important stuff I should go with
>> PostgreSQL... Glad to hear this... Then I know what to look at...
>
> If it were me I wouldn't use MySQL for anything at all. I'd use sqlite
> for little non-critical local applications, and Postgres for the rest.

Ok, thank you. I just came across a blog that said pytables is also a 
very good option?

http://www.pytables.org/moin/PyTables?action=AttachFile&do=view&target=non-indexed.png

> Postgres is not difficult at all, provided you RTFM and follow the
> instructions (the documentation is superb). And whichever you use, you
> need to learn SQL anyway.

Good to hear... I'll dig more into it, thank you...

[toc] | [prev] | [next] | [standalone]

#43553

On Sun, Apr 14, 2013 at 8:31 AM, someone <newsboost@gmail.com> wrote:
> Ok, thank you. I just came across a blog that said pytables is also a very
> good option?
>
> http://www.pytables.org/moin/PyTables?action=AttachFile&do=view&target=non-indexed.png

>From what I gather, that's looking at performance of a non-indexable
query on a 10,000,000-row table. That's going to suck whatever you do,
and the exact level of suckitude doesn't really prove much. (Note that
even the best options are taking half a second for this single query.)

A better test of a database is transactions per second of something
that approximates to your real workload. For instance, English
Wikipedia has roughly a hundred edits per minute (assessed by me just
now by looking at the Recent Changes), and some ridiculous number of
page reads per minute (not assessed, but believed to be somewhere
between 11 and Graham's number); so a test of a proposed new database
would have to mimic this ratio. Most of the queries involved should be
able to be answered using indexes; in some cases, ONLY using the index
(eg if you just want to know whether or not a row exists).

PyTables may well outperform PostgreSQL in real usage, but that one
graph doesn't tell me that. (Not to mention that it's measuring a
somewhat old PG.)

ChrisA

[toc] | [prev] | [next] | [standalone]

#43557

On 04/14/2013 12:54 AM, Chris Angelico wrote:
> On Sun, Apr 14, 2013 at 8:31 AM, someone <newsboost@gmail.com> wrote:
>> Ok, thank you. I just came across a blog that said pytables is also a very
>> good option?
>>
>> http://www.pytables.org/moin/PyTables?action=AttachFile&do=view&target=non-indexed.png
>
>>From what I gather, that's looking at performance of a non-indexable
> query on a 10,000,000-row table. That's going to suck whatever you do,
> and the exact level of suckitude doesn't really prove much. (Note that
> even the best options are taking half a second for this single query.)

Interesting... Thank you very much for that information...

> A better test of a database is transactions per second of something
> that approximates to your real workload. For instance, English
> Wikipedia has roughly a hundred edits per minute (assessed by me just
> now by looking at the Recent Changes), and some ridiculous number of
> page reads per minute (not assessed, but believed to be somewhere
> between 11 and Graham's number); so a test of a proposed new database
> would have to mimic this ratio. Most of the queries involved should be
> able to be answered using indexes; in some cases, ONLY using the index
> (eg if you just want to know whether or not a row exists).
>
> PyTables may well outperform PostgreSQL in real usage, but that one
> graph doesn't tell me that. (Not to mention that it's measuring a
> somewhat old PG.)

Ok, thank you very much... Sounds to me like PostgreSQL it is, then :-)

[toc] | [prev] | [next] | [standalone]

#43551

On Sun, Apr 14, 2013 at 5:34 AM, someone <newsboost@gmail.com> wrote:
> I think maybe I'll experiment a bit with both mySql (small/medium sized
> databases) and for critical/important stuff I should go with PostgreSQL

PostgreSQL isn't majorly slower than MySQL, and it's a lot more
trustworthy in terms of database constraints and so on. MySQL is
designed as a place for a single application to store its data, and it
assumes that the application is king; PostgreSQL is designed as a
database against which application(s) may execute queries, therefore
it assumes that the database administrator is king.

With heavy read/write workloads, I'd put my money on PostgreSQL every
time; MySQL has a much greater problem with wide locks (eg
table-level) and consequent loss of concurrency.

ChrisA

[toc] | [prev] | [next] | [standalone]

#43560

On 04/14/2013 12:34 AM, Chris Angelico wrote:
> On Sun, Apr 14, 2013 at 5:34 AM, someone <newsboost@gmail.com> wrote:
>> I think maybe I'll experiment a bit with both mySql (small/medium sized
>> databases) and for critical/important stuff I should go with PostgreSQL
>
> PostgreSQL isn't majorly slower than MySQL, and it's a lot more
> trustworthy in terms of database constraints and so on. MySQL is
> designed as a place for a single application to store its data, and it
> assumes that the application is king; PostgreSQL is designed as a
> database against which application(s) may execute queries, therefore
> it assumes that the database administrator is king.
>
> With heavy read/write workloads, I'd put my money on PostgreSQL every
> time; MySQL has a much greater problem with wide locks (eg
> table-level) and consequent loss of concurrency.

Ok, thank you very much... Sounds like PostgreSQL is the best option for 
me to go on to from here, after I've played a bit my sqlite...

[toc] | [prev] | [next] | [standalone]

#43521

On Sun, Apr 14, 2013 at 12:39 AM, someone <newsboost@gmail.com> wrote:
> On 04/13/2013 04:03 PM, Chris Angelico wrote:
>> Failure at any level means the overall system is not ACID compliant.
>
> Roger... But google says sqlite is supposed to be ACID compliant (although
> maybe not "fully" as you indicate, I'm not sure about this)...

What your Google hits are telling you is that sqlite can (if
configured correctly) pass level 2. But it doesn't guarantee anything
about the other levels, so it's easy to have an, uhh, ACID leak.

>> You'd have to actually test it. The easiest way is to get two
>> computers, side by side, and run the database engine on one and a
>> monitor on the other.
>
> Ok, that doesn't sound to be so simple after all...

I gave a fairly wordy run-down of what I tested, but it's actually
fairly simple in concept: Do a huge bunch of transactions, and keep a
log of what's returned from the COMMIT query; then pull the power out.

> Ok, it would be nice to hear/read the opinion from another in here who've
> been working (a lot?) with sqlite...

Agreed. I'm sure someone will chime in.

> I'm not so rich, so I prefer to go for a free database solution rather than
> an expensive license... I've heard good things about oracle and that's also
> what they used at my previous company, but it's not something I am willing
> to pay for, from my private/own money for my sparetime-projects...

I concur with Walter's assessment: You want PostgreSQL. It's free/open
source software (highly permissive MIT-like license), massively
trusted, and scales up beautifully. (That last one may not be
significant to you, but it's still good to know your database can
handle hundreds or thousands of tps on basic hardware.)

ChrisA

[toc] | [prev] | [next] | [standalone]

#43525

On Apr 13, 9:15 pm, Chris Angelico <ros...@gmail.com> wrote:
> On Sun, Apr 14, 2013 at 12:39 AM, someone <newsbo...@gmail.com> wrote:
> > On 04/13/2013 04:03 PM, Chris Angelico wrote:
> >> Failure at any level means the overall system is not ACID compliant.
>
> > Roger... But google says sqlite is supposed to be ACID compliant (although
> > maybe not "fully" as you indicate, I'm not sure about this)...
>
> What your Google hits are telling you is that sqlite can (if
> configured correctly) pass level 2. But it doesn't guarantee anything
> about the other levels, so it's easy to have an, uhh, ACID leak.
>
> >> You'd have to actually test it. The easiest way is to get two
> >> computers, side by side, and run the database engine on one and a
> >> monitor on the other.
>
> > Ok, that doesn't sound to be so simple after all...
>
> I gave a fairly wordy run-down of what I tested, but it's actually
> fairly simple in concept: Do a huge bunch of transactions, and keep a
> log of what's returned from the COMMIT query; then pull the power out.
>
> > Ok, it would be nice to hear/read the opinion from another in here who've
> > been working (a lot?) with sqlite...
>
> Agreed. I'm sure someone will chime in.
>
> > I'm not so rich, so I prefer to go for a free database solution rather than
> > an expensive license... I've heard good things about oracle and that's also
> > what they used at my previous company, but it's not something I am willing
> > to pay for, from my private/own money for my sparetime-projects...
>
> I concur with Walter's assessment: You want PostgreSQL. It's free/open
> source software (highly permissive MIT-like license), massively
> trusted, and scales up beautifully. (That last one may not be
> significant to you, but it's still good to know your database can
> handle hundreds or thousands of tps on basic hardware.)
>
> ChrisA

Dunno why you guys are ACIDing a hapless python+SQL noob.
As far as I can see he did not even know what ACID was... Just
happened to start with mysql (without evidently knowing the DBMS area)
and Cousin Stanley's recommendation to step a notch down from mysql to
sqlite seems to me to be spot-on for his requirement.

To the OP:
Steven is welcome to his views about use of databases.  Good to
remember that everyone does not agree with him. This includes the
firefox devs as well as python devs.

In particular, sqlite in python is quite special.  All the other
databases have bridge modules to talk from python to the database.
Which means that python runs and the database runs and the two talk
asynchronously across the bridge using what is called a 'client-server
model'. Now client-server is powerful and sophisticated and earlier it
was the only option. That is the noob database programmer had to
grapple with sql (the basic stuff) along with the transaction/ACID
advanced stuff.

Sqlite changed the rules of the game. Sqlite allows programmers to
play with sql without having to deal with client server headaches at
the same time.
Python amplified that change by bundling it with python.

In short Python+Sqlite is a boon for beginners to programming+DBMS

[toc] | [prev] | [next] | [standalone]

#43542

On 04/13/2013 07:02 PM, rusi wrote:
> On Apr 13, 9:15 pm, Chris Angelico <ros...@gmail.com> wrote:
>> On Sun, Apr 14, 2013 at 12:39 AM, someone <newsbo...@gmail.com> wrote:
>>> On 04/13/2013 04:03 PM, Chris Angelico wrote:
.....
.....
>>>> Failure at any level means the overall system is not ACID compliant.
>>> Ok, it would be nice to hear/read the opinion from another in here who've
>>> been working (a lot?) with sqlite...
>>
>> Agreed. I'm sure someone will chime in.
>>
>>> I'm not so rich, so I prefer to go for a free database solution rather than
>>> an expensive license... I've heard good things about oracle and that's also
>>> what they used at my previous company, but it's not something I am willing
>>> to pay for, from my private/own money for my sparetime-projects...
>>
>> I concur with Walter's assessment: You want PostgreSQL. It's free/open
>> source software (highly permissive MIT-like license), massively
>> trusted, and scales up beautifully. (That last one may not be
>> significant to you, but it's still good to know your database can
>> handle hundreds or thousands of tps on basic hardware.)
>>
>> ChrisA
>
> Dunno why you guys are ACIDing a hapless python+SQL noob.

That's ok - I'm very interested in hearing/reading this, so don't worry :-)

> As far as I can see he did not even know what ACID was... Just

I think I know it know (maybe not all the details, but generally I know 
that it should be ACID-compliant for critical data to avoid corruption 
and bad data) :-)

> happened to start with mysql (without evidently knowing the DBMS area)
> and Cousin Stanley's recommendation to step a notch down from mysql to
> sqlite seems to me to be spot-on for his requirement.

Agree - but after that I would like to play with a client/server-system, 
so that's also interesting to hear about...

> To the OP:
> Steven is welcome to his views about use of databases.  Good to
> remember that everyone does not agree with him. This includes the
> firefox devs as well as python devs.

Yes, I think I understand this discussion. I'm sorry to hear that the 
sqlite-database-files sometimes become corrupted. I haven't experienced 
this problem myself (AFAIR), because ~90% of the time I'm on chromium.

> In particular, sqlite in python is quite special.  All the other
> databases have bridge modules to talk from python to the database.
> Which means that python runs and the database runs and the two talk
> asynchronously across the bridge using what is called a 'client-server
> model'. Now client-server is powerful and sophisticated and earlier it

Yes, got it :-)

> was the only option. That is the noob database programmer had to
> grapple with sql (the basic stuff) along with the transaction/ACID
> advanced stuff.

Yep, I understand your intentions...

> Sqlite changed the rules of the game. Sqlite allows programmers to
> play with sql without having to deal with client server headaches at
> the same time.
> Python amplified that change by bundling it with python.
>
> In short Python+Sqlite is a boon for beginners to programming+DBMS

I completely agree with you that Python+Sqlite is really really great... 
But soon I'll also move on to using a client/server model and therefore 
I also appreciate the other comments/discussion related to e.g. failure 
or non-"fully-ACID compliance" of sqlite, which maybe can explain this 
firefox problem with corrupted database(s)...

I think I learned a lot from this thread and know what I should be 
working on now...

[toc] | [prev] | [next] | [standalone]

#43563

On Sat, 13 Apr 2013 10:02:18 -0700, rusi wrote:

> To the OP:
> Steven is welcome to his views about use of databases.

I haven't given any views about databases. I've given my view on 
application developers -- specifically, Firefox -- using a not-quite ACID 
database in a way that is fragile, can cause data loss, and adds lots 
more complexity to the application AND the end-user experience. And for 
what? Simple data that would be much better in a simpler format, such as 
bookmarks.


> Good to remember
> that everyone does not agree with him. This includes the firefox devs as
> well as python devs.

I don't see what the Python devs have to do with it. They don't use 
Sqlite for Python's internals, and the fact that there is a module for 
sqlite doesn't mean squat. There's a module for parsing Sun AU audio 
files, that doesn't mean the Python devs recommend that they are the best 
solution to your audio processing and multimedia needs.

I'm not saying that Sqlite doesn't have it's uses, although I personally 
haven't found them yet. And as for the Firefox devs, well, I'll just let 
Jamie Zawinski show their l33t des1gn ski11z in context:

http://www.jwz.org/blog/2003/01/more-khtml/

Okay, that's ten years old. What do you think the odds are that Firefox 
has a nice, clean design by now? Well, I suppose it's possible, but when 
it takes a minimum of NINE files to do the equivalent of "Hello World" in 
Firefox, I wouldn't put money on it:

http://kb.mozillazine.org/Getting_started_with_extension_development

I mean, really -- bookmarks, in a single-user application, and they store 
it in a database. You can't even have two instances of Firefox running at 
the same time.

The consequences of this over-engineered solution is that Firefox is more 
complex and fragile than it needs be, and less reliable than it could be. 
When your bookmarks database gets corrupt, which is easy, the browser 
History and Back button stop working, which then pushes responsibility 
for fixing the database corruption back on the user. So the Firefox 
developers actually end up paying the costs of a non-lightweight 
implementation, but without the benefits. They don't even get to remove 
the old bookmarks to HTML code, since they still need it for manual 
exports and backups.

Considering the rest of the Firefox architecture (XUL, XUL everywhere!), 
using sqlite probably feels like a lightweight solution to the devs.

"The Mork database structure used by Mozilla Firefox v1-2 is unusual to 
say the least.  It was originally developed by Netscape for their browser 
(Netscape v6) and the format was later adopted by Mozilla to be used in 
Firefox.  It is a plain text format which is not easily human readable 
and is not efficient in its storage structures.  For example, a single 
Unicode character can take many bytes to store.  The developers 
themselves complained it was extremely difficult to parse correctly and 
from Firefox v3, it was replaced by MozStorage which is based on an 
SQLite database."


http://wordpress.bladeforensics.com/?p=357

http://en.wikipedia.org/wiki/Mork_%28file_format%29




-- 
Steven

[toc] | [prev] | [next] | [standalone]

#43568

On Apr 14, 12:56 pm, Steven D'Aprano <steve
+comp.lang.pyt...@pearwood.info> wrote:
> On Sat, 13 Apr 2013 10:02:18 -0700, rusi wrote:
> > To the OP:
> > Steven is welcome to his views about use of databases.
>
> I haven't given any views about databases.

You are twisting "use of databases" to just "about databases"

And heres what you said:

> Using a database for such lightweight data as bookmarks is, in my
> opinion, gross overkill and adds to the complexity of Firefox. More
> complexity leads to more bugs…

Not that I would disagree with that for general databases, just for
something as atypical as sqlite.
In short, you are being hypnotized by the word 'database' and not
seeing that sqlite is a very strange instance of that species.
http://en.wikipedia.org/wiki/Etymological_fallacy
+
http://en.wikipedia.org/wiki/Accident_%28fallacy%29

> I've given my view on
> application developers -- specifically, Firefox -- using a not-quite ACID
> database in a way that is fragile, can cause data loss,

FUD
Are you saying that flat-files dont lose data?

> and adds lots
> more complexity to the application AND the end-user experience. And for
> what?

Strange argument: If I call a one line re.match(..) that hooks into
5000 arcane lines of the re module, on whose account is the complexity
-- mine or python's?

From a programmer's POV if 10 lines of flat-file munging are reduced
to two lines of SQL its a reduction of 10 to 2.

> Simple data that would be much better in a simpler format, such as
> bookmarks.
>
> > Good to remember
> > that everyone does not agree with him. This includes the firefox devs as
> > well as python devs.
>
> I don't see what the Python devs have to do with it. They don't use
> Sqlite for Python's internals, and the fact that there is a module for
> sqlite doesn't mean squat. There's a module for parsing Sun AU audio
> files, that doesn't mean the Python devs recommend that they are the best
> solution to your audio processing and multimedia needs.

Python made a choice to include AU file support when Sun existed and
looked more respectable than MS. Today the support continues to exist
probably for backward compatibility reasons.  "The code's already
written. Why remove it?"
Sure but it has its costs -- memory footprint, sources-size etc --
which are deemed negligible enough to not bother.

Likewise python 2.5 made a choice to include sqlite. Following RoR's D
Hansson we may call it an 'opinionated choice.'  That choice implies
that the devs decided that a fixed-cost of bundling sqlite with python
is deemed better than each programmer installing/rolling-his-own etc


>
> I'm not saying that Sqlite doesn't have it's uses, although I personally
> haven't found them yet. And as for the Firefox devs, well, I'll just let
> Jamie Zawinski show their l33t des1gn ski11z in context:
>
> http://www.jwz.org/blog/2003/01/more-khtml/
>

Faulty generalization fallacy:
http://en.wikipedia.org/wiki/Faulty_generalization
Because some code in firefox is bad, every choice of firefox is bad?
[Actually I am surprised that you agree with *that example*: Would you
claim that a void returning, no-argument function is better than one
with arguments and return values?  Anyways thats really far away from
this discussion…]

To the OP:
Lets deconstruct ACID.

Consistency+Atomicity:
Lets say you write some stack code like this
  stack[top] = newvalue
  top += 1

And if you catch the machine state between the two assignments, you
will find an *inconsistent* stack because that code is *non-atomic*
Should you bother? Yes if you have concurrency, no if not.

Likewise Isolation is vacuously guaranteed if you are the sole guy
running your code.

As for Durability, if you randomly turn off your machine when your
program is running, yes you may lose the results of your program. You
may lose much else!

IOW if you are alone on your machine, all discussion of ACID is moot

[toc] | [prev] | [next] | [standalone]

#43571

On Sun, Apr 14, 2013 at 9:17 PM, rusi <rustompmody@gmail.com> wrote:
> On Apr 14, 12:56 pm, Steven D'Aprano <steve
> +comp.lang.pyt...@pearwood.info> wrote:
>> I've given my view on
>> application developers -- specifically, Firefox -- using a not-quite ACID
>> database in a way that is fragile, can cause data loss,
>
> FUD
> Are you saying that flat-files dont lose data?

If they do, a human being can easily open them up and see what's
inside. Suppose bookmarks are stored like this:

r"""Some-Browser-Name web bookmarks file - edit with care
url: http://www.google.com/
title: Search engine
icon: whatever-format-you-want-to-use

url: http://www.duckduckgo.com/
title: Another search engine

url: http://www.python.org/

url: ftp://192.168.0.12/
title: My FTP Server
desc: Photos are in photos/, videos are in videos/
 Everything else is in other/
user: root
pass: secret
"""

The parsing of this file is pretty simple. Blank line marks end of
entry; indented line continues the previous attribute (like RFC822),
everything else is "attribute: value". (You might even be able to
abuse an RFC822 parser/compositor for the job.) The whole file has to
be read and rewritten for any edits, so it's unsuited to gigabytes of
content; but we're talking about *web browser bookmarks* here. I know
some people have a lot of them, but hardly gigs and gigs. And if you
think they will, then all you need to do is have multiple files, eg
one for each folder in the bookmark tree.

Now suppose it gets damaged somehow. Firstly, that's a lot less likely
with a simple file format and a "write to temp file, then move temp
file over main file" setup; but mainly, it's very easy to
resynchronize - maybe there'll be one bookmark (or a group of
bookmarks) that get flagged as corrupted, but everything after that
can be parsed just fine - as soon as you get to a blank line, you
start parsing again. Very simple. Well suited to a simple task. (Note,
however, that the uber-simple concept I've posited here would have the
same concurrency problems that Firefox has. At very least, it'd rely
on some sort of filesystem-level lock when it starts rewriting the
file. But this is approximately similar to running two instances of a
text editor and trying to work with the same file.)

> From a programmer's POV if 10 lines of flat-file munging are reduced
> to two lines of SQL its a reduction of 10 to 2.

The complexity exists in a variety of places. The two lines of SQL
hide a morass of potential complexity; so would a massive regex. The
file itself is way harder for external tools to manage. And all of it
can be buggy. With a simple flat-file system, chances are you can turn
it into a nested list structure and a dict for indexing (or possibly a
collections.OrderedDict), and then you have the same reduction - it's
just simple in-memory operations, possibly followed by a save() call.
All the options available will do that, whether flat-file or database.

>> I don't see what the Python devs have to do with it. They don't use
>> Sqlite for Python's internals, and the fact that there is a module for
>> sqlite doesn't mean squat. There's a module for parsing Sun AU audio
>> files, that doesn't mean the Python devs recommend that they are the best
>> solution to your audio processing and multimedia needs.
>
> Python made a choice to include AU file support when Sun existed and
> looked more respectable than MS. Today the support continues to exist
> probably for backward compatibility reasons.  "The code's already
> written. Why remove it?"
> Sure but it has its costs -- memory footprint, sources-size etc --
> which are deemed negligible enough to not bother.

Actually, this is one place where I disagree with the current decision
of the Python core devs: I think bindings for other popular databases
(most notably PostgreSQL, and probably MySQL since it's so widely
used) ought to be included in core, rather than being shoved off to
PyPI. Databasing is so important to today's world that it would really
help if people had all the options right there in core, if only so
they're more findable (if you're browsing docs.python.org, you won't
know that psycopg is available). Currently the policy seems to be "we
don't include the server so why should we include the client"; I
disagree, I think the client would stand nicely on its own. (Does
Python have a DNS server module? DNS client? I haven't dug deep, but
I'm pretty sure I can do name lookups in Python, yet running a DNS
server is sufficiently arcane that it can, quite rightly, be pushed
off to PyPI.) But this is minor, and tangential to this discussion.

> Faulty generalization fallacy:
> http://en.wikipedia.org/wiki/Faulty_generalization
> Because some code in firefox is bad, every choice of firefox is bad?

It's a matter of windows into the philosophy, rather than specific
examples. Requiring nine files to do a "Hello World" extension
suggests a large corpus of mandatory boilerplate; imagine, for
instance, that my example bookmarks file structure had demanded
_every_ attribute be provided for _every_ bookmark, instead of
permitting the defaults. That would demonstrate overkill in design,
and the sort of person who would produce that is probably unable to
simplify code for the same reasons.

> As for Durability, if you randomly turn off your machine when your
> program is running, yes you may lose the results of your program. You
> may lose much else!
>
> IOW if you are alone on your machine, all discussion of ACID is moot

No, no, a thousand times no! If I am doing financial transactions,
even if I'm alone on my machine, I will demand full ACID compliance.
Randomly turning off the machine is a simulation of the myriad
possible failures - incoming power failure (or UPS failure, if you
have one), power supply goes boom, motherboard gets fried, operating
system encounters a hard failure condition, cleaning lady unplugs the
server to put her vacuum cleaner onto the UPS... anything. The point
of ACID compliance is that you might lose the results of *this run* of
the program, but nothing more; and if any other program has been told
"That's committed", then it really has been. Without some such
guarantee, you might lose *all the data you have stored*, because
something got corrupted. Partial guarantees of acidity are
insufficient; imagine if power failure during ALTER TABLE can result
in your whole database being unreadable.

With the setup I described above, everything works beautifully if the
OS guarantees an atomic mv() operation. Even if it doesn't, you can
probably figure out what's going on by inspecting the file state; for
instance, you can assume that a non-empty main file should be kept
(discarding the temporary), but if the main file is empty or absent
AND the temporary is readable and parseable, use the temporary. (This
assumes that a fresh install creates a non-empty file, otherwise
there's ambiguity at initial file creation which would need to be
resolved. But you get the idea.)

Of course, that uber-simple option does require a full file rewrite
for every edit. But like I said, it's designed for simplicity, not
concurrent writing.

ChrisA

[toc] | [prev] | [next] | [standalone]

#43616

I am trying to understand your points Chris. On the one hand you say:

On Apr 14, 6:22 pm, Chris Angelico <ros...@gmail.com> wrote:
> No, no, a thousand times no! If I am doing financial transactions,
> even if I'm alone on my machine, I will demand full ACID compliance.



On the other you describe a bookmark storage scheme (which it seems
you are recommending); to wit

>  Suppose bookmarks are stored like this:
>
> r"""Some-Browser-Name web bookmarks file - edit with care
> url:http://www.google.com/
> title: Search engine
> icon: whatever-format-you-want-to-use
>
> url:http://www.duckduckgo.com/
> title: Another search engine
>
> url:http://www.python.org/
>
> url:ftp://192.168.0.12/
> title: My FTP Server
> desc: Photos are in photos/, videos are in videos/
>  Everything else is in other/
> user: root
> pass: secret
> """
>
> The parsing of this file is pretty simple. Blank line marks end of
> entry;…

So are you saying that if one switches from the non-ACID compliant
sqlite to your simple-text data-format, the new 'database' (note the
quote marks) will now become ACID compliant?

[toc] | [prev] | [next] | [standalone]

#43621

On Mon, Apr 15, 2013 at 9:45 PM, rusi <rustompmody@gmail.com> wrote:
> I am trying to understand your points Chris. On the one hand you say:
>
> On Apr 14, 6:22 pm, Chris Angelico <ros...@gmail.com> wrote:
>> No, no, a thousand times no! If I am doing financial transactions,
>> even if I'm alone on my machine, I will demand full ACID compliance.
>
> On the other you describe a bookmark storage scheme (which it seems
> you are recommending); to wit
> ...
> So are you saying that if one switches from the non-ACID compliant
> sqlite to your simple-text data-format, the new 'database' (note the
> quote marks) will now become ACID compliant?

Unlikely. It theoretically could be made ACID compliant (all it needs
is an OS-guaranteed atomic move/rename operation), but my point is
that some things don't _need_ full-on databases. Financial work *does*
(if I'm accepting money from people, I'd better make pretty sure I
know who's paid me and how much); bookmarks usually don't. Also,
bookmarks are the exclusive property of the person who creates them,
so it's helpful to store them in a way that can be edited; with money
movements, you often want some kind of indelibility guarantee, too
(you can't go back and edit a previous transaction, you have to put in
a correcting transaction). Different tasks demand different storage
schemes.

ChrisA

[toc] | [prev] | [next] | [standalone]

#43572

In article 
<CAPTjJmrP_9saiG89DKse-P6D0kPBjXMfe1731dfFAguKvaSO+Q@mail.gmail.com>,
 Chris Angelico <rosuav@gmail.com> wrote:
 > Actually, this is one place where I disagree with the current decision
> of the Python core devs: I think bindings for other popular databases
> (most notably PostgreSQL, and probably MySQL since it's so widely
> used) ought to be included in core, rather than being shoved off to
> PyPI. Databasing is so important to today's world that it would really
> help if people had all the options right there in core, if only so
> they're more findable (if you're browsing docs.python.org, you won't
> know that psycopg is available). Currently the policy seems to be "we
> don't include the server so why should we include the client"; I
> disagree, I think the client would stand nicely on its own. (Does
> Python have a DNS server module? DNS client? I haven't dug deep, but
> I'm pretty sure I can do name lookups in Python, yet running a DNS
> server is sufficiently arcane that it can, quite rightly, be pushed
> off to PyPI.) But this is minor, and tangential to this discussion.

For the bindings to be useful, Python batteries-included distributions 
(like python.org installers) would either need to also ship the various 
DB client libraries for all supported platforms (including Windows), 
which adds complexity and potentially intractable license issues, or 
there would need to be reverse-engineered implementations of the client 
libs or wire protocols, either option adding fragility and complex 
testing issues.  DNS client lookups use published, well-understood 
Internet-standard protocols, not at all like talking to a third-party 
database, be it open-source or not.  Sqlite3 is certainly an anomaly in 
that it is not-only open source but designed to be a lightweight, 
compatible library that runs on just about everything, and with a 
fanatical devotion to compatibility and documentation.  These days just 
about every major product or operating system platform ships with or 
uses a copy of sqllite3 for something.

-- 
 Ned Deily,
 nad@acm.org

[toc] | [prev] | [next] | [standalone]

#43580

On 2013-04-14 09:40, Ned Deily wrote:
> DNS client lookups use published, well-understood 
> Internet-standard protocols, not at all like talking to a
> third-party database, be it open-source or not.

That said, even though DNS is a publicly documented standard, I've
reached for DNS code in the Python stdlib on multiple occasions
(usually to try and snag the MX record for a customer, so smtplib can
send stuff to it), and get disappointed each time.  I'd really love
if there was a simple DNS-lookup module available in the stdlib,
especially if it allowed overriding the server to ask.  I mean...POP,
IMAP and SMTP are all publicly documented standards that Python makes
easily accessible.  DNS would be a good addition.

-tkc

[toc] | [prev] | [next] | [standalone]

Page 2 of 3 — ← Prev page 1 [2] 3  Next page →

Back to top | Article view | comp.lang.python

csiph-web