CODEWITHSUNDEEP
pythonmysqlmysql-python
dakov
dakov

Reputation: 1139

Connector/python executemany NoneType

I have a little problem with Connector/python executing insert query.

I have function to insert data into database. Argument data is list of tuples: [(2652884, 'http://www.example.com/', '5.0.5.239', 1), ..... ]

def insert_url(self, data):

    query = "INSERT INTO `sp_urls` (`parent_id`, `url`, `version`, `hits`) VALUES (%d, %s, %s, %d) ON DUPLICATE KEY UPDATE url=url"        


    try:
        cursor = self.__cnx.cursor()
        cursor.executemany(query, data)
    except (mysql.connector.errors.IntegrityError) as err:
        print("Query syntax error:", err, file=sys.stderr)
    except (mysql.connector.errors.ProgrammingError) as err:
        print("Programming err:{0}".format(err))
    finally:
        cursor.close()

The query itself works, in self.__cnx is initialized connection. Here's the traceback:

  File "sdi/database/DbValidator.py", line 91, in process_records
    self.__driver.insert_url(urldata)
  File "/home/david/workspace/stickydi/sdi/database/MySQLDriver.py", line 87, in insert_url
    cursor.executemany(query, data)
  File "/usr/lib/python3/dist-packages/mysql/connector/cursor.py", line 492, in executemany
    return self._batch_insert(operation,seq_params)
  File "/usr/lib/python3/dist-packages/mysql/connector/cursor.py", line 428, in _batch_insert
    fmt = m.group(1).encode(self._connection.charset)
AttributeError: 'NoneType' object has no attribute 'group'

I have one very similar method, and it works OK, I just can't see, why does executemany( ) goes wrong.

Upvotes: 1

Views: 2457

Answers (2)

unutbu
unutbu

Reputation: 880627

Use only %s as the parameter marker in query. Do not use %d:

query = """
    INSERT INTO `sp_urls` (`parent_id`, `url`, `version`, `hits`) 
    VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE url=url"""

The %s is the format paramstyle defined in the DB-API. It does not have the same meaning as %s in string formatting.

The correct paramstyle to use depends on the database driver. MySQLdb uses %s. Other database drivers such as oursql and sqlite3 use ?.

Upvotes: 4

Martijn Pieters
Martijn Pieters

Reputation: 1124238

You should not use %d for SQL parameters. Stick to %s and let the MySQL connector handle the types:

query = """\
    INSERT INTO `sp_urls` (`parent_id`, `url`, `version`, `hits`) 
    VALUES (%s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE url=url
    """

Quoting from the Python-MySQL documentation:

paramstyle
String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.

Granted, using %s for SQL parameters is confusingly similar to Python string formatting, but it is not the same.

Upvotes: 2

Related Questions