如何对2个VARCHAR(255)值进行除法并将其插入行(MySQL，Python) [英] How to divide 2 VARCHAR(255) values and insert into row (MySQL, Python)

问题描述

表格数据:

我有一个表cpu，其中包含名称，价格，id，标记，值列

数据格式:

价格值为$ xxx.xx，标记值为xxxxxx，都存储为VARCHAR(255).

问题:

我如何将价格划分为标记，然后使用MySQL python在每一行的值中存储该变量?

我对代码有以下想法:对于每一行都获得价格和标记检查，它们都具有一个值，通过删除"$"并将其设置为浮点数将价格转换为浮点数.然后，我可以将标记设置为浮点数，这样我就可以将价格划分为标记，然后将此值另存为VARCHAR(255)到值列中，然后再移动到下一行，直到遍历所有行为止.

Python表创建代码:

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小提琴:

表结构的SQL提琴

示例:

价格:$ 250，标记:13500

我想将标记除以价格以计算价值(每美元标记)

我希望将此值存储为数字，例如

值:54

如果每个字段都没有值，我希望每一行都这样，直到我想跳过该行为止.

更新:

mycursor = mydb.cursor()

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

result = mycursor.fetchall()
for row in result:
  print(row)

上面的代码循环遍历所有行，但是我不确定如何只打印价格和标记，也不知道如何在每一行中为值插入一个值?

其他

如果您需要更多详细信息，请告诉我.

任何帮助将不胜感激.

谢谢

解决方案

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()

这是我编写的解决了我问题的代码，我几乎可以肯定它可以得到改善，因此随时添加另一个答案或评论，我将对其进行更改.

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:

SQL fiddle of table structure

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.

这篇关于如何对2个VARCHAR(255)值进行除法并将其插入行(MySQL，Python)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！