Sqlalchemy, raw query and parameters

问题:

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.

回答1:

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