Python - How to insert NULL mysql values

Question

I'm generating a sql file with insert statements with a python script, but I don't know how to insert NULL values.

I have a dictionary where keys are the name of the columns and the values the values I want to insert. For example:

d = {"column1":"1", "column2":"test", "column3":None}

Then I want to use that values in my insert statement.

sqlfile.write("INSERT INTO test VALUES ('" + d["column1"] + "', '" + d["column2"] + "', '" + d["column3"] + "')")

But this will return an error because non type and strings cannot be concatenated and if I replace None by 'NULL' it will insert a 0 in database.

How can I solve this?

Why did you put quotes around the NULL then? sqlfile.write("INSERT INTO test VALUES ('1','test', NULL)") inserts an actual NULL. — Martijn Pieters
– Martijn Pieters, Commented Apr 27, 2016 at 18:33
Sorry @MartijnPieters I did not explain well. The question was edited :) — SLFl
– SLFl, Commented Apr 27, 2016 at 18:44

Martijn Pieters · Accepted Answer · 2016-04-27 20:08:00Z

5

You are inserting the string 'NULL', not the NULL value. If these values are coming from a Python structure, you need to use something else to map to the NULL value in SQL.

You could use None for this, and only quote other values:

def sqlquote(value):
    """Naive SQL quoting

    All values except NULL are returned as SQL strings in single quotes,
    with any embedded quotes doubled.

    """
    if value is None:
         return 'NULL'
    return "'{}'".format(str(value).replace("'", "''"))

sql = "INSERT INTO test VALUES ({column1}, {column2}, {column3})".format(
    **{k: sqlquote(v) for k, v in d.items()})

Note that because you have to handle None differently, you also have to handle proper SQL quoting! If any of your values directly or indirectly come from user-supplied data, you'd be open for SQL injection attacks otherwise.

The above sqlquote() function should suffice for SQLite strings, which follow standard SQL quoting rules. Different databases have different rules for this, so tripple check your documentation.

Personally, I'd use the SQLAlchemy library to generate SQL and have it handle quoting for you. You can configure it to produce SQL for different database engines.

edited Apr 27, 2016 at 20:08

answered Apr 27, 2016 at 18:34

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SLFl Over a year ago

Sorry @MartijnPieters I did not explain well. The question was edited :)

Martijn Pieters Over a year ago

@F.Rita: no, you didn't. That was a rather big detail you omitted. Updated.

Martijn Pieters Over a year ago

Add sqlfile.write(sql) to write out the resulting string.

Collectives™ on Stack Overflow

Python - How to insert NULL mysql values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related