in a function,I want to update a value in mysql table with an argument taken in my function

Question

I want to update mysql table in a function,and the updated value is realated to the two argument the function takes

I am a freshman and am doing a python project in which I have a table in mysql to store some data .and there is a function in my projrct which take 2 argument:user and consumption. I want to update a value in my table,and the updated value equel to the previous value plus consumption(an argument taken),I need to creat a sql:

UPDATE consumption_record SET 
total_consumption=total_consumption+consumption WHERE name=user

but there is two syntax in this sql, I want to konw how can I change my code to meet my need?

def increase_consumption(self,user,consumption):
    mycursor.execute("SELECT total_consumption FROM biao WHERE username="+"'"+user+"'")
    ori_con=int(mycursor.fetchall()[0][0])
    updated_con=str(consumption+ori_con)
    sql="UPDATE biao SET total_consumption="+updated_con+"WHERE username="+"\'"+user+"\'"
    mycursor.execute(sql)
    mycursor.commit()

It's not really clear what you are asking. Is your current python code working? If not, add the error message or incorrect result you get (I assume at least the missing space in `"WHERE` should generate a problem, but it might be a copy&paste error). Or do you want to simplify your pythoncode to directly update the table with one query (the first separate update query)? — Solarflare, Dec 22 '18 at 09:40
really sorry! my need to change my statement of my question. First ,the code after WHERE is actually "WHERE username=user",not (WHERE username="+"'"+user+"'"),and the error is "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'username='430423'' at line 1", my question is I that I want to change my a value in my table ,and the value is an argument,how can I do this ? — xiaolei, Dec 23 '18 at 10:59
The error you get (and the problem I meant) is because you have a missing space *before* the `where` (so your update looks like `SET total_consumption=123WHERE username=...`), otherwise your code looks like it should work (although, as mentioned, maybe a bit complicated as you can use a single query for that, and that you should *always* use [prepared statements](https://stackoverflow.com/q/1947750)). — Solarflare, Dec 23 '18 at 12:00
I have change my code ,But its still not legal ,the error is " ProgrammingError: Unknown column 'user' in 'where clause' The machine may does not recognize the argument in the statement when compiling the code, Can you write a function in python code and show me it? As I said before about my requirements, the relevant names are in the problem description code , and Merry Christmas Eve ! Thanks! — xiaolei, Dec 24 '18 at 13:52
my problem have been solved thank you for your response, You are so patient !glad to communicate with you ! — xiaolei, Dec 25 '18 at 08:17

in a function,I want to update a value in mysql table with an argument taken in my function

0 Answers0