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,
ininsert
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?
>>> > >
>>> > >
>>> > > --
>>> > >
>>> > >
>>> > >
>>> >
>>> > --
>>> >
>>> >
>>> >
>>>
>>>
--
testtable.id,testtable.column1,testtable.column2,testtable.column3
1,record1,,
2,record2,text,text
3,record3,text,
