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
You can accomplish this directly with SQL. First use the
replace()
function to remove the $, then usecast()
function to the desired numeric type. So somethin like:You could also use the Postgres specific :: to cast to desired type:
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: