Re: Connection function - Mailing list psycopg

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.

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
    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.

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

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)
  #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