CODEWITHSUNDEEP
pythonmysqldatabasemariadbsql-insert
RobinW
RobinW

Reputation: 322

mariadb-connector executemany throws DataError

I'm trying to implement a few simple SQL insert statements with python-mariadb-connector but cannot figure out what I'm doing wrong.

The database looks like this:

SET FOREIGN_KEY_CHECKS = false;

CREATE OR REPLACE TABLE `forums` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE OR REPLACE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE OR REPLACE TABLE `posts` (
  `id` int(10) unsigned NOT NULL,
  `forum_id` int(10) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `posts_forum_fk` (`forum_id`),
  KEY `posts_account_fk` (`account_id`),
  CONSTRAINT `posts_account_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  CONSTRAINT `posts_forum_fk` FOREIGN KEY (`forum_id`) REFERENCES `forums` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE OR REPLACE TABLE `comments` (
  `id` int(10) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `comments_post_fk` (`post_id`),
  KEY `comments_account_fk` (`account_id`),
--  KEY `comments_comments_fk` (`parent_id`),
--  CONSTRAINT `comments_comments_fk` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`),
  CONSTRAINT `comments_account_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  CONSTRAINT `comments_post_fk` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = true;

The code for inserting data looks like this:

import mariadb


config = {
    "user": "db_user_name",
    "password": "db_passwd",
    "host": "db_host",
    "port": 3306,
    "database": "db_name"
}


if __name__ == '__main__':
    with mariadb.connect(**config) as conn:
        cur = conn.cursor()
        cur.executemany(
            "INSERT INTO `forums` (`id`) VALUES (?)",
            [(1,), (2,), (3,)]
        )
        cur.executemany(
            "INSERT INTO `accounts` (`id`) VALUES (?)",
            [(1,), (2,), (3,), (4,)]
        )
        cur.executemany(
            "INSERT INTO `posts` (`id`, `forum_id`, `account_id`) VALUES (?, ?, ?)",
            [(6, 3, 1)]
        )
        cur.executemany(
            "INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (?, ?, ?, ?)",
            [(1, 6, 1, None), (2, 6, 2, 1)]
        ) # exception happens here

When executing this, I get the following error:

Traceback (most recent call last):
  File ".../db_test.py", line 28, in <module>
    cur.executemany(
mariadb.DatabaseError.DataError: Invalid parameter type at row 2, column 4

Im not sure how executemany is implemented but I think it should do something similar to the following SQL-query:

INSERT INTO `forums` (`id`) VALUES (1), (2), (3);

INSERT INTO `accounts` (`id`) VALUES (1), (2), (3), (4);

INSERT INTO `posts` (`id`, `forum_id`, `account_id`) VALUES (6, 3, 1);

INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`)
VALUES (1, 6, 1, NULL), (2, 6, 2, 1);

which works just fine for me...

Is it a bug or am I doing something wrong here?

Upvotes: 2

Views: 3602

Answers (1)

nbk
nbk

Reputation: 49375

It took me a while

    cur.executemany(
        "INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (?, ?, ?, ?)",
        [(1, 6, 1, None), (2, 6, 2, 1)]
    )

But in your comment table, you have this constraint


CONSTRAINT `comments_comments_fk` FOREIGN KEY (`parent_id`)
REFERENCES `comments` (`id`),

Before you can enter (2, 6, 2, 1) the tuple (1, 6, 1, None) has to already commit in the database and in a bulk insert the commit is made after all inserts are in the database, but the first tuple isn't at the time not there

so if you make this, both rows will appear in the database(i also committed all other tables after bulk insert):

MariaDB

mycursor.execute(
    "INSERT INTO `comments` (`id`, `post_id`, `account_id`, 
     `parent_id`) VALUES (?, ?, ?, ?)",
        (1, 6, 1,None ))
     mydb.commit()    
     mycursor.executemany(
        "INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (?, ?, ?, ?)",
        [ (2, 6, 2, 1)])    
     mydb.commit()

MySQL

     sql = """INSERT INTO forums (id) VALUES (%s)"""
     val = [("1",), ("2",), ("3",)]
     mycursor.executemany(sql,val)
     mydb.commit()
     mycursor.executemany( 
        "INSERT INTO accounts (id) VALUES (%s)",
        [(1,), (2,), (3,), (4,)]
     )
     mydb.commit()
     mycursor.executemany(
        "INSERT INTO posts (id, forum_id, account_id) VALUES (%s, %s, %s)",
        [(6, 3, 1)]
     )
     mydb.commit()
     mycursor.execute(
        "INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (%s, %s, %s, %s)",
        (1, 6, 1,None ))
     mydb.commit()    
     mycursor.executemany(
        "INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (%s, %s, %s, %s)",
        [ (2, 6, 2, 1)])    
     mydb.commit()

Upvotes: 1

Related Questions