Trying to INSERT None results in CompileError

[jdabtieu]

I'm working on a project where sometimes I have to insert a None value into the database. This would be done in a way like this:

something = None
otherthing = 2
...  # doing some stuff
db.execute("INSERT INTO some_table (col1, col2) VALUES (?, ?)", something, otherthing)

The database schema allows the column to be NULL.

However, this results in an error: sqlalchemy.exc.CompileError: Don't know how to render literal SQL value: None

I tried testing just this insert statement by itself, on a fresh install, just to be sure.

python3 -m venv random
cd random
touch test.db
. bin/activate
pip install cs50
python
>>> import cs50
>>> db = cs50.SQL("sqlite:///test.db")
>>> db.execute("INSERT INTO test VALUES(?)", None)

which resulted in the following traceback:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/tmp/random/lib/python3.8/site-packages/cs50/sql.py", line 21, in decorator
    return f(*args, **kwargs)
  File "/tmp/random/lib/python3.8/site-packages/cs50/sql.py", line 178, in execute
    _args = ", ".join([str(self._escape(arg)) for arg in args])
  File "/tmp/random/lib/python3.8/site-packages/cs50/sql.py", line 178, in <listcomp>
    _args = ", ".join([str(self._escape(arg)) for arg in args])
  File "/tmp/random/lib/python3.8/site-packages/cs50/sql.py", line 475, in _escape
    return __escape(value)
  File "/tmp/random/lib/python3.8/site-packages/cs50/sql.py", line 465, in __escape
    sqlalchemy.types.NullType().literal_processor(self._engine.dialect)(value))
  File "/tmp/random/lib/python3.8/site-packages/sqlalchemy/sql/sqltypes.py", line 3169, in process
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Don't know how to render literal SQL value: None

The problem seems to be with line 465 in sql.py (from manual testing and the traceback) with sqlalchemy.types.NullType().literal_processor(self._engine.dialect)(value)

Trying to call sqlalchemy.types.NullType().literal_processor(self._engine.dialect)(None) directly results in the same error.

The problem can be fixed by changing

    return sqlparse.sql.Token(
        sqlparse.tokens.Keyword,
        sqlalchemy.types.NullType().literal_processor(self._engine.dialect)(value))

to

    return sqlparse.sql.Token(
        sqlparse.tokens.Keyword,
        "NULL")

since None is always going to be NULL.

I've gotten the same problem on both Ubuntu 20.04 and Windows 10, using the same setup as I described above. Both were running Python 3.8.5 and cs50 6.0.4.

[kzidane]

Thank you. Should be fixed in 7.0.1.

[victorabarros]

Hi @kzidane and @dmalan
I'm having the same problem with version 9.2.0:

root@43095595abf1:/cs50chain/app# pip3 list | grep cs50
cs50         9.2.0
root@43095595abf1:/cs50chain/app# python3
Python 3.10.4 (main, May 28 2022, 13:14:58) [GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from cs50 import SQL
>>> db = SQL("sqlite:///database.db")
>>> db.execute("select * from test")
[]
>>> db.execute("insert into test values (?, ?)", 1, 'null string')
1
>>> db.execute("select * from test")
[{'id': 1, 'data': 'null string'}]
>>> db.execute("insert into test values (?, ?)", 2, None)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.10/site-packages/cs50/sql.py", line 28, in decorator
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/cs50/sql.py", line 190, in execute
    _args = ", ".join([str(self._escape(arg)) for arg in args])
  File "/usr/local/lib/python3.10/site-packages/cs50/sql.py", line 190, in <listcomp>
    _args = ", ".join([str(self._escape(arg)) for arg in args])
  File "/usr/local/lib/python3.10/site-packages/cs50/sql.py", line 489, in _escape
    return __escape(value)
  File "/usr/local/lib/python3.10/site-packages/cs50/sql.py", line 479, in __escape
    sqlalchemy.types.NullType().literal_processor(self._engine.dialect)(value))
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/sqltypes.py", line 3237, in process
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Don't know how to render literal SQL value: None 

[dmalan]

Sorry about that, pip install cs50 --upgrade should fix!

[victorabarros]

Sorry about that, pip install cs50 --upgrade should fix!

Perfect!
Thank you, David. You're the best!