Geek Answers Handbook

Sqlalchemy, raw queries and parameters

I am trying to execute a raw SQL query using sqlalchemy and wondering how this is the “right” way to do this.

My request is as follows (for now):

db.my_session.execute(
    """UPDATE client SET musicVol = {}, messageVol = {}""".format(
    music_volume, message_volume))

What I don't like is the formatting of the strings and the absence of any processing of parameters (hello to quotes in music_volume: -D).

I tried to follow this answer:

How to execute raw SQL in SQLAlchemy-flask application

And after applying what I read, my fragment looks like this:

db.my_session.execute(
    "UPDATE client SET musicVol = :mv , messageVol = :ml", mv=music_volume, ml=message_volume)

However, I get an error that mv and ml are not a recognized parameter.

If I change my code snippet to this, this will work:

db.my_session.execute(
    "UPDATE client SET musicVol = :mv , messageVol = :ml", {mv: music_volume, ml: message_volume})

Finally, my_session runs like this 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)

, , , :

http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-text

, .

, .

+9
1

mv ml , .

execute - , "UPDATE client SET musicVol = :mv, messageVol = :ml" , . execute 'mv' 'ml' , .

:

db.my_session.execute(
    "UPDATE client SET musicVol = :mv, messageVol = :ml",
    {'mv': music_volume, 'ml': message_volume}
)
+18

More articles:


All Articles