I cannot reproduce the problem:
$ python web2py.py -S welcome -N
>>> db=DAL()
>>> db.define_table('testtable',
... Field('column1'),
... Field('column2'),
... Field('column3','string'))
>>>
db.testtable.import_from_csv_file(open('/Users/massimodipierro/Downloads/example.csv'),null='')
>>> print db(db.testtable).select()
testtable.id,testtable.column1,testtable.column2,testtable.column3
1,record1,<NULL>,<NULL>
2,record2,text,text
3,record3,text,<NULL>
I tested with trunk. Can you try the same example from the shell, as I did?
On Wednesday, 25 July 2012 16:27:44 UTC-5, Mark Li wrote:
>
> I'm using SQLite3 on Windows (running from source). I didn't install
> anything extra, just been using SQLite that came with web2py
>
> I attempted
>
>
> try:
> db.testtable.import_from_csv_file('example.csv', null='')
>
> except:
> print db._lastsql
> db.rollback()
>
>
>
>
> However, print db._lastsql doesn't print anything.
>
> I made a new app and the problem still exists (can't import CSV from shell
> and importing from database administration still gives empty string instead
> of None).
>
>
> Here is my db.py code:
>
>
> db = DAL('sqlite://storage.sqlite')
>
> db.define_table('testtable',
> Field('column1'),
> Field('column2'),
> Field('column3','string'))
>
> From the web2py shell, I use:
>
> db.testtable.import_from_csv_file('example.csv', null='')
>
> which gives me the following error
>
> Traceback (most recent call last):
> File "c:\Users\Mark\Documents\dubliners\web2py\gluon\contrib\shell.py",line
> 233, in run
> exec compiled in statement_module.__dict__
> File "<string>", line 1, in <module>
> File "c:\Users\Mark\Documents\dubliners\web2py\gluon\dal.py", line 6955,
> in import_from_csv_file
> new_id = self.insert(**dict(items))
> File "c:\Users\Mark\Documents\dubliners\web2py\gluon\dal.py", line 6829,
> in insert
> return self._db._adapter.insert(self,self._listify(fields))
> File "c:\Users\Mark\Documents\dubliners\web2py\gluon\dal.py", line 928,
> in insert
> raise e
> OperationalError: near ")": syntax error
>
> The 'example.csv' file is located in my web2py folder.
>
>
>
>
>
> On Wednesday, July 25, 2012 6:23:56 AM UTC-7, Anthony wrote:
>>
>> Can you attach a sample CSV file that fails, and show your table model
>> code as well as any code used to do the import?
>>
>> Anthony
>>
>> On Tuesday, July 24, 2012 11:31:27 PM UTC-4, Mark Li wrote:
>>>
>>> Anthony, I tried
>>>
>>> db.mytable.import_from_csv_file([file object], null='')
>>>
>>> from the web2py shell, but it gave me an error:
>>>
>>> OperationalError: near ")": syntax error
>>>
>>>
>>> In fact, trying to import from csv using the book's method does not work
>>> at all, although exporting works fine.
>>>
>>>
>>> Viniciusban, I'm inserting data through the database administation
>>> interface (filling out the form). I've also tried importing the CSV file
>>> through the administrative interface as well, where all blank spaces in the
>>> csv some out to empty strings (unless the field type is 'integer').
>>>
>>>
>>>
>>> On Monday, July 23, 2012 10:47:24 PM UTC-7, viniciusban wrote:
>>>>
>>>> Yes, they are default settings.
>>>>
>>>> It's quite strange you get empty strings instead null (None, in
>>>> Python).
>>>>
>>>> How are you inserting data into db? Are you using a SQLFORM or
>>>> my_table.validate_and_insert() or simply mytable.insert()?
>>>>
>>>> Do you get same results (blank values) inserting it from web2py shell
>>>> using simply mytable.insert()?
>>>>
>>>> --
>>>> Vinicius Assef
>>>>
>>>>
>>>> > On Monday, July 23, 2012 5:58:19 PM UTC-4, Mark Li wrote:
>>>> >
>>>> > Aren't those the default values for a Field Contructor? I tried
>>>> > explicitly adding "notnull=False" and "required=False", and
>>>> didn't
>>>> > set the default property, but empty values still come out as an
>>>> > empty string instead of None.
>>>> >
>>>> > On Monday, July 23, 2012 2:48:56 PM UTC-7, viniciusban wrote:
>>>> >
>>>> > As far as I know, let "notnull=False" and "required=False"
>>>> for your
>>>> > fields and don't set "default" property.
>>>> >
>>>> >
>>>> >
>>>> > On 07/23/2012 06:32 PM, Mark Li wrote:
>>>> > > Unfortunately the lambda method didn't work, Anthony. Any
>>>> other ideas
>>>> > > for having a None default for empty entries?
>>>> > >
>>>> > >
>>>> > > On a side note, if the 'integer' field type is used, then a
>>>> blank entry
>>>> > > results in a None. Don't know if that helps but it's
>>>> something I've noticed.
>>>> > >
>>>> > > On Monday, July 23, 2012 2:07:51 PM UTC-7, Anthony wrote:
>>>> > >
>>>> > > To enter a value of None, this might work:
>>>> > >
>>>> > > |
>>>> > > default=lambda:None
>>>> > > |
>>>> > >
>>>> > > Anthony
>>>> > >
>>>> > > On Monday, July 23, 2012 5:04:44 PM UTC-4, Anthony
>>>> wrote:
>>>> > >
>>>> > > default=None means that no default is specified,
>>>> not that a
>>>> > > default value of None will be inserted.
>>>> > >
>>>> > > Anthony
>>>> > >
>>>> > > On Monday, July 23, 2012 5:02:33 PM UTC-4, Mark Li
>>>> wrote:
>>>> > >
>>>> > > I have a table defined in the following manner:
>>>> > >
>>>> > > db.define_table('songinfo',
>>>> > > Field('songtitle'),
>>>> > > Field('artist'))
>>>> > >
>>>> > > When I add an empty entry, or upload a CSV with
>>>> empty
>>>> > > values, I can only access those values with a
>>>> database call like
>>>> > >
>>>> > > songs = db(db.songinfo.artist=="").select()
>>>> > >
>>>> > > as opposed to
>>>> db(db.songinfo.artist==None).select()
>>>> > >
>>>> > >
>>>> > > The web2py book states that fields
>>>> default=None, but I'm
>>>> > > getting an empty string. Is there an
>>>> appropriate way to have
>>>> > > None instead of an empty string in the
>>>> database?
>>>> > >
>>>> > >
>>>> > > --
>>>> > >
>>>> > >
>>>> > >
>>>> >
>>>> > --
>>>> >
>>>> >
>>>> >
>>>>
>>>>
--
