2

This is my version of SQL command line. 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 122
Server version: 5.0.95 Source distribution

I have a table, and from Python, I want to write the last column as a null. Eventually, the last column of a particular row will get a date stamp I will use as a flag not to process that row again.

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Action        | char(1)     | NO   |     | R       |       | 
| EndpointId    | int(11)     | NO   | MUL | NULL    |       | 
| DeviceType    | smallint(6) | NO   |     | NULL    |       | 
| ChannelNumber | smallint(6) | NO   |     | 0       |       | 
| ActionDate    | date        | YES  |     | NULL    |       | 
| InsertDate    | date        | YES  |     | NULL    |       | 
+---------------+-------------+------+-----+---------+-------+

How do I put a null into the insert query string? In other words, I don't know how to represent NULL in a Python query string.

sql_cmd = \
                """insert into cs_remove
                (
                Action, EndpointId, DeviceType, ChannelNumber, ActionDate, InsertDate
                )
                VALUES 
                (
                %s, %s, %s, %s, %s, %s
                )
                """

            print(base_date)
            sql_data = ('R', ept_id, ept_type, ept_ch, "NULL", base_date);

The example winds up putting 0000-00-00 into the date string. I want NULL. I can do this from the mysql command line, but not from Python. Any help would be appreciated.

octopusgrabbus
  • 10,555
  • 15
  • 68
  • 131

1 Answers1

5

You should use the None value in python to represent the NULL mysql value you are attempting to insert. Like so:

sql_data = ('R', ept_id, ept_type, ept_ch, base_date, None);

Here is a reference to another post with this problem: https://bytes.com/topic/python/answers/166025-python-mysql-insert-null

As well as this post: How can I insert NULL data into MySQL database with Python?

sadmicrowave
  • 39,964
  • 34
  • 108
  • 180