2

Insert query for string having \" character in it in mysql db-

How to write insert query for string such as:

This is the string i want to insert into my table,

reg="item-cell\"(.*?)</span></div>"

cur = db.cursor()
query='INSERT into table_name(col_name) values("%s")'%(reg)
cur.execute(query)
cur.close()

Below is the error:

_mysql_exceptions.ProgrammingError: (1064, '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 \'(.*?)</span></div>")\' at line 1')

I know its something related to escape character, but don't know how to make that work.

EDIT: This string reg is variable i.e. I am getting this string from some API and I want to insert it into my database. So inserting escape characters in between the string literal will not suffice my case. I want something that can generalize single quote, double quote or one double quote(eg. reg) all these cases.

I hope I made my point clear.

EDIT: This is how i am getting the value of reg(froma json file)

import urllib, json
import MySQLdb
url = "some_url"
response = urllib.urlopen(url)
data = json.loads(response.read())
for item in data["key1"]["key2"]["key3"]["key4"]:
    prop=str(item)
    reg=str(data["key1"]["key2"]["key3"]["key4"][prop]["regex"])
ujjawl saini
  • 133
  • 3
  • 15

1 Answers1

0

The problem is in the part where you convert the json object to string. To properly do this without altering the string you need to use json.dumps

for item in data["key1"]["key2"]["key3"]["key4"]:
    prop = json.dumps(item)
    reg = json.dumps(data["key1"]["key2"]["key3"]["key4"][prop]["regex"])
A.Sherif
  • 144
  • 2
  • 8
  • This is good when i have just one string but what if i have thousands of such strings. Surely, we are not going to check for every string. Is there any way that can generalise this? – ujjawl saini Feb 22 '17 at 18:56
  • @ujjawlsaini I edited the answer to use raw strings, have u tried that yet? – A.Sherif Feb 22 '17 at 19:29
  • Yeah, this works fine. But the way you have written the string "item-cell\"(.*?)" as r"item-cell\"(.*?)", How to write same for variable holding the string. Like say "mystr" is what i am getting from some API, now how to write for this reg= r"mystr". I know this is not correct, Hope you understand what i am asking. – ujjawl saini Feb 22 '17 at 19:40
  • It failed for this - "item-cell\"(.*?)" resulting as" item-cell"(.*?)" – ujjawl saini Feb 22 '17 at 19:53
  • @ujjawlsaini can you post the code that gets the string? after testing for a while, it turns out that if you get a string as input from user using 'raw_input()' python 2.x or 'input()' python 3.x it is already a raw string, the same applies if its read from a file. – A.Sherif Feb 22 '17 at 21:06
  • @ujjawlsaini updated the answer with the new solution, I hope it works this time :) – A.Sherif Feb 23 '17 at 16:51
  • you saved my life dude! Yes you caught the problem right. Just a small change, instead of doing " reg.encode('string-escape') " , I did reg = reg[1:-1], because it was returning " "some_value" " so i removed first and last comma's. It worked for me. – ujjawl saini Feb 23 '17 at 17:30
  • @ujjawlsaini Am glad it worked, don't forget to mark the answer, good luck! – A.Sherif Feb 23 '17 at 17:36