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.

score 27 · Accepted Answer · edited Feb 06 '19 at 15:55

27

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

edited Feb 06 '19 at 15:55

Stefan Falk

21,778
41
170
332

answered Sep 01 '15 at 14:05

mpiskore

581
9
17

1

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? – Drachenfels Sep 01 '15 at 14:47
6

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)`. – mpiskore Sep 02 '15 at 12:37
When working with SqlAlchemy core, use `text('UPDATE ...')` or parameters won't work – kolypto Sep 22 '20 at 10:43

Sqlalchemy, raw query and parameters

1 Answers1

Linked