I am trying to perform raw sql query using sqlalchemy and wondering what is a 'proper' way to do it.
My query looks as follows (for now):
db.my_session.execute(
"""UPDATE client SET musicVol = {}, messageVol = {}""".format(
music_volume, message_volume))
What I don't like is string formatting and lack of any parameter handling (hello to quotation marks in music_volume :-D).
I tried to follow this answer:
How to execute raw SQL in SQLAlchemy-flask app
And after applying what I read, my snippet looks as follows:
db.my_session.execute(
"UPDATE client SET musicVol = :mv , messageVol = :ml", mv=music_volume, ml=message_volume)
However I am getting error that mv and ml is not recognized parameter.
If I change my snippet into this, it works:
db.my_session.execute(
"UPDATE client SET musicVol = :mv , messageVol = :ml", {mv: music_volume, ml: message_volume})
Lastly my_session is initiated like that in a file called db.py:
engi = sqlalchemy.create_engine(
'mysql://{user}:{passwd}@{host}/{db}'.format(
host=settings.HOST,
user=settings.USER,
passwd=settings.PASS,
db=settings.DB_NAME), execution_options={
'autocommit': True,
})
my_session = sqlalchemy.orm.scoped_session(sqlalchemy.orm.sessionmaker(bind=engi), scopefunc=os.getpid)
sqlalchemy.orm.scoped_session.configure(my_session, autocommit=True)
What I would like to know is why answer linked above and this part of documentation:
http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-text
Are showing slightly different solution to what actually is working for me.
Also if my approach is one to go.
Both mv
and ml
will not be recognized, since you haven't defined them as variables.
The second argument of execute
statement is a dictionary, and all the elements of your plain query "UPDATE client SET musicVol = :mv , messageVol = :ml"
escaped with a colon are being searched for in this dictionary's keys. The execute
method did not found a key 'mv'
nor 'ml'
in this dictionary, therefore an error is raised.
This is the correct version:
db.my_session.execute(
"UPDATE client SET musicVol = :mv, messageVol = :ml",
{'mv': music_volume, 'ml': message_volume}
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With