Transform query into dataframe [sqlalchemy + pandas]

Question

Transform query into dataframe [sqlalchemy + pandas]

Navigation

#1 by (3 votes)

1

I'm new to python and want to create a function that queries the database [mysql] and converts it to a dataframe so that it can be emailed in .csv format later.

But I'm having trouble with this query conversion to the dataframe.

Here's 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 error:

> --------------------------------------------------------------------------- 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 anyone help me?

python python-2.7 pandas

asked by anonymous 06.10.2015 / 20:42

1 answer

Destroying Tedits at runtime java.lang.ClassNotFoundException: javax.faces.webapp.FacesServlet

score 3 · Accepted Answer

From the Pandas documentation: link function pd.read_sql_query receives a String or a SQLAlchemy Selectable Object and a connection object as parameters, but the conn.execute method returns an Object of type ResultProxy that is not of a Selectable type, try to use only the string of your query, like this:

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)