CODEWITHSUNDEEP
pythonmysql
Avinash Raj
Avinash Raj

Reputation: 174706

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

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

Upvotes: 1

Views: 401

Answers (1)

Kasravnd
Kasravnd

Reputation: 107287

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

Upvotes: 1

Related Questions