I need to replace a certain number in a SQL file with python

May 5, 2022

I have this simple sql query in a sql file:

SELECT * FROM public.flores_comahue
WHERE codigo_postal::int > 7000

in this case i need to replace the number 7000 but it could be any other number.
I tried this, but obviously didn’t work:

fin = open("prueba.sql", "r")

fout = open("prueba.sql", "w")

for line in fin:
    for i in line:
        if isinstance(i, int):
            fout.write(fout.replace(i, 5))

fin.close()
fout.close()

I would really apreciate your help

>Solution :

When you open a file in w mode, the file is truncated. So you emptied the file before you read it.

You should do the read and write as separate steps — first read the whole thing, then open it for writing.

Another problem is your for i in line: loop. line is a string, so i is a character (a string with one element). It will never be an int.

You can use a regular expression to find a number and replace it.

import re

with open("prueba.sql", "r") as fin:
    contents = fin.read()

contents = re.sub(r'\b\d+\b', '5000', contents)

with open("prueba.sql", "w") as fout:
    fout.write(contents)