Python MySQLdb is not inserting all the records

Question

I'm stuck working on an Python script. This script should to populate a MySQL table getting values from select statement.

The problem is when I get the values from the select statement, insert into statement doesn't work correctly and not insert all the records. I have check the same select statement in MySQL WorkBench and returns the correct number of records that I want to put in the destination table. The select statement is this and the number of rows expected are more than 100.000 (is a big database):

SELECT MAX(c1),MAX(c2),MAX(c3),MAX(c4),c5,c6,MAX(c7)
FROM Table1 as t1
INNER JOIN Table2 as t2 ON t1.Id = t2.Id
INNER JOIN Table3 as t3 ON t1.Id = t3.Id
WHERE c4 NOT LIKE '%A%'
GROUP BY t1.c1;

This select statement should return records that not contains 'A' character.

And this is my Python code:

import MySQLdb

db = MySQLdb.connect(host,user,pass,dbName)
cursor = db.cursor()
cursor.execute("SELECT MAX(c1),MAX(c2),MAX(c3),MAX(c4),c5,c6,MAX(c7)
     FROM Table1 as t1
     INNER JOIN Table2 as t2 ON t1.Id = t2.Id
     INNER JOIN Table3 as t3 ON t1.Id = t3.Id
     WHERE c4 NOT LIKE '%A%'
     GROUP BY t1.c1")

results = cursor.fetchall()

for row in results:
    cursor.execute("INSERT INTO DestinationTable(col1,col2,col3,col4,col5,col6)VALUES('%s','%s','%s','%s','%s','%s')"
         %(str(row[1]),str(row[2]),str(row[3]),str(row[4]),str(row[5]),str(row[6]))
   db.commit()

I think the problem is in the Insert into statement, but I'm not sure.

Is there a reason you are not using `INSERT INTO ... SELECT ...` all in one execute call? — Parfait, Aug 22 '17 at 19:14
I think that you propose is a slow statement, and I don't know if its optimal...maybe I'm wrong sorry — SirGuacamole, Aug 22 '17 at 19:22
You avoid looping and all processes are handled in MySQL engine. — Parfait, Aug 22 '17 at 19:23

score 2 · Answer 1 · answered Aug 22 '17 at 19:22

2

Simply run an INSERT INTO ... SELECT instead of ...VALUES which does not require looping or SQL string interpolation (which should be parameterized anyway).

Below MAX(c1) is removed from SELECT statement since you skip it in attempted query:

sql = "INSERT INTO DestinationTable(col1, col2, col3, col4, col5, col6)
       SELECT MAX(c2), MAX(c3), MAX(c4), c5, c6, MAX(c7)
       FROM Table1 as t1
       INNER JOIN Table2 as t2 ON t1.Id = t2.Id
       INNER JOIN Table3 as t3 ON t1.Id = t3.Id
       WHERE c4 NOT LIKE '%A%'
       GROUP BY t1.c1"

cursor.execute(sql)
db.commit()

answered Aug 22 '17 at 19:22

Parfait

104,375
17
94
125

Parfait I have tried it but not works, some results are lost. – SirGuacamole Aug 23 '17 at 14:46
How can that be? Check types between `SELECT` columns and corresponding columns in `INSERT INTO`. Be sure they align. Additionally, you might have table constraints like primary keys/indexes disallowing dup records. Post the `SHOW CREATE TABLE DestinationTable` output. – Parfait Aug 23 '17 at 16:13

Python MySQLdb is not inserting all the records

1 Answers1