Sqlalchemy, raw query and parameters

Question

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.

Solution

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}
)

Comments

[Drachenfels]

While your comment is helpful there is one thing that you did not answered. If you follow the link I have added to the question, there is a snippet of code like that: # recommended cmd = 'select * from Employees where EmployeeGroup == :group' employeeGroup = 'Staff' employees = connection.execute(text(cmd), group = employeeGroup) And it works without using dictionary. Why is that?

[mpiskore]

It's because you're using execute(type_str) and example shows the usage of execute(type_text). You can have the same result as in example by using from sqlalchemy.sql import text and cmd = text('select * from Employees where EmployeeGroup == :group') and finally connection.execute(text(cmd), group=employeeGroup).

[kolypto]

When working with SqlAlchemy core, use text('UPDATE ...') or parameters won't work