Question 406143 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Convert a string num to an int and write to an SQL file?

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

Tags:

2

Answers


  1. 0

    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. 0

    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