根据SQL结果创建熊猫数据框架

Question

我正在尝试使用以下代码

mysql = MySQL()
app = Flask(__name__)
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'compData'
app.config['MYSQL_DATABASE_HOST'] = '0.0.0.0'
mysql.init_app(app)

@app.route("/Authenticate")
def Authenticate():
    cursor = mysql.connect().cursor()
    cursor.execute("SELECT * from abclimit 5")
    pro_info = pd.DataFrame(data=cursor.fetchall(), index=None,columns=[i[0] for i in cursor.description])

    return Response(json.dumps(pro_info),  mimetype='application/json')

if __name__ == "__main__":
    app.run()

但却给了我错误

File "pathe\frame.py", line 303, in __init__
    raise PandasError('DataFrame constructor not properly called!')
pandas.core.common.PandasError: DataFrame constructor not properly called!

我想从sql查询结果创建熊猫DF。

Answer 1

在Pandas中，您可以直接从DB中读取

from sqlalchemy import create_engine
import pymysql
import pandas as pd

db_connection = 'mysql+pymysql://mysql_user:mysql_password@mysql_host/mysql_db'
conn = create_engine(db_connection)

df = pd.read_sql("SELECT * from abc limit 5", conn)