1
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?
thanks to @Victor Capone
– scob