[Tutor] py-postgressql v1.0.1 question

[Bill Allen]

Rance,

I was doing something similar, except I was querying an Oracle database,
using the cx_Oracle module.  I wanted the non-duplicated count of parts in
my database that met certain criteria.  All the output that met the criteria
of the select statements is loaded into the cursor object.  I then loop
through the cursor object appending the contents into a list.   Then I
converted the list to a set to blow out the duplicates and then back to a
list and took the len of the list for my final count.

#Fetch the list of parts
cursor.execute("select pobj_name from pfmc_part where pmodel = :arg_1 and
pstatus = :arg_2", arg_1 = "PN-DWG", arg_2 = "RELEASED")
for pobj_name in cursor:
    Parts_List.append(pobj_name)

print("size of Parts_List before set operation =", len(Parts_List))
Parts_List = list(set(Parts_List))
print("size of Parts_List after set operation  =", len(Parts_List))

Perhaps you could loop though your get_todo object and load into a list and
do similar or just take a len of it directly if it is already a list.

-Bill

On Sun, Sep 12, 2010 at 10:55 AM, Rance Hall <ranceh at gmail.com> wrote:

> I'm not sure if this is the right forum for this or not, if there is a
> better place to ask this question please let me know and I'll re-post
> elsewhere.
>
> I'm using python v3.1 and the py-postgresql v1.0.1 module located at
> http://python.projects.postgresql.org/docs/1.0/
>
> I'm using prepared sql statements like:
>
> insertnote = db.prepare("INSERT INTO business.to_do_list (note) VALUES
> ($1)")
> delete_note = db.prepare("DELETE FROM business.to_do_list WHERE item = $1")
>
> so far so good, everything is working well and I understand whats going on.
>
> But I have a situation where I want to count the number of notes in
> the database, and if 0 do something, and if 1 do something else.
> I have another occasion where I only want the first few records to be
> returned.
>
> So for testing the idea I did this:
>
> get_todo = db.prepare("SELECT note FROM business.to_do_list ORDER BY item")
> get_todo_limit = db.prepare("SELECT note FROM business.to_do_list
> ORDER BY item LIMIT 10")
> get_todo_count = db.prepare("SELECT COUNT(note) AS notecount FROM
> business.to_do_list")
>
> I *think* there is a better way to do this, but I'm not seeing it in
> the documentation, or its there and I'm not understanding it
> correctly.
>
> I suspect that the get_todo_count statement is not required at all.  I
> have a hunch, although I can't prove yet that the result set returned
> by the SQL SELECT statement will have some way to access the record
> count directly
>
> Something like this:
>
> m = get_todo_limit()
>
> if m.count == 0:
>    do stuff
> else:
>    do other stuff
>
> I can't quite narrow this down.  I'm sure its possible, It likely
> depends on what python variable type is used by the py-postgresql
> module, but I'm not seeing this in the docs.
>
> Second question is more of a performance question:
>
> I don't suspect a "large" # of items in the to_do list, so I *think*
> that it would be better to just have one SQL statement and then loop
> through the results 10 times to get the first few records rather than
> having a seperate sql statement as I have shown here.  I'm too new at
> python to have a feel for the *right* way to go about this part
>
> Could someone point me in the right direction please?
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> http://mail.python.org/mailman/listinfo/tutor
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20100912/dc00962a/attachment-0001.html>