None null mysql,在准备的INSERT语句中无法在python mysql.connector中使用None（NULL）值

When trying to use prepared cursor and insert NULL values the mysql.connector reports an error:

mysql.Error: 1210 (HY000): Incorrect arguments to mysqld_stmt_execute

Here is a code that shows this exactly.

from __future__ import print_function

import mysql.connector

def execsql(cursor, sqlstmt):

try:

cursor.execute(sqlstmt)

except mysql.connector.Error as e:

print("mysql.Error: %s" % e)

print(cursor._executed, '\n')

def execsqlwithparams(cursor, sqlstmt, params):

try:

cursor.execute(sqlstmt, params)

except mysql.connector.Error as e:

print("mysql.Error: %s" % e)

print(cursor._executed, "params:", params, '\n')

def main():

print("mysql.connector.__version__", mysql.connector.__version__)

db = mysql.connector.connect(db="test")

print("mysql.db. get server version", db.get_server_version())

c1 = db.cursor()

c2 = db.cursor(prepared=False)

c3 = db.cursor(prepared=True)

execsql(c1, "drop table if exists test1")

execsql(c1, "create table test1 (col1 int not null, col2 int null, primary key(col1))")

print("insert with pure SQL")

execsql(c1, "insert into test1(col1,col2) values (1, 1)")

execsql(c1, "insert into test1(col1,col2) values (2, NULL)")

print("insert with prepared statement format %s")

sql = "insert into test1(col1,col2) values (%s, %s)"

execsql(c2, sql)

execsqlwithparams(c2, sql, (20, 20))

execsqlwithparams(c2, sql, (21, None))

print("insert with prepared statement format %s using prepared cursor")

sql = "insert into test1(col1,col2) values (%s, %s)"

execsql(c3, sql)

execsqlwithparams(c3, sql, (30, 30))

execsqlwithparams(c3, sql, (31, None))

execsql(c1, "select * from test1")

row = c1.fetchone()

while row:

print(row)

row = c1.fetchone()

db.close()

if __name__ == '__main__':

status = main()

The output is like this:

mysql.connector.__version__ 1.2.2

mysql.db. get server version (5, 5, 37)

drop table if exists test1

create table test1 (col1 int not null, col2 int null, primary key(col1))

insert with pure SQL

insert into test1(col1,col2) values (1, 1)

insert into test1(col1,col2) values (2, NULL)

insert with prepared statement format %s

mysql.Error: 1064 (42000): 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, %s)' at line 1

insert into test1(col1,col2) values (%s, %s)

insert into test1(col1,col2) values (20, 20) params: (20, 20)

insert into test1(col1,col2) values (21, NULL) params: (21, None)

insert with prepared statement format %s using prepared cursor

insert into test1(col1,col2) values (%s, %s)

insert into test1(col1,col2) values (%s, %s) params: (30, 30)

mysql.Error: 1210 (HY000): Incorrect arguments to mysqld_stmt_execute

insert into test1(col1,col2) values (%s, %s) params: (31, None)

select * from test1

(1, 1)

(2, None)

(20, 20)

(21, None)

(30, 30)

The unexpected result is this:

mysql.Error: 1210 (HY000): Incorrect arguments to mysqld_stmt_execute

insert into test1(col1,col2) values (%s, %s) params: (31, None)

And missing row (31, None) from the database.

解决方案

Your problem related with the None passed in the last case can be caused by the fact that the None is not being interpreted as the type defined in your table.