Cómo convertir SQL Query result a PANDAS Data Structure?
Cualquier ayuda en este problema será muy apreciada.
Así que básicamente quiero ejecutar una consulta a mi base de datos SQL y almacenar los datos devueltos como estructura de datos Pandas.
He adjuntado el código para la consulta.
Estoy leyendo la documentación sobre Pandas, pero tengo problemas para identificar el tipo de retorno de mi consulta.
Traté de imprimir el resultado de la consulta, pero no da ninguna información útil.
Gracias!!!!
from sqlalchemy import create_engine
engine2 = create_engine('mysql://THE DATABASE I AM ACCESSING')
connection2 = engine2.connect()
dataid = 1022
resoverall = connection2.execute("
SELECT
sum(BLABLA) AS BLA,
sum(BLABLABLA2) AS BLABLABLA2,
sum(SOME_INT) AS SOME_INT,
sum(SOME_INT2) AS SOME_INT2,
100*sum(SOME_INT2)/sum(SOME_INT) AS ctr,
sum(SOME_INT2)/sum(SOME_INT) AS cpc
FROM daily_report_cooked
WHERE campaign_id = '%s'", %dataid)
Así que en cierto modo quiero entender cuál es el formato / tipo de datos de mi variable "resoverall" y cómo ponerlo con PANDAS data structure.
15 answers
Aquí está el código más corto que hará el trabajo:
from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()
Puedes ir más elegante y analizar los tipos como en la respuesta de Pablo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-08-21 18:28:30
Edit: Mar. 2015
Como se indica a continuación, pandas ahora usa SQLAlchemy tanto para leer desde ( read_sql) como para insertar en ( to_sql) una base de datos. Lo siguiente debería funcionar
import pandas as pd
df = pd.read_sql(sql, cnxn)
Respuesta Anterior: Vía mikebmassey de una pregunta similar
import pyodbc
import pandas.io.sql as psql
cnxn = pyodbc.connect(connection_info)
cursor = cnxn.cursor()
sql = "SELECT * FROM TABLE"
df = psql.frame_query(sql, cnxn)
cnxn.close()
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:47:32
Si está utilizando el language de SQLAlchemy en lugar del lenguaje de expresiones, es posible que desee convertir un objeto de tipo sqlalchemy.orm.query.Query
a un marco de datos Pandas.
El enfoque más limpio es obtener el SQL generado a partir del atributo de sentencia de la consulta, y luego ejecutarlo con el método read_sql()
de pandas. Por ejemplo, comenzando con un objeto de consulta llamado query
:
df = pd.read_sql(query.statement, query.session.bind)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-11 03:00:55
Editar 2014-09-30:
Los Pandas ahora tienen una función read_sql
. Definitivamente quieres usar eso en su lugar.
Respuesta original:
No puedo ayudarte con SQLAlchemy always siempre uso pyodbc, MySQLdb o psychopg2 según sea necesario. Pero al hacerlo, una función tan simple como la siguiente tiende a satisfacer mis necesidades:
import decimal
import pydobc
import numpy as np
import pandas
cnn, cur = myConnectToDBfunction()
cmd = "SELECT * FROM myTable"
cur.execute(cmd)
dataframe = __processCursor(cur, dataframe=True)
def __processCursor(cur, dataframe=False, index=None):
'''
Processes a database cursor with data on it into either
a structured numpy array or a pandas dataframe.
input:
cur - a pyodbc cursor that has just received data
dataframe - bool. if false, a numpy record array is returned
if true, return a pandas dataframe
index - list of column(s) to use as index in a pandas dataframe
'''
datatypes = []
colinfo = cur.description
for col in colinfo:
if col[1] == unicode:
datatypes.append((col[0], 'U%d' % col[3]))
elif col[1] == str:
datatypes.append((col[0], 'S%d' % col[3]))
elif col[1] in [float, decimal.Decimal]:
datatypes.append((col[0], 'f4'))
elif col[1] == datetime.datetime:
datatypes.append((col[0], 'O4'))
elif col[1] == int:
datatypes.append((col[0], 'i4'))
data = []
for row in cur:
data.append(tuple(row))
array = np.array(data, dtype=datatypes)
if dataframe:
output = pandas.DataFrame.from_records(array)
if index is not None:
output = output.set_index(index)
else:
output = array
return output
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-09-28 21:00:38
Al igual que Nathan, a menudo quiero volcar los resultados de una consulta sqlalchemy o sqlsoup en un marco de datos Pandas. Mi propia solución para esto es:
query = session.query(tbl.Field1, tbl.Field2)
DataFrame(query.all(), columns=[column['name'] for column in query.column_descriptions])
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-10-02 18:53:25
Simplemente use pandas
y pyodbc
juntos. Tendrá que modificar su cadena de conexión (connstr
) de acuerdo con las especificaciones de su base de datos.
import pyodbc
import pandas as pd
# MSSQL Connection String Example
connstr = "Server=myServerAddress;Database=myDB;User Id=myUsername;Password=myPass;"
# Query Database and Create DataFrame Using Results
df = pd.read_sql("select * from myTable", pyodbc.connect(connstr))
He utilizado pyodbc
con varias bases de datos empresariales (por ejemplo, SQL Server, MySQL, MariaDB, IBM).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-16 15:59:21
resoverall
es un objeto sqlalchemy ResultProxy. Puede leer más al respecto en sqlalchemy docs, este último explica el uso básico del trabajo con Motores y Conexiones. Importante aquí es que resoverall
es dict like.
A Pandas le gustan los objetos tipo dict para crear sus estructuras de datos, vea los documentos en línea
Buena suerte con sqlalchemy y pandas.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-08-21 12:36:18
Esta pregunta es antigua, pero quería añadir mis dos centavos. Leo la pregunta como " Quiero ejecutar una consulta a mi [mi] base de datos SQL y almacenar los datos devueltos como Pandas data structure [DataFrame]."
Desde el código parece que te refieres a mysql database y asumes que te refieres a pandas DataFrame.
import MySQLdb as mdb
import pandas.io.sql as sql
from pandas import *
conn = mdb.connect('<server>','<user>','<pass>','<db>');
df = sql.read_frame('<query>', conn)
Por ejemplo,
conn = mdb.connect('localhost','myname','mypass','testdb');
df = sql.read_frame('select * from testTable', conn)
Esto importará todas las filas de testTable en un DataFrame.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-31 18:55:22
Conector MySQL
Para aquellos que trabajan con el conector mysql, puede usar este código como inicio. (Gracias a @ Daniel Velkov)
Referencias utilizadas:
import pandas as pd
import mysql.connector
# Setup MySQL connection
db = mysql.connector.connect(
host="<IP>", # your host, usually localhost
user="<USER>", # your username
password="<PASS>", # your password
database="<DATABASE>" # name of the data base
)
# You must create a Cursor object. It will let you execute all the queries you need
cur = db.cursor()
# Use all the SQL you like
cur.execute("SELECT * FROM <TABLE>")
# Put it all to a data frame
sql_data = pd.DataFrame(cur.fetchall())
sql_data.columns = cur.column_names
# Close the session
db.close()
# Show the data
print(sql_data.head())
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-06 09:06:28
Aquí está el código que uso. Espero que esto ayude.
import pandas as pd
from sqlalchemy import create_engine
def getData():
# Parameters
ServerName = "my_server"
Database = "my_db"
UserPwd = "user:pwd"
Driver = "driver=SQL Server Native Client 11.0"
# Create the connection
engine = create_engine('mssql+pyodbc://' + UserPwd + '@' + ServerName + '/' + Database + "?" + Driver)
sql = "select * from mytable"
df = pd.read_sql(sql, engine)
return df
df2 = getData()
print(df2)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-06 16:01:00
Esta es una respuesta corta y nítida a su problema:
from __future__ import print_function
import MySQLdb
import numpy as np
import pandas as pd
import xlrd
# Connecting to MySQL Database
connection = MySQLdb.connect(
host="hostname",
port=0000,
user="userID",
passwd="password",
db="table_documents",
charset='utf8'
)
print(connection)
#getting data from database into a dataframe
sql_for_df = 'select * from tabledata'
df_from_database = pd.read_sql(sql_for_df , connection)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-12 14:55:47
Mucho tiempo desde el último post, pero tal vez ayuda a alguien...
Corto camino que Pablo H:
my_dic = session.query(query.all())
my_df = pandas.DataFrame.from_dict(my_dic)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-07-23 09:21:05
La mejor manera de hacer esto
db.execute(query) where db=db_class() #database class
mydata=[x for x in db.fetchall()]
df=pd.DataFrame(data=mydata)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-09-09 08:55:58
Aquí está el mío. Por si acaso si está usando "pymysql":
import pymysql
from pandas import DataFrame
host = 'localhost'
port = 3306
user = 'yourUserName'
passwd = 'yourPassword'
db = 'yourDatabase'
cnx = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)
cur = cnx.cursor()
query = """ SELECT * FROM yourTable LIMIT 10"""
cur.execute(query)
field_names = [i[0] for i in cur.description]
get_data = [xx for xx in cur]
cur.close()
cnx.close()
df = DataFrame(get_data)
df.columns = field_names
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-06-05 15:57:02
Si el tipo de resultado es ResultSet, primero debe convertirlo a diccionario. Luego, las columnas DataFrame se recopilarán automáticamente.
Esto funciona en mi caso:
df = pd.DataFrame([dict(r) for r in resoverall])
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-14 06:43:07