Description:
Not all insert statements have VALUES ('%s'), for example INSERT SELECT does not.
Easy fix, if VALUES regex does not match just execute the regular flow of execution.
I used python 2.7 and only tested my fix on that.
How to repeat:
data = [
('Jane','555-001', 'Munich'),
('Joe', '555-001', 'San Francisco'),
('John', '555-003', 'Moscow')
]
stmt = '''INSERT INTO employees (name, phone, city_id)
SELECT %s, %s, cities.city_id
FROM cities
WHERE cities.name=%s'''
Suggested fix:
--- a/python2/mysql/connector/cursor.py
+++ b/python2/mysql/connector/cursor.py
@@ -412,6 +412,18 @@ class MySQLCursor(CursorBase):
]
stmt = "INSERT INTO employees (name, phone) VALUES ('%s','%s')"
cursor.executemany(stmt, data)
+
+ or
+
+ data = [
+ ('Jane','555-001', 'Munich'),
+ ('Joe', '555-001', 'San Francisco'),
+ ('John', '555-003', 'Moscow')
+ ]
+ stmt = '''INSERT INTO employees (name, phone, city_id)
+ SELECT %s, %s, cities.city_id
+ FROM cities
+ WHERE cities.name=%s'''
INSERT statements are optimized by batching the data, that is
using the MySQL multiple rows syntax.
@@ -431,12 +443,13 @@ class MySQLCursor(CursorBase):
if re.match(RE_SQL_INSERT_STMT,operation):
opnocom = re.sub(RE_SQL_COMMENT, '', operation)
m = re.search(RE_SQL_INSERT_VALUES, opnocom)
- fmt = m.group(1)
- values = []
- for params in seq_params:
- values.append(fmt % self._process_params(params))
- operation = operation.replace(m.group(1), ','.join(values), 1)
- return self.execute(operation)
+ if m:
+ fmt = m.group(1)
+ values = []
+ for params in seq_params:
+ values.append(fmt % self._process_params(params))
+ operation = operation.replace(m.group(1), ','.join(values), 1)
+ return self.execute(operation)
rowcnt = 0
Description: Not all insert statements have VALUES ('%s'), for example INSERT SELECT does not. Easy fix, if VALUES regex does not match just execute the regular flow of execution. I used python 2.7 and only tested my fix on that. How to repeat: data = [ ('Jane','555-001', 'Munich'), ('Joe', '555-001', 'San Francisco'), ('John', '555-003', 'Moscow') ] stmt = '''INSERT INTO employees (name, phone, city_id) SELECT %s, %s, cities.city_id FROM cities WHERE cities.name=%s''' Suggested fix: --- a/python2/mysql/connector/cursor.py +++ b/python2/mysql/connector/cursor.py @@ -412,6 +412,18 @@ class MySQLCursor(CursorBase): ] stmt = "INSERT INTO employees (name, phone) VALUES ('%s','%s')" cursor.executemany(stmt, data) + + or + + data = [ + ('Jane','555-001', 'Munich'), + ('Joe', '555-001', 'San Francisco'), + ('John', '555-003', 'Moscow') + ] + stmt = '''INSERT INTO employees (name, phone, city_id) + SELECT %s, %s, cities.city_id + FROM cities + WHERE cities.name=%s''' INSERT statements are optimized by batching the data, that is using the MySQL multiple rows syntax. @@ -431,12 +443,13 @@ class MySQLCursor(CursorBase): if re.match(RE_SQL_INSERT_STMT,operation): opnocom = re.sub(RE_SQL_COMMENT, '', operation) m = re.search(RE_SQL_INSERT_VALUES, opnocom) - fmt = m.group(1) - values = [] - for params in seq_params: - values.append(fmt % self._process_params(params)) - operation = operation.replace(m.group(1), ','.join(values), 1) - return self.execute(operation) + if m: + fmt = m.group(1) + values = [] + for params in seq_params: + values.append(fmt % self._process_params(params)) + operation = operation.replace(m.group(1), ','.join(values), 1) + return self.execute(operation) rowcnt = 0