0

How do I implement placeholders in raw SQL queries in SQLAlchemy?

try:
    username: 'Pat'
    email: 'pat@gmail'
    password: 'pat'
    engine.execute("INSERT INTO users (username, email, password) VALUES (:username, :email, :password)", 
    {"username": username, "email": email, "password": password})
    session = db_session()
    session.commit()
    print('Inserted into db')
except:
    print('not inserted')
davidism
  • 121,510
  • 29
  • 395
  • 339
Nsikan Adaowo
  • 181
  • 1
  • 1
  • 5

1 Answers1

2

Placeholders are given as arguments to execute:

from sqlalchemy.sql import text

engine.execute(text("INSERT INTO ..."), username=username, email=email, password=password)

.. or if you want to expand your dictionary into arguments, you can use **arguments, where arguments = {"username": username, "email": email, "password": password}.

Make sure you wrap the query in a text() call to have placeholders expanded.

MatsLindh
  • 49,529
  • 4
  • 53
  • 84
  • Hello MatsLindh, i really appreciate your response; However; my major concern is on the arguments part that you wrote. How do i relate my sql query with it. If you could, please re write the code for clarification. Much regards. – Nsikan Adaowo Apr 14 '20 at 14:49
  • this is what i did: values = {"username": "Pat", "email": "pat@gmail.com", "password": "pat"} engine.execute("INSERT INTO users (username, email, password) VALUES (:username, :email, :password)", values) but it's still not running – Nsikan Adaowo Apr 14 '20 at 16:42
  • You'll have to do `**values` in that case to expand it into `username="pat", email="pat@gmail.com", ...` behind the scenes. But instead of doing that, it's easier to just give the arguments directly to execute(..., username="Pat", email="pat@gmail.com", ...) unless you already have it as a dictionary from another call. In that case, prefix it with `**` to unpack the arguments into named arguments for `execute` instead. https://stackoverflow.com/a/334666/137650 – MatsLindh Apr 14 '20 at 16:46