Insert empty string ('') for column of float/int type?

Help
Kevin
2014-04-08
2014-04-08
  • Kevin
    Kevin
    2014-04-08

    Assuming I have a data table structured as tb_user(id int primary key, name varchar(15), age int, score float).
    The insert_sql = 'insert into tb_user values(%s, %s, %s, %s)'.
    If the command c.execute(insert_sql, [1, 'John', '', '85.0']) is executed, an exception will be raised saying "Incorrect integer value: '' for column 'age' at row 1".
    If the command c.execute(insert_sql, [1, 'John', '23', '']) is executed, an exception will be raised saying "Data truncated for column 'score' at row 1".
    My question is, when inserting empty string ('') for column of float/int type, how to keep the inserted value NULL (e.g. age = NULL, or score = NULL), instead of raising an exception?
    Thanks for your help!

     
    • jentyk
      jentyk
      2014-04-08

      Hi,

      Have you tried None?

      On 8 Apr 2014, at 14:53, Kevin farewellwho@users.sf.net wrote:

      Assuming I have a data table structured as tb_user(id int primary key, name varchar(15), age int, score float).
      The insert_sql = 'insert into tb_user values(%s, %s, %s, %s)'.
      If the command c.execute(insert_sql, [1, 'John', '', '85.0']) is executed, an exception will be raised saying "Incorrect integer value: '' for column 'age' at row 1".
      If the command c.execute(insert_sql, [1, 'John', '23', '']) is executed, an exception will be raised saying "Data truncated for column 'score' at row 1".
      My question is, when inserting empty string ('') for column of float/int type, how to keep the inserted value NULL (e.g. age = NULL, or score = NULL), instead of raising an exception?
      Thanks for your help!

      Insert empty string ('') for column of float/int type?

      Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/mysql-python/discussion/70461/

      To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

       
      Attachments
      • David Woods
        David Woods
        2014-04-08

        You might also try "age IS NULL" and "score IS NULL" in your SQL.

        David

        On 04/08/2014 10:20 AM, jentyk wrote:

        Hi,

        Have you tried None?

        On 8 Apr 2014, at 14:53, Kevin farewellwho@users.sf.net
        farewellwho@users.sf.net wrote:

        Assuming I have a data table structured as tb_user(id int primary
        key, name varchar(15), age int, score float).
        The insert_sql = 'insert into tb_user values(%s, %s, %s, %s)'.
        If the command c.execute(insert_sql, [1, 'John', '', '85.0']) is
        executed, an exception will be raised saying "Incorrect integer
        value: '' for column 'age' at row 1".
        If the command c.execute(insert_sql, [1, 'John', '23', '']) is
        executed, an exception will be raised saying "Data truncated for
        column 'score' at row 1".
        My question is, when inserting empty string ('') for column of
        float/int type, how to keep the inserted value NULL (e.g. age =
        NULL, or score = NULL), instead of raising an exception?
        Thanks for your help!
        
        Insert empty string ('') for column of float/int type?
        
        Sent from sourceforge.net because you indicated interest in
        https://sourceforge.net/p/mysql-python/discussion/70461/
        
        To unsubscribe from further messages, please visit
        https://sourceforge.net/auth/subscriptions/
        

        Insert empty string ('') for column of float/int type?
        http://sourceforge.net/p/mysql-python/discussion/70461/thread/d8e95d91/?limit=50#14d6/c9af


        Sent from sourceforge.net because you indicated interest in
        https://sourceforge.net/p/mysql-python/discussion/70461/

        To unsubscribe from further messages, please visit
        https://sourceforge.net/auth/subscriptions/

        --
        David K. Woods, Ph.D.
        Researcher, Transana Lead Developer
        Wisconsin Center for Education Research
        University of Wisconsin, Madison
        http://www.transana.org

         
        Attachments
  • Andy Dustman
    Andy Dustman
    2014-04-08

    Python None == SQL NULL