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-25 19:12:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: psycopg
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:

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:

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

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

In response to


psycopg by date

Next:From: Bill HouseDate: 2012-03-26 01:00:45
Subject: Re: Connection function
Previous:From: Bill HouseDate: 2012-03-25 15:14:56
Subject: Re: Connection function

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