How to modify a column present in a mysql database through python?

Question

I'm able to connect with my database. But I want to change the values present in that particular column.

cursor.execute("SELECT * FROM foo;")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output of the above code is,

('foo,bar,foo,bar',)
('foobar,bar',)
('foo,bar,buz,buz',)

I'm able to replace the value by,

rows = cursor.fetchall()
for row in rows:
    print(re.sub(r'^[^,]*,', '', row[0]))
cursor.close()

returns,

bar,foo,bar
bar
bar,buz,buz

but I don't know how to setback the altered string to that particular column.

I think i need to use update query, so I tried

for row in rows:
    cursor.execute("UPDATE foo SET  categories=%s;", re.sub(r'^[^,]*,', '', row[0]))

But it returns an error message of,

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 '%s' at line 1

@nu11p01n73R nooo, it would turn the corresponding column to %s — Avinash Raj
– Avinash Raj, Commented Jun 4, 2015 at 12:50
@AvinashRaj Do you have tried my deleted answer? or do you write the all of your columns within the query? maybe you have an ID — Kasravnd
– Kasravnd, Commented Jun 4, 2015 at 12:52
@Kasra i tried, 1st fails with the error message i mentioned. 2nd one shows error at the % symbol. — Avinash Raj
– Avinash Raj, Commented Jun 4, 2015 at 12:53
I have a doubt of, why we loop through each row where this UPDATE drug_specifications SET categories=%s; query applies to the whole. — Avinash Raj
– Avinash Raj, Commented Jun 4, 2015 at 12:55
What about "UPDATE drug_specifications SET categories={};".format(re.sub(r'^[^,]*,', '', row[0])) — Kasravnd
– Kasravnd, Commented Jun 4, 2015 at 12:55

Kasravnd · Accepted Answer · 2015-06-04 14:12:01Z

1

As i said in comment you need to specify the column ID for your query :

for row in rows:
        sub=re.sub(r'^[^,]*,', '', row[0])
        cursor.execute("UPDATE drug_specifications SET  categories=%s where id=%s;",(sub, str(row[0])))

edited Jun 4, 2015 at 14:12

answered Jun 4, 2015 at 12:47

Kasravnd

108k19 gold badges167 silver badges195 bronze badges

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

Collectives™ on Stack Overflow

How to modify a column present in a mysql database through python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related