mysql同字段值相除,如何将2个VARCHAR（255）值相除并插入行（MySQL，Python）

Table data:

I have a table cpu with the columns name, price, id, mark, value

Data format:

The price value is $xxx.xx and the mark value is xxxxxx both stored as VARCHAR(255)'s.

Question:

How can i divide price into mark and then store that variable in value for each row using MySQL python?

I had the following idea regarding code: for each row get price and mark check they both have a value, convert price to a float by removing the "$" and setting it to a float. I could then set mark to a float so i can divide price into mark and save this value as a VARCHAR(255) into the value column before moving onto the next row until i have been through all rows.

Python table creation code:

mycursor.execute("CREATE TABLE IF NOT EXISTS cpu (name VARCHAR(255) UNIQUE, price VARCHAR(255), id VARCHAR(255) UNIQUE, mark VARCHAR(255), value VARCHAR(255))")

SQL fiddle:

Example:

price: $250, mark: 13500

I want to divide mark by price to calculate the value (marks per dollar)

I want this value stored as number e.g.

value: 54

I want this to happen for each row untill all rows are complete, if there is no value for either field I want to skip the row.

Update:

mycursor = mydb.cursor()

number_of_rows = mycursor.execute("select * from cpu")

result = mycursor.fetchall()

for row in result:

print(row)

The above code loop's through all rows but im not sure how to only print price and mark, im also not sure how to insert a value for value in each row?

Other

If you need any more details please let me know.

Any help would be greatly appreciated.

Thanks

解决方案

mycursor = mydb.cursor()

mycursor.execute("SELECT num, price, mark FROM cpu")

myresult = mycursor.fetchall()

for x in myresult:

print(x)

y = str(x)

num, price, mark = y.split(',')

num = num.replace("(", "")

price = price.replace("'", "")

price = price.replace(" ", "")

price = price.replace("$", "")

mark = mark.replace(")", "")

mark = mark.replace("'", "")

mark = mark.replace(" ", "")

price = float(price)

if mark != "None":

mark = float(mark)

value = mark/price

else:

value = 0

value = round(value, 2)

value = str(value)

num = str(num)

print(num)

print(price)

print(mark)

print(value)

sql = "UPDATE cpu SET value = " + value + " WHERE num = " + num +";"

print(sql)

mycursor.execute(sql)

mydb.commit()

mydb.commit()

This is the code i made which solved my problem, I'm almost sure it could be improved so feel feel free to add another answer or comment and i'll change it.