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

CodePudding user response：

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)

CodePudding user response：

If you are running the query from a script a function like the bellow may be a more useful way to change the query

import pyodbc

def run_query(number):
    query = f"SELECT * FROM public.flores_comahue WHERE codigo_postal::int > {number}"
    conn = pyodbc.connect(server_connection) # some connection
    results = pd.read_sql_query(query, conn) # run query
    conn.close()
    return restults

this is just an example of how this could be done but in general constructing a string of the query should solve your issue