Transform query into dataframe [sqlalchemy + pandas]

Question

I’m new to python and I want to create a function that will query the database[mysql] and convert it into a dataframe so that it can be sent by e-mail in format. csv.

But I’m having problems in this conversion query to dataframe.

Here is my code:

import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine
import MySQLdb as mysql
import sys

engine = create_engine('mysql://root:12345@localhost/Luca')
conn = engine.connect()
result = pd.read_sql_query(conn.execute("SELECT * FROM countries limit 5"), engine)

Now the mistake:

> --------------------------------------------------------------------------- InvalidRequestError                       Traceback (most recent call
> last) /home/scobino/Documentos/QueryParse/queryparser.py in <module>()
>      10 engine = create_engine('mysql://root:12345@localhost/Luca')
>      11 conn = engine.connect()
> ---> 12 result = pd.read_sql_query(conn.execute("SELECT * FROM countries limit 5"), engine)
>      13 
>      14 #df = pd.read_sql_query(result, engine)
> 
> /usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in
> read_sql_query(sql, con, index_col, coerce_float, params, parse_dates,
> chunksize)
>     409     return pandas_sql.read_query(
>     410         sql, index_col=index_col, params=params, coerce_float=coerce_float,
> --> 411         parse_dates=parse_dates, chunksize=chunksize)
>     412 
>     413 
> 
> /usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in
> read_query(self, sql, index_col, coerce_float, parse_dates, params,
> chunksize)    1126         args = _convert_params(sql, params)    1127
> 
> -> 1128         result = self.execute(*args)    1129         columns = result.keys()    1130 
> 
> /usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in
> execute(self, *args, **kwargs)    1020     def execute(self, *args,
> **kwargs):    1021         """Simple passthrough to SQLAlchemy engine"""
> -> 1022         return self.engine.execute(*args, **kwargs)    1023     1024     def read_table(self, table_name, index_col=None,
> coerce_float=True,
> 
> /usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.pyc in
> execute(self, statement, *multiparams, **params)    1989     1990     
> connection = self.contextual_connect(close_with_result=True)
> -> 1991         return connection.execute(statement, *multiparams, **params)    1992     1993     def scalar(self, statement, *multiparams, **params):
> 
> /usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.pyc in
> execute(self, object, *multiparams, **params)
>     910             raise exc.InvalidRequestError(
>     911                 "Unexecutable object type: %s" %
> --> 912                 type(object))
>     913         else:
>     914             return meth(self, multiparams, params)
> 
> InvalidRequestError: Unexecutable object type: <class
> 'sqlalchemy.engine.result.ResultProxy'>

Can someone help me?

Answer 1

From the documentation of Pandas: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html the function pd.read_sql_query takes as parameters a string or a Sqlalchemy Selectable Object and a connection object, but the Conn.execute method returns an Object of type Resultproxy that is not of type Selectable, try to use only the string of your query, thus:

import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine
import MySQLdb as mysql
import sys

engine = create_engine('mysql://root:12345@localhost/Luca')
conn = engine.connect()
result = pd.read_sql_query("SELECT * FROM countries limit 5", engine)