1

I want to insert a \ before each quotes in my string to insert it to my SQL database using pymysql. If I don't escape quotes I can not insert strings in my database.

For exemple:

str = "ok I'm ready"

must be :

str = "ok I\'m ready"

but print str must be : "ok I'm ready"

To perform it I have done:

str = str.replace("'", "\'")

But it's not working and I still can not insert my string in my database. I have the error message :

(1064, u"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 Semantic Search software can power everything from intuitive chatbots to searc' at line 1"): ProgrammingError Traceback (most recent call last): File "/var/task/setter_organizations.py", line 38, in handler structured_data.insert() File "/var/task/setter_organizations.py", line 107, in insert self.rds.insertItem(self.type, self.data) File "/var/task/RDS/rds.py", line 92, in insertItem return self.insert(req) File "/var/task/RDS/rds.py", line 35, in insert affected_rows = self.cursor.execute(request) File "/var/task/RDS/pymysql/cursors.py", line 166, in execute result = self._query(query) File "/var/task/RDS/pymysql/cursors.py", line 322, in _query conn.query(q) File "/var/task/RDS/pymysql/connections.py", line 856, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/var/task/RDS/pymysql/connections.py", line 1057, in _read_query_result result.read() File "/var/task/RDS/pymysql/connections.py", line 1340, in read first_packet = self.connection._read_packet() File "/var/task/RDS/pymysql/connections.py", line 1014, in _read_packet packet.check_error() File "/var/task/RDS/pymysql/connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "/var/task/RDS/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) ProgrammingError: (1064, u"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 Semantic Search software can power everything from intuitive chatbots to searc' at line 1")

I also would print my string and don't see the \

Does anyone know how can I do ?

Improve this question
7
  • "it's not working" is not a precise enough error description for us to help you. What isn't working? How isn't it working? What trouble do you have with your code? Do you get an error message? What is the error message? Is the result you are getting not the result you are expecting? What result do you expect and why, what is the result you are getting and how do the two differ? Is the behavior you are observing not the desired behavior? What is the desired behavior and why, what is the observed behavior, and in what way do they differ? Commented Jun 29, 2017 at 9:02
  • this is because \ is an escape character. Add another one and you're fine Commented Jun 29, 2017 at 9:03
  • So basically you want to escape such characters when your inserting it into the SQL database using an SQL query correct? Commented Jun 29, 2017 at 9:12
  • yes exactly @code_byter Commented Jun 29, 2017 at 9:14
  • @JakyChane Please see my edit. Commented Jun 29, 2017 at 9:17

5 Answers 5

Reset to default
6

You should add a second \:

your_str= your_str.replace("'", "\\'")
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thank you for your answer but it does : ok I\'m ready when printing if I put a second \
@JakyChane isn't that what you are expecting?
this is you want right?
It's not correct. Don't understand why this answer got several upvotes.
As an aide try in the interpreter >>> repr(s.replace("'", "\'")) to think about string representation; also please don't shadow the in-built str, use a different name
|
5

This works, but is not what the OP wanted:

str = "ok I'm ready"
str = str.replace("'", "\\'")
print(str)

EDIT:

Please take a look at this question: A good way to escape quotes in a database query string?

And this other one: Escape string Python for MySQL They used:

conn.escape_string()

EDIT 2: Please see this: Python pymySQL sending a string with quotes

and note how they have used placeholders:

cur.execute('UPDATE connections SET cmd=%s, client_new=1 where ip=%s', (command, ip))
Improve this answer

2 Comments

Thank you for your help @code_byter, I'm using pymysql and I don't know how to use escape_string with it
@JakyChane check my edit
1

if you try this will displayed "ok I\\'m ready" in terminal actually its "ok I\'m ready" you need to print it .

The result '\&' is only displayed - actually the string is \&:

str = "ok I'm ready"
str = str.replace("'", "\\'")

or 

str = "ok I'm ready".replace("'","\\'")

do, 

print str  # python 2

print(str) # python 3
Improve this answer

Comments

1

No problem simply use r before text like following: 

str = r"ok I\'m ready"

No need to replace.

Improve this answer

2 Comments

r means the string will be treated as raw string.
then, how do you add 'r' to a string variable which contains " ' " ?
0

You can use double backlash:

str = "ok I\\'m ready"

Edit: Now that you clarify, a way to solve it is json.dumps("I'm")

Improve this answer

2 Comments

But I don't to see \ when I print the string...
@JakyChane That's what you mention in the description.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.