Can't insert None into mysql int field

Question

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?

nacho · Accepted Answer · 2018-07-01T17:47:03.073

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

edited Jul 01 '18 at 17:47

answered Jul 01 '18 at 17:37

nacho

5,280
2
25
34

if I do that, I get `pymysql.err.InternalError: (1366, "Incorrect integer value: 'NULL' for column 'blubb' at row 1")` – LizzAlice Jul 01 '18 at 17:39
Contents = ['',3]? – Bleach Jul 01 '18 at 17:42
pymysql.err.InternalError: (1366, "Incorrect integer value: '' for column 'blubb' at row 1") – LizzAlice Jul 01 '18 at 17:45

score 0 · Answer 2 · answered Jul 01 '18 at 17:41

0

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,

answered Jul 01 '18 at 17:41

Alexandre Almeida

34
2

How would I do that? If I try to exchange None for 'Null' or '', I get an error... (See other answer) – LizzAlice Jul 01 '18 at 17:44

Can't insert None into mysql int field

2 Answers2