Thread: Python Driver
Just a small warning for those people using python with postgresql: pysgresql and psycopg are very different animals. You cannot drop in one as a replacement for the other, even though both 'claim' to be DB API 2.0 compliant. If you are starting out with python on postgresql, I would strongly recommend looking at psycopg. It seems a much cleaner implementation of the DB API. Alex Turner NetEconomist
On Thursday 10 February 2005 08:56 pm, Alex Turner wrote: > Just a small warning for those people using python with postgresql: > pysgresql and psycopg are very different animals. You cannot drop in > one as a replacement for the other, even though both 'claim' to be DB > API 2.0 compliant. None of the python postgresql drivers are drop in replacements for each other. Some drivers, such as popy return all data as strings, some have broken fetchmany support, each IIRC return different exceptions in different situations. Pygresql has its own API that I believe predates DBAPI 2.0 and support for the standard API was added at a later date. shameless plug..... One feature of gnuenterprize.org's common library is a datasource system that uses a single connections.conf file that contains entries like [devel] comment = Development DB provider = psycopg host = rdbms.gnuenterprise.org dbname = devel to setup a connection to a database named "devel". Providers can be any of the 4 postgresql drivers, oracle, db2, ingres, mysql, ado, odbc, etc, etc. Most if not all the dbsig 2 drivers are supported. Other formats such a dbf and csv are supported to varying degrees. You can switch out databases by editing that one file. Providers are not limited to just databases either, our application server is just another provider in our system. Differences between providers are handled transparently to the developer. Take Care, James
Funny you should say that - the system I am developing has a similar system, although not as fully developed. I am now having trouble with psycopg locking up my database somehow. It's almost like there are outstanding locks on objects in the database that are preventing other threads operating. Talk about a frustrating week. Alex Turner netEconomist On Fri, 11 Feb 2005 08:58:21 -0600, James Thompson <jamest@ajrs.com> wrote: > On Thursday 10 February 2005 08:56 pm, Alex Turner wrote: > > Just a small warning for those people using python with postgresql: > > pysgresql and psycopg are very different animals. You cannot drop in > > one as a replacement for the other, even though both 'claim' to be DB > > API 2.0 compliant. > > None of the python postgresql drivers are drop in replacements for each other. > Some drivers, such as popy return all data as strings, some have broken > fetchmany support, each IIRC return different exceptions in different > situations. Pygresql has its own API that I believe predates DBAPI 2.0 and > support for the standard API was added at a later date. > > shameless plug..... > > One feature of gnuenterprize.org's common library is a datasource system that > uses a single connections.conf file that contains entries like > > [devel] > comment = Development DB > provider = psycopg > host = rdbms.gnuenterprise.org > dbname = devel > > to setup a connection to a database named "devel". Providers can be any of > the 4 postgresql drivers, oracle, db2, ingres, mysql, ado, odbc, etc, etc. > Most if not all the dbsig 2 drivers are supported. Other formats such a dbf > and csv are supported to varying degrees. You can switch out databases by > editing that one file. Providers are not limited to just databases either, > our application server is just another provider in our system. Differences > between providers are handled transparently to the developer. > > Take Care, > James > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote: > > I am now having trouble with psycopg locking up my database somehow. > It's almost like there are outstanding locks on objects in the database > that are preventing other threads operating. Do you mean the Python script itself is locked up, or just that locks in the database are causing certain transactions to block? Have you looked at pg_locks? Are locks being held by idle uncommitted transactions? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
pg_locks - awesome - I will check it out... I think it's uncommitted transactions that are causing the problem. The original code was written very transactionaly. Alex Turner netEconomist On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr <mike@fuhr.org> wrote: > On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote: > > > > I am now having trouble with psycopg locking up my database somehow. > > It's almost like there are outstanding locks on objects in the database > > that are preventing other threads operating. > > Do you mean the Python script itself is locked up, or just that > locks in the database are causing certain transactions to block? > Have you looked at pg_locks? Are locks being held by idle uncommitted > transactions? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > >
What does the column 'relation' in pg_locks key to (Is there any docs on the website for this?) Alex On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr <mike@fuhr.org> wrote: > On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote: > > > > I am now having trouble with psycopg locking up my database somehow. > > It's almost like there are outstanding locks on objects in the database > > that are preventing other threads operating. > > Do you mean the Python script itself is locked up, or just that > locks in the database are causing certain transactions to block? > Have you looked at pg_locks? Are locks being held by idle uncommitted > transactions? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > >
On Fri, Feb 11, 2005 at 04:24:17PM -0500, Alex Turner wrote: > > pg_locks - awesome - I will check it out... See also pg_stat_activity. If you don't see anything in the current_query column then edit postgresql.conf and set stats_command_string = true, then restart the database. With this configuration you'll be able to see what each connection is doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Feb 11, 2005 at 04:26:04PM -0500, Alex Turner wrote: > > What does the column 'relation' in pg_locks key to (Is there any docs > on the website for this?) See the "System Catalogs" chapter in the documentation (substitute your version of PostgreSQL in the link): http://www.postgresql.org/docs/8.0/static/catalogs.html "relation" refers to an oid in pg_class. An easy way to convert it to a relation name is to cast it to regclass: SELECT relation::regclass, * FROM pg_locks; -- Michael Fuhr http://www.fuhr.org/~mfuhr/