ZSQL Methods and +
I have just joined this list, so forgive me as I break my cardinal rule of reaidng a list for a while before posting. I am working on a banner ad rotation system for my Zope installation. To that end I am using a rather simple algorithm to determine which ad should be shown: Each time an ad is selected, ads not shown have their "notshown" count incremented by 2. An ad is selected whenever the number of impressions remaining PLUS its "notshown" is the largest in the DB. To that end I am trying to use the following query template to return the pointers to the ad to be displayed: select image, alt, id, imprem+notshown as fr from ads where imprem > 0 order by fr DESC LIMIT 1 As I quickly discovered the use of the '+' causes Zope to choke with a "keyError": Error, exceptions.KeyError: unhandled SQL used: select image, alt, id, imprem+notshown as fr from ads where imprem > 0 order by fr desc LIMIT 1 Traceback (innermost last): File /usr/local/www/Zope-2.0.0b5/lib/python/ZPublisher/Publish.py, line 209, in publish_module File /usr/local/www/Zope-2.0.0b5/lib/python/ZPublisher/Publish.py, line 179, in publish File /usr/local/www/Zope-2.0.0b5/lib/python/Zope/__init__.py, line 199, in zpublisher_exception_hook (Object: getad) File /usr/local/www/Zope-2.0.0b5/lib/python/ZPublisher/Publish.py, line 165, in publish File /usr/local/www/Zope-2.0.0b5/lib/python/ZPublisher/mapply.py, line 154, in mapply (Object: manage_test) File /usr/local/www/Zope-2.0.0b5/lib/python/ZPublisher/Publish.py, line 102, in call_object (Object: manage_test) File /usr/local/www/Zope-2.0.0b5/lib/python/Shared/DC/ZRDB/DA.py, line 310, in manage_test (Object: getad) File /usr/local/www/Zope-2.0.0b5/lib/python/Shared/DC/ZRDB/DA.py, line 291, in manage_test (Object: getad) File /usr/local/www/Zope-2.0.0b5/lib/python/Shared/DC/ZRDB/DA.py, line 395, in __call__ (Object: getad) File lib/python/Products/ZMySQLDA/db.py, line 191, in query KeyError: (see above) Is this a known problem? Have I done something incredibly stupid? I am able to use this syntax from the mysql monitor as well as in perl cripts. I have tried various combos of parens and escaping, to no avail. Thanks. -- Matt Miller matt.miller@thelinuxstore.com
Matt Miller wrote:
I have just joined this list, so forgive me as I break my cardinal rule of reaidng a list for a while before posting.
I am working on a banner ad rotation system for my Zope installation. To that end I am using a rather simple algorithm to determine which ad should be shown:
While not specifically solving your problem, have you looked at the Advertsising Banner Folder product? I am currently working on modifiying and extending it, but am running into some issues.
Each time an ad is selected, ads not shown have their "notshown" count incremented by 2. An ad is selected whenever the number of impressions remaining PLUS its "notshown" is the largest in the DB.
Hmmm sounds like that number could get quite large. Is this a method you are using for 'weighting' various banners? IOW, give certain banners a higher base notshown count? This is part of where I am working on in a banner product. -- In flying I have learned that carelessness and overconfidence are usually far more dangerous than deliberately accepted risks. -- Wilbur Wright in a letter to his father, September 1900
Bill Anderson wrote:
Matt Miller wrote:
I have just joined this list, so forgive me as I break my cardinal rule of reaidng a list for a while before posting.
I am working on a banner ad rotation system for my Zope installation. To that end I am using a rather simple algorithm to determine which ad should be shown:
While not specifically solving your problem, have you looked at the Advertsising Banner Folder product?
Yes, I have. I couldn't find a good way to produce the reports which my boss (and presumeably our customers) demand. I need to be able to count impresions AND click throughs.
I am currently working on modifiying and extending it, but am running into some issues.
Welcome to my world!
Each time an ad is selected, ads not shown have their "notshown" count incremented by 2. An ad is selected whenever the number of impressions remaining PLUS its "notshown" is the largest in the DB.
Hmmm sounds like that number could get quite large. Is this a method you are using for 'weighting' various banners? IOW, give certain banners a higher base notshown count?
Yes, this is a eighting. I have two considerations in the weighting. One I don't want to just shuffle thru the banners.. some customers ma buy twice as many banners as another.. usually they want maximum short term impact, so I don't want to stretch their 10 million impressions over 100 million hits (in the case of ten banner sin the system).. but I don't want to wait for the numbers to equalize, either. This method seems like a 'fair' way to get heavy buyers heavy rotation up front. I forgot an important piece of this. When a banner IS shown, its notshown gets reset to 0. So the idea is if you have more impressions, we show you more often.. but not to the exclusion of the other ads in the system... eventually they will get 'niced' up to the top, even if they only have one impression left and another has 10 million. I know I will need a more sophisticated algorithm in the future, but for today, I just need my addition!
This is part of where I am working on in a banner product.
Cool, maybe I should look more carefully at the banner product. I have written one similar to the one I am trying to recreate here, so it seemed this would be quicker... but perhaps not if I can't add in my SQL selects. -- Matt Miller matt.miller@thelinuxstore.com
participants (2)
-
Bill Anderson -
Matt Miller