0

This is similar with imploding a list for use in a python MySQLDB IN clause and python list in sql query as parameter, but their list's elements are all integer.

param = []
param.append(post.title)
param.append(post.count_vote)
param.append(post.last_reply_time)
param.append(post.last_replyer_id)
param.append(post.poster_id)
param.append(post.content)
param.append(post.plain_content)
param.append(post.relate_unit)

query = "INSERT INTO post (title, countVote, lastReplyTime, lastReplyerId, posterId, content, plainContent, relateUnit) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');".format(*param)

cursor.execute(query)

The elements have many types such as int, str and None. What stucks me is the string type, the string may contain ' and ". I just simply replaced ' with " before. However, I accidently saw some C code snippet like char ch = 'a'; in content field. ' replacement will disobey the syntax.

I want to know is there a way to insert string without modification? I come across the idea to replace ' with \', is that a good idea?

I also try to replace {0} etc. in the query string with ?

query = "INSERT INTO post (title, countVote, lastReplyTime, lastReplyerId, posterId, content, plainContent, relateUnit) VALUES (?, ?, ?, ?, ?, ?, ?, ?);"

cursor.execute(query, tuple(param))
# cursor.execute(query, [param,])

Both give me the error

_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting
Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
  • What kind of MySQL connector are you using? If you're using `mysql-connector-python` you should use `format` or `pyformat` style for the notation of your variables. Can you narrow down which variable is causing the problem? What type is it? – yvesonline Apr 03 '20 at 10:41
  • @yvesonline Thank you for your comment, I'm using [MySQLdb](https://mysqlclient.readthedocs.io/user_guide.html#mysqldb). I don't quite understand what do you mean in `Can you narrow down which variable is causing the problem?`. If you are asking about my question, it is the content variable whose type is string. If you are asking about the error I paste at the bottom, I can't figure that out. – Ynjxsjmh Apr 03 '20 at 10:47
  • Have a look at my answer and see if this brings you any further. – yvesonline Apr 03 '20 at 10:57

1 Answers1

0

Based on the info that you're using MySQLdb.

  • Try using format for parameter marker formatting, i.e. %s, %d, and so on, see below, I assumed everything is a string which is most likely wrong.
  • Make sure to escape %, see the doc: Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%. do this wherever applicable, I did it examplory for post.content & post.plain_content.
query = "INSERT INTO post (title, countVote, lastReplyTime, lastReplyerId, posterId, content, plainContent, relateUnit) VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"
cursor.execute(query,post.title, post.count_vote, post.last_reply_time, post.last_replyer_id, post.poster_id, post.content.replace("%", "%%"), post.plain_content.replace("%", "%%"), post.relate_unit)
yvesonline
  • 4,609
  • 2
  • 21
  • 32
  • Thanks for your answer, can the post.count_vote etc. be passed as list? Honestly speaking, post object has 30+ fields. I have ommitted unrelated one in my question. I also encapsulate cursor.execute to a function, so that I could use it in other object like comment, reply. – Ynjxsjmh Apr 03 '20 at 11:09
  • I try to replace `?` with `%s`, but the program is crashed at `'` too. I decide just to replace `'` with `\'` to save my day. – Ynjxsjmh Apr 03 '20 at 11:20