mysql - Python - convert multiple insert statements into one

Question

I have the following problem right now:

I have a database table with only 2 columns: ID (primary key, auto increment) and value (varchar(100)).

Now I have a sql file to fill that table with values. Here comes the point though: First of all, the text file has only single inserts, so every value inserted is a single value. Wouldn't be a problem in general, if it wouldn't be about 10 million lines...

To make it even more funny, I've got 5 of this tables and 5 of this .sql files with the insert statements.

Now I've tried to run the sql file as it is and for even only 100k lines, it took an hour... So inserting 10 million entries would take about 100 hours... x5 for all tables would take about 500 hours then, being about 21 days.. Nothing that I'd like to see going on here...

Now from what I've read, the speed would be much faster, if one insert statement would contain multiple values to insert... for example:

insert into knownPasswords3 (password)
values ('! -');

insert into knownPasswords3 (password)
values ('! 3');

insert into knownPasswords3 (password)
values ('! 5');

insert into knownPasswords3 (password)
values ('! ?');

insert into knownPasswords3 (password)
values ('! C');

insert into knownPasswords3 (password)
values ('! c');

would become:

insert into knownPasswords3 (password)
values ('! -','! 3','! 5','! ?','! C''! c');

As an example... From what I've read, about 40 values per insert statement will be a pretty good value...

So thats what I'd like to get.. From a sql file with 400 single statements (as an example) to an sql file with 10 statements with 40 values each.

Now I've tried around a lot with regular expressions and different methods in Notepad++ as an example, but I couldn't really get it working...

I then though if python might be able to do this in an elegant way (either inserting it directly into the database or just converting the text file). I figured that inserting wouldn't help much, so now I'm looking for a way to convert the file into another file, just that it has the mentioned decrease of single statements..

My pseudo code for that would be something like:

open file.sql
i = 0;
for each line in file.sql:
   if i==40:
      i=0
      break
   else:
      remove text "insert into knownPasswords3 (password) values ("
      remove text ");"
      add text ,

However, I've tried that and just can't get these parts working...

Can anybody put me on the right track for that? is there maybe a lib or sth making that easier?

Answer 1

I think the problem is in the insert statement you are trying to prepare.It should look like this :

insert into knownPasswords3 (password)
values ('! -'),('! 3'),('! 5'),('! ?'),('! C'),('! c');

now you are inserting 6 rows with 1 column whereas your query was inserting 1 row with 6 columns.