Skip site navigation (1) Skip section navigation (2)

Re: Connection function

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Bill House <wch-tech(at)house-grp(dot)net>
Cc: psycopg(at)postgresql(dot)org
Subject: Re: Connection function
Date: 2012-03-26 01:58:31
Message-ID: 4F6FCD47.7080409@gmail.com (view raw or flat)
Thread:
Lists: psycopg
On 03/25/2012 06:00 PM, Bill House wrote:
>
>>
> Thanks, I will do the reading you have suggested.
>
> Regarding your question about why the use of exec: I read where psycopg2
> has quite a bit of capability for parameter substitution, but that
> functionality is limited to just psycopg2.

The benefit of using the psycopg2 parameter substitution is it takes 
care of escaping strings and avoiding SQL injection. See here for 
humorous take on that:

http://xkcd.com/327/

>
> I grew to rely on runtime construction, substitution and execution of
> commands in another environment and I was trying to mimic that behavior
> in a more general way in python.  This way works, I was just wondering
> if there was a better way.

I have been told over the years that using exec is not a good idea. For 
more detail:

http://books.google.com/books?id=JnR9hQA3SncC&pg=PA328&lpg=PA328&dq=python++exec+bad&source=bl&ots=Jb4TLr-03u&sig=_Jz5cTdPZq1PeU1vajQgaDIgtgo&hl=en&sa=X&ei=GslvT-q1FuzYiAKI85zDBQ&ved=0CGMQ6AEwCA


Python has a variety of ways to work with strings. I found the following 
a good introduction to Python:

http://shop.oreilly.com/product/9780596158071.do

>
> Regarding the statistics, I have since learned that the command:
>
>      select reltuples from pg_class where relname = 'your_file_name';
>
> Will give a record count.  I don't know how well it will keep up in a
> dynamic environment but it's a start.

If you have not already found it, look at:

http://wiki.postgresql.org/wiki/Slow_Counting

Not sure what your use case for the count is but remember when using 
named cursors:
"
Named cursors are also iterable like regular cursors. Note however that 
before Psycopg 2.4 iteration was performed fetching one record at time 
from the backend, resulting in a large overhead. The attribute itersize 
now controls how many records are fetched at time during the iteration: 
the default value of 2000 allows to fetch about 100KB per roundtrip 
assuming records of 10-20 columns of mixed number and strings; you may 
decrease this value if you are dealing with huge records."

>
> Thanks,
>
> Bill
>
>
>
>


-- 
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

In response to

psycopg by date

Next:From: Joe AbbateDate: 2012-03-26 04:07:44
Subject: Re: Connection function
Previous:From: Bill HouseDate: 2012-03-26 01:00:45
Subject: Re: Connection function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group