[Tutor] Postgresql+Python -tutorial?

[Joal Heagney]

Alan G wrote:
>> I've been using MySQL up this day, but would like to convert 
>> my program to use Postgresql. 
> 
> I'm curious. Why?
> Is there some advantage to Postgres over MySql?

Yes and no. Postgresql offers more features and is IMO more flexible 
than most SQL servers out there.

Example: postgresql enables you to set up editable sql views. This means 
that you can concatenate several tables into a view (A stored SQL 
query), and use this as if it was an ordinary table by adding, deleting 
and editing data within it.
Very handy if you want to set up "infinite keyword" systems.

E.g. MemoTable with an integer "memoid" field and a text field for a "memo"
      KeywordTable with an integer "keyid" field and a text "keyword" field.
      MemoKeyTable with an integer "memokeyid" field, an integer 
"memoid" field, and an integer "keyid" field - with a UNIQUE 
index/constraint across the "memoid" and "keyid" fields.

To get the keywords for each memo, use a view that contains something 
like the following SQL query. (Warning, the syntax is probably incorrect.)
FROM MemoTable, KeywordTable, MemoKeyTable GET MemoTable.memoid, 
MemoTable.memo, KeywordTable.keyword
WHERE (MemoTable.memoid == MemoKeyTable.memoid) AND (KeywordTable.keyid 
== MemoKeyTable.keyid)
SORT BY MemoTable.memoid;

In MySQL, this would let you retrieve the data from the tables, but 
Postgresql will let you add entirely new memos, add a new keyword to a 
memo, remove a memo cleanly, remove a keyword from a memo, etc. all from 
the view.

However, MySQL is used a lot in web pages because it can return queries 
much faster than any other database - the catch is that it can only do 
this with transaction-less tables.
(I don't know your level of SQL experience, so please forgive me if I 
explain things you already know.) Transactions enable you to have a 
whole series of SQL commands fail if one fails. Useful if you want to 
add interconnected data across a series of tables.

E.g. a finance company moving money from one account to another. You 
definitely don't want the money to be removed from the first account 
unless you can add it to the second account.

(MySQL can use transactions, however, you immediately lose the speed 
advantage.)

Finally, Postgresql does some things differently from other databases 
(Calling a function to fill a default value for autoincrementing, rather 
than a dedicated SERIAL field type.) If you're planing to access the 
database directly from python, this is no hassle, but if you want to use 
it through something like OpenOffice.org or ODBC drivers, some things 
don't work to well.

Hope that helped?

Joal Heagney