I find sometimes the admin shell doesn't work as expected, particularly for
db related operations.
Anthony
On Wednesday, July 25, 2012 6:34:49 PM UTC-4, Mark Li wrote:
>
> 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?
>>>>>> > >
>>>>>> > >
>>>>>> > > --
>>>>>> > >
>>>>>> > >
>>>>>> > >
>>>>>> >
>>>>>> > --
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>>
>>>>>>
--
