CODEWITHSUNDEEP
pythonsql
SLFl
SLFl

Reputation: 313

Python - How to insert NULL mysql values

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?

Upvotes: 4

Views: 8157

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121584

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.

Upvotes: 5

Related Questions