skip to Main Content

I have a price column in my .sql file that is currently a string of numbers (prices). I want to use python to modify the string of numbers and convert them into actual numbers (floats to be specific). This is so I don’t have to keep using the ::numeric type cast to do some arithmetic functions such as SUM or AVG.

-- SQL input (1000 lines):
insert into car (id, make, model, price) values (1, 'Suzuki', 'Vitara', '$44490.95');
insert into car (id, make, model, price) values (2, 'Ford', 'Explorer Sport Trac', '$292375.11');

-- Python Script

-- SQL output (1000 lines):
insert into car (id, make, model, price) values (1, 'Suzuki', 'Vitara', 44490.95);
insert into car (id, make, model, price) values (2, 'Ford', 'Explorer Sport Trac', 292375.11);

I managed to remove the ‘$’ sign with this in Python:

#Python Script
import re

def convert_to_num():
    term = re.compile('(\$)([\d.]+)')

    file_in = 'car.sql'
    file_out = 'car1.sql'
    with open(file_in, 'r') as f_in:
        with open(file_out, 'w') as f_out:
            for line in f_in.readlines():
                res = term.search(line)
                if res is not None:
                    print(res.group(2))
                    fix = re.sub('(\$)([\d.]+)', res.group(2), line)
                    f_out.writelines(fix)
                else:
                    f_out.writelines(line)

But I don’t know how to move forward from here. Also, is it possible to modify the file in place or do I really have to save the modification into another file?

I can just regenerate the TABLE from Mockaroo and use ‘numbers (w/ decimals)’ for the prices instead of ‘money’ but I got curious if I could do it and now I’m in this hole.

Wanted to convert string num to int/float num and save to an .sql file

2

Answers


  1. You can accomplish this directly with SQL. First use the replace() function to remove the $, then use cast() function to the desired numeric type. So somethin like:

    insert into car (id, make, model, price) 
          values (1, 'Suzuki', 'Vitara', cast(replace('$44490.95','$','') as numeric))
               , (2, 'Ford', 'Explorer Sport Trac', cast(replace('$292375.11','$','')as numeric)); 
    

    You could also use the Postgres specific :: to cast to desired type:

    insert into car (id, make, model, price) 
             values (1, 'Toyota', 'Prius', replace('$32678.49','$','')::numeric);
    
    Login or Signup to reply.
  2. You could identify the value string (‘$99999.99’) with a regular expression then replace the original pattern with a slice of the value string that you’ve identified.

    Like this:

    import re
    from pathlib import Path
    
    PATTERN = re.compile(r"'$d+.d+'")
    
    sql_in = Path("car.sql")
    sql_out = Path("car1.sql")
    
    with sql_in.open() as source, sql_out.open("w") as target:
        for line in source:
            try:
                value, *_ = PATTERN.findall(line)
                target.write(line.replace(value, value[2:-1]))
            except ValueError:
                target.write(line)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search