Empty Database value is not None, but an empty string

[Mark Li]

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?

[Anthony]

default=None means that no default is specified, not that a default value of None will be inserted.

Anthony

[Anthony]

To enter a value of None, this might work:

default=lambda: None

Anthony

[Mark Li]

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.

[vinic...@gmail.com]

As far as I know, let "notnull=False" and "required=False" for your
fields and don't set "default" property.

> --
>
>
>

[Mark Li]

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.

[Anthony]

From the code, it looks like doing an insert without specifying a field should result in a NULL, not an empty string. However, I think a CSV file with empty fields is handled differently. The .import_from_csv_file() method takes a "null" argument that defaults to "<NULL>" -- so, I think if the CSV file contains a "<NULL>", it will be converted to a NULL in the database insert -- otherwise the value will be inserted as is (presumably even an empty string). To convert empty strings to NULL's, perhaps you just have to do:

db.mytable.import_from_csv_file([file object], null='')

Does that work?

Anthony

[vinic...@gmail.com]

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

> --
>
>
>

[Mark Li]

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').

[Massimo Di Pierro]

What database are you using? What driver?

The operational error is not a web2py error. It is a database error. It means web2py is sending invalid SQL, for example referring a column that does not exist.

You may want to try:

try:

   db.mytable.import_from_csv_file([file object], null='')

except:

   print db._lastsql

   db.rollback()

and see what is lastsql.

[vinic...@gmail.com]

Sorry, but I cannot figure out why this is happening.

Here, my data becomes None if I do this. I'm using SQLite.
If you define a fresh new table in your db.py with just one string
field, just using default configs, this problem still happens?

--
Vinicius Assef

On 07/25/2012 12:31 AM, Mark Li wrote:
> Anthony, I tried
>
> |

> db.mytable.import_from_csv_file([file object],null='')

> --
>
>
>

[Anthony]

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

[Mark Li]

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.

[Massimo Di Pierro]

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?

[Mark Li]

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.

[Anthony]

I find sometimes the admin shell doesn't work as expected, particularly for db related operations.

Anthony