How to have a null value for MySQL in Python

Question

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.

Try using None.

wpercy
– wpercy

2017-09-15 16:46:57 +00:00
Commented Sep 15, 2017 at 16:46 — wpercy
– wpercy, Commented Sep 15, 2017 at 16:46

sadmicrowave · Accepted Answer · 2017-09-15 17:27:32Z

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?

answered Sep 15, 2017 at 17:27

sadmicrowave

41k34 gold badges116 silver badges184 bronze badges

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

1 Comment

octopusgrabbus Over a year ago

I had a feeling it might be None. I'll go try this. Thanks.

Collectives™ on Stack Overflow

How to have a null value for MySQL in Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related