CODEWITHSUNDEEP
pythonmysqlsqlalchemy
user713713
user713713

Reputation: 617

How to escape % in a query using python's sqlalchemy's execute() and pymysql?

My query is:

result = connection.execute(
         "select id_number from Table where string like '_stringStart%' limit 1;")

gives the error: 

query = query % escaped_args
TypeError: not enough arguments for format string

A quick google said to use %% instead of % but that doesn't work either. How do I escape the % or is there another way to query for a string that starts with a random letter then a certain sequence?

Upvotes: 28

Views: 19367

Answers (2)

Denys Horobchenko
Denys Horobchenko

Reputation: 539

Another way to implement bound parameters:

from sqlalchemy import text

connection.execute(
    text("select id_number from Table where string like :string limit 1").\
    bindparams(string="_stringStart%")
)

or even typed strictly:

from sqlalchemy import bindparam, String, text

connection.execute(
    text("select id_number from Table where string like :string limit 1").\
    bindparams(bindparam("string", type_=String)),
    {"string"="_stringStart%"}
)

Bear in mind that text() construct is deprecated sine SQLAlchemy 1.4 and will be removed in SQLAlchemy 2.0.

Upvotes: 0

zzzeek
zzzeek

Reputation: 75207

Since this is a literal string, you're better off using a bound parameter here (illustrated using text()):

from sqlalchemy import text

connection.execute(
    text("select * from table where "
         "string like :string limit 1"), 
    string="_stringStart%")

Upvotes: 45

Related Questions