CODEWITHSUNDEEP
pythonmysqlsqlalchemyflask-sqlalchemy
user3527975
user3527975

Reputation: 1773

How to pass current date in raw sql in sqlalchemy execute

I want to update a datetime column in mysql database. The application uses sqlalchemy but for some reason I have to pass a raw sql here. The query is 

from sqlalchemy import func
session.execute(text("update table set col1=:value, date=:curr_date"),{'value:' my_value, 'curr_date:' func.now()})

The query throws an error expected string or buffer near 'curr_date:' func.now()}) . How do I format the return value to string? Even if I do format it to string, how would it be converted to datetime when inserted into database?

Upvotes: 4

Views: 4306

Answers (1)

John Adjei
John Adjei

Reputation: 1653

I'd suggest reading up a bit on SQLAlchemy's TextClause.bindparams. I think that may be what you're looking for. For the current datetime, I'd recommend just using datetime.datetime.now(). Please see below:

from sqlalchemy import text
import datetime

stmt = text("UPDATE test_table SET name=:value, update_date=:curr_date WHERE id=:the_id")
stmt = stmt.bindparams(the_id=1, value='John', curr_date=datetime.datetime.now())
session.execute(stmt)  # where session has already been defined

Using the following environment, I tried the above code with a session and it worked just fine:

OS X El Capitan
MySQL Server 5.7
PyMySQL (0.7.2)
SQLAlchemy (1.0.12)
Python 3.5.1

I hope that helped!

Upvotes: 4

Related Questions