insert NEVER validates data types. FORMs and SQLFORMs validate input.
That is for speed.
You can call
r = db.table.validate_and_insert(**fields)
which returns r.id and r.errors
On May 24, 8:47 pm, Brian M <[email protected]> wrote:
> I just found what might be a bug, when using db.table.insert(field = value)
> it appears that the type of value isn't checked against the declared type of
> field. So for example you can declare a field to be of the date type, but
> then insert a record with a non-date value (at least in sqlite). I would
> have expected the DAL to give an error when attempting to do something like
> this, but it does not. However, when you then attempt to retrieve the
> inserted record, an error does occur. If you separate the db insert from
> the retrieval query and check in the database, you can see that "invalid"
> data has been stored in the database.
>
> Granted, if you're using forms & validators then the data type conflict is
> caught before the insert, but I thought it may be something that should be
> pointed out - it isn't enough just to define data types in your model &
> expect web2py to always automatically enforce them.
>
> Sample Code:
>
> model.py
> -----------------------
> db.define_table('mytable',
> Field('a_date_field', 'date')
> )
>
> controllers/default.py
> --------------------------------
> def date_insert():
> my_date = '05/24/2011' #note, this is a string not a date type. Should
> have done datetime.strptime('05/24/2011','%m/%d/%Y) or date(2011,5,24)
>
> record = db.mytable.insert(a_date_field = my_date)
>
> results = db(db.mytable.id>0).select()
>
> return dict(results = results, record = record)
>
> Error Ticket:
> ---------------------
>
> Traceback (most recent call last):
> File "C:\Users\Brian\Documents\development\web2py\stable\web2py_src
> (1.95.1)\web2py\gluon\restricted.py", line 181, in restricted
> exec ccode in environment
> File "C:/Users/Brian/Documents/development/web2py/stable/web2py_src
> (1.95.1)/web2py/applications/date_test/controllers/default.py"
> <http://localhost:8000/admin/default/edit/date_test/controllers/defaul...>,
> line 80, in <module>
> File "C:\Users\Brian\Documents\development\web2py\stable\web2py_src
> (1.95.1)\web2py\gluon\globals.py", line 133, in <lambda>
> self._caller = lambda f: f()
> File "C:/Users/Brian/Documents/development/web2py/stable/web2py_src
> (1.95.1)/web2py/applications/date_test/controllers/default.py"
> <http://localhost:8000/admin/default/edit/date_test/controllers/defaul...>,
> line 41, in date_insert_test
> results = db(db.mytable.id>0).select()
> File "C:\Users\Brian\Documents\development\web2py\stable\web2py_src
> (1.95.1)\web2py\gluon\dal.py", line 5164, in select
> return self.db._adapter.select(self.query,fields,attributes)
> File "C:\Users\Brian\Documents\development\web2py\stable\web2py_src
> (1.95.1)\web2py\gluon\dal.py", line 1082, in select
> return self.parse(rows,self._colnames)
> File "C:\Users\Brian\Documents\development\web2py\stable\web2py_src
> (1.95.1)\web2py\gluon\dal.py", line 1291, in parse
> str(value)[:10].strip().split('-')]
> ValueError: invalid literal for int() with base 10: '05/24/2011'
