How can I reduce the number of queries to my PostgreSQL database?
As a starter project for learning Python/PostgreSQL, I am building a Books database that stores information on the books on my bookshelf. Say I have three tables. Table "books" contains rows for book_id, title, subtitle, ISBN. Table "authors" contains rows for author_id, author surname, author first names, biographical notes. Table "bookauthors" contains two rows: book_id, author_id. The bookauthors table links the books and authors tables. Scenario: I have a python script which creates web page listing all books in the database, and all authors for each book. My python script does essentially three things: 1. retrieve a list of all book_ids and book_titles. 2. for each book_id, query the bookauthors table and retrieve all author names for that book_id. 3. display it all out as an html table on a web page. The script works fine, if a little slow. I think that's because if I have 50 books in my database, my script performs 51 database queries (1 for all book names; then 1 for each book). A colleague of mine suggested that I could get away with two queries, 1 to read the book ids and titles, and 1 to read the bookauthors table to pull in *all* relations, and then do all the work in Python. I think I know where he's coming from, but I don't know where to begin. Any clues? Is there a specific name for this technique? -- http://mail.python.org/mailman/listinfo/python-list
Re: How can I reduce the number of queries to my PostgreSQL database?
Martin Christensen said: >SR> Scenario: I have a python script which creates web page listing >SR> all books in the database, and all authors for each book. My >SR> python script does essentially three things: > >SR> 1. retrieve a list of all book_ids and book_titles. > >SR> 2. for each book_id, query the bookauthors table and retrieve all >SR> author names for that book_id. > >SR> 3. display it all out as an html table on a web page. > >That's one query, if you're willing to make it advanced enough, >although you need to make an aggregate to enable PostgreSQL to >concatenate and comma separate author names. However, this aggregate >will typically need more than one database function. Such an aggregate >could be as follows: >This is the solution that I would use after working nearly a decade >with databases. It is neither simple nor obvious to the novice, but >it's the Right Way To Do It. For a learning exercise, this is way over >the top, but I thought you might benefit from seeing that - so long as >you only need information that would reasonably fit in one table on a >web page or the equivalent - one query is always enough. Or perhaps >that should be One Query Is Always Enough. :-) Learn at your own pace, >though, but you might want to keep this in mind for future reference. Thanks for that... I'm not going to argue with a decade's experience! I'd never heard of aggregates before, but I'll look into them. Perhaps I'll be able to impress my friends with them one day. The reason for keeping the authors separate was to wrap them with an appropriate HTML href, but presumably your solution could be adapted for this purpose? Cheers, Shay -- http://mail.python.org/mailman/listinfo/python-list
Re: How can I reduce the number of queries to my PostgreSQL database?
>> Say I have three tables.
>
> Only three?
Well, yeah, OK, it's more than that, but after years of being worn away
by "Post a minimal example" requests on comp.text.tex, a minimal
example is what you got...
> Something like {untested... Might need to do a subselect for the
> second JOIN}:
>
> SELECT book_id, title, subtitle, ISBN, surname, firstname, notes from
> books
> LEFT OUTER JOIN bookauthors on books.book_id = bookauthors.book_id
> JOIN authors on bookauthors.author_id = authors.author_id
> ORDER BY books.book_id
>
> The reason for the LEFT OUTER JOIN, if I recall the syntax, is to
> ensure that you get any books that don't have any authors. The sort
> order is to: one) make sure the records are grouped properly for later
> processing
Thanks for the stuff on LEFT OUTER JOIN. Authorless books would be one
of those things I wouldn't have noticed going astray.
> The output will duplicate the book information for those books that
> have multiple authors (the simple meaning of "unnormalized"):
>
> 2,A Book, Of Nothing, 123, Who, Guess, something
> 2,A Book, Of Nothing, 123, Second, I'm, or other
I think this goes along with what I thought of immediately after
posting the question: one query to gather all info needed, then
post-process in Python to order it all (so *that's* why I posted
here...). My thoughts had been to turn
[ 1, "Puppetry", "Bill" ]
[ 1, "Puppetry", "Ben" ]
[ 1, "Puppetry", "Flowerpot Men" ]
into
[ 1, "Puppetry", [ "Bill", "Ben", "Flowerpot Men" ] ]
(if that's not overcomplicating it a bit)...
>To make your report, you would output the book specific information
> only when it changes (this means you need to initialize a temp record to
> null data, and compare each record to the temp; when the compare fails,
> put out the new book data, and copy it into the temp -- in this example,
> just saving the book ID number would be sufficient, as long as it is a
> unique/primary key). THEN, put out the Author information. If the
> comparison of book data passes, it is the same book with an additional
> author, you just need to output the author data.
>
> tmp_bookID = None
> for bk in theCursor:
> if tmp_bookID != bk[0]: #assumes book_id is first field
> Output_Book_Data(bk)
> tmp_bookID = bk[0]
> Output_Author_Data(bk)
... which appears to be along the lines of what your code does! (Where
Output_Author_Data(bk) could append to the author list of the current
book.
I'll go away and see how I can 'adapt' your example code.
Thanks!
Shay
--
http://mail.python.org/mailman/listinfo/python-list
Re: Class Not Auto-Init On Import
On 4/21/07, Robert Rawlins - Think Blue <[EMAIL PROTECTED]>
wrote:
Hello Guys,
From my understanding of what I've read, the 'import' is meant to auto
Init my class ready for me to access its methods, but it doesn't appear too,
I'm having to init them myself before I can access them, like this.
import LocationService
Location = LocationService.LocationService()
LocationService.setIP('192.168.1.1')
Why is this the case? Should i not just be able to access the setIP()
method by doing LocationService.setIP('192.168.1.1') Without having to
create my own inited reference?
Did you try this,
from LocationService import *
LocationService.setIP('192.168.1.1')
# If you have something like LocationService --> LocationService() -->
setIP()
This is similar to using datetime.now() of the datetime module.
import datetime
datetime.now()
Traceback (most recent call last):
File "", line 1, in
AttributeError: 'module' object has no attribute 'now'
datetime.datetime.now()
datetime.datetime(2007, 4, 21, 14, 49, 4, 506309)
Now for the same,
from datetime import datetime
datetime.now()
datetime.datetime(2007, 4, 21, 14, 45, 42, 463064)
Or,
from datetime import *
datetime.now()
datetime.datetime(2007, 4, 21, 14, 46, 27, 767389)
--
With Regards
Parthan "Technofreak"
http://technofreakatchennai.wordpress.com
--
http://mail.python.org/mailman/listinfo/python-list
Re: python on window
On 26 Mar 2007 05:00:54 -0700, sandeep patil <[EMAIL PROTECTED]> wrote:
i have written this program but i have gott following error,
in anather proram "indentation error" sir how i will indent in my
editor
#test.py
>>> def invert(table):
index=()
for key in table:
value=table[key]
if not index.has_key(value):
index[value]=[]
index[value].append(key)
return index
>>> phonebook = {'sandeep':9325, 'amit':9822, 'anand':9890, 'titu': 9325}
>>> phonebook
{'titu': 9325, 'amit': 9822, 'anand': 9890, 'sandeep': 9325}
>>> print phonebook
{'titu': 9325, 'amit': 9822, 'anand': 9890, 'sandeep': 9325}
>>> inverted_phonebook = invert(phonebook)
Traceback (most recent call last):
File "", line 1, in
inverted_phonebook = invert(phonebook)
File "", line 5, in invert
if not index.has_key(value):
AttributeError: 'tuple' object has no attribute 'has_key'
>>> interted_phonebook= invert(phonebook)
Traceback (most recent call last):
File "", line 1, in
interted_phonebook= invert(phonebook)
File "", line 5, in invert
if not index.has_key(value):
AttributeError: 'tuple' object has no attribute 'has_key'
>>>
In your code, index = () means it is a tuple. IIRC, it should be a
dictionary. For that, index = {}. This is the one causing the following
error in your code,
if not index.has_key(value):
AttributeError: 'tuple' object has no attribute 'has_key'
You can not use has_key over a tuple object.
But where is the "indentation error" ? I see nothing like that in the error
message.
--
With Regards
---
Parthan.S.R.
--
http://mail.python.org/mailman/listinfo/python-list
Re: help with dates
On 31 Mar 2007 14:21:23 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:
I'm working on a website for some firefighters that want to be able to
sign-up for overtime and I need some help figuring out a date related
problem.
Here's the scenario:
Four groups of firefighters (group1, group2, group3, group4). Each
group works a 24 hr shift. So group1 works April 1, group2 works April
2, group3 works April 3, group4 works April 4, group 1 works April 5,
etc. It just keeps rolling like this forever into next year, etc.
I need to come up with a methodology for the following:
I have a small program for you, hope this helps.
dutycycle.py
#!/usr/bin/env python
"""Data Cycle Finder
The input date is of the form dd/mm/.
"""
from datetime import date
import sys
def dutyfinder(_date):
start = date(2007,1,1) # the duty cycle started on 1st jan 2007
return (date.toordinal(_date)-date.toordinal(start))%4
indate = sys.argv[1].split('/')
enddate = date(int(indate[2]),int(indate[1]),int(indate[0]))
onduty = int(dutyfinder(enddate)) + 1
print onduty
---
Now, call this program with your input date in dd/mm/ format. You can
modify the code to suit mm/dd/ format . Also modify the start date which
is now 1st January 2007. The output will be the number of the group.
Ex: $ python dutycycle.py 10/1/2007
output: 2
--
With Regards
Parthan "Technofreak"
http://technofreakatchennai.wordpress.com
--
http://mail.python.org/mailman/listinfo/python-list
Re: Python editor/IDE on Linux?
OMG, all of you forgot IDLE ? http://www.python.org/idle/ It is very good for the starters and it looks simple. There are also Eric http://www.die-offenbachs.de/detlev/eric.html and SPE IDE http://pythonide.stani.be/ And there is always the uber cool editor (aka OS), Emacs :) -- With Regards Parthan "Technofreak" http://technofreakatchennai.wordpress.com -- http://mail.python.org/mailman/listinfo/python-list
Re: Qt4 in ubuntu
On 15 Apr 2007 13:24:22 -0700, Marcpp <[EMAIL PROTECTED]> wrote: Is possible install Qt4 pyqt4 under kubuntu? Few times ago i tried to run pyqt in ubuntu (gnome) but i can't do it. Installation as in [1] Installing using the apt-get/synaptic/adept/aptitude which is present in Ubuntu/Kubuntu or [2] Installing from the source ? I have both of the above in both Ubuntu and Kubuntu. To install from the Ubuntu repository, try $ sudo apt-get install qt4 pyqt4 Or, you can download the source tar ball of Qt4 from Trolltech website and build it on your computer :) If you have any problem during source installation, ask it down here. -- With Regards Parthan "Technofreak" http://technofreakatchennai.wordpress.com -- http://mail.python.org/mailman/listinfo/python-list
Re: **argv can't work
On 1/20/07, Jm lists <[EMAIL PROTECTED]> wrote:
hello members,
See my script piece below:
def testB(shift,**argv):
print "first argument is %s" %shift
print "all other arguments are:",argv
testB('mails','Jen','[EMAIL PROTECTED]','Joe','[EMAIL PROTECTED]')
It can't work at all.please help tell me the reasons.thanks.
It works for me, check this one :
def argtst(x, **y):
... print "first arument is %s" % x
... print "other arguments are", y
...
argtst(x=10,a=1,b=2,c=3)
first arument is 10
other arguments are {'a': 1, 'c': 3, 'b': 2}
(!) : Do not use the name 'argv' for the second argument.
--
With Regards,
TechnoFreak
--
http://mail.python.org/mailman/listinfo/python-list
Re: Windows 7 : any problems installing or running Python ?
Hello Skippy, In response to your message "Windows 7 : any problems installing or running Python ?" I found posted at (http://mail.python.org/pipermail/python-list/2009-August/1215524.html), I've got to say that I can't seem to get any version of Python to work on my computer. I have a Toshiba Satellite laptop running Windows 7 Home Premium with an AMD Turion II Dual-Core processor. I've tried all of the versions listed at http://python.org/download with no success. They install fine but when I try to run the IDLE (Python GUI), it does nothing at all. Do you have any suggestions that might help me out here? I would really appreciate your input. Thank you, David M Covey Sr. ad...@daffitt.com -- http://mail.python.org/mailman/listinfo/python-list
