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 35 · Accepted Answer · edited Feb 06 '19 at 15:55

35

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

23,898
50
191
378

answered Sep 01 '15 at 14:05

mpiskore

671
8
18

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
Can you use parameters to allow the user to apply filtering operations? – Jul 10 '22 at 15:08
For example, if the user wants to filter by album creation date and/or by author, can I say something like "SELECT...WHERE :filter"? – Jul 10 '22 at 15:08
Don't really know how this looks like in SQLAlchemy as I no longer have up to date experience with it, but since the first argument to `execute` is a plain Python string you can easily work with it. If you have an optional `query_filter` argument that can be `None` or e.g. `"type = 'new'"` you can easily add this optionally to the query. `query = "UPDATE client SET musicVol = :mv, messageVol = :ml" if query_filter is not None: query = f"{query} WHERE {query_filter}" result = db.my_session.execute(query, {"mv": music_volume, "ml": message_volume})` – mpiskore Jul 11 '22 at 16:12

Sqlalchemy, raw query and parameters

1 Answers1

Linked