python- Insert query to store regex expression in mysql database

Question

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

A.Sherif · Accepted Answer · 2017-02-23 17:35:35Z

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

edited Feb 23, 2017 at 17:35

answered Feb 22, 2017 at 18:50

A.Sherif

1442 silver badges8 bronze badges

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

8 Comments

ujjawl saini Over a year ago

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?

A.Sherif Over a year ago

@ujjawlsaini I edited the answer to use raw strings, have u tried that yet?

ujjawl saini Over a year ago

Yeah, this works fine. But the way you have written the string "item-cell\"(.*?)</span></div>" as r"item-cell\"(.*?)</span></div>", 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 Over a year ago

It failed for this - "item-cell\"(.*?)</span></div>" resulting as" item-cell"(.*?)</span></div>"

A.Sherif Over a year ago

@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.

|

Collectives™ on Stack Overflow

python- Insert query to store regex expression in mysql database

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related