database in python ?

[peufeu]

postgresql
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.

I'd suggest that on anything medium postgres will be a lot faster.

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 :-().

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]

Transactions available since 3.23.17 (June 2000)

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.

Foreign keys available since 3.23.44 (Oct 2001)

They are syntactically accepted but not forced.

Stored procedures available since 5.0 (5.0.3 is the current beta)

It's a beta.

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.

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]

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 :-().

There are a couple reasons for the interchangability: 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]

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.

You know that the default server setting has autocommit on, right?

They are syntactically accepted but not forced.

http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.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_REFERENCED_ROW)
Message: Cannot add or update a child row: a foreign key constraint
fails

# Error: 1217 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED)
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.

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

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.