How to handle None with mysql.connector

How to handle None with mysql.connector - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: How to handle None with mysql.connector (/thread-14139.html)

How to handle None with mysql.connector - verb - Nov-16-2018

Hello everyone, Can anyone advice how to handle empty string or None when insert data in mysql integer column.
Here is the code i have:

import mysql.connector
cnx = mysql.connector.connect(user='user',
                                  password='pass',
                                  host='192.168.18.1',
                                  database='db')
data = None
conn = cnx.cursor()
conn.execute("insert into contract(red) values('{}')".format(data))
cnx.commit()

and here is the otput :

mysql.connector.errors.DataError: 1366 (22007): Incorrect integer value: 'None' for column 'red' at row 1

Problem is that i don't know when the data would be integer or None.

RE: How to handle None with mysql.connector - buran - Nov-16-2018

You have two alternative options/paradigms: Ask for permission or Ask forgiveness, not permission

In the former you would check that data is indeed what you expect it to be, e.g. using if block
In the later you will use try/except block to handle gracefully the error if there is any

In most cases in python the preferred approach is the second one

RE: How to handle None with mysql.connector - verb - Nov-16-2018

Thanks for the reply.
The problem is that mysql.connector doesn't convert None or empty string to NULL so here is how i did it:

data = None or "NULL"
conn = cnx.cursor()
conn.execute("insert into contract(red) values({}) ".format(str(data)))
cnx.commit()

this way even if red column is integer it will insert the data correctly. It would be great if there is an easier way to do it.