0

I'm struggling to write this SQL query into a Python script.

All the variables are stored in Python. I need the SQL statement to pass my python variables and then execute the query.

The SQL statement should query col1, col2, col3 in the DB for 'this_string', and then update field1, field2 and field3 with string1, string2, and string3.

But I can't figure it out..

mystring = 'this_string'
string1 = 'test1'
string2 = 'test2'
string3 = 'test3'

UPDATE databaseb.name SET field1 = string1, field2 = string2, field3 = string3 WHERE CONCAT_WS('', column1,column2,column3) LIKE '%this_string%'

I tried this

conn = MySQLdb.connect(host= "1.2.3.4",
user="me",
passwd="passwd",
db="dbname",
use_unicode=True,
charset="utf8")
x = conn.cursor()
x.execute(UPDATE databaseb.name SET field1 = string1, field2 = string2, field3 = string3 WHERE CONCAT_WS('', column1,column2,column3) LIKE '%this_string%'
)
conn.commit()
conn.close()
user7433145
  • 21
  • 7

2 Answers2

0

What is the error that you are getting? If it is syntax error, it is because you didn't surround your UPDATE command in quotes (").

I mean:

x.execute("UPDATE databaseb.name SET field1 = string1, field2 = string2, field3 = string3 WHERE CONCAT_WS('', column1,column2,column3) LIKE '%this_string%'")

Remember that x.execure is a function that receives a string object as input. This string should specify the SQL command to execute, but it a string nonetheless.

tmrlvi
  • 2,235
  • 17
  • 35
0

The query should be in quotes and it is a good practice to parameterize your sql. It could look something like this: 

x.execute("""UPDATE databaseb.name SET field1 = %s, field2 = %s, field3 = %s WHERE CONCAT_WS('', column1,column2,column3) LIKE %s""",(string1, string2, string3, '%'+this_string+'%'))

Reference: http://mysql-python.sourceforge.net/MySQLdb.html

BobMaertz
  • 36
  • 4
  • I've gotten further with this, but it's not quite working. Can you please explain this part a bit more?: LIKE '%%%s%%'""" – user7433145 Apr 13 '17 at 03:32
  • It gives this error which shows the % isn't quite right: Syntax error in your SQL syntax; near 'this_string'%'' at line 1" – user7433145 Apr 13 '17 at 03:33
  • Yes, the comment directly above seems to show the error: 'this_string'% is what is being inserted into the query via the %%%s%% listed at top. But what it needs to insert is: '%this_string%' .... I've tried to tinker with the %s thing, but I get errors. – user7433145 Apr 13 '17 at 03:44
  • Ok, well your edit fixes the issue. But I still get a syntax error: Syntax error in your SQL syntax near '%this_string%''' at line 1. Any idea how I can get Python to print out what the SQL statement will look like with variables and all? – user7433145 Apr 13 '17 at 03:55
  • @user7433145 I fixed the query to remove the incorrect ticks around the search string. I would check out this answer to view the last query run (even queries run with exceptions) - [link](http://stackoverflow.com/a/7190914/7016601) – BobMaertz Apr 13 '17 at 04:01
  • Ok.. well no more errors... but the data isn't being written to the DB. – user7433145 Apr 13 '17 at 04:16
  • Nevermind.... I had an error in my CONCAT_WS and I was looking in the wrong column. Your final solution works. Thanks so much!!! – user7433145 Apr 13 '17 at 05:21
  • And for anyone else that stumbles upon this... I used this::: x = conn.cursor() print x._executed :::to see the SQL query within Python – user7433145 Apr 13 '17 at 16:31