Re: Connection function

On 03/25/2012 08:14 AM, Bill House wrote:
> On 03/24/2012 08:25 PM, Adrian Klaver wrote:
>> On 03/24/2012 05:03 PM, Bill House wrote:
>>> Hello all,
>>>
>>> I am very new to postgresql and python, but I have written a function to
>>> retrieve some admin data from the server.
>>>
>>> I think this is more of a python question rather than a psycopg2
>>> question, but the context is psycopg2.
>>>
>>> I was wondering if this is the right place to post it and ask for a
>>> critique?
>>
>> I would say yes, though I am not sure what the question is:)?
>>
>>>
>>> Thanks,
>>>
>>> Bill
>>>
>>
>>
> Thanks,
>
> Sometimes it is helpful to know how many records one is working with.
>
> From my reading in the psycopg2 documentation (2.4.4) and
> experimentation, I think that this information is only available if one
> uses an unnamed cursor (in which case all the data will have been
> fetched to the client) or with an named cursor (but only after all the
> data has been fetched).
>
> If there is a large amount of data, this may be problematic.
>
> Am I wrong about this? If so, please let me know.

See comments in line.

>
> Anyway, the way I approached this issue was to count the records I was
> interested in and return the value.
>
> The code below works.
>
> I am interested in a critique of it from more experienced persons. Are
> there more elegant or efficient ways to do what I have done?
>
> In particular, I am interested in:
> 1) the python string substitution technique I used

That works, though it also possible to use dictionaries for the Psycopg
strings see here:

http://initd.org/psycopg/docs/usage.html#query-parameters

I find the dict form easier to use for many parameters because it is
self documenting.

The rest of the string substitution seems to be tied to using exec which
I do not think is necessary, see below for more.

> 2) the choice of the postgresql source of data I was seeking (the
> record count)
> 3) if there was an existing function I overlooked which does the
> same thing (in psycopg2 or postgresql)
>
> Regarding # 2 above, I had been relying on the table:
> pg_stat_user_tables and the n_live_tup value. This item has shown the
> correct count for many days. Today, after updating my machines and
> rebooting, the count is 0 and I am not really sure why. Records are
> still there; and even though I have selected data from the database, the
> count is still 0.

The above are system tables/views used by the statistics mechanism of
Postgres. For a starting point on this look at:

http://www.postgresql.org/docs/9.0/interactive/sql-analyze.html

Short version running Analyze will repopulate the tables.

>
> So I took the more direct but time consuming approach of just counting
> the records in the table.

That is the only way to get a 'true' count due to the MVCC nature of
Postgres. There has been a recent optimization of that, but I cannot
remember what version of Postgres has it.

>
> This will be better anyway because many times one will not be selecting
> all the records in a table, so this function can be expanded to include
> a parameter for the selection criteria so that that identical criteria
> may be used again in the work portion of any operation.
>
> Thanks in advance for your input.
>
> Bill
>
> code (indent 2):
> -------------------------------------------------------
> #!/usr/bin/python
> # z_psycopg2_row_cnt7f.py
> # A program to illustrate the methods of creating or supplying runtime
> variables
> # to functions using psycopg2 functions
>
> import sys
> import os
>
> import psycopg2
> import psycopg2.extras
> import psycopg2.extensions
>
> #for string generator
> import string
> import random
>
> con = None
>
> def gen_random_str(str_len):
> """generates a random string str_len long -
> randomly chooses and assembles a number of characters from the digits
> and the upper and lower case alphabet """
> rand_str = ''.join(random.choice(string.ascii_uppercase +
> string.digits + string.ascii_lowercase) for x in range(str_len))
> return rand_str
>
> def rec_cnt_func(conn, table_name):
> """Count the records selected from the table, right now all of them"""
> #Generate a random string to use as a curson name (short right now for
> debugging purposes)
> #The commands following will use this string
> rand_curs_name = gen_random_str(3)
> #Substitute data into a command string and execute
> #create the cursor
> exec "%s = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)" %
> (rand_curs_name,)
> #count the records in the table
> exec "%s.execute(\"SELECT COUNT (*) FROM %s;\")" % (rand_curs_name,
> table_name)

Not sure why you are using exec? Why not?:

cur =
conn.cursor(cursor_factory=psycopg2.extras.DictCursor,name=rand_curs_name)

Same for below.

> #fetch the data
> exec "row = %s.fetchone()" % (rand_curs_name,)
> #All done now, throw the cursor away
> exec "%s.close()" % (rand_curs_name,)
> #return the data to the calling program
> return row[0]
>
>
> def main():
> #supply your connection string here
> current_dsn = "host=xx"
>
> try:
> con = psycopg2.connect(current_dsn)
>
> #supply your table name here
> table_name_in = "xxxx"
>
> #Call the function which will retrieve the data from postgresql
> rec_cnt_val = rec_cnt_func(con, table_name_in)
>
> #print the formatted data in an informative way
> print "Records in table: {}: {:> 20,}".format(table_name_in,
> rec_cnt_val)
>
> except psycopg2.DatabaseError, e:
> print "psycopg2 reports error: {}".format(e)
> sys.exit(1)
>
> finally:
>
> if con:
> con.close()
> return
>
> if __name__ == '__main__':
> main()
> -----------------------------------------------------
> end code
>

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