Re: Connection function

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