Write and execute SQL directly in Elixir

When using Elixir, a type of Python ORM, I will leave a way when I need to write SQL directly.

• Here, I will not discuss the pros and cons of writing SQL directly even though I am using ORM.

code

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from elixir import *

days = 10

metadata.bind = "mysql://userid:[email protected]/test"
setup_all()

sql   = """ 
SELECT DATE_FORMAT( NOW() - INTERVAL %(days)s DAY, '%%Y-%%m-%%d' ) date,
       DATE_FORMAT( NOW(), '%%Y-%%m-%%d' ) today
  FROM DUAL
"""
conn   = metadata.bind.engine.connect()
result = conn.execute( sql, { "days": days } ) 

for row in result:
    print "%s%d days ago: %s" % ( row.today, days, row.date )

The execution result is as follows.

2014-04-18 10 days ago: 2014-04-08

Supplement

The reality is that you're hitting the SQLAlchemy API that Elixir wraps.

It may be easier to use if you know how to execute SQL using old placeholders.

Reference material

• SQL Statements and Expressions API — SQLAlchemy Documentation