database in python ?

[Pierre-Frédéric Caillaud]

Re: database in python ?

[color=blue]
> Bottomline - mysql has a lot of marketshare, is improving, and I'm sure
> that it'll eventually be a credible product. But right now it's has a
> wide range of inexcusable problems.[/color]

I so totally agree with you.
I find that mysql promotes bad coding practices by ignoring errors and
substituting invalid data with factory defaults. If it complained when an
obvious error occured (like inserting an invalid date or saying "I can't
fit this data in this column") it would be easy to find the bug ; instead
it shows up months later in creepy ways when you realize part of your data
was truncated, or otherwise screwed up in quite imaginative ways, and you
have to fix the damn thing by hand, burning your eyes on phpmyadmin
screens.
Also most of the interesting features are in the Beta 5.0 !
I find it ironic that it's the biggest open source database while there
are a lot of really free alternatives like firebird or postgres, which
have all the good features Right Now, and which Just Work. There is also a
lot of marketing hype and frankly I have no trust at all in a company
which was making public statements like "Who needs foreign keys ?" just
because their product didnot supports them, then when it supports them,
changing their PR to match.
[color=blue]
> More info at http://sql-info.de/mysql/gotchas.html
> buck[/color]

[Andy Dustman]

Re: database in python ?

Buck Nuggets wrote:
[color=blue]
> 1. mysql doesn't support transactions - one of its io layers[/color]
(innodb)[color=blue]
> does. If you're hoping to get your application hosted you will find
> that most mysql installations don't support innodb. And due to the
> bugs in mysql, when you attempt to create a transaction-safe table in
> mysql if innodb isn't available it will just silently create it in
> myisam, and your transactions will be silently ignored.[/color]

That's not a bug; it's an explicitly-stated design choice.

http://dev.mysql.com/doc/mysql/en/create-table.html

If a storage engine is specified that is not available, MySQL
uses MyISAM instead.
[color=blue]
> 2. mysql is still missing quite a few database basics - views are[/color]
the[color=blue]
> most amazing omission, but the list also includes triggers and stored
> procedures as well. Although most of these features are included in
> the new beta, they aren't yet available in production.[/color]

Views, triggers, stored procedures are all available in 5.0.2 (beta).
[color=blue]
> 3. mysql has an enormous number of non-standard features such as
> comment formatting, how nulls work, concatenation operator, etc.[/color]
This[color=blue]
> means that you'll learn non-standard sql, and most likely write
> non-portable sql.[/color]

SET GLOBAL sql_mode='ansi' ; and you won't have to worry about it.
[color=blue]
> 4. additionally, mysql has a peculiar set of bugs - in which the
> database will change your data and report no exception. These bugs
> were probably a reflection of mysql's marketing message that the
> database should do nothing but persist data, and data quality was the
> responsibility of the application. This self-serving message appears
> to have been dropped now that they are catching up with other[/color]
products,[color=blue]
> but there's a legacy of cruft that still remains. Examples of these
> errors include: silent truncation of strings to fit max varchar
> length, allows invalid dates, truncation of numeric data to fit max
> numeric values, etc.[/color]

MySQL gives warnings when data is truncated or misformatted.

http://dev.mysql.com/doc/mysql/en/mysql-info.html
http://dev.mysql.com/doc/mysql/en/my...ing-count.html
http://dev.mysql.com/doc/mysql/en/show-warnings.html

Since we're in comp.lang.pytho n, MySQLdb-1.2 uses the warnings module
to alert you to this. MySQLdb-1.0 and earlier would raise a Warning
exception (or not, if you used a different cursor class).

Implicit default column values can disabled in 5.0.2 by running the
server in strict mode.

http://dev.mysql.com/doc/mysql/en/server-sql-mode.html
[color=blue]
> 5. cost: mysql isn't expensive, but it isn't free either. Whether[/color]
or[color=blue]
> not you get to use it for free depends on how you interpret their
> licensing info and faq. MySQL's recommendation if you're confused[/color]
(and[color=blue]
> many are) is to license the product or call one of their reps.[/color]

MySQL is licensed under the GPL. You can buy a commercial license if
you don't want the GPL's restrictions.

http://www.mysql.com/company/legal/licensing/
[color=blue]
> Bottomline - mysql has a lot of marketshare, is improving, and I'm[/color]
sure[color=blue]
> that it'll eventually be a credible product. But right now it's has[/color]
a[color=blue]
> wide range of inexcusable problems.[/color]

It's not a bug if you didn't RTFM.

[Buck Nuggets]

Re: database in python ?

> It's not a bug if you didn't RTFM.

Maybe it's not a bug if it's the only DBMS you've ever used and you
actually believe that overriding explicit & critical declaratives is a
valid "design choice". But it is a bug if it's still only partially
supported in a beta version that nobody is yet hosting.

But maybe this release will actually fix ten years of negligence in one
fell swoop - and all these issues will be easily eliminated. But just
in case that turns out to be difficult, and there's some reason it has
taken all this time to achive, just wait and see what this guys finds:

http://sql-info.de/mysql/gotchas.html

BTW, you should upgrade, they're now on 5.0.3. Their support site
appears to be down right now (timeouts) so I can't check the new bug
list, but since 5.0.2 is beta, it may have introduced more problems
than it solved.

buck

[Pierre-Frédéric Caillaud]

Re: database in python ?

[color=blue]
> It's not a bug if you didn't RTFM.[/color]

I did read it in much detail !

In fact I spent a lot of time trying to make understand how it could do a
simple 4-table join to display also purchased products on an online store.
The damn query took 0.5 seconds to execute no matter how I twisted it in
and out !
Postgres did it in 0.5 milliseconds.
I had to split the query in two in the application !

Speaking of the manual, the mysql manual is quite... well... i don't
quite find the word, but it has many sentences which sound like PR stuff.
Like, we don't do this like you or anyone would expect, but there is a
reason ! Embrace our school of thought, stop worrying about integrity !
Peace, my friend, etc. And the non-working examples posted in the user
comments are nice to look at, too. The organization of the manual is a
mess, too, it's often quite difficult to find what I seek. The postgres
manual is just wonderful.

I know I'm feeding the flamewar, but I can't resist, once I came up on a
post on the mysql website from a guy basically saying "wow, the fulltext
is so powerful, I can search this document set in only half a second !"
and then the same day, on the postgres mailinglist, there was a message
from a guy who was really upset because his full text search on something
like 1000 times bigger would take more than one tenth a second, and that
wan't really acceptable for him, and then several competent people
responded and helped him make it work.

That's also why I want to use postgres.

[Steve Holden]

Re: database in python ?

Terry Hancock wrote:
[...][color=blue]
> That's interesting. Most sources I've read seemed to suggest that postgresql
> is slower than MySQL, at least for modest size tables. There must, I suppose,
> be some turnover point on the size of the database? Or are you arguing that
> postgresql is now faster than MySQL in the general case? Can you suggest
> sources for investigating that formally?
>
> It's just possible that I should reconsider Postgresql compatibility. I
> would assume that using the Python DB API would make portability
> between the two easier in any case, wouldn't it?
>
> Cheers,
> Terry
>
> --
> Terry Hancock ( hancock at anansispacework s.com )
> Anansi Spaceworks http://www.anansispaceworks.com
>[/color]

I don't know about the whole picture, but I know form evidence on this
group that there are PostgreSQL driver modules (the name "psycopg" comes
to mind, but this may be false memory) that appear to take diabolical
liberties with DBAPI-2.0, whereas my experience with MySQLdb has been
that I can interchange the driver with mxODBC (for example) as a drop-in
replacement (modulo the differing paramstyles :-().

regards
Steve
--
Steve Holden +1 703 861 4237 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/
Python Web Programming http://pydish.holdenweb.com/

[peufeu@free.fr]

Re: database in python ?

>> postgresql[color=blue][color=green]
>> is slower than MySQL, at least for modest size tables. There must, I[/color][/color]

When not using transactions, MySQL will blow away postgres in
INSERT/UPDATE speed until the concurrency gets up a bit and the readers
block writers strategy used by MyISAM starts to show its weaknesses.
This is in agreement with mass hosting for instance, a lot of small
databases on a mysql will not have concurrency problems.
Of course when not using transactions you have to remind that your data
is not secure, and any power crash might corrupt your database.
Postgres on a RAID with battery backed up cache will no longer have to
sync the disk on each transaction so it gets a lot faster, and you still
have data security. You can also run it with fsync=off for a massive
speedup in transactions per second but you lose data security.
When using transactions (innodb) I've read that postgres is a bit faster.
Regarding query optimization, for simple dumb queries like grabbing a row
from a table, mysql will be a little faster (say 0.2 vs 0.3 ms), and for
medium complex queries like joins on >= 4 medium sized tables (>10 K rows)
postgres can be faster by anything from 1x to 1000x. I've seen it happen,
the same query taking 0.5 seconds in my and 0.5 ms in postgres, simply
because mysql can't plan it correctly.
[color=blue][color=green]
>> suppose,
>> be some turnover point on the size of the database? Or are you arguing
>> that
>> postgresql is now faster than MySQL in the general case? Can you[/color][/color]

I'd suggest that on anything medium postgres will be a lot faster.
[color=blue]
> I don't know about the whole picture, but I know form evidence on this
> group that there are PostgreSQL driver modules (the name "psycopg" comes
> to mind, but this may be false memory) that appear to take diabolical
> liberties with DBAPI-2.0, whereas my experience with MySQLdb has been
> that I can interchange the driver with mxODBC (for example) as a drop-in
> replacement (modulo the differing paramstyles :-().[/color]

psycopg is extremely fast and powerful, so it makes a lot more things
that the dbapi says.
I'd say that database independence is an utopia, once you start to use
triggers and stored procedures and specific column types, you'll be more
or less tied to one database, and doing this is necessary to get good
performance and generally do things right.

[Mage]

Re: database in python ?

Andy Dustman wrote:
[color=blue]
>
>Transactions available since 3.23.17 (June 2000)
>
>[/color]
Transactions only supported on slow database types like innodb.

If you ever tried it you cannot say that mysql supports transactions. No.
Last time when I tried mysql 4.x did explicit commit if you simply
disconnected inside a transaction block. It was a year ago, but I will
never forget it.
[color=blue][color=green]
>>foreign keys,
>>
>>[/color]
>
>Foreign keys available since 3.23.44 (Oct 2001)
>
>[/color]
They are syntactically accepted but not forced.
[color=blue]
>[color=green]
>>procedures,
>>
>>[/color]
>
>Stored procedures available since 5.0 (5.0.3 is the current beta)
>
>[/color]
It's a beta.
[color=blue]
>
>Postgresql is also a fine database. But note that MySQLdb (the Python
>adapter) also has an equivalent mechanism for returning rows as
>dictionaries . As for speed: I don't do any benchmarking, but there
>should be no substantial speed differences between the two interfaces.
>
>
>[/color]

I have seen real benchmarks. MySQL 5 is slower than postgresql and it's
also slower than firebird if you do some real work.

Mage

[Andy Dustman]

Re: database in python ?

Steve Holden wrote:
[color=blue]
> I don't know about the whole picture, but I know form evidence on[/color]
this[color=blue]
> group that there are PostgreSQL driver modules (the name "psycopg"[/color]
comes[color=blue]
> to mind, but this may be false memory) that appear to take diabolical[/color]
[color=blue]
> liberties with DBAPI-2.0, whereas my experience with MySQLdb has been[/color]
[color=blue]
> that I can interchange the driver with mxODBC (for example) as a[/color]
drop-in[color=blue]
> replacement (modulo the differing paramstyles :-().[/color]

There are a couple reasons for the interchangabili ty: Marc-Andre
Lemburg, the author of mxODBC, was also one of the main people involed
with the DB API spec (now PEP-249). MySQLdb was also written with the
spec in mind. One reason for this is that prior to writing MySQLdb, I
used mxODBC in an application I wrote, connecting to a database called
Solid: http://www.solidtech.com/

I don't think they sell the database as a separate product any more; at
least my memory from the late 90's is that they stopped doing that.

I've also done a bit of database work with pyscopg (a few years back),
and there's not a marked difference between that and MySQLdb, as I
recall. Parameter styles are the main difference between the three
(MySQLdb and psycopg use pyformat or %s, and mxODBC uses qmark or ?).
MySQLdb-1.4/2.0 will most likely support both styles as the latter is
what MySQL expects for the Prepared Statement API that is new in 4.1.
By "support both", I mean that you'll be able to configure at run-time
which style you want to use.

For feature comparisons, check out:

http://dev.mysql.com/tech-resources/crash-me.php

These comparisons are a little old, but you can compare MySQL-4.1.1
against PostgreSQL-7.3.3 and others.

If speed is a critical factor, get both (they're free), put in some
representative data for your application, and start benchmarking.

[Andy Dustman]

Re: database in python ?

Mage wrote:[color=blue]
> Andy Dustman wrote:
>[color=green]
> >
> >Transactions available since 3.23.17 (June 2000)
> >
> >[/color]
> Transactions only supported on slow database types like innodb.
>
> If you ever tried it you cannot say that mysql supports transactions.[/color]
No.[color=blue]
> Last time when I tried mysql 4.x did explicit commit if you simply
> disconnected inside a transaction block. It was a year ago, but I[/color]
will[color=blue]
> never forget it.[/color]

You know that the default server setting has autocommit on, right?
[color=blue][color=green][color=darkred]
> >>foreign keys,
> >>
> >>[/color]
> >
> >Foreign keys available since 3.23.44 (Oct 2001)
> >
> >[/color]
> They are syntactically accepted but not forced.[/color]

http://dev.mysql.com/doc/mysql/en/in...nstraints.html
http://dev.mysql.com/doc/mysql/en/error-handling.html

# Error: 1215 SQLSTATE: HY000 (ER_CANNOT_ADD_ FOREIGN)
Message: Cannot add foreign key constraint

# Error: 1216 SQLSTATE: 23000 (ER_NO_REFERENC ED_ROW)
Message: Cannot add or update a child row: a foreign key constraint
fails

# Error: 1217 SQLSTATE: 23000 (ER_ROW_IS_REFE RENCED)
Message: Cannot delete or update a parent row: a foreign key
constraint
fails

Foreign keys work. Prior to 3.23, it was just syntactical support. But
that was 5 years ago.
[color=blue]
> I have seen real benchmarks. MySQL 5 is slower than postgresql and[/color]
it's[color=blue]
> also slower than firebird if you do some real work.[/color]

So use the database that works best for your application. MySQL is not
a panacea, but there are a lot of applications it works well for.