Uploaded image for project: 'MariaDB Connector/Python'
  1. MariaDB Connector/Python
  2. CONPY-61

executemany() doesn't allow inserting optional entries

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 0.9.57
    • 0.9.58
    • Generic
    • None
    • Archlinux 5.6.8-arch1-1 x86_64
      Python 3.8.2
      python-mariadb 0.9.57
      Server version: 10.4.12-MariaDB Arch Linux

    Description

      Description:

      When inserting multiple entries into a column that allows NULL values executemany() throws a DataError. This only happens if some of the tuples contain values with the coresponding type and some are of type None. If all values are None or if all values are not None and have the proper type, this error doesn't occur.

      Steps to reproduce:

      1. create a table with a column that allows NULL values

      CREATE OR REPLACE TABLE `some_table` (`some_data` int(10) NULL);

      2. use executemany() to insert values that can either be of type int (in this example) or None

      conn = mariadb.connect(**config)
      		 
      cur = conn.cursor()
      		 
      cur.executemany("INSERT INTO `some_table` (`some_data`) VALUES (?)", [(1,), (None,), (2,)])
      		 
      conn.close()

      Expected result:

      Either a proper exception that describes why None cannot be inserted together with the proper type or insertion of NULL values.

      Actual result:

      A mariadb.DatabaseError.DataError with the message "Invalid parameter type at row 2, column 1".

      (Another example can be found on StackOverflow)

      Attachments

        Activity

          georg Georg Richter added a comment -

          Thanks for your bug report.

          It seems like a regression bug, since the check for indicator variable was removed in a previous commit.

          Added the following test (using None and/or indicators):

           con= create_connection()
          		 
                  cursor=con.cursor()
          		 
                  cursor.execute("CREATE TEMPORARY TABLE ind1 (a int, b int default 2,c int)")
          		 
                  vals = [(1,4,3),(None, 2, 3)]
          		 
                  cursor.executemany("INSERT INTO ind1 VALUES (?,?,?)", vals)
          		 
                  cursor.execute("SELECT a, b, c FROM ind1")
          		 
                  row= cursor.fetchone()
          		 
                  self.assertEqual(row[0], 1)
          		 
                  row= cursor.fetchone()
          		 
                  self.assertEqual(row[0], None)
          		 
                  cursor.execute("DELETE FROM ind1")
          		 
                  vals=[(1,4,3), (mariadb.indicator_null, mariadb.indicator_default, None)]
          		 
           
          		 
                  cursor.executemany("INSERT INTO ind1 VALUES (?,?,?)", vals)
          		 
                  cursor.execute("SELECT a, b, c FROM ind1")
          		 
                  row= cursor.fetchone()
          		 
                  self.assertEqual(row[0], 1)
          		 
                  row= cursor.fetchone()
          		 
                  self.assertEqual(row[0], None)
          		 
                  self.assertEqual(row[1], 2)
          		 
                  self.assertEqual(row[2], None)

          georg Georg Richter added a comment - Thanks for your bug report. It seems like a regression bug, since the check for indicator variable was removed in a previous commit. Added the following test (using None and/or indicators): con = create_connection() cursor = con.cursor() cursor.execute( "CREATE TEMPORARY TABLE ind1 (a int, b int default 2,c int)" ) vals = [( 1 , 4 , 3 ),( None , 2 , 3 )] cursor.executemany( "INSERT INTO ind1 VALUES (?,?,?)" , vals) cursor.execute( "SELECT a, b, c FROM ind1" ) row = cursor.fetchone() self .assertEqual(row[ 0 ], 1 ) row = cursor.fetchone() self .assertEqual(row[ 0 ], None ) cursor.execute( "DELETE FROM ind1" ) vals = [( 1 , 4 , 3 ), (mariadb.indicator_null, mariadb.indicator_default, None )]   cursor.executemany( "INSERT INTO ind1 VALUES (?,?,?)" , vals) cursor.execute( "SELECT a, b, c FROM ind1" ) row = cursor.fetchone() self .assertEqual(row[ 0 ], 1 ) row = cursor.fetchone() self .assertEqual(row[ 0 ], None ) self .assertEqual(row[ 1 ], 2 ) self .assertEqual(row[ 2 ], None )

          People

            georg Georg Richter
            RobinWA Robin Wismeth
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.