[web2py] ValueError: invalid literal for int() with base 10:

[annet]

In db.py I defined the following tables:


db.define_table('company',
Field('company',length=54,default='',notnull=True),
migrate=False)

db.define_table('application',

Field('application',length=24,default='',notnull=True,unique=True),
migrate=False)

db.define_table('companyapplication',

Field('company',db.company,default='',notnull=True,ondelete='CASCADE'),

Field('application',db.application,default='',notnull=True,ondelete='RESTRICT'),
migrate=False)

db.companyapplication.company.requires=[IS_IN_DB(db,db.company.id,'%
(company)s'),IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),db.companyapplication.company,error_message='combination
of company en application already in database')]
db.companyapplication.application.requires=IS_IN_DB(db,db.application.id,'%
(application)s')


In appadmin/insert/db/application I get the following error when I
want to insert an application:

Traceback (most recent call last):
File "/Library/Python/2.5/site-packages/web2py/gluon/restricted.py",
line 173, in restricted
exec ccode in environment
File "/Library/Python/2.5/site-packages/web2py/applications/base/
models/db.py", line 216, in <module>

db.companyapplication.company.requires=[IS_IN_DB(db,db.company.id,'%
(company)s'),IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),db.companyapplication.company,error_message='combination
of company and application already in database')]
File "/Library/Python/2.5/site-packages/web2py/gluon/sql.py", line
2424, in __eq__
return Query(self, '=', value)
File "/Library/Python/2.5/site-packages/web2py/gluon/sql.py", line
2839, in __init__
right = sql_represent(right, left.type, left._db._dbname,
left._db._db_codec)
File "/Library/Python/2.5/site-packages/web2py/gluon/sql.py", line
510, in sql_represent
return str(int(obj))
ValueError: invalid literal for int() with base 10: 'Supplier'


I don't understand why I get an error on the validator of the
companyapplication table whereas I want to insert a record into the
application table.

What's causing this error? How do I solve it?


Kind regards,

Annet.


--
Subscription settings: http://groups.google.com/group/web2py/subscribe?hl=en

[mdipierro]

The error refer to a field Supplier and I do not see it defined. Some
other part of the code is causing the problem and I cannot help tell
without look at it all.

[annet]

Massimo,

> The error refer to a field Supplier and I do not see it defined.

'Supplier is the value I enter for the Field application in the
application table.

> Some other part of the code is causing the problem and I cannot help tell
> without look at it all.

Yes, the validator defined after the companyapplication table:

db.define_table('companyapplication',
Field('company',db.company,),
Field('application',db.application),
migrate=False)

db.companyapplication.company.requires=[IS_IN_DB(db,db.company.id,'%
(company)s'),\
IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),
\

db.companyapplication.company,error_message='combination
of company en application already in database')]

That's all the information related to this problem. I hope it's enough
to solve the problem, because it's a recurrent one.

[mdipierro]

In this line:

IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),

db.companyapplication.application is a reference field (i.e. an
integer)
request.vars.application is a string 'Supplier'. That is the problem.

Please explain in words what these table represent and what you want
to achieve. I am sure we can find a solution.

Massimo

[annet]

Companies in the company table can apply for one or more applications,
therefore I defined the combination table companyapplication. The
company table contains records like:

1 Butson Consultancy
2 Dreamwall
3 Preston Counselors

The application table contains records like:

1 Supplier
2 Consultants
3 Counselors

The companyapplication table contains records like:

1 2
2 1
2 2
3 3


I can insert companies without problems, however, when I want to
insert an application, e.g. Supplier I get the error mentioned above.

I hope I provided you with sufficient information.

[DenesL]

The problem happens in this requires

IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),db.companyapplication.company,error_message='combination

of company en application already in database')]

because the field naming choice makes it impossible to tell apart in
request.vars which application is being referenced on each side of the
comparison, on the left side companyapplication.application refers to
an application.id while on the right side it refers to
application.application, a string.

to get around this redefine table companyapplication as follows

db.define_table('companyapplication',
Field('company_id',db.company,),
Field('application_id',db.application),
migrate=False)

and update the requires to reflect the new naming

db.companyapplication.company_id.requires=[IS_IN_DB(db,db.company.id,'%
(company)s'),

IS_NOT_IN_DB(db(db.companyapplication.application_id==request.vars.application_id),db.companyapplication.company_id,error_message='combination
of company and application already in database')]

db.companyapplication.application_id.requires=IS_IN_DB(db,db.application.id,'%
(application)s')

Following such a convention (adding '_id' to denote a reference) is
highly recommended.

Denes.

[annet]

Hi Denes,

Thanks for providing me with an explanation of why this problem occurs
and with a solution.

> Following such a convention (adding '_id' to denote a reference) is
> highly recommended.

From now on I will follow this convention! I don't know if I am the
only one making this mistake, if not it may be worth posting a slice
on it. It's rather time consuming having alter the database tables in
you model and adjusting your application(s) accordingly.


Kind regards,

Annet

[DenesL]

Appending '_id' to references helps to keep names different so clashes
like the one above don't happen, while at the same time they make the
program more readable for the developer, specially at a later time.

But conventions are a matter of personal preference so you can follow
whichever one you prefer.
Personally I like shorter names so I would have used something like
co_id and app_id, or even coid and appid, which are easier to type.

From my experience:
- try to keep all names unique
- in particular never have fields names equal to table names
- don't be too cryptic with the name, you will regret it later

web2py eases the creation of web apps but there still a lot of things
to remember and learn.
A checklist or tip list would be helpful.
What do others use? please share your tips and tricks.

Denes.

[mdipierro]

I think there is said to say pro and con this convention.

case1)
db.define_table('person',Field('name'))
db.define_table('dog',Field('name'),Field('owner_id',db.person)

db.god.owner_id makes sense because it stores an integer but
db.dog.owner_id.name is odd because 'name' is not n attribute of an
id.

case2)
db.define_table('person',Field('name'))
db.define_table('dog',Field('name'),Field('owner',db.person)

db.god.owner technically stores only an id
db.dog.owne.name to me is better than db.dog.owner_id.name

Massimo

[DenesL]

On Apr 24, 9:11 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> I think there is said to say pro and con this convention.
>
> case1)
> db.define_table('person',Field('name'))
> db.define_table('dog',Field('name'),Field('owner_id',db.person)
>
> db.god.owner_id makes sense because it stores an integer but
> db.dog.owner_id.name is odd because 'name' is not n attribute of an
> id.

The convention makes more sense when you use the same name:
db.define_table('dog',Field('name'),Field('person_id',db.person)

Since you are using 'owner' (an unique field name) then there is no
need to append id, but owner_id immediately suggests what type of
field it is and it can avoid a possible clash in request.vars with
another field when things get more complicated (more tables and
fields).

> case2)
> db.define_table('person',Field('name'))
> db.define_table('dog',Field('name'),Field('owner',db.person)
>
> db.god.owner technically stores only an id
> db.dog.owne.name to me is better than db.dog.owner_id.name

Maybe you meant db.person[owner].name
but either db.person[person_id].name or
db.person[owner_id].name are perfectly readable,

db.dog.owner.name is just the name of the field ('owner').

Even though it might not be perfect it seems like a good idea.
Anyway, I would like to hear what others do.

[Thadeus Burgess]

Since the Reference object now will query for a reference record when
you call it, you can do things like::

dogs = db().select(db.dog.ALL)
for dog in dogs:
dog.name
dog.owner.name

So the refernece column is actually called owner, insetad of owner_id.
It doesn't make sense to do

``dog.owner_id.name``

Since the record gets pulled behind the scenes by web2py.

However, even though this convention allows for a nice shortcut during
development, it can easily get out of hand on production systems. For
example, I have a patch on gluon.sql that keeps track of what kind of
queries are being made and how many of them throughout the life of the
request. Linking this with my blog, I end up with one page equals a
total of 234 SELECT statements from the database. Its not cool,
however with a little re-designing and I can bring it down to just a
handful of selects by not taking advantage of this "convenience".

--
Thadeus

[DenesL]

On Apr 24, 11:06 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Since the Reference object now will query for a reference record when
> you call it, you can do things like::

What do you mean by 'now'? This has always worked for Rows.

>   dogs = db().select(db.dog.ALL)
>   for dog in dogs:
>       dog.name
>       dog.owner.name
>
> So the refernece column is actually called owner, insetad of owner_id.
> It doesn't make sense to do
>
>   ``dog.owner_id.name``
>
> Since the record gets pulled behind the scenes by web2py.

The convention is used to avoid conflicts in request.vars and to make
it clear that it is a reference field.
If 'owner' is a unique name then using it is fine.

However if you have 'owner' somewhere else along with a requires like
the one Annet had then there will be a conflict, since in request.vars
you can not tell them apart, i.e. when you say request.vars.owner you
don't know which one you are referring to because request.vars only
knows about field names and has no knowledge of the tables they belong
to.

> However, even though this convention allows for a nice shortcut during
> development, it can easily get out of hand on production systems. For
> example, I have a patch on gluon.sql that keeps track of what kind of
> queries are being made and how many of them throughout the life of the
> request. Linking this with my blog, I end up with one page equals a
> total of 234 SELECT statements from the database. Its not cool,
> however with a little re-designing and I can bring it down to just a
> handful of selects by not taking advantage of this "convenience".

You lost me here, I can't picture what you are doing.

[Yarko Tymciurak]

in quickly scanning this thread, I am struck by a few observations:

- it is useful for the human reading to have a hint that a variable is
an 'indirect" reference;
- the specific details - that the inderection is by a table index
called "id", and that it is an integer seems irrelevant (even, if at
first convenient);
- the "naming sense" and discussions seem to have tripped over the two
preceding observations.

To wit: consider (using Massimo's example):

db.dog.i_owner.name

It reads as _well_ as db.dog.owner.name, while addressing what Denes
points out: readability, and "hint" to the code reader that it's an
"indirect reference".

I think it helps to forget about the literal reference to an "id"
field, and instead consider what kind of thing it represents (i.e.
focus on the abstract concept, not the specific implementation
detail).

The last observation I have: naming conflicts / ambiguity: this
(correct me if I missed something) is not about naming comflicts, as
these are not variable names / scopes, but rather strings being
interpreted "in the absense" of a traditional symbol table, etc. ---
this is a good reason to heed Denes's warning to keep names separate:
that is, note that much of what is manipulated in Fields, queries is
(in fact) strings which are later converted to specific SQL.

- Yarko

[DenesL]

On Apr 24, 12:06 pm, Yarko Tymciurak <resultsinsoftw...@gmail.com>
wrote:

> in quickly scanning this thread, I am struck by a few observations:
>
> - it is useful for the human reading to have a hint that a variable is
> an 'indirect" reference;
> - the specific details - that the inderection is by a table index
> called "id", and that it is an integer seems irrelevant (even, if at
> first convenient);
> - the "naming sense" and discussions seem to have tripped over the two
> preceding observations.
>
> To wit:   consider (using Massimo's example):
>
> db.dog.i_owner.name

db.dog.i_owner.name == 'i_owner'
db.dog.owner_id.name == 'owner_id'
db.dog.owner.name == 'owner'

and it has nothing to do with the issue.

> It reads as _well_ as   db.dog.owner.name, while addressing what Denes
> points out: readability, and "hint" to the code reader that it's an
> "indirect reference".
>
> I think it helps to forget about the literal reference to an "id"
> field, and instead consider what kind of thing it represents (i.e.
> focus on the abstract concept, not the specific implementation
> detail).

On the contrary, using '_id' helps me to relate it to the web2py field
type being used and therefore the concept behind it.
But again, you can use whatever convention you like as long as it
keeps the names unique.

> The last observation I have:   naming conflicts / ambiguity:   this
> (correct me if I missed something) is not about naming comflicts, as
> these are not variable names / scopes, but rather strings being
> interpreted "in the absense" of a traditional symbol table, etc. ---
> this is a good reason to heed Denes's warning to keep names separate:
> that is, note that much of what is manipulated in Fields, queries is
> (in fact) strings which are later converted to specific SQL.

Lets use the 1st post by Annet as case study.
She had an 'application' table with an 'application' field.
She also had a 'companyapplication' table with an 'application' field.

Since she wanted the entries in 'companyapplication' table to be
unique she used this requires:
IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),db.companyapplication.company)

A request to add a new entry in table 'application' will have a
request.vars.application, which a request to add a new entry in table
'companyapplication' will also have.
That is the problem!
It is a field naming problem.
When the requires is checked there is no way to know which
'application' is request.vars.application referring to.
It should be the one in table 'companyapplication' obviously, but
request.vars does not know which fields belong to which table.
I hope this clarifies the issue.

[Thadeus Burgess]

I see.

Clarified!
--
Thadeus

On Sat, Apr 24, 2010 at 1:34 PM, DenesL <dene...@yahoo.ca> wrote:
> he also had a 'companyapplication' table with an 'app

[Yarko Tymciurak]

On Apr 24, 1:34 pm, DenesL <denes1...@yahoo.ca> wrote:
> On Apr 24, 12:06 pm, Yarko Tymciurak <resultsinsoftw...@gmail.com>
> wrote:
>
> > in quickly scanning this thread, I am struck by a few observations:
>
> > - it is useful for the human reading to have a hint that a variable is
> > an 'indirect" reference;
> > - the specific details - that the inderection is by a table index
> > called "id", and that it is an integer seems irrelevant (even, if at
> > first convenient);
> > - the "naming sense" and discussions seem to have tripped over the two
> > preceding observations.
>
> > To wit:   consider (using Massimo's example):
>
> > db.dog.i_owner.name
>
> db.dog.i_owner.name == 'i_owner'
> db.dog.owner_id.name == 'owner_id'
> db.dog.owner.name == 'owner'
>
> and it has nothing to do with the issue.

Yes - and you've clarified well - it is the "symbol table" vs. simple
string representation of a field, and the ambiguity that involved...

(I was referring to Massimo's objection - and am with you; the naming
is whatever works for you; for Massimo, I wanted to suggest de-
coupling his view from "id" as an automatic field, and look at the
naming solution as one that suggests an indirection - whatever way you
find clear).

>
> > It reads as _well_ as   db.dog.owner.name, while addressing what Denes
> > points out: readability, and "hint" to the code reader that it's an
> > "indirect reference".
>
> > I think it helps to forget about the literal reference to an "id"
> > field, and instead consider what kind of thing it represents (i.e.
> > focus on the abstract concept, not the specific implementation
> > detail).
>
> On the contrary, using '_id' helps me to relate it to the web2py field
> type being used and therefore the concept behind it.
> But again, you can use whatever convention you like as long as it
> keeps the names unique.

Exactly - an indicator to "the humans" that this is not a normal
field, but a reference field (too bad we can't use the C notatoin of
"*owner" ! ... just kidding... partially...)

>
> > The last observation I have:   naming conflicts / ambiguity:   this
> > (correct me if I missed something) is not about naming comflicts, as
> > these are not variable names / scopes, but rather strings being
> > interpreted "in the absense" of a traditional symbol table, etc. ---
> > this is a good reason to heed Denes's warning to keep names separate:
> > that is, note that much of what is manipulated in Fields, queries is
> > (in fact) strings which are later converted to specific SQL.
>
> Lets use the 1st post by Annet as case study.
> She had an 'application' table with an 'application' field.
> She also had a 'companyapplication' table with an 'application' field.
>
> Since she wanted the entries in 'companyapplication' table to be
> unique she used this requires:
> IS_NOT_IN_DB(db(db.companyapplication.application==request.vars.application),db.companyapplication.company)
>
> A request to add a new entry in table 'application' will have a
> request.vars.application, which a request to add a new entry in table
> 'companyapplication' will also have.
> That is the problem!

Exactly

> It is a field naming problem.
> When the requires is checked there is no way to know which
> 'application' is request.vars.application referring to.

RIght!

> It should be the one in table 'companyapplication' obviously, but
> request.vars does not know which fields belong to which table.
> I hope this clarifies the issue.

Yes - and that is the motivation for making field names more
indicative, partly to help human-hinting at what you are looking at,
and partly to avoid clashes like this (which will not occur
everywhere, which is why - generally - unique filed naming is not
generally important / an issue).

>
> Denes.
>

- Yarko

[DenesL]

Since that seems to be settled, I want to take Annet's suggestion
beyond a slice and hear ideas about how to avoid pitfalls, and tips to
follow to steer clear of trouble while developing an app.
We can learn from each other.