Read from source.sql write to destiantion.sql with python script?

Question

I Have a file source.sql

INSERT INTO `Tbl_ABC` VALUES (1, 0, 'MMB', '2 MB INTERNATIONAL', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (2, 12, '3D STRUCTURES', '3D STRUCTURES', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (2, 0, '2 STRUCTURES', '2D STRUCTURES', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (2, 111, '2D STRUCTURES', '3D STRUCTURES', NULL, NULL, 1)

I am going to write a new file called destination.sql.It will contains: The new file will ignore

`INSERT INTO `Tbl_ABC` VALUES (1, dont wirte if !=0, 'MMB', '2 MB INTERNATIONAL', NULL, NULL, don't write if !=0)

My sql may loger than this.but positon is keep in general. In this case the first number 0 at position[1] and the second 0 at the position[6] (start count from 0)

That the results should be like this .

INSERT INTO `Tbl_ABC` VALUES (1, 0, 'MMB', '2 MB INTERNATIONAL', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (2, 0, '2 STRUCTURES', '2D STRUCTURES', NULL, NULL, 0)

Anybody Here Could help me to find the way to format the source.sql file and write new file. destination.sql

Thanks ..

YOU · Accepted Answer · 2009-11-27 04:55:57Z

1

You can read line by line and check if it is ends with 0) and match with regex for the other one.

import re
dest=open("destination.sql","w+")
for line in open("source.sql","r"):
    if line.strip().endswith("0)") and re.search("\(\d+, 0,",line):
        dest.write(line)

edited Nov 27, 2009 at 4:55

answered Nov 27, 2009 at 4:50

YOU

124k34 gold badges191 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tree em Over a year ago

Infact 0 it's always end of line. INSERT INTO Tbl_ABC VALUES (1, 0, 'MMB', '2 MB INTERNATIONAL', NULL, NULL, 0,0) It will problem for. I prefered it's the position. in this case 0 at postion[1] (count from 0) the seconde o at postion [6]

tree em Over a year ago

In fact I have along line more than this. but the case is postion of line in first 0 this case at postion[1] (count from 0) the seconde 0 at postion [6] Sorry for my unsure question.

tree em Over a year ago

for example: INSERT INTO Tbl_ABC VALUES (2, 0, '2D STRUCTURES', '2D STRUCTURES', NULL, NULL, 0, 15, 2, '', NULL, NULL, NULL, NULL, '2D STRUCTURES', 'MAILLOT 12/08/05', -1, 'tata 20/05/02', 0, NULL, 0, NULL, NULL);

fviktor · Accepted Answer · 2009-11-27 04:59:35Z

1

Something like this:

import re

RX = re.compile(r'^.*?\(\d+,\s0,.*\s0\)\s*$')

outfile = open('destination.sql', 'w')
for ln in open('source.sql', 'r').xreadlines():
    if RX.match(ln):
        outfile.write(ln)

edited Nov 27, 2009 at 4:59

answered Nov 27, 2009 at 4:53

fviktor

2,96621 silver badges26 bronze badges

4 Comments

tree em Over a year ago

it's quite what I need .I my case if script source like this: for example: INSERT INTO Tbl_ABC VALUES (2, 0, '2D STRUCTURES', '2D STRUCTURES', NULL, NULL, 0, 15, 2, '', NULL, NULL, NULL, NULL, '2D STRUCTURES', 'MAILLOT 12/08/05', -1, 'tata 20/05/02', 0, NULL, 0, NULL, NULL); How I modified this to mach? RX = re.compile(r'^.*?(\d+,\s0,.*\s0)\s*$')

fviktor Over a year ago

Which zeros your need to check in these new INSERT statements?

fviktor Over a year ago

BTW: That .xreadlines() is not really needed since Python 2.3. It does the same as simply iterating on the file. I'm an old timer Python developer, it seems. ;-)

tree em Over a year ago

(2, x1, '2D STRUCTURES', '2D STRUCTURES', NULL, NULL, x6, 15, 2, '', NULL, NULL, NULL, NULL, '2D STRUCTURES', 'MAILLOT 12/08/05', -1, 'tata 20/05/02', 0, NULL, 0, NULL, NULL) of cause I don't need to write to destination.sql if the position[1] != 0 and position[6] != 0 in this case are x1 and x6. thanks for your helping.

tree em · Accepted Answer · 2009-11-28 03:26:33Z

0

My sql may loger than this.but positon is keep in general. In this case the first number 0 at position[1] and the second 0 at the position[6] (start count from 0)

to S.Mark, fviktor I have tried like this

import re

RX = re.compile(r'^.*?\(\d+,\s0,.*\s0\)\s*$')

outfile = open('destination.sql', 'w')
for ln in open('source.sql', 'r').xreadlines():
    replace1 = ln.replace("INSERT INTO `Tbl_ABC` VALUES (", "")
    replace2 = replace1.replace(")", "")
    list_replace = replace2.split(',')
    #print list_replace
    #print '%s ,%s' % (list_replace[1], list_replace[6])
    if list_replace[6]==0 and list_replace[1] == 0:
        #start write line to destination.sql!!!!!!!! NEED HELP
        #if RX.match(ln):
        outfile.write(ln)

(2, x1, '2D STRUCTURES', '2D STRUCTURES', NULL, NULL, x6, 15, 2, '', NULL, NULL, NULL, NULL, '2D STRUCTURES', 'MAILLOT 12/08/05', -1, 'tata 20/05/02', 0, NULL, 0, NULL, NULL)

Of cause I don't need to write to destination.sql if the position[1] != 0 and position[6] != 0 in this case are x1 and x6. thanks for your

edited Nov 28, 2009 at 3:26

answered Nov 27, 2009 at 8:12

tree em

22k30 gold badges98 silver badges136 bronze badges

2 Comments

fviktor Over a year ago

There is not reliable way to filter on those fields using regular expressions or any text parsing except of fully parsing the SQL statement. I can come up with a regexp for you, but that might not be reliable. Better solution would be to load the data into MySQL (I guess you use that) by executing those INSERT statements on an empty SQL table having those fields, then delete the records you don't need in your output, then dump that table the get your INSERT statements as text. It would be a much more reliable and generic solution than trying to filter the textual dump.

fviktor Over a year ago

But if you have no choice and have to filter the text dump anyway, then here is an (unreliable) regexp for the above:

^INSERT\sINTO.*?\((?:(\w.*?|'.*?'|NULL))(?:,\s(\w.*?|'.*?'|NULL))(?:,\s(\w.*?|'.*?'|NULL))(?:,\s(\w.*?|'.*?'|NULL))(?:,\s(\w.*?|'.*?'|NULL))(?:,\s(\w.*?|'.*?'|NULL))(?:,\s(\w.*?|'.*?'|NULL)),.*\)\s*$

This regexp will match on such INSERT statements and extract you the first seven values as match groups. Please note, that unusual values (not a string or a string with an escaped apostrophe in it, NULL is also accepted) before the 7th value might cause problems. You're warned!

Collectives™ on Stack Overflow

Read from source.sql write to destiantion.sql with python script?

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related