Как преобразовать результат SQL-запроса в структуру данных PANDAS?
Любая помощь по этой проблеме будет принята с благодарностью.
Поэтому в основном я хочу выполнить запрос к моей базе данных SQL и сохранить возвращенные данные в виде структуры данных Pandas.
Я приложил код для запроса.
Я читаю документацию по Пандам, но у меня проблема с определением типа возврата моего запроса.
Я попытался напечатать результат запроса, но он не дает никакой полезной информации.
Спасибо!!!!
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)
Поэтому я хочу понять, каков формат / тип данных моей переменной "resoverall" и как поместить ее в структуру данных PANDAS.
18 ответов
Вот самый короткий код, который сделает эту работу:
from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()
Вы можете пойти поудобнее и разобрать типы, как в ответе Павла.
Изменить: март 2015
Как отмечено ниже, pandas теперь использует SQLAlchemy для чтения из ( read_sql) и вставки в ( to_sql) базы данных. Следующее должно работать
import pandas as pd
df = pd.read_sql(sql, cnxn)
Предыдущий ответ: Via Mikebmassey от аналогичного вопроса
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()
Если вы используете ORM SQLAlchemy, а не язык выражений, вы можете захотеть преобразовать объект типа sqlalchemy.orm.query.Query к фрейму данных Pandas.
Самый чистый подход - получить сгенерированный SQL из атрибута оператора запроса, а затем выполнить его с помощью pandas. read_sql() метод. Например, начиная с объекта Query query:
df = pd.read_sql(query.statement, query.session.bind)
Изменить 2014-09-30:
Панды теперь имеет read_sql функция. Вы определенно хотите использовать это вместо этого.
Оригинальный ответ:
Я не могу помочь вам с SQLAlchemy - я всегда использую pyodbc, MySQLdb или psychopg2 по мере необходимости. Но при этом такая простая функция, как приведенная ниже, стремится удовлетворить мои потребности:
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
1. Использование MySQL-connector-python
# pip install mysql-connector-python
import mysql.connector
import pandas as pd
mydb = mysql.connector.connect(
host = 'host',
user = 'username',
passwd = 'pass',
database = 'db_name'
)
query = 'select * from table_name'
df = pd.read_sql(query, con = mydb)
print(df)
2. Использование SQLAlchemy
# pip install pymysql
# pip install sqlalchemy
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost:3306/db_name')
query = '''
select * from table_name
'''
df = pd.read_sql_query(query, engine)
print(df)
MySQL Connector
Для тех, кто работает с соединителем mysql, вы можете использовать этот код для начала. (Спасибо @Daniel Velkov)
Использованные ссылки:
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())
Вот код, который я использую. Надеюсь это поможет.
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)
Это короткий и четкий ответ на вашу проблему:
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)
Как и Натан, я часто хочу поместить результаты запроса sqlalchemy или sqlsoup в фрейм данных Pandas. Мое собственное решение для этого:
query = session.query(tbl.Field1, tbl.Field2)
DataFrame(query.all(), columns=[column['name'] for column in query.column_descriptions])
Просто использовать pandas а также pyodbc все вместе. Вам придется изменить строку подключения (connstr) согласно спецификациям вашей базы данных.
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))
Я использовал pyodbc с несколькими корпоративными базами данных (например, SQL Server, MySQL, MariaDB, IBM).
resoverall является объектом sqlalchemy ResultProxy Вы можете прочитать больше об этом в документации по sqlalchemy, последняя объясняет основные принципы работы с двигателями и соединениями. Важно то, что resoverall это как
Пандам нравится, как объекты диктуют, чтобы создавать свои структуры данных, смотрите онлайн-документы
Удачи с sqlalchemy и пандами.
Этот вопрос старый, но я хотел добавить свои два цента. Я прочитал вопрос как "Я хочу выполнить запрос к моей [моей] базе данных SQL и сохранить возвращенные данные в виде структуры данных Pandas [DataFrame]".
Из кода выглядит так, будто вы имеете в виду базу данных mysql и предполагаете, что имеете в виду 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)
Например,
conn = mdb.connect('localhost','myname','mypass','testdb');
df = sql.read_frame('select * from testTable', conn)
Это импортирует все строки testTable в DataFrame.
Pandas .io.sql.write_frame УСТАРЕЛО. https://pandas.pydata.org/pandas-docs/version/0.15.2/generated/pandas.io.sql.write_frame.html
Следует изменить, чтобы использовать pandas.DataFrame.to_sql https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html
Есть другое решение. PYODBC to Pandas - DataFrame не работает - форма передаваемых значений (x,y), индексы подразумевают (w,z)
Начиная с Pandas 0.12 (я верю) вы можете сделать:
import pandas
import pyodbc
sql = 'select * from table'
cnn = pyodbc.connect(...)
data = pandas.read_sql(sql, cnn)
До 0.12 вы могли сделать:
import pandas
from pandas.io.sql import read_frame
import pyodbc
sql = 'select * from table'
cnn = pyodbc.connect(...)
data = read_frame(sql, cnn)
Вот мой. На всякий случай, если вы используете "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
Лучший способ сделать это
db.execute(query) where db=db_class() #database class
mydata=[x for x in db.fetchall()]
df=pd.DataFrame(data=mydata)
Долгое время с последнего поста, но, возможно, это кому-то помогает...
Короче, чем Пол Х:
my_dic = session.query(query.all())
my_df = pandas.DataFrame.from_dict(my_dic)
Вот простое решение, которое мне нравится:
Поместите информацию о подключении к БД в файл YAML в безопасном месте (не обновляйте его в репозитории кода).
---
host: 'hostname'
port: port_number_integer
database: 'databasename'
user: 'username'
password: 'password'
Затем загрузите conf в словарь, откройте соединение с базой данных и загрузите набор результатов SQL-запроса во фрейм данных:
import yaml
import pymysql
import pandas as pd
db_conf_path = '/path/to/db-conf.yaml'
# Load DB conf
with open(db_conf_path) as db_conf_file:
db_conf = yaml.safe_load(db_conf_file)
# Connect to the DB
db_connection = pymysql.connect(**db_conf)
# Load the data into a DF
query = '''
SELECT *
FROM my_table
LIMIT 10
'''
df = pd.read_sql(query, con=db_connection)
Если тип результата ResultSet, вы должны сначала преобразовать его в словарь. Тогда столбцы DataFrame будут собраны автоматически.
Это работает в моем случае:
df = pd.DataFrame([dict(r) for r in resoverall])