What is the recommended python module for SQL database access?

[Asaf Las]

On Tuesday, February 11, 2014 5:31:35 AM UTC+2, Chris Angelico wrote:
> On Tue, Feb 11, 2014 at 2:02 PM, Asaf Las <r... at gmail.com> wrote:
> 
> > On Tuesday, February 11, 2014 4:57:30 AM UTC+2, Walter Hurry wrote:
> >> Chris Angelico wrote:
> >> >
> >> > 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).
> >> >
> >>
> >> I agree 100% with this. And speaking as an ex Oracle and DB2 DBA -
> >> not to mention MS-SQL (spit), with which I occasionally had to dabble,
> >> avoid them like the plague unless circumstances dictate.
> >
> > What is about clustering? Do we have such option for free alternatives?
> >
> > Thanks
> 
> PostgreSQL has replication in-built now, which will do most forms of
> clustering. With some third-party software like Slony (also free), you
> can do even more (including replicating between different PostgreSQL
> versions, so you can upgrade progressively without any downtime; PG's
> internal replication has tight restrictions on that). I've used PG's
> streaming replication to fairly good effect. You do need some kind of
> system to decide when to promote a slave to master, though - my boss
> had this weird idea that each node had to be a perfect peer with no
> external authority [1], which led to unsolvable problems, but if you
> have an external system that declares which of several slaves should
> be promoted, it's pretty easy to do. I could whip you up a
> proof-of-concept in an hour, probably; just needs a heartbeat script
> and some way of signalling them to fail over to the new master.
> 
> Clustering for performance, as opposed to reliability, is a bit
> trickier. You can do read-only queries on slaves (so if you have a
> many-readers-few-writers model, this can work nicely), but otherwise,
> you probably need some third-party middleware. I haven't looked into
> that side of things. Ultimately your biggest bottleneck is going to be
> locking, which fundamentally has to be done in one place... or else
> you have to deal with merge conflicts (the bane of true multi-master
> replication).
> 
> So, it all depends on what you need to accomplish, and how much work
> you're willing to do. Postgres offers a particular set of primitives
> (including replication, promotion of a slave to master, etc), and lets
> you trigger things from scripts (execute "pg_ctl promote" to make this
> node become master). Advanced logic can be done by writing a Python
> script that edits config files, runs programs, sends Unix signals,
> whatever. There are pay-for Postgres support companies, too, if you
> need that sort of thing.
> 
> tl;dr: Yeah, you can do that too. :)
> 
> ChrisA
> 
> [1] He had a weird issue with the concept of authority, actually. I
> think his dislike of any form of government polluted his thinking so
> he wouldn't accept even the IT sense of the word "authority". Never
> mind that that's the best way to solve a lot of problems. But I
> digress.

Chris, Thank You very much for your detailed answer 

Regards

/Asaf