0

I am looking for a way to drop values from a list into a prepared SQL string which has question marks as placeholders. I have used this before in PyQT, but there I use the bindValue function. Using pymysql there looks to be no such function.

Here's an example of the type of SQL string I have prepared:

INSERT INTO my_table (`Column1Name`, `Column2Name`, `Column3Name`) VALUES (?,?,?);

I then have a list of values I am looking to insert into (or link to) the question mark placeholders.

my_values_list['string_1', '3', 'anothervalue']

Like I say, I have used this method before in PyQT, so I know this '?' placeholder method works, but without the bindValue function I can't see how to get it to work using pymysql.

Here's how I got it to work using PyQT's QSqlQuery bindValues function if it helps, where query.exec_() executes the SQL string:

if my_values_list:
        [self.query.bindValue(i, my_values_list[i]) for i in range(len(my_values_list))]
self.query.exec_()
MikG
  • 1,019
  • 1
  • 15
  • 34

2 Answers2

2

Here is an example of how to do this with PyMySQL:

query = 'INSERT INTO my_table (Column1Name, Column2Name, Column3Name) VALUES (%s, %s, %s);'
cur.execute(query, ('string_1', '3', 'anothervalue', ))

? is not a valid placeholder here, but %s is valid.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks Tim, I hoping to avoid distinguishing between placeholder types like %s for strings etc, and I know the '?' placeholder works, at least when I have used it in PyQt's QSqlQuery bindValue function. However your suggestion does get me a step further. – MikG Oct 22 '18 at 08:09
  • 2
    @MikG [Read here](https://github.com/PyMySQL/PyMySQL/issues/329), which says that "PyMySQL convert all objects including integer to formatted and quoted string. You can use only %s or %(name)s as placeholder." – Tim Biegeleisen Oct 22 '18 at 08:10
1

Maybe this post helps you, it's old but it's still mostly valid AFAIU, and I found it gave me a great overview: What does a question mark represent in SQL queries?

dennisdee
  • 160
  • 10