0

I have created a table which has a default NULL field of type int. I need to insert data using a parameter query, but I am unable to pass NULL via variable into int field.

import sys
import os
import datetime
import logging
import rds_config
import pymysql

conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5,  sql_mode="")

def mk_int (value):
    if value in (None, "",''):
        return 'NULL'
    else:
        return int(value)

blankStrOfInt = ''

insertSQL   = "INSERT INTO `patientinsight`.`Employee`(`id`,`LastName`,`FirstName`, `DepartmentCode`) VALUES (6, 'Doe', 'John', %s)"
conn.cursor().execute(insertSQL,mk_int(blankStrOfInt))
conn.commit()

I have tried the following as return from mk_int

  • return NULL
  • return 'NULL'
  • return None
  • return ''

But of the above allows inserting NULL into the field.

  • When I return 'NULL' from mk_int .. it gives a warning of type mismatch and inserts 0 instead of NULL.
jumanji
  • 1
  • 2
  • try this [https://stackoverflow.com/questions/5507948/how-can-i-insert-null-data-into-mysql-database-with-python](https://stackoverflow.com/questions/5507948/how-can-i-insert-null-data-into-mysql-database-with-python) – Orpheus Dec 28 '18 at 12:38

1 Answers1

0

I suggest using Python None as the bind value.

I think this could be achieved by returning None in place of 'NULL' in the current code, here:

if value in (None, "",''):
    return None

I can't explain why that wouldn't work. But I'm not a Python expert.

As a test for debugging, I'd try using None in place of the call to the function, here: 

  conn.cursor().execute(insertSQL,None)

EDIT

The test returns SQL error 1604 ... syntax to use near '%s'

That indicates the SQL text sent to MySQL includes the %s placeholder.

So that means execute didn't do a bind of the value.

Probably because it didn't see a list of values to bind, it saw None and didn't do anything.

Let's trying giving execute a list of values. Then execute will see that gave it something. We gave it a list, not just None. And the first item in the list is None.

Let's try adding parens:

  conn.cursor().execute(insertSQL, ( None ) )
                                   ^      ^

If we get that to work, then we can test replacing None with the call to the function (wrapped in parens).

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I had already tried using 'None' and get the following error. (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"): ProgrammingError – jumanji May 03 '18 at 19:52
  • maybe adding parens? **`.execute(insertSql, ( None ) )`** The error message indicates that the sql text getting sent to MySQL includes a literal `%s`, so that must mean that `execute` isn't doing a bind (replacing the placeholder with a value.). So, how do we get `execute` to recognize that a bind needs to be done? `execute` is probably expecting a list of values. So let's give it a list. A list containing one value. If that doesn't work, we'll try something else... – spencer7593 May 03 '18 at 22:29