Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 620
Drachenfels Avatar asked Mar 23 '15 11:03

Drachenfels


1 Answers

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}
)
like image 123
mpiskore Avatar answered Oct 23 '22 04:10

mpiskore