0

I'm executing this query

engine = create_engine(os.getenv("DATABASE_URL")) 
db = scoped_session(sessionmaker(bind=engine))
isbn = 03759
a = db.execute("SELECT * FROM books WHERE idnumber LIKE '%:temp%'", {"temp":isbn}).fetchall()

A picture of the books table is referenced: books database

I want to query the rows which have 03759 in their idnumber but python does not allow to add leading zeroes. I tried converting it to string and ran the query but it gave me an error since there was an extra pair of quotes like this:

SELECT * FROM books WHERE idnumber LIKE '%'03759'%'

This is how it got converted by sqlalchemy. Is there any workaround to this so that I can query only those which have 03759 in their idnumber?

Rajamani Sarvesh
  • 38
  • 1
  • 5

1 Answers1

-3

Maybe use f-string to first generate the query string and then use it in the execute command

isbn = '03759'
q = f"SELECT * FROM books WHERE idnumber LIKE '%{isbn}%'"
a = db.execute(q).fetchall()

EDIT:

I think Ilja Everilä is correct, the above code is susceptible to query injection, if the the variable isbn comes from a user input. To fix this problem use this, as given in the Ilja Everilä answer:

isbn = '03759'
q = "SELECT * FROM books WHERE idnumber LIKE :isbn"
q_params = { "isbn": f"%{isbn}%" }
a = db.execute(text(q), q_params).fetchall()
Rishabh Gupta
  • 734
  • 6
  • 10