Can't insert None into mysql int field

I need to insert an int or None into a MySql int field (which is nullable).
This is my code:

cur = db.cursor()
contents = [None, 3]
for element in contents:
print(element)
cur.execute('insert into test.new_test (blubb) values (%s)', element)
db.commit()


When I don't use None, but another int, this works. I just don't understand, why None does not work, I get the error

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '%s)' at line 1"),

despite all of the solutions that I found for this saying that %s is able to handle None...
Also, is there maybe a way to add the entire list at once (a new row for each entry) without using a for loop?

2 Answers
2

In mysql it should be NULL so you need to change it to:

cur = db.cursor()
contents = ['NULL', 3]
for element in contents:
print(element)
cur.execute('insert into test.new_test (blubb) values (%s)', element)
db.commit()


EDIT

To solve the new isue you can change it to:

cur = db.cursor()
contents = [None, 3]
for element in contents:
print(element)
if element==None:
cur.execute('insert into test.new_test (blubb) values (NULL)')
else:
cur.execute('insert into test.new_test (blubb) values (%s)', element)
db.commit()


Another way is to change the strict mode like it explains here

pymysql.err.InternalError: (1366, "Incorrect integer value: 'NULL' for column 'blubb' at row 1")

Please replace the NONE by NULL. The command delivered to the MySQL server should looks like:

insert into test.new_test (blubb) values (NULL)


Where blubb is a valid column in the table new_test, and, must be allowed NULL values.

Cheers,

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.