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.
<code>-- 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);
</code>
<code>-- 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);
</code>
-- 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:
<code>#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)
</code>
<code>#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)
</code>
#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
As example per my comment:
<code>create table money_test(id integer, price money);
insert into money_test values (1, '$10.00'), (2, '50.00'), (3, '$25.00');
select * from money_test ;
id | price
----+--------
1 | $10.00
2 | $50.00
3 | $25.00
alter table money_test alter column price type numeric;
select * from money_test ;
id | price
----+-------
1 | 10.00
2 | 50.00
3 | 25.00
</code>
<code>create table money_test(id integer, price money);
insert into money_test values (1, '$10.00'), (2, '50.00'), (3, '$25.00');
select * from money_test ;
id | price
----+--------
1 | $10.00
2 | $50.00
3 | $25.00
alter table money_test alter column price type numeric;
select * from money_test ;
id | price
----+-------
1 | 10.00
2 | 50.00
3 | 25.00
</code>
create table money_test(id integer, price money);
insert into money_test values (1, '$10.00'), (2, '50.00'), (3, '$25.00');
select * from money_test ;
id | price
----+--------
1 | $10.00
2 | $50.00
3 | $25.00
alter table money_test alter column price type numeric;
select * from money_test ;
id | price
----+-------
1 | 10.00
2 | 50.00
3 | 25.00
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:
<code>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));
</code>
<code>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));
</code>
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:
<code>insert into car (id, make, model, price)
values (1, 'Toyota', 'Prius', replace('$32678.49','$','')::numeric);
</code>
<code>insert into car (id, make, model, price)
values (1, 'Toyota', 'Prius', replace('$32678.49','$','')::numeric);
</code>
insert into car (id, make, model, price)
values (1, 'Toyota', 'Prius', replace('$32678.49','$','')::numeric);
1