[web2py] How to change db servers (same underlying type)?
My application currently works fine, accessing a MySQL server on host H1
("H1" is simply a name I've made up for this example.) I now want to start
using MySQL on host H2 (hardware different from H1), same username and pwd.
Once the tables/structure are created I'll just dump data from the server
on H1 and restore (INSERTs) to the server on H2.
1. Is there an easier way than what I've outlined?
2. How do I go about getting web2py to create the structure on H2? I
assume the first step is to change the uri to point to the server on H2. Is
that it? Or do I delete all the files in the 'database' dir under the
application. Or something else entirely?
Thanks.
Michael
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: session and/or auth-login problems
The problem turned out to be that I was trying to save the results of Mail.Attachment() in the session, and those results seem to be a dal (if I remember correctly) object w/ connection. On Monday, October 8, 2012 4:19:59 PM UTC-6, MichaelF wrote: > > I've seen this before but I cannot remember what's causing it. > > I have @auth.requires_login() decorating several of my controller > functions. I log in first, 'go' to one of those controller functions/pages > in my browser, and I get challenged to log in (even though I'm already > logged in). I log in and it takes me where I want to go. On that page I > fill out a form and submit it. That page/controller requires login, and I'm > challenged again, even though the previous page (where I pressed 'submit') > said I was logged in. How did I get logged out? > > On a related note: I take out the 'requires_login' decoration and run. It > seems, though, that the session isn't working. (I know, I know; it's > working, but I'm doing something to prevent it from doing so!) For example, > on the page/controller that creates the form I save a Storage object in > session.meetInfo. When I submit, session.meetInfo isn't there. 'session' is > there but not meetInfo. I have no session.forget() calls anywhere (that I > can see). > > This happens in Chrome and Firefox. I close all Chrome/FF tabs/windows and > start fresh (without restarting); no luck. This is Windows 7, web2py 2.x. > > Thoughts/suggestions? > --
[web2py] Re: session and/or auth-login problems
Thanks for that. Here's where I'm confused: 1. In your suggested 'else' you say to get the values from the form. Where do I create the form? Do I do it there (as well as in the GET branch)? I'd like to create the form only one time. 2. You say I do a form.process in the GET branch? I thought I do form.process() only on a POST. Sorry for my rookie questions. After some reading (and before reading your most recent post) I thought I should do this (comments welcome): form = FORM(...) # don't insert any values if form.accepts(...): # process request.post_vars... redirect(...) elif form.errors: # error processing else: # Pre-populate form elements return dict(form=form) Or am I off base here? Thanks. On Wednesday, October 31, 2012 6:45:36 PM UTC-6, Niphlod wrote: > > If you cached the values no distinction between post or get matters. you > fetch them one-time only. > If you don't want to cache them, it's another story > > Basic workflow > > - requesting values > - form = FORM() > - users press submit (form.process) > - re-requesting values (your unfortunate case) > - process the values sent by the form > > But, you are afraid that requesting values from db 2 times is too much > (sound like premature optimization, but anyway, let's go with it). > You did > > if post_vars > process the values sent > else >fetch values >form = FORM() > > This leaves you with open doors to XSRF and double submissions (not using > form.process or form.accepts). > You can still skip the fetch part if the page has been POSTed (don't need > for default values at that point), so the logic goes this way > > if page is GETted > fetch values > form = FORM() > user press submit (form.process) > else (page is POSTed) > don't fetch the data > process the values from the form > > --
[web2py] Re: Best practice for prepopulating regular form
Jim,
Thanks. I had already tried that, and it doesn't work for me. I wrote a
little controller to test it:
@auth.requires_login()
def test_vars():
form = FORM(
FIELDSET('Subject: ', INPUT(_name='subject')),
FIELDSET('Other subject: ', INPUT(_name='otherSubject')),
FIELDSET('Recipients: ', INPUT(_name='recips', )),
FIELDSET('Message: ', TEXTAREA(_name='message')),
INPUT(_type='submit', _value='send', _name='sendBtn'),
INPUT(_type='submit', _value='cancel', _name='cancelBtn')
)
if form.accepts(request, keepvalues=True):
response.flash = 'oky'
elif form.errors:
response.flash = 'form has errors'
else:
form.vars.subject = 'my subject'
form.element(_name='otherSubject')['_value'] = 'other subject'
return dict(form=form)
I have no view, so I get the default. The form I see displayed has a blank
for the field 'subject', and the field 'otherSubject' is filled with 'other
subject'.
Now, that said, I could *easily* believe I'm screwing something else up. If
that example works on your system, then there's something else I'm doing
wrong. Thoughts?
On Wednesday, October 31, 2012 5:59:26 PM UTC-6, Jim S wrote:
>
> form.vars.fieldname = 'fieldvalue'
>
>
> -Jim
>
> On Wednesday, October 31, 2012 6:13:20 PM UTC-5, MichaelF wrote:
>>
>> I have a 'regular' form (i.e., FORM, not SQLFORM). I want to prepopulate
>> some of the fields, but I don't know the values to use for them when I
>> first create the form. What's the best practice for populating field
>> 'subject'? Is it using the 'element' function? For example:
>>
>> form = FORM(
>> FIELDSET('Subject: ', INPUT(_name='subject')),
>> FIELDSET('Recipients: ', INPUT(_name='recips', )),
>> FIELDSET('Message: ', TEXTAREA(_name='message')),
>> INPUT(_type='submit', _value='send', _name='sendBtn'),
>> INPUT(_type='submit', _value='cancel', _name='cancelBtn'))
>> if form.accepts(...):
>>...
>> elif form.errors:
>>...
>> else:
>>someCalculatedValue = some_database_call()
>>form.element(_name='subject')['_value'] = someCalculatedValue
>>
>> Thanks.
>>
>
--
[web2py] Best practice for prepopulating regular form
I have a 'regular' form (i.e., FORM, not SQLFORM). I want to prepopulate
some of the fields, but I don't know the values to use for them when I
first create the form. What's the best practice for populating field
'subject'? Is it using the 'element' function? For example:
form = FORM(
FIELDSET('Subject: ', INPUT(_name='subject')),
FIELDSET('Recipients: ', INPUT(_name='recips', )),
FIELDSET('Message: ', TEXTAREA(_name='message')),
INPUT(_type='submit', _value='send', _name='sendBtn'),
INPUT(_type='submit', _value='cancel', _name='cancelBtn'))
if form.accepts(...):
...
elif form.errors:
...
else:
someCalculatedValue = some_database_call()
form.element(_name='subject')['_value'] = someCalculatedValue
Thanks.
--
[web2py] Re: session and/or auth-login problems
Thanks for the insights. Re. my #2: you suggest caching values or fetch them only on a GET. Ok, so does that mean I create the form up front without the "value=..." clauses, and set the values only when I determine the request is a GET? I'm not sure of the exact sequence here. Thanks again. On Wednesday, October 31, 2012 4:04:25 PM UTC-6, Niphlod wrote: > > On Wednesday, October 31, 2012 10:08:41 PM UTC+1, MichaelF wrote: >> >> 1. Re. PS2: Thanks for the info on the public functions. Sorry about not >> attaching a complete app; I thought that really wouldn't have been >> reasonable on my part. Also, it requires db access, etc. I'll try to repro >> the problem on a smaller scale. > > > Yep, the point wans't really "tied" to your case, but to all users posting > their controllers claiming misunderstandings and/or uncommon behaviours. > You can contribute to web2py too just allowing "testers" to spend less > time on reproducing, hence replying faster/better with the solution (or the > identification of the claimed bug). > Packing apps is one way, but if you feel compelled to reproduce only one > part of it, you must "trim" it down in a way that's reproducible by others, > or only "structural" errors will be spotted. > Reducing your code to something small and reproducible makes your life > easier too (you become a "tester", grasp what goes on web2py code, and > maybe you end up helping others with the saved time :-P). You can also > pinpoint better the root cause of your behaviour (e.g. are sessions values > saved even if I redirect ? if not, then found a problem, if yes, then it's > something else my controller is trying to do) > > >> >> 2. Re. PS1: Please forgive my ignorance/incompetence. When I build my >> form I have values in it (taken from the db), so building the form at the >> start means I have to go to the db to retrieve those values (as in, >> "value=retVals.subject"). I'm trying to avoid doing that on a POST. Or >> should I build a 'valueless' form at the start, and add values only if I'm >> not handling the POST? >> > > There's no notion of fetching values from db in your code, so I couldn't > guess a reason. > Cache the fetched values if it takes so much to retrieve them (or fetch > them only if request.env.request_method == 'GET'). You are exposing youself > to double submits and XSRF leaving a form without accepts() or process(). > > >> >> 3. Given that your example works fine, what sorts of things should I look >> for in my app that might cause the session to be recreated? >> >> > Try to trim away unnecessary code and reduce until you can't verify the > behaviour anymore (or start from my example, see if it's fine even for you, > then add back your code) > > --
[web2py] Re: session and/or auth-login problems
1. Re. PS2: Thanks for the info on the public functions. Sorry about not
attaching a complete app; I thought that really wouldn't have been
reasonable on my part. Also, it requires db access, etc. I'll try to repro
the problem on a smaller scale.
2. Re. PS1: Please forgive my ignorance/incompetence. When I build my form
I have values in it (taken from the db), so building the form at the start
means I have to go to the db to retrieve those values (as in,
"value=retVals.subject"). I'm trying to avoid doing that on a POST. Or
should I build a 'valueless' form at the start, and add values only if I'm
not handling the POST?
3. Given that your example works fine, what sorts of things should I look
for in my app that might cause the session to be recreated?
Thanks.
On Wednesday, October 31, 2012 2:32:21 PM UTC-6, Niphlod wrote:
>
> on my pc the session files are not recreated.
>
> session values are saved even if you redirect (just to test it out)
>
> @auth.requires_login()
> def send_info():
>setupMeetInSession()
>form = dict()
>session.customvar = True
>redirect(URL('send_info2'))
>return dict(form=form)
>
> @auth.requires_login()
> def send_info2():
> return dict(session=session)
>
>
> def setupMeetInSession():
>session.meetInfo = session.meetInfo or Storage()
>return
>
> hitting the page send_info redirects you to send_info2 that holds both
> meetInfo and customvar values.
>
> PS1: the reason behind defining the form without accepts() and recatching
> all the post_vars on the beggining of the function is a mistery to me
> PS2: functions without params are exposed to the public. If this is an
> internal function you should avoid someone ending in setupMeetInSession
> while navigating with a default parameter, if that function is never going
> to be exposed to the public
>
>
> def setupMeetInSession(fake=True):
>session.meetInfo = session.meetInfo or Storage()
>return
>
> BTW for everyone: try to attach something that users passing by (as me)
> can reproduce. Here it's missing 3 controllers functions, 3 inner
> functions, the default values for retval, the concatenation of a string
> with a possibly none request.args(0), etc.
> Having to hack your examples to reproduce may end up in not reproducing
> the behaviour is happening on your development machine just because the bug
> is originated on the hacked parts.
>
>
--
[web2py] Re: session and/or auth-login problems
As expected, every time the problem appears (that is, I submit a form and
get challenged to login again), there's an additional/new session file in
the session dir. The only usages of 'session' are reading/writing, such as
session.meetInfo.currAssgmts = False
attachments = session.meetInfo.attachments
Would a redirect in my controller be doing anything to reset the session?
Here's my controller that's involved. Note the call to setupMeetInSession.
I'm assuming (uh-oh) that this isn't recreating a session:
@auth.requires_login()
def send_info():
setupMeetInSession()
if request.post_vars.sendBtn:
attachments = session.meetInfo.attachments
recipients = request.post_vars.recips.split(',')
mailResult = mail.send(recipients,
subject = request.post_vars.subject,
message = request.post_vars.message,
attachments = attachments
,cc = ['x...@gmail.com',
'y...@gmail.com']
)
if mailResult:
session.flash = 'Email(s) sent'
if request.args(0) == 'Meet':
url = 'all_meets'
elif request.args(0) == 'Team':
url = 'all_teams'
elif request.args(0) == 'Person_certification':
url = 'all_officials'
elif request.args(0) == 'Official':
url = 'all_officials'
else:
url = 'index'
redirect(URL(url))
else:
response.flash = 'Error sending mail'
else:
if request.args(0) == 'Meet':
retVals = send_info_meet()
elif request.args(0) == 'Team':
retVals = send_info_team()
elif request.args(0) == 'Official':
retVals = send_info_official()
else:
print ' Got bogus args(0): ' + request.args(0)
redirect(URL('index'))
session.meetInfo.attachments = retVals.attachments
form = FORM(
FIELDSET('Subject: ',
INPUT(_name='subject', value=retVals.subject)),
FIELDSET('Recipients: ',
INPUT(_name='recips',
value=','.join(retVals.recipients))),
FIELDSET('Message: ',
TEXTAREA(_name='message', value=retVals.message)),
INPUT(_type='submit', _value='send', _name='sendBtn'),
INPUT(_type='submit', _value='cancel', _name='cancelBtn')
,FIELDSET('debug', TEXTAREA(value='retVals len: %d; sess len: %d'
% (len(retVals.attachments), len(session.meetInfo.attachments
)
return dict(form=form)
def setupMeetInSession():
session.meetInfo = session.meetInfo or Storage()
return
The view is simple:
{{extend 'layout.html'}}
{{"""
Send meet information
"""}}
{{=' '.join(x.capitalize() for x in request.function.split('_'))}}
{{=form}}
{{if request.is_local:}}
{{=response.toolbar()}}
{{pass}}
???
Thanks in advance.
On Wednesday, October 31, 2012 10:29:14 AM UTC-6, Niphlod wrote:
>
> inspect the session/ folder and clean it.
> Then try again with your preferred browser and see if a file is created.
> Then make sure that another file doesn't get created as long as you don't
> clear the cache/open the page with another browser
> Then make sure that for every time you store something in session
> (session.something = 1) the session file is updated (watch the last
> modified time)
>
>
> On Wednesday, October 31, 2012 5:02:01 PM UTC+1, MichaelF wrote:
>>
>> Any pointers on how I can debug this? Should I insert a debug trace and
>> step through the controller and the view? Should I enable logging and
>> insert various log statements?
>>
>> Thanks.
>>
>> On Monday, October 8, 2012 4:19:59 PM UTC-6, MichaelF wrote:
>>>
>>> I've seen this before but I cannot remember what's causing it.
>>>
>>> I have @auth.requires_login() decorating several of my controller
>>> functions. I log in first, 'go' to one of those controller functions/pages
>>> in my browser, and I get challenged to log in (even though I'm already
>>> logged in). I log in and it takes me where I want to go. On that page I
>>> fill out a form and submit it. That page/controller requires login, and I'm
>>> challenged again, even though the previous page (where I pressed 'submit')
>>>
[web2py] Re: session and/or auth-login problems
Any pointers on how I can debug this? Should I insert a debug trace and step through the controller and the view? Should I enable logging and insert various log statements? Thanks. On Monday, October 8, 2012 4:19:59 PM UTC-6, MichaelF wrote: > > I've seen this before but I cannot remember what's causing it. > > I have @auth.requires_login() decorating several of my controller > functions. I log in first, 'go' to one of those controller functions/pages > in my browser, and I get challenged to log in (even though I'm already > logged in). I log in and it takes me where I want to go. On that page I > fill out a form and submit it. That page/controller requires login, and I'm > challenged again, even though the previous page (where I pressed 'submit') > said I was logged in. How did I get logged out? > > On a related note: I take out the 'requires_login' decoration and run. It > seems, though, that the session isn't working. (I know, I know; it's > working, but I'm doing something to prevent it from doing so!) For example, > on the page/controller that creates the form I save a Storage object in > session.meetInfo. When I submit, session.meetInfo isn't there. 'session' is > there but not meetInfo. I have no session.forget() calls anywhere (that I > can see). > > This happens in Chrome and Firefox. I close all Chrome/FF tabs/windows and > start fresh (without restarting); no luck. This is Windows 7, web2py 2.x. > > Thoughts/suggestions? > --
[web2py] session and/or auth-login problems
I've seen this before but I cannot remember what's causing it. I have @auth.requires_login() decorating several of my controller functions. I log in first, 'go' to one of those controller functions/pages in my browser, and I get challenged to log in (even though I'm already logged in). I log in and it takes me where I want to go. On that page I fill out a form and submit it. That page/controller requires login, and I'm challenged again, even though the previous page (where I pressed 'submit') said I was logged in. How did I get logged out? On a related note: I take out the 'requires_login' decoration and run. It seems, though, that the session isn't working. (I know, I know; it's working, but I'm doing something to prevent it from doing so!) For example, on the page/controller that creates the form I save a Storage object in session.meetInfo. When I submit, session.meetInfo isn't there. 'session' is there but not meetInfo. I have no session.forget() calls anywhere (that I can see). This happens in Chrome and Firefox. I close all Chrome/FF tabs/windows and start fresh (without restarting); no luck. This is Windows 7, web2py 2.x. Thoughts/suggestions? --
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
I spoke too soon about this fixing the problem. It seems that
adding/updating a record with such a field using the admin interface, and
using a smartgrid, doesn't do it.
I create this table:
db.define_table('Test_bool',
Field('test_bool', 'boolean'))
I also have the following at the start of my db.py model file:
db._adapter.types = copy.copy(db._adapter.types)
db._adapter.types['boolean']='TINYINT(1)'
Once web2py creates the table I confirm that MySQL has ccreated the field
as TINYINT(1).
I go into the admin interface and insert a record, checking the test_bool
checkbox. The INSERTed record has a 0 for that field. I try it again, same
result. I then UPDATE one of those records, checking the test_bool
checkbox, and the field remains at 0.
I then create a simple smartgrid:
def test_bool():
grid = SQLFORM.smartgrid(
db.Test_bool,
deletable = True, editable = True, create = True
)
return locals()
I edit one of the records, checking the box, yet it doesn't 'take'.
Now, if I go in manually ans set the field to 1 (using MySQL Workbench,
outside the web2py environment), then go to the grid, I see that the box is
checked. If I uncheck it, that 'takes'.
??
Thanks.
On Saturday, September 22, 2012 7:21:07 AM UTC-6, MichaelF wrote:
>
> Converting to 2.x fixed the problems.
>
> On Wednesday, September 19, 2012 12:04:28 PM UTC-6, MichaelF wrote:
>>
>> I have come across one bug with this. If I add a record using the admin
>> interface, I check the 'Is_home_team' checkbox (Is_home_team is defined as
>> a boolean, of course), yet the record has 0 for that field. Given that, as
>> you might expect then, all records have a 0 for that field.
>>
>> ??
>>
>> On Monday, September 17, 2012 9:53:34 PM UTC-6, MichaelF wrote:
>>>
>>> Well, that's unfortunate. I've migrated this semi-manually; I had only
>>> four 'boolean' fields.
>>>
>>> Other than that, the suggested fix (
>>> db._adapter.types['boolean']='TINYINT(1)' ) seems to work.
>>>
>>> On Monday, September 17, 2012 8:42:24 PM UTC-6, Massimo Di Pierro wrote:
>>>>
>>>> I cannot reproduce this error with your code in 2.0.9 and the lines in
>>>> your traceback do not correspond to the source code I have. I think you
>>>> may
>>>> be using an older dal.py
>>>>
>>>> On Monday, 17 September 2012 16:43:30 UTC-5, MichaelF wrote:
>>>>>
>>>>> Yes; here it is:
>>>>>
>>>>> 1.
>>>>> 2.
>>>>> 3.
>>>>> 4.
>>>>> 5.
>>>>> 6.
>>>>> 7.
>>>>> 8.
>>>>> 9.
>>>>>
>>>>> Traceback (most recent call last):
>>>>> File "gluon/restricted.py", line 205, in restricted
>>>>> File "C:/Program Files
>>>>> (x86)/web2py/applications/NCAA_schedule/models/db.py"
>>>>> <http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>,
>>>>> line 165, in
>>>>> File "gluon/dal.py", line 6320, in define_table
>>>>> File "gluon/dal.py", line 742, in create_table
>>>>> File "gluon/dal.py", line 797, in migrate_table
>>>>> File "gluon/dal.py", line 6714, in __getitem__
>>>>> KeyError: 'length_is_yards'
>>>>>
>>>>> The table definition follows:
>>>>>
>>>>> db.define_table('Pool',
>>>>> Field('Pool_name', 'string', required=True,
>>>>> unique=True),
>>>>> Field('Address1', 'string', length=60),
>>>>> Field('Address2', 'string', length=60),
>>>>> Field('City', 'string', length=60),
>>>>> Field('State', 'string', length=2),
>>>>> Field('Zip', 'string', length=15),
>>>>> Field('Nr_lanes', 'integer', required=True),
>>>>> Field('Length', 'integer', required=True),
>>>>> Field('Length_is_yards', 'boolean',
>>>>> required=True,default=True),
>>>>> Field('Has_movea
Re: [web2py] Looking for multi-select widget that uses two text boxes
I finally got around to implementing the multi-select suggested by Anthony ( http://quasipartikel.at/multiselect_next/). It works fine, with a gotcha or two that might be my ignorance. Its use of doesn't seem to play nice with web2py, so some layout broke from using 'regular' multi-select and the suggested one. I added some table 'formatting' and that fixed it. Sort of a 'hammer' approach rather than a high-tech fix, but it works. I'm not skilled enough in css/html to determine what the new multi-select was doing with divs (and who knows; it might not even be divs), but other than that layout problem it works well, and has some nice config/run-time functions (sorting, etc.). On Monday, July 9, 2012 5:47:13 PM UTC-6, villas wrote: > > That's good, I thought you still might have some other requirement(s). > Anyhow, if you implement it, please advise how it goes. I might try > switch to that one if you found it easy to use. All the best, David > > > On Tuesday, July 10, 2012 12:10:50 AM UTC+1, MichaelF wrote: >> >> Nothing's wrong with Anthony's answer; I like it. >> >> On Monday, July 9, 2012 4:54:42 PM UTC-6, villas wrote: >>> >>> I used this one once: >>> http://www.senamion.com/blog/jmultiselect2side.html >>> >>> However, it appears the one Anthony suggested would be better -- what's >>> wrong with that suggestion? >>> >>> >>> On Sunday, July 8, 2012 9:00:04 PM UTC+1, MichaelF wrote: >>>> >>>> Thanks, Bruno. That's not quite what I need for this job, but I do need >>>> that in another. I need something more like what Anthony posted ( >>>> http://quasipartikel.at/multiselect_next/). >>>> >>>> On Sunday, July 8, 2012 12:42:09 PM UTC-6, rochacbruno wrote: >>>>> >>>>> I guess you need this: >>>>> >>>>> >>>>> http://www.web2pyslices.com/slice/show/1526/cascading-drop-down-lists-with-ajax-2 >>>>> >>>>> >>>>> >>>>> *Bruno Cezar Rocha* >>>>> >>>>> http://www.CursoDePython.com.br >>>>> [image: Facebook] <http://facebook.com/rochacbruno> [image: >>>>> Twitter]<http://twitter.com/rochacbruno> [image: >>>>> LinkedIn] <http://linkedin.com/in/rochacbruno> [image: >>>>> about.me]<http://about.me/rochacbruno> [image: >>>>> Amazon] <http://amazon.com/author/rochacbruno> [image: >>>>> AngelList]<http://angel.co/rochacbruno> [image: >>>>> Blog RSS] <http://www.web2pyslices.com/slice/list.rss?author=1> [image: >>>>> Facebook Page] <http://facebook.com/CursoDePython> [image: >>>>> foursquare]<http://foursquare.com/rochacbruno> [image: >>>>> Google Plus] <https://plus.google.com/u/0/116110204708544946953/posts> >>>>> [image: >>>>> pinterest] <http://pinterest.com/rochacbruno> [image: >>>>> SlideShare]<http://slideshare.com/rochacbruno> [image: >>>>> YouTube] <http://youtube.com/user/brunovegan> >>>>> [image: Google Talk] rochacbruno [image: Skype] blouweb >>>>> Blog: Generate a thumbnail that fits in a >>>>> box<http://www.web2pyslices.com/slice/show/1522/generate-a-thumbnail-that-fits-in-a-box> >>>>> Get a signature like this. >>>>> <http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18> >>>>> Click >>>>> here.<http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18> >>>>> >>>>> >>>>> >>>>> >>>>> On Sun, Jul 8, 2012 at 3:10 PM, MichaelF wrote: >>>>> >>>>>> I'm aware of the multiple and checkbox widgets. I'm looking for a >>>>>> widget whose name I don't know. It consists of two side-by-side boxes, >>>>>> each >>>>>> box can contain a list of items, and usually starts off with all the >>>>>> items >>>>>> in the left-hand box. Between the two boxes are two arrows, usually one >>>>>> on >>>>>> top of the other, one pointing to the right, one to the left. The >>>>>> left-hand >>>>>> box contains those items not 'chosen'; the right-hand box contains those >>>>>> chosen. To 'choose' items (that is, to move an item from the left-hand >>>>>> box >>>>>> to the right-hand box) one selects the item(s) (ctrl-click, shift-click, >>>>>> etc.) in the left-hand box, then presses the right-pointing arrow. The >>>>>> selected items move from the left-hand box to the right-hand box. To >>>>>> 'un-choose' items one selects items in the right-hand box, then presses >>>>>> the >>>>>> left-pointing arrow. >>>>>> >>>>>> 1. What is this widget usually called? >>>>>> >>>>>> 2. Does web2py have such a widget? >>>>>> >>>>> >>>>> --
[web2py] Re: Inconsistent multi-select POST values?
Makes sense; thanks. Posting a list on multi-selects would be a help, but there's still the other problem you mentioned (other input types). I've written code to try to take this into account by always converting the value. Any thoughts on why sometimes the value comes across as an int, other times as a string? Michael On Friday, September 28, 2012 7:01:44 PM UTC-6, Massimo Di Pierro wrote: > > Good question. > > The issues is the following. When an HTML form is submitted with one > variable (a) the query_string contains ?a=value. If it is submitted with > two values, the query string contains ?a=value&a=other. > > When web2py (or any other framework) parses the query_string it may > find ?a=value or ?a=value&a=other but it does not know if the variable > comes from a normal input, a select, a multiple select, or checkboxes. > > Different frameworks take the same approach. Web2py parses ?a=value into > request.vars.a='value' and ?a=value&a=other into > request.vars.a=['value','other']. > > Other frameworks do what you suggest and always use a list: ?a=value into > request.vars.a=['value'] but the drawback is that they always do it even > for regular input fields (). If we were to do it we would > have lots more request.vars.a[0] everywhere. > > The web2py solution is a compromise, as many other design decisions are. > > Massimo > > > > > > > > > > On Friday, 28 September 2012 17:33:18 UTC-5, MichaelF wrote: >> >> I have a form with a multi-select. If I choose one value and submit, the >> value is set in request.vars.xxx as a single integer (e.g., 2). If I choose >> more than one value and submit I get a list of string values (e.g., ['1', >> '5']). If I select no values I get None. >> >> Expected? Why the inconsistency? Or am I doing something wrong? Why not >> ['2'], ['1', '5'], and []? That's consistent, orthogonal, etc. >> >> Thanks. >> > --
[web2py] Inconsistent multi-select POST values?
I have a form with a multi-select. If I choose one value and submit, the value is set in request.vars.xxx as a single integer (e.g., 2). If I choose more than one value and submit I get a list of string values (e.g., ['1', '5']). If I select no values I get None. Expected? Why the inconsistency? Or am I doing something wrong? Why not ['2'], ['1', '5'], and []? That's consistent, orthogonal, etc. Thanks. --
[web2py] Re: Rows to Storage conversion available?
No, it doesn't work. The results of the select (with cacheable=True) is a
Rows object:
Person.id,Person.First_name,Person.Last_name,Person.Address1,Person.Address2,Per
son.City,Person.State,Person.Zip,Session.Start_datetime,Session.End_datetime
32,Sue,Clavin,1720 Wood Ave.,,Colorado Springs,CO,80907,2013-01-18
18:00:00,2013
-01-18 20:00:00
38,Julie,O'Neill,1052 Walters Point,,Monument,CO,80132,2013-01-18
18:00:00,2013-
01-18 20:00:00
I store that into a session variable (session.currAssgmts = currAssgmts),
and then also use the object (currAssgmts, NOT session.currAssgmts) for my
processing. The second time through I notice that it's in a session
variable, so I pull it out (currAssgmts = session.currAssgmts), but now
it's a list of Row objects:
[, ]
When I try to reference it thusly:
startDatetime = currAssgmts[0].Session.Start_datetime
I get:
'dict' object has no attribute
'Start_datetime'
I don't get that error when I work on the results of the select; everything
works. It's only when I pull the stored Rows back out of the session
variable.
On Thursday, September 27, 2012 8:41:44 PM UTC-6, Massimo Di Pierro wrote:
>
> While we can do this. I think you try d
>
> rows = db().select(cacheable=True)
>
> rows will lack update_record and delete_record methods but they should be
> pickable. Let is know if that works.
>
> On Thursday, 27 September 2012 21:26:32 UTC-5, MichaelF wrote:
>>
>> I want to store Rows values in a session variable. I realize I can't
>> store the Rows object directly, so I use rows.to_list(). The problem is
>> that where I used to be able to use attributes on the Rows object (e.g.,
>> rows[0].Meet.Name), now I would have to use string keys when I access the
>> list of dicts:
>>
>> rows = db...select()
>> if rows[0].Meet.Name == ...
>> session.rowsList = rows.to_list()
>> ...
>> rows = session.rowsList
>> if rows[0]['Meet']['Name'] == ...
>>
>> Is there a simple way to convert the Rows object (or the result of the
>> to_list() call) to a list of Storage objects?
>>
>> Realize that each Row (and each dict object in the to_list()) can have at
>> least ("at most," maybe) two levels of dicts. The select might have
>> selected from several tables. For example, a simplified result of a select
>> from two joined tables (Rows.to_list(), returning two rows):
>>
>> [{'Person': {'City': 'Springs', 'First_name': 'Sue'},
>> 'Meet': {'Start_datetime': datetime.datetime(2013, 1, 18, 18, 0)}},
>> {'Person': {'City': '...}}]
>>
>> Yes, I can write the code; but does the code already exist...say
>> Rows.to_storage()?
>>
>> Thanks.
>>
>
--
[web2py] Rows to Storage conversion available?
I want to store Rows values in a session variable. I realize I can't store
the Rows object directly, so I use rows.to_list(). The problem is that
where I used to be able to use attributes on the Rows object (e.g.,
rows[0].Meet.Name), now I would have to use string keys when I access the
list of dicts:
rows = db...select()
if rows[0].Meet.Name == ...
session.rowsList = rows.to_list()
...
rows = session.rowsList
if rows[0]['Meet']['Name'] == ...
Is there a simple way to convert the Rows object (or the result of the
to_list() call) to a Storage object? I can say:
rowStorage = Storage(rows.to_list())
but that only handles the "first-level" dicts. I would say:
rows[0].Meet['Name']
Realize that each Row (and each dict object in the to_list()) can have at
least ("at most," maybe) two levels of dicts. The select might have
selected from several tables. For example, a simplified result of a select
from two joined tables (Rows.to_list(), returning two rows):
[{'Person': {'City': 'Springs', 'First_name': 'Sue'},
'Meet': {'Start_datetime': datetime.datetime(2013, 1, 18, 18, 0)}},
{'Person': {'City': '...}}]
Yes, I can write the code; but does the code already exist...say
Rows.to_storage()?
Thanks.
--
[web2py] Re: how to avoid an overhead
By 'cache' do you mean a session variable (or several)? Wouldn't that work? On Thursday, September 27, 2012 8:00:59 AM UTC-6, Anthony wrote: > > I suppose it depends on whether all the calculations and db accesses are > needed for the processing as well as the initial creation. If not, you > could do something like: > > if not request.post_vars: > form = a_lot_of_calculations_and_db_accesses() > else: > form = minimal_form_without_all_the_calculations() > > Another option is to cache some of the results of the calculations as well > as the db queries. You could clear the cache whenever the form is created > (i.e., when there are no request.post_vars) but pull from the cache when > the form is submitted. > > Anthony > > On Thursday, September 27, 2012 1:05:43 AM UTC-4, mweissen wrote: >> >> I have a fundamental question. Let's say I have a function like >> >> def mypage(): >> form = a_lot_of_calculations_and_db_accesses() >> if form.process().accepted: >> do_something() >> return dict(form=form) >> >> The part a_lot_of_calculations_and_db_accesses() runs twice: first time >> to prepare the page and a second time to handle the responses. Is there any >> way to avoid these double call of a_lot_of_calculations_and_db_accesses() >> ? >> >> Regards, Martin >> >> > --
[web2py] Re: Forms - MVC model
That makes sense; thanks. I guess I never looked at it that way.
On Wednesday, September 26, 2012 2:02:41 PM UTC-6, Anthony wrote:
>
> When you build a form object in the controller, although you may make some
> specifications that control its display, you are primarily defining a data
> model for the form. The form object itself is not merely an HTML DOM
> representation but also encapsulates the default and submitted values,
> validators, CSRF token, etc. The form itself is ultimately serialized in
> the view, and if you want something other than the default serialization,
> you would handle that in the view as well (via form.custom or manually
> created HTML markup). So, for the most part, true display-related aspects
> of the form are generally handled in the view, and the controller (and
> model for SQLFORMs) takes care of the data modeling and validation.
>
> Anthony
>
> On Wednesday, September 26, 2012 1:55:32 PM UTC-4, MichaelF wrote:
>>
>> I'm still struggling with the lines of control/authority/info-sharing
>> between displaying/processing the data/form. (I realize not everyone agrees
>> where these lines should be drawn, and even when drawn they're not
>> necessarily "sharp, bright lines.") I like the idea of having the
>> controller simply (!) get appropriate data and send it (say in a dict of
>> lists of rows) to the view, which displays the data appropriately (in a
>> form) and lets the controller process the form results
>> (adding/updating/deleting records, etc.). The controller neither knows nor
>> cares about the display of the data (but *does* need to know some of the
>> form structure, so it will be able to know what to do with the data). The
>> view needs to know what the data is, etc.
>>
>> But much of the code I see in the manual has the controller
>> create/populate/process the form...and so then I wonder what the view is
>> for. (I realize a page is usually more than just a form, and *that* is what
>> the view is for.) I'd like for the controller not to have to create the
>> form with all its fields. (This isn't a huge problem for me, but I *would*
>> like to have the controller not create the form, if possible.)
>>
>> If I have a SQLFORM or CRUD, then the processing part is 'inside' the
>> form processing, which is fine. But if I have a 'manual' form (meaning
>> still using FORM and other helpers, but a non-SQLFORM, non-CRUD form), then
>> the controller should process the posted data.
>>
>> As things exist now (by convention, I think), the controller creates the
>> form and processes the results. Is there a prescribed way (convention, I
>> guess) that would allow me to have the controller simply gather the data
>> needed by a form (other than SQLFORM and CRUD), and yet be able to process
>> the form but not build it?
>>
>> As the controller needs to process the form it needs to have a ref to the
>> form object. I assume there's no easy way for the view to actually create
>> the form and somehow tell the controller about it. So I'd have to do
>> something like create a form object in the controller, then return that
>> object (and related data). The form object would have NO items (input,
>> checkbox, etc.). The view would add those items, essentially creating the
>> 'stuff' that goes inside the form object. With this division of labor the
>> controller still can process the form, and the view would essentially build
>> it (really, update the form object the controller created). So, something
>> like:
>>
>> #in controller file
>> def my_stuff():
>>form = FORM() #note: pretty much an empty form
>>if form.accepts(request, session):
>> ...
>>elif form.errors:
>> ...
>>else:
>> data = db...
>>
>> # my_stuff.html VIEW
>> {{# build the form, using the form obj created by the controller}}
>> {{form.append(INPUT...)}}
>> {{form.append(_type=SUBMIT...))}}
>> {{=form}}
>>
>>1. Is this possible? I suspect not, as the 'recreation' of the form
>>in the controller on submission won't match what was submitted.
>>2. Is this recommended?
>>3. Am I just making things too involved (and should just create the
>>form in the controller)?
>>
>> Thanks.
>>
>> On Wednesday, June 27, 2012 10:39:03 AM UTC-6, Anthony wrote:
>>>
>>> Follow the examples in the book. Most of the form handling (including
>>> creation of the FORM/SQLFORM) object should be handled in the controller.
>>> In the view, you can handle the display of the form, but web2py will give
>>> you a default display if you just do:
>>>
>>> {{=form}}
>>>
>>> Anthony
>>>
>>> On Wednesday, June 27, 2012 7:08:19 AM UTC-4, Pedro Casalinho wrote:
>>>>
>>>> This may be a dumb question but, should forms be created in controllers
>>>> and passed to the view or should they be created in the view, taking into
>>>> consideration the MVC model
>>>
>>>
--
[web2py] Re: Forms - MVC model
I'm still struggling with the lines of control/authority/info-sharing
between displaying/processing the data/form. (I realize not everyone agrees
where these lines should be drawn, and even when drawn they're not
necessarily "sharp, bright lines.") I like the idea of having the
controller simply (!) get appropriate data and send it (say in a dict of
lists of rows) to the view, which displays the data appropriately (in a
form) and lets the controller process the form results
(adding/updating/deleting records, etc.). The controller neither knows nor
cares about the display of the data (but *does* need to know some of the
form structure, so it will be able to know what to do with the data). The
view needs to know what the data is, etc.
But much of the code I see in the manual has the controller
create/populate/process the form...and so then I wonder what the view is
for. (I realize a page is usually more than just a form, and *that* is what
the view is for.) I'd like for the controller not to have to create the
form with all its fields. (This isn't a huge problem for me, but I *would*
like to have the controller not create the form, if possible.)
If I have a SQLFORM or CRUD, then the processing part is 'inside' the form
processing, which is fine. But if I have a 'manual' form (meaning still
using FORM and other helpers, but a non-SQLFORM, non-CRUD form), then the
controller should process the posted data.
As things exist now (by convention, I think), the controller creates the
form and processes the results. Is there a prescribed way (convention, I
guess) that would allow me to have the controller simply gather the data
needed by a form (other than SQLFORM and CRUD), and yet be able to process
the form but not build it?
As the controller needs to process the form it needs to have a ref to the
form object. I assume there's no easy way for the view to actually create
the form and somehow tell the controller about it. So I'd have to do
something like create a form object in the controller, then return that
object (and related data). The form object would have NO items (input,
checkbox, etc.). The view would add those items, essentially creating the
'stuff' that goes inside the form object. With this division of labor the
controller still can process the form, and the view would essentially build
it (really, update the form object the controller created). So, something
like:
#in controller file
def my_stuff():
form = FORM() #note: pretty much an empty form
if form.accepts(request, session):
...
elif form.errors:
...
else:
data = db...
# my_stuff.html VIEW
{{# build the form, using the form obj created by the controller}}
{{form.append(INPUT...)}}
{{form.append(_type=SUBMIT...))}}
{{=form}}
1. Is this possible? I suspect not, as the 'recreation' of the form in
the controller on submission won't match what was submitted.
2. Is this recommended?
3. Am I just making things too involved (and should just create the form
in the controller)?
Thanks.
On Wednesday, June 27, 2012 10:39:03 AM UTC-6, Anthony wrote:
>
> Follow the examples in the book. Most of the form handling (including
> creation of the FORM/SQLFORM) object should be handled in the controller.
> In the view, you can handle the display of the form, but web2py will give
> you a default display if you just do:
>
> {{=form}}
>
> Anthony
>
> On Wednesday, June 27, 2012 7:08:19 AM UTC-4, Pedro Casalinho wrote:
>>
>> This may be a dumb question but, should forms be created in controllers
>> and passed to the view or should they be created in the view, taking into
>> consideration the MVC model
>
>
--
[web2py] Re: Any updates on this problem (boolean for MySQL from CHAR to TINYINT)?
Converting to 2.x fixes the problem. On Thursday, September 20, 2012 10:16:46 AM UTC-6, MichaelF wrote: > > Sorry for being impatient...any updates on this problem ( > https://groups.google.com/d/topic/web2py/IukqqZF_PPE/discussion) > > The problem is changing the web2py type of 'boolean' on MySQL from CHAR(1) > to TINYINT. It works except for UPDATEs. > > Thanks. > --
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
Converting to 2.x fixed the problems.
On Wednesday, September 19, 2012 12:04:28 PM UTC-6, MichaelF wrote:
>
> I have come across one bug with this. If I add a record using the admin
> interface, I check the 'Is_home_team' checkbox (Is_home_team is defined as
> a boolean, of course), yet the record has 0 for that field. Given that, as
> you might expect then, all records have a 0 for that field.
>
> ??
>
> On Monday, September 17, 2012 9:53:34 PM UTC-6, MichaelF wrote:
>>
>> Well, that's unfortunate. I've migrated this semi-manually; I had only
>> four 'boolean' fields.
>>
>> Other than that, the suggested fix (
>> db._adapter.types['boolean']='TINYINT(1)' ) seems to work.
>>
>> On Monday, September 17, 2012 8:42:24 PM UTC-6, Massimo Di Pierro wrote:
>>>
>>> I cannot reproduce this error with your code in 2.0.9 and the lines in
>>> your traceback do not correspond to the source code I have. I think you may
>>> be using an older dal.py
>>>
>>> On Monday, 17 September 2012 16:43:30 UTC-5, MichaelF wrote:
>>>>
>>>> Yes; here it is:
>>>>
>>>> 1.
>>>> 2.
>>>> 3.
>>>> 4.
>>>> 5.
>>>> 6.
>>>> 7.
>>>> 8.
>>>> 9.
>>>>
>>>> Traceback (most recent call last):
>>>> File "gluon/restricted.py", line 205, in restricted
>>>> File "C:/Program Files
>>>> (x86)/web2py/applications/NCAA_schedule/models/db.py"
>>>> <http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>,
>>>> line 165, in
>>>> File "gluon/dal.py", line 6320, in define_table
>>>> File "gluon/dal.py", line 742, in create_table
>>>> File "gluon/dal.py", line 797, in migrate_table
>>>> File "gluon/dal.py", line 6714, in __getitem__
>>>> KeyError: 'length_is_yards'
>>>>
>>>> The table definition follows:
>>>>
>>>> db.define_table('Pool',
>>>> Field('Pool_name', 'string', required=True,
>>>> unique=True),
>>>> Field('Address1', 'string', length=60),
>>>> Field('Address2', 'string', length=60),
>>>> Field('City', 'string', length=60),
>>>> Field('State', 'string', length=2),
>>>> Field('Zip', 'string', length=15),
>>>> Field('Nr_lanes', 'integer', required=True),
>>>> Field('Length', 'integer', required=True),
>>>> Field('Length_is_yards', 'boolean',
>>>> required=True,default=True),
>>>> Field('Has_moveable_bulkhead', 'boolean', required=True,
>>>>default=False),
>>>> format='%(Pool_name)s %(Nr_lanes)s')
>>>>
>>>> Line 165 is the last line of the statement (format=...).
>>>>
>>>> On Monday, September 17, 2012 3:15:08 PM UTC-6, Massimo Di Pierro wrote:
>>>>>
>>>>> Do you have a traceback with more information?
>>>>>
>>>>> On Monday, 17 September 2012 14:23:56 UTC-5, MichaelF wrote:
>>>>>>
>>>>>> Thanks. However, I refer to that field with upper case in all places.
>>>>>> Can you tell me where the lower case 'pending' comes from? The field
>>>>>> name
>>>>>> has always been defined as upper case, and the app has been working up
>>>>>> until I made that latest change. So I went into the db and changed the
>>>>>> field name to start with lower case, then changed the model file to make
>>>>>> it
>>>>>> lower-case 'pending'. That worked, but now the next boolean field in the
>>>>>> db.py file has an upper-case/lower-case problem. The field is
>>>>>> "Length_is_yards" in both the db.py file and the db, and has been that
>>>>>> way
>>>>>> for weeks, and we've been through several db migrations for the past
>>>>>> several weeks (not sure about on those particular tables, though). Now
[web2py] Any updates on this problem (boolean for MySQL from CHAR to TINYINT)?
Sorry for being impatient...any updates on this problem (https://groups.google.com/d/topic/web2py/IukqqZF_PPE/discussion) The problem is changing the web2py type of 'boolean' on MySQL from CHAR(1) to TINYINT. It works except for UPDATEs. Thanks. --
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
I have come across one bug with this. If I add a record using the admin
interface, I check the 'Is_home_team' checkbox (Is_home_team is defined as
a boolean, of course), yet the record has 0 for that field. Given that, as
you might expect then, all records have a 0 for that field.
??
On Monday, September 17, 2012 9:53:34 PM UTC-6, MichaelF wrote:
>
> Well, that's unfortunate. I've migrated this semi-manually; I had only
> four 'boolean' fields.
>
> Other than that, the suggested fix (
> db._adapter.types['boolean']='TINYINT(1)' ) seems to work.
>
> On Monday, September 17, 2012 8:42:24 PM UTC-6, Massimo Di Pierro wrote:
>>
>> I cannot reproduce this error with your code in 2.0.9 and the lines in
>> your traceback do not correspond to the source code I have. I think you may
>> be using an older dal.py
>>
>> On Monday, 17 September 2012 16:43:30 UTC-5, MichaelF wrote:
>>>
>>> Yes; here it is:
>>>
>>> 1.
>>> 2.
>>> 3.
>>> 4.
>>> 5.
>>> 6.
>>> 7.
>>> 8.
>>> 9.
>>>
>>> Traceback (most recent call last):
>>> File "gluon/restricted.py", line 205, in restricted
>>> File "C:/Program Files
>>> (x86)/web2py/applications/NCAA_schedule/models/db.py"
>>> <http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>, line
>>> 165, in
>>> File "gluon/dal.py", line 6320, in define_table
>>> File "gluon/dal.py", line 742, in create_table
>>> File "gluon/dal.py", line 797, in migrate_table
>>> File "gluon/dal.py", line 6714, in __getitem__
>>> KeyError: 'length_is_yards'
>>>
>>> The table definition follows:
>>>
>>> db.define_table('Pool',
>>> Field('Pool_name', 'string', required=True, unique=True),
>>> Field('Address1', 'string', length=60),
>>> Field('Address2', 'string', length=60),
>>> Field('City', 'string', length=60),
>>> Field('State', 'string', length=2),
>>> Field('Zip', 'string', length=15),
>>> Field('Nr_lanes', 'integer', required=True),
>>> Field('Length', 'integer', required=True),
>>> Field('Length_is_yards', 'boolean',
>>> required=True,default=True),
>>> Field('Has_moveable_bulkhead', 'boolean', required=True,
>>>default=False),
>>> format='%(Pool_name)s %(Nr_lanes)s')
>>>
>>> Line 165 is the last line of the statement (format=...).
>>>
>>> On Monday, September 17, 2012 3:15:08 PM UTC-6, Massimo Di Pierro wrote:
>>>>
>>>> Do you have a traceback with more information?
>>>>
>>>> On Monday, 17 September 2012 14:23:56 UTC-5, MichaelF wrote:
>>>>>
>>>>> Thanks. However, I refer to that field with upper case in all places.
>>>>> Can you tell me where the lower case 'pending' comes from? The field name
>>>>> has always been defined as upper case, and the app has been working up
>>>>> until I made that latest change. So I went into the db and changed the
>>>>> field name to start with lower case, then changed the model file to make
>>>>> it
>>>>> lower-case 'pending'. That worked, but now the next boolean field in the
>>>>> db.py file has an upper-case/lower-case problem. The field is
>>>>> "Length_is_yards" in both the db.py file and the db, and has been that
>>>>> way
>>>>> for weeks, and we've been through several db migrations for the past
>>>>> several weeks (not sure about on those particular tables, though). Now I
>>>>> get the KeyError as shown above, but this time it's for field
>>>>> 'length_is_yards'. It looks to me that web2py is assuming it's lower case.
>>>>>
>>>>> One of my migrations last week was the "fake_migrate_all=True" type;
>>>>> don't know if that's relevant.
>>>>>
>>>>> Also, in the .database file the field name is Length_is_yards
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
Well, that's unfortunate. I've migrated this semi-manually; I had only four
'boolean' fields.
Other than that, the suggested fix (
db._adapter.types['boolean']='TINYINT(1)' ) seems to work.
On Monday, September 17, 2012 8:42:24 PM UTC-6, Massimo Di Pierro wrote:
>
> I cannot reproduce this error with your code in 2.0.9 and the lines in
> your traceback do not correspond to the source code I have. I think you may
> be using an older dal.py
>
> On Monday, 17 September 2012 16:43:30 UTC-5, MichaelF wrote:
>>
>> Yes; here it is:
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>>
>> Traceback (most recent call last):
>> File "gluon/restricted.py", line 205, in restricted
>> File "C:/Program Files
>> (x86)/web2py/applications/NCAA_schedule/models/db.py"
>> <http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>, line
>> 165, in
>> File "gluon/dal.py", line 6320, in define_table
>> File "gluon/dal.py", line 742, in create_table
>> File "gluon/dal.py", line 797, in migrate_table
>> File "gluon/dal.py", line 6714, in __getitem__
>> KeyError: 'length_is_yards'
>>
>> The table definition follows:
>>
>> db.define_table('Pool',
>> Field('Pool_name', 'string', required=True, unique=True),
>> Field('Address1', 'string', length=60),
>> Field('Address2', 'string', length=60),
>> Field('City', 'string', length=60),
>> Field('State', 'string', length=2),
>> Field('Zip', 'string', length=15),
>> Field('Nr_lanes', 'integer', required=True),
>> Field('Length', 'integer', required=True),
>> Field('Length_is_yards', 'boolean',
>> required=True,default=True),
>> Field('Has_moveable_bulkhead', 'boolean', required=True,
>>default=False),
>> format='%(Pool_name)s %(Nr_lanes)s')
>>
>> Line 165 is the last line of the statement (format=...).
>>
>> On Monday, September 17, 2012 3:15:08 PM UTC-6, Massimo Di Pierro wrote:
>>>
>>> Do you have a traceback with more information?
>>>
>>> On Monday, 17 September 2012 14:23:56 UTC-5, MichaelF wrote:
>>>>
>>>> Thanks. However, I refer to that field with upper case in all places.
>>>> Can you tell me where the lower case 'pending' comes from? The field name
>>>> has always been defined as upper case, and the app has been working up
>>>> until I made that latest change. So I went into the db and changed the
>>>> field name to start with lower case, then changed the model file to make
>>>> it
>>>> lower-case 'pending'. That worked, but now the next boolean field in the
>>>> db.py file has an upper-case/lower-case problem. The field is
>>>> "Length_is_yards" in both the db.py file and the db, and has been that way
>>>> for weeks, and we've been through several db migrations for the past
>>>> several weeks (not sure about on those particular tables, though). Now I
>>>> get the KeyError as shown above, but this time it's for field
>>>> 'length_is_yards'. It looks to me that web2py is assuming it's lower case.
>>>>
>>>> One of my migrations last week was the "fake_migrate_all=True" type;
>>>> don't know if that's relevant.
>>>>
>>>> Also, in the .database file the field name is Length_is_yards (leading
>>>> "L" is capital), as is the field name in the MySQL db.
>>>>
>>>> I'm confused.
>>>>
>>>> Michael
>>>>
>>>> On Monday, September 17, 2012 12:51:34 PM UTC-6, Massimo Di Pierro
>>>> wrote:
>>>>>
>>>>> Field('Pending' <<< upper case
>>>>> ...
>>>>> 'pending' <<< lower case
>>>>>
>>>>>
>>>>>
>>>>> On Monday, 17 September 2012 11:37:13 UTC-5, MichaelF wrote:
>>>>>>
>>>>>> I did a simple import of &
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
Yes; here it is:
1.
2.
3.
4.
5.
6.
7.
8.
9.
Traceback (most recent call last):
File "gluon/restricted.py", line 205, in restricted
File "C:/Program Files (x86)/web2py/applications/NCAA_schedule/models/db.py"
<http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>, line
165, in
File "gluon/dal.py", line 6320, in define_table
File "gluon/dal.py", line 742, in create_table
File "gluon/dal.py", line 797, in migrate_table
File "gluon/dal.py", line 6714, in __getitem__
KeyError: 'length_is_yards'
The table definition follows:
db.define_table('Pool',
Field('Pool_name', 'string', required=True, unique=True),
Field('Address1', 'string', length=60),
Field('Address2', 'string', length=60),
Field('City', 'string', length=60),
Field('State', 'string', length=2),
Field('Zip', 'string', length=15),
Field('Nr_lanes', 'integer', required=True),
Field('Length', 'integer', required=True),
Field('Length_is_yards', 'boolean',
required=True,default=True),
Field('Has_moveable_bulkhead', 'boolean', required=True,
default=False),
format='%(Pool_name)s %(Nr_lanes)s')
Line 165 is the last line of the statement (format=...).
On Monday, September 17, 2012 3:15:08 PM UTC-6, Massimo Di Pierro wrote:
>
> Do you have a traceback with more information?
>
> On Monday, 17 September 2012 14:23:56 UTC-5, MichaelF wrote:
>>
>> Thanks. However, I refer to that field with upper case in all places. Can
>> you tell me where the lower case 'pending' comes from? The field name has
>> always been defined as upper case, and the app has been working up until I
>> made that latest change. So I went into the db and changed the field name
>> to start with lower case, then changed the model file to make it lower-case
>> 'pending'. That worked, but now the next boolean field in the db.py file
>> has an upper-case/lower-case problem. The field is "Length_is_yards" in
>> both the db.py file and the db, and has been that way for weeks, and we've
>> been through several db migrations for the past several weeks (not sure
>> about on those particular tables, though). Now I get the KeyError as shown
>> above, but this time it's for field 'length_is_yards'. It looks to me that
>> web2py is assuming it's lower case.
>>
>> One of my migrations last week was the "fake_migrate_all=True" type;
>> don't know if that's relevant.
>>
>> Also, in the .database file the field name is Length_is_yards (leading
>> "L" is capital), as is the field name in the MySQL db.
>>
>> I'm confused.
>>
>> Michael
>>
>> On Monday, September 17, 2012 12:51:34 PM UTC-6, Massimo Di Pierro wrote:
>>>
>>> Field('Pending' <<< upper case
>>> ...
>>> 'pending' <<< lower case
>>>
>>>
>>>
>>> On Monday, 17 September 2012 11:37:13 UTC-5, MichaelF wrote:
>>>>
>>>> I did a simple import of 'copy' and that got me by that first problem.
>>>> But now I have the following problem:
>>>>
>>>> db.define_table('Person_certification',
>>>> Field('Person', db.Person),
>>>> ...
>>>> Field('Pending', 'boolean', default = False),
>>>> ...
>>>>
>>>> I get the following error on the line that defines field 'Pending' (and
>>>> this is the first 'boolean' type in the file):
>>>> 'pending'I have not changed the
>>>> underlying MySQL db yet; all the booleans are still char(1). Do I need to
>>>> change them first to Tinyint(1)? I tried that; same error.
>>>>
>>>> Thanks.
>>>>
>>>> On Monday, September 17, 2012 9:21:37 AM UTC-6, MichaelF wrote:
>>>>>
>>>>> 1. What will I need to import to get it to recognize 'copy'? I run the
>>>>> suggested code and get told that 'copy' does not exist. (I'm running 2.5;
>>>>> what do I conditionally import?)
>>>>>
>>>>> 2. Are we doing a copy because a
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
Thanks. However, I refer to that field with upper case in all places. Can
you tell me where the lower case 'pending' comes from? The field name has
always been defined as upper case, and the app has been working up until I
made that latest change. So I went into the db and changed the field name
to start with lower case, then changed the model file to make it lower-case
'pending'. That worked, but now the next boolean field in the db.py file
has an upper-case/lower-case problem. The field is "Length_is_yards" in
both the db.py file and the db, and has been that way for weeks, and we've
been through several db migrations for the past several weeks (not sure
about on those particular tables, though). Now I get the KeyError as shown
above, but this time it's for field 'length_is_yards'. It looks to me that
web2py is assuming it's lower case.
I'm confused.
Michael
On Monday, September 17, 2012 12:51:34 PM UTC-6, Massimo Di Pierro wrote:
>
> Field('Pending' <<< upper case
> ...
> 'pending' <<< lower case
>
>
>
> On Monday, 17 September 2012 11:37:13 UTC-5, MichaelF wrote:
>>
>> I did a simple import of 'copy' and that got me by that first problem.
>> But now I have the following problem:
>>
>> db.define_table('Person_certification',
>> Field('Person', db.Person),
>> ...
>> Field('Pending', 'boolean', default = False),
>> ...
>>
>> I get the following error on the line that defines field 'Pending' (and
>> this is the first 'boolean' type in the file):
>> 'pending'I have not changed the underlying
>> MySQL db yet; all the booleans are still char(1). Do I need to change them
>> first to Tinyint(1)? I tried that; same error.
>>
>> Thanks.
>>
>> On Monday, September 17, 2012 9:21:37 AM UTC-6, MichaelF wrote:
>>>
>>> 1. What will I need to import to get it to recognize 'copy'? I run the
>>> suggested code and get told that 'copy' does not exist. (I'm running 2.5;
>>> what do I conditionally import?)
>>>
>>> 2. Are we doing a copy because all the adapters share the same 'types'
>>> object?
>>>
>>> On Tuesday, August 7, 2012 3:48:35 PM UTC-6, Massimo Di Pierro wrote:
>>>>
>>>> On can always do:
>>>>
>>>> db=DAL('mssql://...')
>>>> db._adapter.types = copy.copy(db._adapter.types)
>>>> db._adapter.types['boolean']='TINYINT(1)'
>>>>
>>>> It should work. Can you please check it?
>>>>
>>>> On Tuesday, 7 August 2012 11:56:59 UTC-5, Osman Masood wrote:
>>>>>
>>>>> However, web2py maintains the promise of backwards compatibility. One
>>>>> way is to have a 'tinyint_boolean' datatype for those who want to use
>>>>> tinyints as booleans. But that looks kind of messy and inelegant.
>>>>>
>>>>> An alternative is this: We could add a migration script to /scripts to
>>>>> convert all boolean data types from CHAR(1) to TINYINT(1), and from 'T'
>>>>> to
>>>>> 1 and 'F' to 0. Also, when a table model is called in define_table(), it
>>>>> would check whether its boolean data types are CHAR or INT, and save the
>>>>> result somewhere (so it wouldn't have to keep checking.) If the server is
>>>>> restarted, it would once again perform this check. So, a user would run
>>>>> the
>>>>> migration script and simply restart the server.
>>>>>
>>>>> On Thursday, July 12, 2012 9:18:33 PM UTC+8, simon wrote:
>>>>>>
>>>>>> I have just come across this exact same issue.
>>>>>>
>>>>>> The web2py adapter converts boolean to char(1) but in MySQL the
>>>>>> specification is that boolean is stored as tinyint with 0 and 1. So
>>>>>> web2py
>>>>>> adapter is incorrect. Not changing it perpetuates the mistake.
>>>>>>
>>>>>>
>>>>>> On Sunday, 6 March 2011 05:14:49 UTC, Kevin Ivarsen wrote:
>>>>>>>
>>>>>>> I'm connecting to a legacy MySQL database (migrate=False) with a lot
>>>>>>> of fields declared BOOLEAN, and noticed that attempts to modify
>>>>>>> these
>>>>>>> fields with the DAL failed. The DAL issues a query like this:
>>>>>>>
>>>>>>> UPDATE sometable SET someflag='T' WHERE ...
>>>>>>>
>>>>>>> but this gets rejected by MySQL.
>>>>>>>
>>>>>>> Reading through dal.py, I see that the "boolean" type maps to
>>>>>>> CHAR(1)
>>>>>>> in MySQLAdapter, and represent() converts to "T" and "F" values.
>>>>>>> However, the BOOLEAN type is a synonym for TINYINT(1) in MySQL, with
>>>>>>> values 0 or 1, according to:
>>>>>>>
>>>>>>> http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
>>>>>>>
>>>>>>> I can trivially change this behavior in dal.py for my purposes, but
>>>>>>> it
>>>>>>> would be interested to try to incorporate this into the main web2py
>>>>>>> distribution. Unfortunately, the trivial change will break backwards
>>>>>>> compatibility for people who are already depending on the current
>>>>>>> behavior. Any thoughts on how this could be done in a backwards-
>>>>>>> compatible way, or is it too much of an edge case to worry about?
>>>>>>>
>>>>>>> Cheers,
>>>>>>> Kevin
>>>>>>
>>>>>>
--
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
I did a simple import of 'copy' and that got me by that first problem. But
now I have the following problem:
db.define_table('Person_certification',
Field('Person', db.Person),
...
Field('Pending', 'boolean', default = False),
...
I get the following error on the line that defines field 'Pending' (and
this is the first 'boolean' type in the file):
'pending'I have not changed the underlying
MySQL db yet; all the booleans are still char(1). Do I need to change them
first to Tinyint(1)?
Thanks.
On Monday, September 17, 2012 9:21:37 AM UTC-6, MichaelF wrote:
>
> 1. What will I need to import to get it to recognize 'copy'? I run the
> suggested code and get told that 'copy' does not exist. (I'm running 2.5;
> what do I conditionally import?)
>
> 2. Are we doing a copy because all the adapters share the same 'types'
> object?
>
> On Tuesday, August 7, 2012 3:48:35 PM UTC-6, Massimo Di Pierro wrote:
>>
>> On can always do:
>>
>> db=DAL('mssql://...')
>> db._adapter.types = copy.copy(db._adapter.types)
>> db._adapter.types['boolean']='TINYINT(1)'
>>
>> It should work. Can you please check it?
>>
>> On Tuesday, 7 August 2012 11:56:59 UTC-5, Osman Masood wrote:
>>>
>>> However, web2py maintains the promise of backwards compatibility. One
>>> way is to have a 'tinyint_boolean' datatype for those who want to use
>>> tinyints as booleans. But that looks kind of messy and inelegant.
>>>
>>> An alternative is this: We could add a migration script to /scripts to
>>> convert all boolean data types from CHAR(1) to TINYINT(1), and from 'T' to
>>> 1 and 'F' to 0. Also, when a table model is called in define_table(), it
>>> would check whether its boolean data types are CHAR or INT, and save the
>>> result somewhere (so it wouldn't have to keep checking.) If the server is
>>> restarted, it would once again perform this check. So, a user would run the
>>> migration script and simply restart the server.
>>>
>>> On Thursday, July 12, 2012 9:18:33 PM UTC+8, simon wrote:
>>>>
>>>> I have just come across this exact same issue.
>>>>
>>>> The web2py adapter converts boolean to char(1) but in MySQL the
>>>> specification is that boolean is stored as tinyint with 0 and 1. So web2py
>>>> adapter is incorrect. Not changing it perpetuates the mistake.
>>>>
>>>>
>>>> On Sunday, 6 March 2011 05:14:49 UTC, Kevin Ivarsen wrote:
>>>>>
>>>>> I'm connecting to a legacy MySQL database (migrate=False) with a lot
>>>>> of fields declared BOOLEAN, and noticed that attempts to modify these
>>>>> fields with the DAL failed. The DAL issues a query like this:
>>>>>
>>>>> UPDATE sometable SET someflag='T' WHERE ...
>>>>>
>>>>> but this gets rejected by MySQL.
>>>>>
>>>>> Reading through dal.py, I see that the "boolean" type maps to CHAR(1)
>>>>> in MySQLAdapter, and represent() converts to "T" and "F" values.
>>>>> However, the BOOLEAN type is a synonym for TINYINT(1) in MySQL, with
>>>>> values 0 or 1, according to:
>>>>>
>>>>> http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
>>>>>
>>>>> I can trivially change this behavior in dal.py for my purposes, but it
>>>>> would be interested to try to incorporate this into the main web2py
>>>>> distribution. Unfortunately, the trivial change will break backwards
>>>>> compatibility for people who are already depending on the current
>>>>> behavior. Any thoughts on how this could be done in a backwards-
>>>>> compatible way, or is it too much of an edge case to worry about?
>>>>>
>>>>> Cheers,
>>>>> Kevin
>>>>
>>>>
--
[web2py] Re: "boolean" type in MySQL: CHAR(1) vs TINYINT(1)
1. What will I need to import to get it to recognize 'copy'? I run the
suggested code and get told that 'copy' does not exist. (I'm running 2.5;
what do I conditionally import?)
2. Are we doing a copy because all the adapters share the same 'types'
object?
On Tuesday, August 7, 2012 3:48:35 PM UTC-6, Massimo Di Pierro wrote:
>
> On can always do:
>
> db=DAL('mssql://...')
> db._adapter.types = copy.copy(db._adapter.types)
> db._adapter.types['boolean']='TINYINT(1)'
>
> It should work. Can you please check it?
>
> On Tuesday, 7 August 2012 11:56:59 UTC-5, Osman Masood wrote:
>>
>> However, web2py maintains the promise of backwards compatibility. One way
>> is to have a 'tinyint_boolean' datatype for those who want to use tinyints
>> as booleans. But that looks kind of messy and inelegant.
>>
>> An alternative is this: We could add a migration script to /scripts to
>> convert all boolean data types from CHAR(1) to TINYINT(1), and from 'T' to
>> 1 and 'F' to 0. Also, when a table model is called in define_table(), it
>> would check whether its boolean data types are CHAR or INT, and save the
>> result somewhere (so it wouldn't have to keep checking.) If the server is
>> restarted, it would once again perform this check. So, a user would run the
>> migration script and simply restart the server.
>>
>> On Thursday, July 12, 2012 9:18:33 PM UTC+8, simon wrote:
>>>
>>> I have just come across this exact same issue.
>>>
>>> The web2py adapter converts boolean to char(1) but in MySQL the
>>> specification is that boolean is stored as tinyint with 0 and 1. So web2py
>>> adapter is incorrect. Not changing it perpetuates the mistake.
>>>
>>>
>>> On Sunday, 6 March 2011 05:14:49 UTC, Kevin Ivarsen wrote:
I'm connecting to a legacy MySQL database (migrate=False) with a lot
of fields declared BOOLEAN, and noticed that attempts to modify these
fields with the DAL failed. The DAL issues a query like this:
UPDATE sometable SET someflag='T' WHERE ...
but this gets rejected by MySQL.
Reading through dal.py, I see that the "boolean" type maps to CHAR(1)
in MySQLAdapter, and represent() converts to "T" and "F" values.
However, the BOOLEAN type is a synonym for TINYINT(1) in MySQL, with
values 0 or 1, according to:
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
I can trivially change this behavior in dal.py for my purposes, but it
would be interested to try to incorporate this into the main web2py
distribution. Unfortunately, the trivial change will break backwards
compatibility for people who are already depending on the current
behavior. Any thoughts on how this could be done in a backwards-
compatible way, or is it too much of an edge case to worry about?
Cheers,
Kevin
>>>
>>>
--
[web2py] Using SQL functions in SELECT?
Is there a way I can use 'arbitrary' SQL functions in my queries? For example, I might want to do something akin to: SELECT * FROM tbl WHERE Start_date < NOW(); I realize that in this particular case I can translate the "NOW()" SQL function to the current date/time. But how about other functions? If I use them am I limited to using executesql? Thanks. --
[web2py] Re: Problem accessing Rows/Row
Yes, it makes sense, and I've already modified my code; thanks. It's the inconsistency that gets me. It's not a big deal, but still. If I add another field from another table to my select, then I have to change all my field references to include the table references. I suspect the Rows/Row iterator/accessor is already doing something, and if so it could allow the unnecessary table references. Anyway, not a big deal; I'm glad my code is working. Thanks again. On Friday, September 14, 2012 2:11:57 AM UTC-6, lyn2py wrote: > > If you have data from one table only, leave out the table name. (which is > in your case) > If you have data from more than one table, you need to use the table name > to access the correct info. > > This has always been the way web2py functions. :) > > I think it is a matter of perspective. When you only access data from one > table, it doesn't make sense to need to indicate the table name (more code, > and unnecessary. Some of our table names can be rather complicated acronyms > too.). When you retrieve from more than one table, to specifically access a > particular field from a particular table, we need the table name. It is out > of necessity rather than following some kind of structured rule. > > Does that make sense? > > > On Friday, September 14, 2012 10:43:04 AM UTC+8, MichaelF wrote: >> >> The problem is that I need to change the problem line from: >> 246:if attch.Addl_info_item.Email_text: >> >> to: >> 246:if attch.Email_text: >> >> My previous 'select' statements retrieved fields from several tables, >> while this one (the 'problem' one) retrieves from only one table. So, if I >> understand correctly, if my fields come from only one table I leave off the >> table name when 'addressing' the row, else I do use the table name. That >> seems 'unorthogonal'. Am I misunderstanding? >> >> On Thursday, September 13, 2012 4:16:44 PM UTC-6, MichaelF wrote: >>> >>> I do a similar set of steps, yet one of the sets acts differently. I'm >>> trying to loop through a query return (Rows), but I'm obviously missing >>> something basic. >>> >>> I do a query and 'print' the return. Here's a snippet from my controller: >>> >>> line 144: >>> instAttch = db((db.Meet.id == request.args(1)) & >>>(db.Addl_institution_info.Start_date < >>> db.Meet.Start_date) & >>>(db.Addl_institution_info.End_date > db.Meet.Start_date) & >>>(db.Participant_team.Is_home_team)).select( >>> db.Addl_info_item.ALL, >>> join = [db.Participant_team.on( >>>db.Meet.id == db.Participant_team.Meet), >>> db.Team.on(db.Participant_team.Team == >>> db.Team.id), >>> db.Institution.on(db.Team.Institution == >>> db.Institution.id), >>> db.Addl_institution_info.on( >>> db.Addl_institution_info.Institution == >>> db.Institution.id), >>> db.Addl_info_item.on(db.Addl_info_item.id == >>>db.Addl_institution_info.Addl_info_item)]) >>> >>> ... >>> Later in the same function (displayed with line numbers): >>> 240: print 'Start loop at 240; instAttch:\n' >>> 241: print instAttch >>> 242: for attch in instAttch: >>> 243:print 'attch:\n' >>> 244:# >>> 245:print attch >>> 246:if attch.Addl_info_item.Email_text: >>> >>> The output from line 241 looks like a Rows object to my rookie eye: >>> >>> Start loop at 240; instAttch: >>> >>> >>> Addl_info_item.id,Addl_info_item.File_path,Addl_info_item.Email_text,Addl_info_i >>> tem.Content_id,Addl_info_item.Internal_description >>> 2,C:\Users\mjf\Documents\MJF\WebSite\NCAA\private\Test DU Parking >>> pass.dat,Test >>> email text: addl_info_item 2,,item 2 >>> >>> attch: >>> >>> >> object >>> at 0x05DAA4D0>, 'update_record': at 0x05F158B0>, >>> 'Addl_instit >>> ution_info': , 'File_path': >>> 'C:\\Users\\mjf\ >>> \Documents\\MJF\\WebSite\\NCAA\\private\\Test DU Parking pass.dat', >>> 'Email_text' >>> : 'Test email text: addl_info_item 2', 'Content_id': '', >>> 'Addl_meet_info': >> n.dal.Set object at 0x05DAA1F0>, 'id': 2, 'delete_record': >> at >>> 0x05F15F30>}> >>> >>> I do a 'for ...' on instAttch at line 242 and get: >>> 'Addl_info_item' >>> >>> The traceback tells me it's at line 246: >>>if attch.Addl_info_item.Email_text: >>> >>> What am I missing? >>> >> --
[web2py] Re: Problem accessing Rows/Row
The problem is that I need to change the problem line from: 246:if attch.Addl_info_item.Email_text: to: 246:if attch.Email_text: My previous 'select' statements retrieved fields from several tables, while this one (the 'problem' one) retrieves from only one table. So, if I understand correctly, if my fields come from only one table I leave off the table name when 'addressing' the row, else I do use the table name. That seems 'unorthogonal'. Am I misunderstanding? On Thursday, September 13, 2012 4:16:44 PM UTC-6, MichaelF wrote: > > I do a similar set of steps, yet one of the sets acts differently. I'm > trying to loop through a query return (Rows), but I'm obviously missing > something basic. > > I do a query and 'print' the return. Here's a snippet from my controller: > > line 144: > instAttch = db((db.Meet.id == request.args(1)) & >(db.Addl_institution_info.Start_date < db.Meet.Start_date) & >(db.Addl_institution_info.End_date > db.Meet.Start_date) & >(db.Participant_team.Is_home_team)).select( > db.Addl_info_item.ALL, > join = [db.Participant_team.on( >db.Meet.id == db.Participant_team.Meet), > db.Team.on(db.Participant_team.Team == db.Team.id > ), > db.Institution.on(db.Team.Institution == > db.Institution.id), > db.Addl_institution_info.on( > db.Addl_institution_info.Institution == > db.Institution.id), > db.Addl_info_item.on(db.Addl_info_item.id == >db.Addl_institution_info.Addl_info_item)]) > > ... > Later in the same function (displayed with line numbers): > 240: print 'Start loop at 240; instAttch:\n' > 241: print instAttch > 242: for attch in instAttch: > 243:print 'attch:\n' > 244:# > 245:print attch > 246:if attch.Addl_info_item.Email_text: > > The output from line 241 looks like a Rows object to my rookie eye: > > Start loop at 240; instAttch: > > > Addl_info_item.id,Addl_info_item.File_path,Addl_info_item.Email_text,Addl_info_i > tem.Content_id,Addl_info_item.Internal_description > 2,C:\Users\mjf\Documents\MJF\WebSite\NCAA\private\Test DU Parking > pass.dat,Test > email text: addl_info_item 2,,item 2 > > attch: > > object > at 0x05DAA4D0>, 'update_record': at 0x05F158B0>, > 'Addl_instit > ution_info': , 'File_path': > 'C:\\Users\\mjf\ > \Documents\\MJF\\WebSite\\NCAA\\private\\Test DU Parking pass.dat', > 'Email_text' > : 'Test email text: addl_info_item 2', 'Content_id': '', 'Addl_meet_info': > n.dal.Set object at 0x05DAA1F0>, 'id': 2, 'delete_record': at > 0x05F15F30>}> > > I do a 'for ...' on instAttch at line 242 and get: > 'Addl_info_item' > > The traceback tells me it's at line 246: >if attch.Addl_info_item.Email_text: > > What am I missing? > --
[web2py] Problem accessing Rows/Row
I do a similar set of steps, yet one of the sets acts differently. I'm trying to loop through a query return (Rows), but I'm obviously missing something basic. I do a query and 'print' the return. Here's a snippet from my controller: line 144: instAttch = db((db.Meet.id == request.args(1)) & (db.Addl_institution_info.Start_date < db.Meet.Start_date) & (db.Addl_institution_info.End_date > db.Meet.Start_date) & (db.Participant_team.Is_home_team)).select( db.Addl_info_item.ALL, join = [db.Participant_team.on( db.Meet.id == db.Participant_team.Meet), db.Team.on(db.Participant_team.Team == db.Team.id), db.Institution.on(db.Team.Institution == db.Institution.id), db.Addl_institution_info.on( db.Addl_institution_info.Institution == db.Institution.id), db.Addl_info_item.on(db.Addl_info_item.id == db.Addl_institution_info.Addl_info_item)]) ... Later in the same function (displayed with line numbers): 240: print 'Start loop at 240; instAttch:\n' 241: print instAttch 242: for attch in instAttch: 243:print 'attch:\n' 244:# 245:print attch 246:if attch.Addl_info_item.Email_text: The output from line 241 looks like a Rows object to my rookie eye: Start loop at 240; instAttch: Addl_info_item.id,Addl_info_item.File_path,Addl_info_item.Email_text,Addl_info_i tem.Content_id,Addl_info_item.Internal_description 2,C:\Users\mjf\Documents\MJF\WebSite\NCAA\private\Test DU Parking pass.dat,Test email text: addl_info_item 2,,item 2 attch: , 'update_record': at 0x05F158B0>, 'Addl_instit ution_info': , 'File_path': 'C:\\Users\\mjf\ \Documents\\MJF\\WebSite\\NCAA\\private\\Test DU Parking pass.dat', 'Email_text' : 'Test email text: addl_info_item 2', 'Content_id': '', 'Addl_meet_info': , 'id': 2, 'delete_record': at 0x05F15F30>}> I do a 'for ...' on instAttch at line 242 and get: 'Addl_info_item' The traceback tells me it's at line 246: if attch.Addl_info_item.Email_text: What am I missing? --
Re: [web2py] fake_migrate options works, but then problem when taken out
Nowhere else is [Ii]s_home_team referenced.
I had to move on, so I deleted the table and recreated it.
On Thursday, September 13, 2012 2:01:51 PM UTC-6, Marin Pranjić wrote:
>
> I don't think KeyError is related to migrations. I am not sure.
> Can you give us error traceback?
>
> On Thu, Sep 13, 2012 at 7:20 PM, MichaelF >wrote:
>
>> I don't know where the lower case is_home_team is coming from. In the
>> entire app dir it appears only in the error pages.
>>
>> The 'migrate = true, fake_migrate = true' yields the same error.
>>
>>
>> On Thursday, September 13, 2012 11:14:32 AM UTC-6, Marin Pranjić wrote:
>>
>>> What about the case
>>> Is_home_team / is_home_team?
>>>
>>> On Sep 13, 2012 6:37 PM, "MichaelF" wrote:
>>>
>>> I have a field ('Is_home_team') that was defined as 'boolean', and I
>>> changed it to 'integer'. The migrate failed (I'm using MySQL).
>>>
>>> I then invoked the following on the table def that defines Is_home_team:
>>> migrate = False, fake_migrate = True
>>>
>>> I also altered the underlying MySQL table to change the field from
>>> char(1) to integer, and modified the data.
>>>
>>> I then ran the app again, and everything worked. So I took out the
>>> 'migrate = False, fake_migrate = True', re-ran the app, and got the error
>>> again:
>>> 'is_home_team'
>>> I thought that running the fake_migrate would rebuild according to the
>>> definition, etc.
>>>
>>> No other tables refer to that field.
>>>
>>> Before I do a fake_migrate_all, any thoughts?
>>>
>>> --
>>>
>>>
>>>
>>>
>>> --
>>
>>
>>
>>
>
>
--
Re: [web2py] fake_migrate options works, but then problem when taken out
I don't know where the lower case is_home_team is coming from. In the
entire app dir it appears only in the error pages.
The 'migrate = true, fake_migrate = true' yields the same error.
On Thursday, September 13, 2012 11:14:32 AM UTC-6, Marin Pranjić wrote:
>
> What about the case
> Is_home_team / is_home_team?
>
> On Sep 13, 2012 6:37 PM, "MichaelF" >
> wrote:
>
> I have a field ('Is_home_team') that was defined as 'boolean', and I
> changed it to 'integer'. The migrate failed (I'm using MySQL).
>
> I then invoked the following on the table def that defines Is_home_team:
> migrate = False, fake_migrate = True
>
> I also altered the underlying MySQL table to change the field from char(1)
> to integer, and modified the data.
>
> I then ran the app again, and everything worked. So I took out the
> 'migrate = False, fake_migrate = True', re-ran the app, and got the error
> again:
> 'is_home_team'
> I thought that running the fake_migrate would rebuild according to the
> definition, etc.
>
> No other tables refer to that field.
>
> Before I do a fake_migrate_all, any thoughts?
>
> --
>
>
>
>
>
--
[web2py] fake_migrate options works, but then problem when taken out
I have a field ('Is_home_team') that was defined as 'boolean', and I
changed it to 'integer'. The migrate failed (I'm using MySQL).
I then invoked the following on the table def that defines Is_home_team:
migrate = False, fake_migrate = True
I also altered the underlying MySQL table to change the field from char(1)
to integer, and modified the data.
I then ran the app again, and everything worked. So I took out the 'migrate
= False, fake_migrate = True', re-ran the app, and got the error again:
'is_home_team'
I thought that running the fake_migrate would rebuild according to the
definition, etc.
No other tables refer to that field.
Before I do a fake_migrate_all, any thoughts?
--
Re: [web2py] Many-many options
Thanks. Yes, option 1 is the 'approved' solution, I think. It doesn't get
tricky.
And thanks for the link. I like anti-pattern books...great sanity checks.
Unfortunately, I find a lot of my code in them! :)
On Thursday, September 13, 2012 9:45:38 AM UTC-6, Richard wrote:
>
> In this case the best approach will be to have a normalized schema that
> allow to use all the constraint required at the DB level... I think yo said
> the first proposal you made allow that...
>
> I like this book when it comes to tricky design, it gives ideas on how to
> solve the issues :
> http://pragprog.com/book/bksqla/sql-antipatterns
>
> Richard
>
> On Thu, Sep 13, 2012 at 11:40 AM, MichaelF
> > wrote:
>
>> The P3 record will have text and/or file information that relates to
>> several P1 records, or several P2 records, and sometimes both several P1
>> and several P2 records. The text info will be used to add to a document (a
>> totally separate entity outside the db), and the file will be attached to
>> the document. Also, the linking record will have start/end date fields that
>> specify the valid dates for the relationship.
>>
>> So, a single P3 record (let's call it P3.1) might be associated with P1.4
>> from 1/1/12 through 12/31/12; and also be associated with P1.6 from 6/1/12
>> through 6/2/12; and also be associated with P2.6 record from 11/1/12
>> through 11/11/12. That would be three separate linking records (regardless
>> of which option we used): P1.4 => P3.1; P1.6 => P3.1, and P2.6 => P3.1.
>>
>>
>> On Thursday, September 13, 2012 9:11:14 AM UTC-6, Richard wrote:
>>
>>> Maybe with more details about the nature of the information to store, it
>>> could be easier to give an answer...
>>>
>>> You can also use the junction table to store weak entity attribute, that
>>> could avoid the P3 table.
>>>
>>> Richard
>>>
>>> On Thu, Sep 13, 2012 at 11:01 AM, MichaelF wrote:
>>>
>>>> This might be more of a SQL design question, but if web2py handles one
>>>> better than another, that would be good to know.
>>>>
>>>> Suppose I have three 'parent' records ((P1, P2, and P3), and I want to
>>>> link P1 records with P3 records, and also P2 records with P3 records.
>>>> Several options:
>>>>
>>>> Option 1: obvious: one linking table per relationship
>>>> define_table('P1_P3_linker',
>>>>Field('P1', db.P1), Field('P3', db.P3))
>>>> define_table('P2_P3_linker',
>>>>Field('P2', db.P2), Field('P3', db.P3))
>>>>
>>>> Option 2: one linking table for all relationships; each record still
>>>> links one record (P1 or P2) with one P3 record
>>>> define_table('P1_P2_P3',
>>>>Field('P1', db.P1),
>>>>Field('P2', db.P2),
>>>>Field('P3', db.P3))
>>>>
>>>> Option 3: overload linking field and use a 'type'; each record still
>>>> links one record (P1 or P2) with one P3 record
>>>> define_table('P1_P2_P3',
>>>>Field('Table_type', 'string', IS_IN_SET(['P1', 'P2'])),
>>>>Field('Table_key', 'integer'), # will be P1.id or P2.id
>>>>Field('P3', db.P3))
>>>>
>>>> Using Option 2 I would relate a P1 to a P3 by populating the
>>>> P1_P2_P3.P1 and P1_P2_P3.P3 fields, setting P1_P2_P3.P2 to NULL. I would
>>>> relate a P2 to a P3 by populating the P1_P2_P3.P2 and P1_P2_P3.P3 fields,
>>>> setting P1_P2_P3.P1 to NULL. This assumes the underlying db allows null
>>>> fields for foreign keys.
>>>>
>>>> Using Option 3 I would relate a P1 to a P3 by setting Table_type to
>>>> 'P1', then setting P1_P2_P3.Table_key to P1.id. No constraints can be set
>>>> on this table using this option. This also assumes that the key field of
>>>> the underlying db is integer.
>>>>
>>>> If it were just two tables (P1 and P2) relating to P3 then Option 1
>>>> makes sense. I actually have P1 through P5 relating to P6. I suspect,
>>>> though, that Option 1 is still the best, and that the others are "penny
>>>> wise, pound foolish" in trying to avoid defining the additional linking
>>>> tables.
>>>>
>>>> Thoughts? Thanks.
>>>>
>>>> --
>>>>
>>>>
>>>>
>>>>
>>>
>>> --
>>
>>
>>
>>
>
>
--
Re: [web2py] Many-many options
The P3 record will have text and/or file information that relates to
several P1 records, or several P2 records, and sometimes both several P1
and several P2 records. The text info will be used to add to a document (a
totally separate entity outside the db), and the file will be attached to
the document. Also, the linking record will have start/end date fields that
specify the valid dates for the relationship.
So, a single P3 record (let's call it P3.1) might be associated with P1.4
from 1/1/12 through 12/31/12; and also be associated with P1.6 from 6/1/12
through 6/2/12; and also be associated with P2.6 record from 11/1/12
through 11/11/12. That would be three separate linking records (regardless
of which option we used): P1.4 => P3.1; P1.6 => P3.1, and P2.6 => P3.1.
On Thursday, September 13, 2012 9:11:14 AM UTC-6, Richard wrote:
>
> Maybe with more details about the nature of the information to store, it
> could be easier to give an answer...
>
> You can also use the junction table to store weak entity attribute, that
> could avoid the P3 table.
>
> Richard
>
> On Thu, Sep 13, 2012 at 11:01 AM, MichaelF
> > wrote:
>
>> This might be more of a SQL design question, but if web2py handles one
>> better than another, that would be good to know.
>>
>> Suppose I have three 'parent' records ((P1, P2, and P3), and I want to
>> link P1 records with P3 records, and also P2 records with P3 records.
>> Several options:
>>
>> Option 1: obvious: one linking table per relationship
>> define_table('P1_P3_linker',
>>Field('P1', db.P1), Field('P3', db.P3))
>> define_table('P2_P3_linker',
>>Field('P2', db.P2), Field('P3', db.P3))
>>
>> Option 2: one linking table for all relationships; each record still
>> links one record (P1 or P2) with one P3 record
>> define_table('P1_P2_P3',
>>Field('P1', db.P1),
>>Field('P2', db.P2),
>>Field('P3', db.P3))
>>
>> Option 3: overload linking field and use a 'type'; each record still
>> links one record (P1 or P2) with one P3 record
>> define_table('P1_P2_P3',
>>Field('Table_type', 'string', IS_IN_SET(['P1', 'P2'])),
>>Field('Table_key', 'integer'), # will be P1.id or P2.id
>>Field('P3', db.P3))
>>
>> Using Option 2 I would relate a P1 to a P3 by populating the P1_P2_P3.P1
>> and P1_P2_P3.P3 fields, setting P1_P2_P3.P2 to NULL. I would relate a P2 to
>> a P3 by populating the P1_P2_P3.P2 and P1_P2_P3.P3 fields,
>> setting P1_P2_P3.P1 to NULL. This assumes the underlying db allows null
>> fields for foreign keys.
>>
>> Using Option 3 I would relate a P1 to a P3 by setting Table_type to 'P1',
>> then setting P1_P2_P3.Table_key to P1.id. No constraints can be set on this
>> table using this option. This also assumes that the key field of the
>> underlying db is integer.
>>
>> If it were just two tables (P1 and P2) relating to P3 then Option 1 makes
>> sense. I actually have P1 through P5 relating to P6. I suspect, though,
>> that Option 1 is still the best, and that the others are "penny wise, pound
>> foolish" in trying to avoid defining the additional linking tables.
>>
>> Thoughts? Thanks.
>>
>> --
>>
>>
>>
>>
>
>
--
[web2py] Many-many options
This might be more of a SQL design question, but if web2py handles one
better than another, that would be good to know.
Suppose I have three 'parent' records ((P1, P2, and P3), and I want to link
P1 records with P3 records, and also P2 records with P3 records. Several
options:
Option 1: obvious: one linking table per relationship
define_table('P1_P3_linker',
Field('P1', db.P1), Field('P3', db.P3))
define_table('P2_P3_linker',
Field('P2', db.P2), Field('P3', db.P3))
Option 2: one linking table for all relationships; each record still links
one record (P1 or P2) with one P3 record
define_table('P1_P2_P3',
Field('P1', db.P1),
Field('P2', db.P2),
Field('P3', db.P3))
Option 3: overload linking field and use a 'type'; each record still links
one record (P1 or P2) with one P3 record
define_table('P1_P2_P3',
Field('Table_type', 'string', IS_IN_SET(['P1', 'P2'])),
Field('Table_key', 'integer'), # will be P1.id or P2.id
Field('P3', db.P3))
Using Option 2 I would relate a P1 to a P3 by populating the P1_P2_P3.P1
and P1_P2_P3.P3 fields, setting P1_P2_P3.P2 to NULL. I would relate a P2 to
a P3 by populating the P1_P2_P3.P2 and P1_P2_P3.P3 fields,
setting P1_P2_P3.P1 to NULL. This assumes the underlying db allows null
fields for foreign keys.
Using Option 3 I would relate a P1 to a P3 by setting Table_type to 'P1',
then setting P1_P2_P3.Table_key to P1.id. No constraints can be set on this
table using this option. This also assumes that the key field of the
underlying db is integer.
If it were just two tables (P1 and P2) relating to P3 then Option 1 makes
sense. I actually have P1 through P5 relating to P6. I suspect, though,
that Option 1 is still the best, and that the others are "penny wise, pound
foolish" in trying to avoid defining the additional linking tables.
Thoughts? Thanks.
--
[web2py] Re: When/how is 'format' used?
Thanks. I didn't realize that SQLFORM was used by default to display a Row object; that makes sense. And the presence/invocation of those 'format' expressions is what's causing the various 'additional' SELECT statements I see in the "db stats" display, yes. By 'additional' I mean they're SELECT statements I didn't explicitly code. They're repetitive, but that makes sense if they're applying the 'format'. On using represent or _format: thanks. I saw the 'represent' attribute and assumed (not good!) that that was 'automatically' called, and the results of that were stored in the Row object. I see now that, as you say, if it's not the entire Row then I can explicitly call the function, passing in the field value. Thanks. On Tuesday, September 11, 2012 11:27:32 PM UTC-6, Anthony wrote: > > Three questions: >> >>1. What's the 'magic' going on that tells the display code for >>meetTeams to use the 'format' expression (but not to use it when >> displaying >>'message')? Does it realize it's a row, and the row knows how to display >>using the format? >> >> When you create a reference field, if you don't give it any validator, it > will automatically get an IS_IN_DB validator, and its "represent" attribute > will automatically be set to the "format" attribute of the referenced > table. The "represent" attribute is only used by SQLTABLE (i.e., when you > include an entire Rows object in a view), SQLFORM, and SQLFORM.grid. If you > simply display a single value from a Row object, the "represent" attribute > is not used. > >> >>1. Are the results of that 'format' expression stored in the row, >>available to me to use? As you can see I can explicitly join with the >>Institution to get the name. But if there's an easier way, then I'll do >> it. >> >> You can access the format expression either via db.Institution._format or > db.Team.Institution.represent (the latter is actually a function that will > reference the former). > > Anthony > --
[web2py] When/how is 'format' used?
I have the following parent-child:
db.define_table('Institution',
Field('Institution_name', 'string', length=60,
required=True,
unique=True),
format='%(Institution_name)s')
db.define_table('Team',
Field('Institution', db.Institution),
...
I retrieve some records:
meetTeams = db(db.Team.id == request.args(1)).select(
db.Team.Institution, db.Team.Sex,
db.Institution.Institution_name,
join = [db.Team.on(db.Team.id ==
db.Participant_team.Team),
db.Institution.on(db.Team.Institution ==
db.Institution.id)])
I pull out some data:
for team in meetTeams:
message += "%s %d %s\n" % (team.Team.Sex,
team.Team.Institution,
team.Institution.Institution_name)
I return locals(), and I have no explicit view. When meetTeams is
displayed, the field team.Team.Institution displays with the
expected/defined 'format'. When 'message' is displayed,
team.Team.Institution shows up as an int.
Three questions:
1. What's the 'magic' going on that tells the display code for meetTeams
to use the 'format' expression (but not to use it when displaying
'message')? Does it realize it's a row, and the row knows how to display
using the format?
2. Are the results of that 'format' expression stored in the row,
available to me to use? As you can see I can explicitly join with the
Institution to get the name. But if there's an easier way, then I'll do it.
3. If I don't reference Team.Institution in the SELECT field list, will
that avoid the various other SELECTs that get done to 'translate' an
Institution.id to the corresponding name (according to 'format') when
meetTeams is displayed?
Thanks.
--
[web2py] Re: Is 'second option' for one-many available for many-many?
Yikes! I *think* I have the same (or functionally the same), but maybe not.
Might it be that I'm using an out-of-date version? Here's what I'm using:
web2py™(1, 99, 7, datetime.datetime(2012, 3, 4, 22, 12, 8),
'stable')PythonPython
2.5.4: C:\Program Files (x86)\web2py\web2py_no_console.exe
I assume the db itself (MySQL) isn't even in play with this problem.
???
On Tuesday, September 11, 2012 9:39:56 AM UTC-6, Massimo Di Pierro wrote:
>
> I cannot reproduce the problem. This works for me:
>
> db=DAL()
> db.define_table('Meet',Field('name'))
> db.define_table('Team',Field('name'))
> db.define_table('Participant_team',
> Field('Meet',db.Meet),
> Field('Team',db.Team))
>
> a=db.Meet.insert(name='here')
> b=db.Team.insert(name='snakes')
> db.Participant_team.insert(Meet=a,Team=b)
> teamStaff = db(db.Meet.id == a).select(
> db.Meet.ALL, db.Team.ALL,
> join = db.Team.on(
> (db.Participant_team.Meet == db.Meet.id) &
> (db.Participant_team.Team == db.Team.id)))
>
> print teamStaff
>
>
> On Tuesday, 11 September 2012 10:04:25 UTC-5, MichaelF wrote:
>>
>> Section 6.21.1 in the manual talks about an alternative syntax for 1-many
>> joins, using the 'on' function. Is that technique available for many-many?
>> I try this and get an error:
>>
>> # In this example, Meet and Team are being connected through
>> Participant_team
>> teamStaff = db(db.Meet.id == request.args(1)).select(
>>db.Meet.ALL, db.Team.ALL,
>>join = db.Team.on(
>> (db.Participant_team.Meet == db.Meet.id) &
>> (db.Participant_team.Team == db.Team.id)))
>>
>> The error is: "Unknown column 'Meet.id' in 'on clause'"
>>
>> I can change "db.Team.on" to "db.Participant_team.on", but I get the same
>> error.
>>
>> I have a lot of other JOINs that I must do, but this is the smallest
>> example that shows the problem.
>>
>
--
[web2py] Re: Is 'second option' for one-many available for many-many?
Thanks; I think I have it. The SQL is: SELECT Meet.*, Team.* FROM Meet INNER JOIN Participant_team ON Meet.id = Participant_team.Meet INNER JOIN Team ON Participant_team.Team = Team.id; My web2py below implements that. On Tuesday, September 11, 2012 9:25:01 AM UTC-6, MichaelF wrote: > > I should have added that I can get the same effect with this: > > # In this example, Meet and Team are being connected through > Participant_team > teamStaff = db(db.Meet.id <http://db.meet.id/> == request.args(1)).select( >db.Meet.ALL, db.Team.ALL, >join = [db.Participant_team.on( > db.Participant_team.Meet == > db.Meet.id<http://db.meet.id/> > ), >db.Team.on(db.Participant_team.Team == > db.Team.id<http://db.team.id/> > )]) > > > On Tuesday, September 11, 2012 9:04:25 AM UTC-6, MichaelF wrote: >> >> Section 6.21.1 in the manual talks about an alternative syntax for 1-many >> joins, using the 'on' function. Is that technique available for many-many? >> I try this and get an error: >> >> # In this example, Meet and Team are being connected through >> Participant_team >> teamStaff = db(db.Meet.id == request.args(1)).select( >>db.Meet.ALL, db.Team.ALL, >>join = db.Team.on( >> (db.Participant_team.Meet == db.Meet.id) & >> (db.Participant_team.Team == db.Team.id))) >> >> The error is: "Unknown column 'Meet.id' in 'on clause'" >> >> I can change "db.Team.on" to "db.Participant_team.on", but I get the same >> error. >> >> I have a lot of other JOINs that I must do, but this is the smallest >> example that shows the problem. >> > --
[web2py] Re: Is 'second option' for one-many available for many-many?
I should have added that I can get the same effect with this: # In this example, Meet and Team are being connected through Participant_team teamStaff = db(db.Meet.id <http://db.meet.id/> == request.args(1)).select( db.Meet.ALL, db.Team.ALL, join = [db.Participant_team.on( db.Participant_team.Meet == db.Meet.id<http://db.meet.id/> ), db.Team.on(db.Participant_team.Team == db.Team.id<http://db.team.id/> )]) On Tuesday, September 11, 2012 9:04:25 AM UTC-6, MichaelF wrote: > > Section 6.21.1 in the manual talks about an alternative syntax for 1-many > joins, using the 'on' function. Is that technique available for many-many? > I try this and get an error: > > # In this example, Meet and Team are being connected through > Participant_team > teamStaff = db(db.Meet.id == request.args(1)).select( >db.Meet.ALL, db.Team.ALL, >join = db.Team.on( > (db.Participant_team.Meet == db.Meet.id) & > (db.Participant_team.Team == db.Team.id))) > > The error is: "Unknown column 'Meet.id' in 'on clause'" > > I can change "db.Team.on" to "db.Participant_team.on", but I get the same > error. > > I have a lot of other JOINs that I must do, but this is the smallest > example that shows the problem. > --
[web2py] Is 'second option' for one-many available for many-many?
Section 6.21.1 in the manual talks about an alternative syntax for 1-many joins, using the 'on' function. Is that technique available for many-many? I try this and get an error: # In this example, Meet and Team are being connected through Participant_team teamStaff = db(db.Meet.id == request.args(1)).select( db.Meet.ALL, db.Team.ALL, join = db.Team.on( (db.Participant_team.Meet == db.Meet.id) & (db.Participant_team.Team == db.Team.id))) The error is: "Unknown column 'Meet.id' in 'on clause'" I can change "db.Team.on" to "db.Participant_team.on", but I get the same error. I have a lot of other JOINs that I must do, but this is the smallest example that shows the problem. --
[web2py] Re: Trying to reproduce Ex. in manual, sec. 6.21.1 (inner joins)
Great; I appreciate it.
Do you still want me to open a ticket with a suggestion for an enhancement?
Regards,
Michael
On Monday, September 10, 2012 5:29:12 PM UTC-6, Massimo Di Pierro wrote:
>
> Anyway, I changed the code in trunk so that the example in the book works
> as described.
>
> On Monday, 10 September 2012 17:27:01 UTC-5, Massimo Di Pierro wrote:
>>
>> There is a mistake in the book. It should have been:
>>
>> rows = db(db.person).select(db.person.ALL, db.dog.ALL, join=db.dog.on(
>> db.person.id==db.dog.owner))
>>
>> or
>>
>> rows = db(db.person).select(db.person.ALL, db.dog.ALL, left=db.dog.on(
>> db.person.id==db.dog.owner))
>>
>> Perhaps is should default to select all fields as he book suggests. I
>> will look into this. Please open a ticket with a suggestion for enhancement.
>>
>> On Monday, September 10, 2012 4:58:25 PM UTC-5, MichaelF wrote:
>>>
>>> I have a db structure similar to the person/dog tables in section "6.21
>>> One to many relation." I try the inner join (second form, from the book):
>>>
>>> 1 >>> rows = db(db.person).select(join=db.dog.on(db.person.id
>>> ==db.dog.owner))
>>> 2 >>> for row in rows:
>>> 3 print row.person.name, 'has', row.dog.name
>>> 4 Alex has Skipper
>>> 5 Alex has Snoopy
>>> 6 Bob has Puppy
>>>
>>> In my db the 'person' table is 'Meet', and 'dog' is 'Session'. Here's
>>> what I used:
>>>
>>> meetAndSession = db(db.Meet).select(join=db.Session.on(db.Meet.id ==
>>> db.Session.Meet))
>>>
>>> I get back the 'person' ('Meet') fields, but not the 'dog' ('Session')
>>> fields. For the Meet.Session field I get back a Set object. Should I be
>>> using that as the set of Session records associated with the Meet record.
>>> (I tried to reference row.Meet.Session.id, but got told there was no
>>> such field. I also tried row.Session.id and got told the same thing.)
>>> As the example shows row.dog.name, shouldn't I have a
>>> row.Session.?
>>>
>>> Here's what "db stats" tells me it used:
>>>
>>> SELECT Meet.id, Meet.Meet_name, Meet.Start_date, Meet.End_date,
>>> Meet.Is_championship FROM Meet JOIN Session ON (Meet.id = Session.Meet)
>>> WHERE (Meet.id > 0);
>>>
>>>
>>> Given that, of course I'm getting no 'dog' ('Session') fields. What am I
>>> missing?
>>>
>>>
>>> Thanks.
>>>
>>>
--
[web2py] Trying to reproduce Ex. in manual, sec. 6.21.1 (inner joins)
I have a db structure similar to the person/dog tables in section "6.21 One
to many relation." I try the inner join (second form, from the book):
1 >>> rows =
db(db.person).select(join=db.dog.on(db.person.id==db.dog.owner))
2 >>> for row in rows:
3 print row.person.name, 'has', row.dog.name
4 Alex has Skipper
5 Alex has Snoopy
6 Bob has Puppy
In my db the 'person' table is 'Meet', and 'dog' is 'Session'. Here's what
I used:
meetAndSession = db(db.Meet).select(join=db.Session.on(db.Meet.id ==
db.Session.Meet))
I get back the 'person' ('Meet') fields, but not the 'dog' ('Session')
fields. For the Meet.Session field I get back a Set object. Should I be
using that as the set of Session records associated with the Meet record.
(I tried to reference row.Meet.Session.id, but got told there was no such
field. I also tried row.Session.id and got told the same thing.) As the
example shows row.dog.name, shouldn't I have a row.Session.?
Here's what "db stats" tells me it used:
SELECT Meet.id, Meet.Meet_name, Meet.Start_date, Meet.End_date,
Meet.Is_championship FROM Meet JOIN Session ON (Meet.id = Session.Meet) WHERE
(Meet.id > 0);
Given that, of course I'm getting no 'dog' ('Session') fields. What am I
missing?
Thanks.
--
Re: [web2py] sqlform.grid and query conditions
Ahhh; thanks for pointing that out. I had breezed over the mention about
digitally signed (my fault). Makes sense. I'll have to think about the
public db keys. Using them through web2py seems to be handled, though.
Thanks again.
Michael
On Friday, September 7, 2012 1:39:47 PM UTC-6, Massimo Di Pierro wrote:
>
> In some sense the grid does what you say.
>
> For example:
>
> @auth.requires_login()
> def index():
> db.define_table('thing',Field('name'),auth.signature)
> grid = SQLFORM.grid(db.thing.created_by==auth.user_id)
> return locals()
>
> Notice all the URLs linked by the grid are digitally signed. They are one
> time URLs. They can only be used by the user within this session and they
> cannot be tampered with. For example replacing the id of a record with
> another record in the edit page will not give access to the other record
> because would break the signature. This was broken in 1.99.7 for the grid
> (and in fact it was experimental) but it is fixed in 2.0.x.
>
> Users can digitally sign any URL:
>
> def index():
> ...
> link = URL('edit',args=id,user_signature=True)
> return dict(link=link)
>
> @auth.requires_signature()
> def edit():
> ...
>
> Now the http://.../edit/?signature= is still the id of the
> record but without the signature the URL is not valid.
>
>
>
>
>
> On Friday, 7 September 2012 13:27:49 UTC-5, MichaelF wrote:
>>
>> Thanks, Massimo.
>>
>> Re. needing a way to reference individual records: of course. But it
>> doesn't have to be the internal record id (primary key value). The php code
>> we used gave out unique-per-request values so that one couldn't, say, use a
>> key retrieved from one form in another form.
>>
>> The @auth infrastructure is great. It's not a record-level design (or is
>> it?). I just hate to think that internal db keys are public info. Okay,
>> perhaps I'm going over the edge being worried about exposing database
>> primary keys. But I find that when I decide I'm going over the edge, that
>> means that some cracker will find a way to use that information against my
>> site.
>>
>> I don't think web2py is much different from other infrastructures on this
>> issue. I wanted to know what others thought; thanks for your reply.
>>
>> Michael
>>
>> On Friday, September 7, 2012 10:28:08 AM UTC-6, Massimo Di Pierro wrote:
>>>
>>> I strongly disagree with this.
>>>
>>> Publishing record IDs does not imply indirect object reference
>>> vulnerability. Any application that publishes record information must
>>> have a way to reference individual records. If the individual access is
>>> not validated than the app is vulnerable to indirect object reference,
>>> whether or not the reference is done by id or not.
>>>
>>> Who can access what is a matter of policy and policy must be implemented
>>> by the developer. Web2py provides the @auth.requires_permission and
>>> @auth.requires_membership and $auth.requires_signature.
>>>
>>> Massimo
>>>
>>> On Friday, 7 September 2012 09:22:12 UTC-5, MichaelF wrote:
>>>>
>>>> I appreciate that web2py has ways to handle this, and I also agree that
>>>> it's somewhat hackish. The problem remains, though, that we're still
>>>> exposing (publishing) internal primary keys to the browser. Isn't the main
>>>> problem the fact that we're dealing with primary key values being sent to
>>>> the browser? Look at https://www.owasp.org/index.php/Top_10_2010-A4 for
>>>> one description of the vulnerability.
>>>>
>>>> In our php application we wrote a class that hashed primary keys sent
>>>> to the browser, giving different hashes on each GET/POST so that, for
>>>> example, the hashed primary key 1 would different if the user visited the
>>>> same page two times.
>>>>
>>>> Thoughts?
>>>>
>>>> Thanks.
>>>>
>>>> On Thursday, September 6, 2012 8:18:44 AM UTC-6, Anthony wrote:
>>>>>
>>>>> How about http://web2py.com/books/default/chapter/29/06#Common-filters
>>>>> or
>>>>> http://web2py.com/books/default/chapter/29/06#Common-fields-and-multi-tenancy
>>>>> ?
>>>>>
>>>>> Anthony
>>>>>
>>>>> On Wednesday, September 5, 2012 8:48:49 PM UTC-4, Kevin C wrote:
>>>>>>
>>>
Re: [web2py] sqlform.grid and query conditions
Thanks, Massimo.
Re. needing a way to reference individual records: of course. But it
doesn't have to be the internal record id (primary key value). The php code
we used gave out unique-per-request values so that one couldn't, say, use a
key retrieved from one form in another form.
The @auth infrastructure is great. It's not a record-level design (or is
it?). I just hate to think that internal db keys are public info. Okay,
perhaps I'm going over the edge being worried about exposing database
primary keys. But I find that when I decide I'm going over the edge, that
means that some cracker will find a way to use that information against my
site.
I don't think web2py is much different from other infrastructures on this
issue. I wanted to know what others thought; thanks for your reply.
Michael
On Friday, September 7, 2012 10:28:08 AM UTC-6, Massimo Di Pierro wrote:
>
> I strongly disagree with this.
>
> Publishing record IDs does not imply indirect object reference
> vulnerability. Any application that publishes record information must
> have a way to reference individual records. If the individual access is
> not validated than the app is vulnerable to indirect object reference,
> whether or not the reference is done by id or not.
>
> Who can access what is a matter of policy and policy must be implemented
> by the developer. Web2py provides the @auth.requires_permission and
> @auth.requires_membership and $auth.requires_signature.
>
> Massimo
>
> On Friday, 7 September 2012 09:22:12 UTC-5, MichaelF wrote:
>>
>> I appreciate that web2py has ways to handle this, and I also agree that
>> it's somewhat hackish. The problem remains, though, that we're still
>> exposing (publishing) internal primary keys to the browser. Isn't the main
>> problem the fact that we're dealing with primary key values being sent to
>> the browser? Look at https://www.owasp.org/index.php/Top_10_2010-A4 for
>> one description of the vulnerability.
>>
>> In our php application we wrote a class that hashed primary keys sent to
>> the browser, giving different hashes on each GET/POST so that, for example,
>> the hashed primary key 1 would different if the user visited the same page
>> two times.
>>
>> Thoughts?
>>
>> Thanks.
>>
>> On Thursday, September 6, 2012 8:18:44 AM UTC-6, Anthony wrote:
>>>
>>> How about http://web2py.com/books/default/chapter/29/06#Common-filters
>>> or
>>> http://web2py.com/books/default/chapter/29/06#Common-fields-and-multi-tenancy
>>> ?
>>>
>>> Anthony
>>>
>>> On Wednesday, September 5, 2012 8:48:49 PM UTC-4, Kevin C wrote:
>>>>
>>>> We did something similar but it feels very hackish, considering it
>>>> has to be done in every method of the admin controller. I just wanted to
>>>> see if there was a better way.
>>>>
>>>> Thank you.
>>>>
>>>> Kevin Cackler
>>>> Tech Daddies
>>>> 501-205-1512http://www.techdaddies.com
>>>>
>>>> On 9/5/2012 7:45 PM, Bruno Rocha wrote:
>>>>
>>>> You can do:
>>>>
>>>> if request.args(0) in ['edit', 'delete']:
>>>> STORE_DETAILS.id == int(request.args(2)) or
>>>> redirect(URL('default', 'wherever'))
>>>>
>>>> db.pages.stores_id.default = STORE_DETAILS.id
>>>> query = ((db.pages.stores_id == STORE_DETAILS.id))
>>>> form = SQLFORM.grid(query=query)
>>>>
>>>> return dict(form=form)
>>>>
>>>>
>>>>
>>>> On Wed, Sep 5, 2012 at 9:38 PM, Kevin C wrote:
>>>>
>>>>> Basically, we are generating a SQLFORM.grid with the following code:
>>>>>
>>>>> db.pages.stores_id.default = STORE_DETAILS.id
>>>>> query = ((db.pages.stores_id == STORE_DETAILS.id))
>>>>> form = SQLFORM.grid(query=query)
>>>>>
>>>>> return dict(form=form)
>>>>>
>>>>> This is working perfectly fine for us. However, we have noticed
>>>>> that if we just change the ID in the query string for the edit page, we
>>>>> are
>>>>> allowed to edit other store's entries.
>>>>>
>>>>> IE
>>>>> http://test.oursite.com/test/admin/pages/edit/pages/6?_signature=f8c5560743.<http://test.shofty.com/shofty/admin/pages/edit/pages/6?_signature=f8c55607435864253b5f5b37a6b7109956e4a8fa>
>>>>> ..
>>>>>
>>>>> What is the proper way to do this, then? The grid itself looks
>>>>> great, but just by changing the page ID in the URL, we are allowed to
>>>>> edit
>>>>> pages not belonging to us. I guess I was hoping that the query
>>>>> conditional
>>>>> would be passed to each function (add, edit, delete) but that obviously
>>>>> is
>>>>> not the case. Is multi-tenancy the solution to this issue or are we
>>>>> overlooking something simple?
>>>>> --
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
--
Re: [web2py] sqlform.grid and query conditions
I appreciate that web2py has ways to handle this, and I also agree that
it's somewhat hackish. The problem remains, though, that we're still
exposing (publishing) internal primary keys to the browser. Isn't the main
problem the fact that we're dealing with primary key values being sent to
the browser? Look at https://www.owasp.org/index.php/Top_10_2010-A4 for one
description of the vulnerability.
In our php application we wrote a class that hashed primary keys sent to
the browser, giving different hashes on each GET/POST so that, for example,
the hashed primary key 1 would different if the user visited the same page
two times.
Thoughts?
Thanks.
On Thursday, September 6, 2012 8:18:44 AM UTC-6, Anthony wrote:
>
> How about http://web2py.com/books/default/chapter/29/06#Common-filters or
> http://web2py.com/books/default/chapter/29/06#Common-fields-and-multi-tenancy
> ?
>
> Anthony
>
> On Wednesday, September 5, 2012 8:48:49 PM UTC-4, Kevin C wrote:
>>
>> We did something similar but it feels very hackish, considering it has
>> to be done in every method of the admin controller. I just wanted to see
>> if there was a better way.
>>
>> Thank you.
>>
>> Kevin Cackler
>> Tech Daddies
>> 501-205-1512http://www.techdaddies.com
>>
>> On 9/5/2012 7:45 PM, Bruno Rocha wrote:
>>
>> You can do:
>>
>> if request.args(0) in ['edit', 'delete']:
>> STORE_DETAILS.id == int(request.args(2)) or
>> redirect(URL('default', 'wherever'))
>>
>> db.pages.stores_id.default = STORE_DETAILS.id
>> query = ((db.pages.stores_id == STORE_DETAILS.id))
>> form = SQLFORM.grid(query=query)
>>
>> return dict(form=form)
>>
>>
>>
>> On Wed, Sep 5, 2012 at 9:38 PM, Kevin C wrote:
>>
>>> Basically, we are generating a SQLFORM.grid with the following code:
>>>
>>> db.pages.stores_id.default = STORE_DETAILS.id
>>> query = ((db.pages.stores_id == STORE_DETAILS.id))
>>> form = SQLFORM.grid(query=query)
>>>
>>> return dict(form=form)
>>>
>>> This is working perfectly fine for us. However, we have noticed that
>>> if we just change the ID in the query string for the edit page, we are
>>> allowed to edit other store's entries.
>>>
>>> IE
>>> http://test.oursite.com/test/admin/pages/edit/pages/6?_signature=f8c5560743.
>>> ..
>>>
>>> What is the proper way to do this, then? The grid itself looks great,
>>> but just by changing the page ID in the URL, we are allowed to edit pages
>>> not belonging to us. I guess I was hoping that the query conditional would
>>> be passed to each function (add, edit, delete) but that obviously is not
>>> the case. Is multi-tenancy the solution to this issue or are we
>>> overlooking something simple?
>>> --
>>>
>>>
>>>
>>>
>>
>>
>> --
>>
>>
>>
>>
>>
>>
--
[web2py] When will "web2py Application Development Cookbook" be updated with grid/smartgrid?
When will "web2py Application Development Cookbook" be updated with grid/smartgrid? I purchased the book recently and was disappointed to see that neither SQLFORM.grid nor SQLFORM.smartgrid were mentioned. I assume that's because those are still 'beta' or experimental. Any thoughts on when the book will be updated t include those? Thanks. --
[web2py] Nit in manual (= should be ==)?
Section 7.8 (SQLFORM.grid and smartgrid) in the description of displaying parents and children using grid: SQLFORM.grid(db.parent,left=db.child.on(db.child.parent=db.parent.id)) The second '=' should be '==' yes? --
[web2py] Re: Confused re. "Validators in model not good MVC"
My thoughts exactly, hence my confusion with the highlighted sentence. As
explained by Anthony in another reply, that sentence was let over from a
previous version of the doc. I like the idea of putting validators in the
model, assuming they are valid throughout the app (which makes sense).
Thanks again.
On Monday, July 23, 2012 1:58:26 PM UTC-6, Cliff Kachinske wrote:
>
> Caveat: my opinions. I am not a spokesperson for Web2py. That said:
>
> 1. No. MVC provides separation of functions as a way of organizing code.
> Usually programmers work together with designers, and MVC provides a way
> for them to interact without too much stepping on each other's toes.
> Typically a view would be concerned with css and other design aspects. To
> get some idea of what I mean, study the static/css files in your app. Also
> look at layout.html. MVC
>
> 2. You can put the validators anywhere in your code. It is convenient to
> put them in the model because that way you only have to do them once. Your
> validators would usually be the same across all controllers in your app, so
> why scatter them about?
>
> This situation is not something i 'live with.' It's a great convenience.
>
> On Monday, July 23, 2012 3:41:39 PM UTC-4, MichaelF wrote:
>>
>> The documentation says, in section 6.17.4 (my highlighting):
>>
>> 1 {{extend 'layout.html'}}
>>> 2 Records
>>> 3 {{=SQLTABLE(rows)}}
>>>
>>
>>
>> SQLTABLE converts the rows into an HTML table with a header containing the
>>> column names and one row per record. ...
>>
>> The values extracted from the database are also formatted by the
>>> validators
>>> associated to the field and then escaped. (Note: Using a db in this way
>>> in a
>>> view is usually not considered good MVC practice.)
>>
>>
>> I assume the doc means that validators shouldn't be used in a model file,
>> as the formatting that gets done as a result should be done by the view. Or
>> they should be used in a model file, but it's unfortunate that the
>> formatting gets done as a result, as opposed to being done in the view.
>>
>> 1. Is my assumption correct? If not, then why is this "not considered
>> good MVC practice"?
>>
>> 2. So what should be done in my web2py code, then? Add the validators in
>> the controller code? Or is this just something we live with as web2py
>> developers?
>>
>> Thanks.
>>
>
--
[web2py] Confused re. "Validators in model not good MVC"
The documentation says, in section 6.17.4 (my highlighting):
1 {{extend 'layout.html'}}
> 2 Records
> 3 {{=SQLTABLE(rows)}}
>
SQLTABLE converts the rows into an HTML table with a header containing the
> column names and one row per record. ...
The values extracted from the database are also formatted by the validators
> associated to the field and then escaped. (Note: Using a db in this way
> in a
> view is usually not considered good MVC practice.)
I assume the doc means that validators shouldn't be used in a model file,
as the formatting that gets done as a result should be done by the view. Or
they should be used in a model file, but it's unfortunate that the
formatting gets done as a result, as opposed to being done in the view.
1. Is my assumption correct? If not, then why is this "not considered good
MVC practice"?
2. So what should be done in my web2py code, then? Add the validators in
the controller code? Or is this just something we live with as web2py
developers?
Thanks.
--
[web2py] Documentation nit
The last sentence of section 6.17 says, "Yes this is unusual and not rarely needed." It should read something like, "Yes this is unusual and rarely needed," or "Yes this is unusual and not often needed." --
[web2py] 'best' way to include a JavaScript file
I want to include a JavaScript file, but only for certain views. What's the best way to include it? Do I have the controller append it to the response.files property? Do I simply use the
Re: [web2py] Getting drop-down value names from grandparent record
Problem solved. My mistake was in thinking that the 'r' in the lambda
function referred to the C record. Of course it doesn't; we're trying to
INSERT a new C record. Instead, that 'r' refers to the parent B record. So
the lambda function should be:
lambda r : '%s' % (db.A[r.A_id].A_name)
On Wednesday, July 11, 2012 9:54:24 AM UTC-6, MichaelF wrote:
>
> Okay; I understand. Let me explain using a simpler version, and also come
> up with SQL that would do the equivalent.
>
> Here's a simpler version. This example shows the problem, and simply uses
> a grandparent table (A), a parent table (B), and a child table (C). That
> is, C is a child of B, and B is a child of A:
>
> db.define_table('A',
> Field('A_name', 'string', required=True),
> format='%(A_name)s')
>
> db.define_table('B',
> Field('A_id', db.A),
> Field('B_name', 'string', required=True),
> format='%(B_name)s')
>
> db.define_table('C',
> Field('B_id', db.B),
> Field('C_name', 'string', required=True),
> format='%(C_name)s')
> db.C.B_id.requires=IS_IN_DB(db, 'B.id',
> lambda r : '%s' % (db.A[db.B[r.B_id].A_id].A_name)
> )
>
> If I leave the lambda out, then the drop-down values are B.id values. I
> can make that be B.B_name values easily, but what I want are A.A_name
> values. The lambda is fairly straightforward (although I'm obviously
> missing something).
>
> lambda r : '%s' % (db.A[db.B[r.B_id].A_id].A_name)
>
> The 'r' is the current record, and we're looking at a C record, so it first
> evaluates r.B_id, B_id being the foreign key in the C record of its
> parent B record. Let's say this evaluates to 123 (for example), which would
> then be id 123 in the B table. Now, replacing r.B_id with 123 we get:
>
> lambda r : '%s' % (db.A[db.B[123].A_id].A_name)
>
> So now it evaluates db.B[123].A_id, A_id being the foreign key in the B
> record of its parent A record:
>
> lambda r : '%s' % (db.A[db.B[123].A_id].A_name)
>
> Let's say that evaluates to 456, which would then be id 456 in the A
> table. Replacing db.B[123].A_id with 456 we get:
>
> lambda r : '%s' % (db.A[456].A_name)
>
> So now it evaluates db.A[456].A_name, which is what I'd like to see in the
> drop-down. And yet the error I get is:
> 'B_id'
>
> An equivalent SQL to get the A_name for each C id would be:
>
> SELECT C.id, A.A_name
> FROM A INNER JOIN
> B ON A.id = B.A_id INNER JOIN
> C ON B.id = C.B_id;
>
> Thoughts?
>
> Thanks.
>
> On Wednesday, July 11, 2012 3:24:41 AM UTC-6, Johann Spies wrote:
>>
>> On 11 July 2012 05:27, MichaelF wrote:
>>
>>> Didn't help. I now get the same error, this time in the function. Any
>>> idea what the error message is trying to tell me?
>>
>>
>> Unfortunately I don't have time to try and work out your logic. The
>> error is telling you that the process could not find the key C1_2 in your
>> database query. You have some logical proglem in your code.
>>
>> Try and build an SQL-query to which you can translate into a function.
>>
>> Regards
>> Johann
>>
>> --
>> Because experiencing your loyal love is better than life itself,
>> my lips will praise you. (Psalm 63:3)
>>
>>
Re: [web2py] Getting drop-down value names from grandparent record
Okay; I understand. Let me explain using a simpler version, and also come
up with SQL that would do the equivalent.
Here's a simpler version. This example shows the problem, and simply uses a
grandparent table (A), a parent table (B), and a child table (C). That is,
C is a child of B, and B is a child of A:
db.define_table('A',
Field('A_name', 'string', required=True),
format='%(A_name)s')
db.define_table('B',
Field('A_id', db.A),
Field('B_name', 'string', required=True),
format='%(B_name)s')
db.define_table('C',
Field('B_id', db.B),
Field('C_name', 'string', required=True),
format='%(C_name)s')
db.C.B_id.requires=IS_IN_DB(db, 'B.id',
lambda r : '%s' % (db.A[db.B[r.B_id].A_id].A_name)
)
If I leave the lambda out, then the drop-down values are B.id values. I can
make that be B.B_name values easily, but what I want are A.A_name values.
The lambda is fairly straightforward (although I'm obviously missing
something).
lambda r : '%s' % (db.A[db.B[r.B_id].A_id].A_name)
The 'r' is the current record, and we're looking at a C record, so it first
evaluates r.B_id, B_id being the foreign key in the C record of its parent
B record. Let's say this evaluates to 123 (for example), which would then
be id 123 in the B table. Now, replacing r.B_id with 123 we get:
lambda r : '%s' % (db.A[db.B[123].A_id].A_name)
So now it evaluates db.B[123].A_id, A_id being the foreign key in the B
record of its parent A record:
lambda r : '%s' % (db.A[db.B[123].A_id].A_name)
Let's say that evaluates to 456, which would then be id 456 in the A table.
Replacing db.B[123].A_id with 456 we get:
lambda r : '%s' % (db.A[456].A_name)
So now it evaluates db.A[456].A_name, which is what I'd like to see in the
drop-down. And yet the error I get is:
'B_id'
An equivalent SQL to get the A_name for each C id would be:
SELECT C.id, A.A_name
FROM A INNER JOIN
B ON A.id = B.A_id INNER JOIN
C ON B.id = C.B_id;
Thoughts?
Thanks.
On Wednesday, July 11, 2012 3:24:41 AM UTC-6, Johann Spies wrote:
>
> On 11 July 2012 05:27, MichaelF wrote:
>
>> Didn't help. I now get the same error, this time in the function. Any
>> idea what the error message is trying to tell me?
>
>
> Unfortunately I don't have time to try and work out your logic. The error
> is telling you that the process could not find the key C1_2 in your
> database query. You have some logical proglem in your code.
>
> Try and build an SQL-query to which you can translate into a function.
>
> Regards
> Johann
>
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)
>
>
Re: [web2py] Getting drop-down value names from grandparent record
Didn't help. I now get the same error, this time in the function. Any idea what the error message is trying to tell me? On Tuesday, July 10, 2012 12:50:01 AM UTC-6, Johann Spies wrote: > > On 10 July 2012 01:09, MichaelF wrote: > >> >>lambda r: '%s' % (db.I1[db.C1_2[r.C1_2].I1].Name), >>_and=IS_NOT_IN_DB(db(db.C1_2_3.C1_2==request.vars.C1_2), 'C1_2_3.C3')) >> db.C1_2_3.C1_2.requires=IS_IN_DB(db, 'C1_2.id', >> >> ... > >> 1. Am I even allowed to have a lambda function there? >> 2. In any case, how can I get the drop-down to show me I1.Name? >> > > I would try the following: > > A separate function to do what you tried to do in your lambda. > Let the lambda call that function which returns the value you want . > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > >
Re: [web2py] Looking for multi-select widget that uses two text boxes
Nothing's wrong with Anthony's answer; I like it. On Monday, July 9, 2012 4:54:42 PM UTC-6, villas wrote: > > I used this one once: > http://www.senamion.com/blog/jmultiselect2side.html > > However, it appears the one Anthony suggested would be better -- what's > wrong with that suggestion? > > > On Sunday, July 8, 2012 9:00:04 PM UTC+1, MichaelF wrote: >> >> Thanks, Bruno. That's not quite what I need for this job, but I do need >> that in another. I need something more like what Anthony posted ( >> http://quasipartikel.at/multiselect_next/). >> >> On Sunday, July 8, 2012 12:42:09 PM UTC-6, rochacbruno wrote: >>> >>> I guess you need this: >>> >>> >>> http://www.web2pyslices.com/slice/show/1526/cascading-drop-down-lists-with-ajax-2 >>> >>> >>> >>> *Bruno Cezar Rocha* >>> >>> http://www.CursoDePython.com.br >>> [image: Facebook] <http://facebook.com/rochacbruno> [image: >>> Twitter]<http://twitter.com/rochacbruno> [image: >>> LinkedIn] <http://linkedin.com/in/rochacbruno> [image: >>> about.me]<http://about.me/rochacbruno> [image: >>> Amazon] <http://amazon.com/author/rochacbruno> [image: >>> AngelList]<http://angel.co/rochacbruno> [image: >>> Blog RSS] <http://www.web2pyslices.com/slice/list.rss?author=1> [image: >>> Facebook Page] <http://facebook.com/CursoDePython> [image: >>> foursquare]<http://foursquare.com/rochacbruno> [image: >>> Google Plus] <https://plus.google.com/u/0/116110204708544946953/posts> >>> [image: >>> pinterest] <http://pinterest.com/rochacbruno> [image: >>> SlideShare]<http://slideshare.com/rochacbruno> [image: >>> YouTube] <http://youtube.com/user/brunovegan> >>> [image: Google Talk] rochacbruno [image: Skype] blouweb >>> Blog: Generate a thumbnail that fits in a >>> box<http://www.web2pyslices.com/slice/show/1522/generate-a-thumbnail-that-fits-in-a-box> >>> Get a signature like this. >>> <http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18> >>> Click >>> here.<http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18> >>> >>> >>> >>> >>> On Sun, Jul 8, 2012 at 3:10 PM, MichaelF wrote: >>> >>>> I'm aware of the multiple and checkbox widgets. I'm looking for a >>>> widget whose name I don't know. It consists of two side-by-side boxes, >>>> each >>>> box can contain a list of items, and usually starts off with all the items >>>> in the left-hand box. Between the two boxes are two arrows, usually one on >>>> top of the other, one pointing to the right, one to the left. The >>>> left-hand >>>> box contains those items not 'chosen'; the right-hand box contains those >>>> chosen. To 'choose' items (that is, to move an item from the left-hand box >>>> to the right-hand box) one selects the item(s) (ctrl-click, shift-click, >>>> etc.) in the left-hand box, then presses the right-pointing arrow. The >>>> selected items move from the left-hand box to the right-hand box. To >>>> 'un-choose' items one selects items in the right-hand box, then presses >>>> the >>>> left-pointing arrow. >>>> >>>> 1. What is this widget usually called? >>>> >>>> 2. Does web2py have such a widget? >>>> >>> >>>
[web2py] Getting drop-down value names from grandparent record
In short, I'm having problems getting the drop-down value for a field in a
child-of-a-child record; I want that value to be a field from the
grandparent.
In more detail:
1. I have three 'independent' tables ('independent' in that they have no
parents). Let's call them I1, I2, and I3.
2. I have a child table that links I1 and I2 in a many-many
relationship. Let's call that table C1_2.
3. I have a child table that links I3 and C1_2 in a many-to-many. Let's
call that table C1_2_3.
4. When I add a C1_2_3 record, I'd like the values in the drop-down for
the C1_2 field to be taken from two of its parents' fields: I1.Name and
I2.I2.
Here's the table definitions:
db.define_table('I1',
Field('Name', 'string', length=60, required=True),
format='%(Name)s')
db.define_table('I2',
Field('I2', 'string', length=60, required=True,
unique=True),
format='%(I2)s')
db.define_table('C1_2',
Field('I1', db.I1),
Field('I2', db.I2),
Field('Start_date', 'date', required=True),
format=lambda r: '%s %s' % (db.I1[r.I1].Name,
db.I2[r.I2].I2))
db.C1_2.Start_date.requires = \
[IS_DATE(),
IS_NOT_IN_DB(
db((db.C1_2.I1==request.vars.I1) &\
(db.C1_2.I2==request.vars.I2)),
'C1_2.Start_date')]
db.define_table('I3',
Field('I3_name', 'string', required=True, unique=True),
format='%(I3_name)s')
db.define_table('C1_2_3',
Field('C1_2', db.C1_2),
Field('C3', db.I3))
db.C1_2_3.C1_2.requires=IS_IN_DB(db, 'C1_2.id',
lambda r: '%s' % (db.I1[db.C1_2[r.C1_2].I1].Name),
_and=IS_NOT_IN_DB(db(db.C1_2_3.C1_2==request.vars.C1_2), 'C1_2_3.C3'))
For simplicity, in this example the value I want to see for the dropdown
for field C1_2_3.C1_2 is just I1.Name (not I1.name and I2.I2 as described
above).
Without the highlighted line the dropdown shows only the C1_2 id values.
With the highlighted line I get the error, "
'C1_2'" on the lambda definition.
1. Am I even allowed to have a lambda function there?
2. In any case, how can I get the drop-down to show me I1.Name?
[web2py] Any difference passing a string vs. a field for arg #2 in IS_IN_DB?
The doc shows two calling patterns for IS_IN_DB, the difference being the
type of the second positional argument:
The example in section 3.6 shows:
db.comment.image_id.requires = IS_IN_DB(db, db.image.id, '%(title)s')
Section 6.5 has a table for default validators:
IS_IN_DB(db,table.field,format)
Section 7.6.2 *Database Validators* (and other places) shows:
db.dog.owner.requires = IS_IN_DB(db, 'person.id',
'%(name)s', zero=T('choose one'))
Is there a functional difference in passing the string vs. passing the
field?
Re: [web2py] Looking for multi-select widget that uses two text boxes
Thanks, Bruno. That's not quite what I need for this job, but I do need that in another. I need something more like what Anthony posted ( http://quasipartikel.at/multiselect_next/). On Sunday, July 8, 2012 12:42:09 PM UTC-6, rochacbruno wrote: > > I guess you need this: > > > http://www.web2pyslices.com/slice/show/1526/cascading-drop-down-lists-with-ajax-2 > > > > *Bruno Cezar Rocha* > > http://www.CursoDePython.com.br > [image: Facebook] <http://facebook.com/rochacbruno> [image: > Twitter]<http://twitter.com/rochacbruno> [image: > LinkedIn] <http://linkedin.com/in/rochacbruno> [image: > about.me]<http://about.me/rochacbruno> [image: > Amazon] <http://amazon.com/author/rochacbruno> [image: > AngelList]<http://angel.co/rochacbruno> [image: > Blog RSS] <http://www.web2pyslices.com/slice/list.rss?author=1> [image: > Facebook Page] <http://facebook.com/CursoDePython> [image: > foursquare]<http://foursquare.com/rochacbruno> [image: > Google Plus] <https://plus.google.com/u/0/116110204708544946953/posts> > [image: > pinterest] <http://pinterest.com/rochacbruno> [image: > SlideShare]<http://slideshare.com/rochacbruno> [image: > YouTube] <http://youtube.com/user/brunovegan> > [image: Google Talk] rochacbruno [image: Skype] blouweb > Blog: Generate a thumbnail that fits in a > box<http://www.web2pyslices.com/slice/show/1522/generate-a-thumbnail-that-fits-in-a-box> > Get a signature like this. > <http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18> > Click > here.<http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18> > > > > > On Sun, Jul 8, 2012 at 3:10 PM, MichaelF wrote: > >> I'm aware of the multiple and checkbox widgets. I'm looking for a widget >> whose name I don't know. It consists of two side-by-side boxes, each box >> can contain a list of items, and usually starts off with all the items in >> the left-hand box. Between the two boxes are two arrows, usually one on top >> of the other, one pointing to the right, one to the left. The left-hand box >> contains those items not 'chosen'; the right-hand box contains those >> chosen. To 'choose' items (that is, to move an item from the left-hand box >> to the right-hand box) one selects the item(s) (ctrl-click, shift-click, >> etc.) in the left-hand box, then presses the right-pointing arrow. The >> selected items move from the left-hand box to the right-hand box. To >> 'un-choose' items one selects items in the right-hand box, then presses the >> left-pointing arrow. >> >> 1. What is this widget usually called? >> >> 2. Does web2py have such a widget? >> > >
[web2py] Looking for multi-select widget that uses two text boxes
I'm aware of the multiple and checkbox widgets. I'm looking for a widget whose name I don't know. It consists of two side-by-side boxes, each box can contain a list of items, and usually starts off with all the items in the left-hand box. Between the two boxes are two arrows, usually one on top of the other, one pointing to the right, one to the left. The left-hand box contains those items not 'chosen'; the right-hand box contains those chosen. To 'choose' items (that is, to move an item from the left-hand box to the right-hand box) one selects the item(s) (ctrl-click, shift-click, etc.) in the left-hand box, then presses the right-pointing arrow. The selected items move from the left-hand box to the right-hand box. To 'un-choose' items one selects items in the right-hand box, then presses the left-pointing arrow. 1. What is this widget usually called? 2. Does web2py have such a widget?
[web2py] Re: MySQL migration fails adding unique=
Thanks for that. I didn't realize field-level migrations don't work with
MySQL. I'm no expert on web2py migrations (or even web2py!); is it just
with MySQL that it has these problems?
I suppose I could do this as an alternative to what you suggested: In
web2py add a new field with unique, then in MySQL UPDATE all records,
setting newField = oldField. I'd have to handle duplicates here, but at
least I wouldn't lost all my data. Then, when satisfied, delete oldField
and maybe rename newField to oldField.
On Thursday, July 5, 2012 12:26:31 PM UTC-6, Jim S wrote:
>
> I believe that field level migrations do not work with MySQL. I get
> around this by removing the column, saving, run the app to force migration,
> and then add the field back the way I want it. I know this causes you to
> lose the data in that column, but I only do this in my test environment and
> have migrations turned off in production.
>
> Alternatively, you could update the column def in web2py, change manually
> in mysql and then run with migrate=False, fake_migrate=True to get things
> back in sync.
>
> Hope that helps.
>
> -Jim
>
> On Wednesday, July 4, 2012 10:29:10 AM UTC-5, MichaelF wrote:
>>
>> I have a working app using web2py `(1, 99, 7, datetime.datetime(2012, 3,
>> 4, 22, 12, 8), 'stable'); Python 2.5.4: C:\Program Files
>> (x86)\web2py\web2py_no_console.exe`) and MySQL 5.5. If I change one field
>> to add `unique=True` the web2py migration fails with this error: `"> 'exceptions.KeyError'> 'institution_name'"` where institution_name is the
>> name of the field in question.
>>
>> I've recreated the problem using a single-table application in web2py
>> using MySQL. Here's the model code:
>>
>> To start off (field not defined as unique):
>>
>> ... (usual model/db.py boilerplate)
>> db = DAL('mysql://w2ptest:abcde...@mysql5.server.com:3307/abc_web2py
>> ')
>> ...
>> db.define_table('Institution',
>> Field('Institution_name', 'string', length=60,
>> required=True),
>> format='%(Institution_name)s')
>>
>> I go to the appadmin page and everything looks fine. Then, making
>> Institution_name unique:
>>
>> db.define_table('Institution',
>> Field('Institution_name', 'string', length=60,
>> required=True,
>>unique=True),
>> format='%(Institution_name)s')
>>
>> I then refresh the appadmin page and get a ticket with the error. The
>> error line in the traceback is the last line of the modified statement
>> above. And, to make things worse, I can go in and undo the `unique=True`,
>> but web2py doesn't respond if I refresh the appadmin page...or any page
>> served by that web server, even in other applications! The cpu is
>> not pinned while in this state. I have to recreate the app and
>> database to clear the problem. (Well, I think I have to go that far. Just
>> restarting web2py doesn't clear it in the full case, but does clear it in
>> my little one-table test case.) I try to stop the server
>> (web2py_no_console.exe), but it fails to respond.
>>
>> Instead of the `unique=True` I can `db.executesql('ALTER TABLE
>> abc_web2py.Institution ADD UNIQUE INDEX UX_Iname (Institution_name) ;');`
>> but I'd rather not, particularly as then I have to `try` that statement
>> because MySQL has no `...IF NOT EXIST...` capability for index creation.
>>
>> Also, if I start off the model with `unique=True` in the first place,
>> everything is fine, and MySQL even shows the unique index as created.
>>
>>
[web2py] MySQL migration fails adding unique=
I have a working app using web2py `(1, 99, 7, datetime.datetime(2012, 3, 4,
22, 12, 8), 'stable'); Python 2.5.4: C:\Program Files
(x86)\web2py\web2py_no_console.exe`) and MySQL 5.5. If I change one field
to add `unique=True` the web2py migration fails with this error: `" 'institution_name'"` where institution_name is the
name of the field in question.
I've recreated the problem using a single-table application in web2py using
MySQL. Here's the model code:
To start off (field not defined as unique):
... (usual model/db.py boilerplate)
db = DAL('mysql://w2ptest:abcde...@mysql5.server.com:3307/abc_web2py')
...
db.define_table('Institution',
Field('Institution_name', 'string', length=60,
required=True),
format='%(Institution_name)s')
I go to the appadmin page and everything looks fine. Then, making
Institution_name unique:
db.define_table('Institution',
Field('Institution_name', 'string', length=60,
required=True,
unique=True),
format='%(Institution_name)s')
I then refresh the appadmin page and get a ticket with the error. The error
line in the traceback is the last line of the modified statement above.
And, to make things worse, I can go in and undo the `unique=True`, but
web2py doesn't respond if I refresh the appadmin page...or any page served
by that web server, even in other applications! The cpu is not
pinned while in this state. I have to recreate the app and database to
clear the problem. (Well, I think I have to go that far. Just restarting
web2py doesn't clear it in the full case, but does clear it in my little
one-table test case.) I try to stop the server (web2py_no_console.exe), but
it fails to respond.
Instead of the `unique=True` I can `db.executesql('ALTER TABLE
abc_web2py.Institution ADD UNIQUE INDEX UX_Iname (Institution_name) ;');`
but I'd rather not, particularly as then I have to `try` that statement
because MySQL has no `...IF NOT EXIST...` capability for index creation.
Also, if I start off the model with `unique=True` in the first place,
everything is fine, and MySQL even shows the unique index as created.
[web2py] MySQL migration fails adding unique
I have a working app using web2py `(1, 99, 7, datetime.datetime(2012, 3, 4,
22, 12, 8), 'stable'); Python 2.5.4: C:\Program Files
(x86)\web2py\web2py_no_console.exe`) and MySQL 5.5. If I change one field
to add `unique=True` the web2py migration fails with this error: `" 'institution_name'"` where institution_name is the
name of the field in question.
I've recreated the problem using a single-table application in web2py using
MySQL. Here's the model code:
To start off (field not defined as unique):
... (usual model/db.py boilerplate)
db = DAL('mysql://w2ptest:abcde...@mysql5.server.com:3307/abc_web2py')
...
db.define_table('Institution',
Field('Institution_name', 'string', length=60,
required=True),
format='%(Institution_name)s')
I go to the appadmin page and everything looks fine. Then, making
Institution_name unique:
db.define_table('Institution',
Field('Institution_name', 'string', length=60,
required=True,
unique=True),
format='%(Institution_name)s')
I then refresh the appadmin page and get a ticket with the error. The error
line in the traceback is the last line of the modified statement above.
And, to make things worse, I can go in and undo the `unique=True`, but
web2py doesn't respond if I refresh the appadmin page...or any page served
by that web server, even in other applications! The cpu is not
pinned while in this state. I have to recreate the app and database to
clear the problem. (Well, I think I have to go that far. Just restarting
web2py doesn't clear it in the full case, but does clear it in my little
one-table test case.) I try to stop the server (web2py_no_console.exe), but
it fails to respond.
Instead of the `unique=True` I can `db.executesql('ALTER TABLE
abc_web2py.Institution ADD UNIQUE INDEX UX_Iname (Institution_name) ;');`
but I'd rather not, particularly as then I have to `try` that statement
because MySQL has no `...IF NOT EXIST...` capability for index creation.
Also, if I start off the model with `unique=True` in the first place,
everything is fine, and MySQL even shows the unique index as created.
