Python 3 Update MySQL database table

Question

I have just found the pymysql module for connecting Python to a MySQL database. I have a database set up with a table named 'loot', loot contains a column called 'wins'. My code contains a variable named 'won' that is given a value right before the SQL line. I want the variable 'won' to be entered into the 'wins' column where id=1. The id=1 row already exists in the database.

The code below throws an error pymysql.err.InternalError: (1054, "Unknown column 'won' in 'field list'")

My Question: Why am I getting this error and what am I doing incorrectly?

The Code:

import pymysql

# Open database connection
db = pymysql.connect(host='*******',user='******',password='*****',db='******')

# prepare a cursor object using cursor() method
cursor = db.cursor()

won=1

# Prepare SQL query to UPDATE required records
sql = "UPDATE loot SET wins = won WHERE id = 1"

# Execute the SQL command
cursor.execute(sql)

# Commit your changes in the database
db.commit()

# disconnect from server
db.close()

mechanical_meat · Accepted Answer · 2017-04-02 00:07:09Z

5

MySQL isn't able to read the variable won so you must pass it in as an argument to .execute():

won = 1
sql = "UPDATE loot SET win = %s WHERE id = %s"
cursor.execute(sql,(won,1))
db.commit()

Please note that you have to have a container of some sort as the second argument to .execute(). In this case it is a tuple.

edited Apr 2, 2017 at 0:07

answered Apr 1, 2017 at 23:59

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brandon Over a year ago

im still learning :-) This would actually be a tuple inside of a tuple?

mechanical_meat Over a year ago

This is a tuple inside of a method call.

Collectives™ on Stack Overflow

Python 3 Update MySQL database table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related