Updating MySQL table using Python script

Question

I'm still learning python and I struggle with sql queries

I have a simple table called diverts

column 1 is called chute column 2 is called five

chute is the primary key and contains Fa01 to Fa60

| chute | five |
|-------|------|
| Fa01  | null |
|-------|------|
| Fa02  | null |
|-------|------|
| Fa03  | null |
|-------|------|

I have a list of values called fiveMin in my script that contains the number of times a specific event relating to that chute happened in a five minute period. I pull this data from elsewhere and populate the list. fiveMin = [1.0, 23.0, 56.0, .... 39.0, 56.0] 60 elements in all

I want to update five column with that data and every five minutes update with the new data not append it.

I have been successful in updating the five column using the following

cursor.executemany('INSERT INTO `ship_diverts` (five) VALUES (%s),(%s),(%s)',
[(item, item, item) for item in fiveMin])

but this just adds data to the five column, I would like to update each column based on the primary key

 | chute | five |
 |-------|------|
 | Fa01  | 1.0  |
 |-------|------|
 | Fa02  | 23.0 |
 |-------|------|
 | Fa03  | 56.0 |
 |-------|------|

I am not sure how to achieve this, any suggestions?

I also have chute_list = ['Fa01', 'Fa02', .... 'Fa60'] which I can pass in to the query for the primary key.

nick · Accepted Answer · 2017-03-25 12:25:54Z

1

You have to use mysql update statement. it would look like this:

UPDATE diverts
SET column1=value, column2=value2,...
WHERE chute=some_value

You could either build an object by first selecting all primary keys, put them in an array and then add whatever values you want where it should go . But that's the general idea.

There is also a statement called insert on duplicate key update (if column exists it will update, if not it will make new column).

execute many:

data = [
( 'Jane', date(2005, 2, 12) , 1),
( 'Joe', date(2006, 5, 23) , 2),
( 'John', date(2010, 10, 3) , 3),
]
stmt = "update employees set first_name = %s, hire_date = %s where id=%s"
cursor.executemany(stmt, data)

 execute:


items = [
( 'Jane', date(2005, 2, 12) , 1),
( 'Joe', date(2006, 5, 23) , 2),
( 'John', date(2010, 10, 3) , 3),
]
stmt = "update employees set first_name = %s, hire_date = %s where id=%s"
for item in items 
    cursor.execute(stmt, item)

edited Mar 25, 2017 at 12:25

answered Mar 24, 2017 at 19:47

nick

9012 gold badges13 silver badges30 bronze badges

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

8 Comments

pyth0nBen Over a year ago

based on that i tried

cursor.executemany("'UPDATE ship_divert SET five='%s', WHERE chute='%s'"%[(chute, item) for chute, item in chute_list, fiveMin])

but i get the error ValueError: too many values to unpack

nick Over a year ago

Do your loop outside of the statement and for every item make a new statement with the item as value

nick Over a year ago

actually just saw the execute many function you can do that as well but still not using the for loop in the statement. You write out whatever the general statement is with %s placeholders for value and then do executemany(statement,items)

pyth0nBen Over a year ago

do you mean something like statement = "'UPDATE diverts SET five='s%' WHERE chute='s%'"(chute,item) then c.executemany(statement,items) but i'm not sure how to do the loop to pass in the variables

nick Over a year ago

execute many does the loop for you, you need to loop if just using execute I guess if you need to do some further checks or something like that. I'll put an example for execute in my answer as well.

|

Collectives™ on Stack Overflow

Updating MySQL table using Python script

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related