Massimo I tried your example and it worked! I was using the web2py web
shell from the admin interface before, but using the interactive console
shell through cygwin worked.
Thanks to you, Anthony, and viniciusban for helping out.
On Wednesday, July 25, 2012 2:51:01 PM UTC-7, Massimo Di Pierro wrote:
>
> 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?
>>>>> > >
>>>>> > >
>>>>> > > --
>>>>> > >
>>>>> > >
>>>>> > >
>>>>> >
>>>>> > --
>>>>> >
>>>>> >
>>>>> >
>>>>>
>>>>>
--
