title: Pandas read_sql db engine under flask-sqlachemy
body: <p>I am writing a web app in Flask, and I am using pandas to retrieve data from a MySQL DB. The following used to work, using what I read from another post:</p>
  
  <pre><code>db = SQLAlchemy()
  app = Flask(__name__)
  app.config.from_object(config['default'])
  db.init_app(app)
  conn = db.engine.connect().connection
  </code></pre>
  
  <p>Then in the view function (in the same .py file):</p>
  
  <pre><code>@app.route('/report', methods=['GET', 'POST'])
  def report():
      form = ReportForm()
  ...
      sql = '''SELECT * FROM availability ...'''
      df = psql.read_sql(sql, conn)
  ...
  
      return render_template('report.html', form=form, df=df)
  </code></pre>
  
  <p>This webpage displays a table based on the date interval chosen (from the form).
  I added inline editing of this table on the webpage via x-editable, and I see that the changes have been posted to the database, but if I submit the form again the table only loads up the old data before the change was made. I only see the changes after I reopen the webpage, which is very strange.</p>
  
  <p>Moving the 'conn' line right before read_sql works:</p>
  
  <pre><code>sql = '''SELECT * FROM availability ...'''
  conn = db.engine.connect().connection
  df = psql.read_sql(sql, conn)
  </code></pre>
  
  <p>But this is not nice as I have to repeat this for every query I make (and there are a few for each view). Is there a way where I just declare this connection object once at the beginning and get it over with? SQLAlchemy's ORM works, but I prefer writing raw SQL in this case.</p>
  
  <p>Edit:</p>
  
  <p>joris' suggestion worked for one query, but now I ran into another error:</p>
  
  <pre><code>File "C:\Users\KF\flask-test\hello.py", line 107, in report
  df = pd.read_sql_query(sql.format(vd='20140727', sd=sd, ed=ed), db.engine)
  File "C:\Anaconda\envs\lightson\lib\site-packages\pandas\io\sql.py", line 363, in read_sql_query
  parse_dates=parse_dates)
  File "C:\Anaconda\envs\lightson\lib\site-packages\pandas\io\sql.py", line 823, in read_sql
  result = self.execute(*args)
  File "C:\Anaconda\envs\lightson\lib\site-packages\pandas\io\sql.py", line 810, in execute
  return self.engine.execute(*args, **kwargs)
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\base.py", line 1752, in execute
  return connection.execute(statement, *multiparams, **params)
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\base.py", line 721, in execute
  return self._execute_text(object, multiparams, params)
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\base.py", line 870, in _execute_text
  statement, parameters
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\base.py", line 958, in _execute_context
  context)
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\base.py", line 1163, in _handle_dbapi_exception
  util.reraise(*exc_info)
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\base.py", line 951, in _execute_context
  context)
  File "C:\Anaconda\envs\lightson\lib\site-packages\sqlalchemy\engine\default.py", line 436, in do_execute
  cursor.execute(statement, parameters)
  File "c:\users\kf\appdata\local\temp\easy_install-_444w8\MySQL_python-1.2.5-py2.7-win-amd64.egg.tmp\MySQLdb\cursors.py", line 187, in execute
  query = query % tuple([db.literal(item) for item in args])
  TypeError: not enough arguments for format string
  </code></pre>
  
  <p>But the raw sql string is fine - I ran the query myself and is valid. Actual SQL:</p>
  
  <pre><code>sql = '''SELECT * FROM availability WHERE view_date = str_to_date('{vd}', '%Y%m%d') and book_date &gt;= str_to_date('{sd}','%Y%m%d') and book_date &lt;= str_to_date('{ed}', '%Y%m%d')'''
  </code></pre>
  
  <p>Here 'sd' and 'ed' are strings that look like '20140801'. Looks like I have to escape those '%'s for MySQL but I can't seem to find a way to do it.</p>
  
  <p>Edit:</p>
  
  <p>Got it to work. Add another '%' in front of '%Y','%m' and '%d' to escape the percent signs.
  Basically, as joris said, use <code>pd.read_sql_query</code> and you should be fine.</p>