如何将 SQL 查询结果转换为 PANDAS 数据结构？

如能在这个问题上给予帮助，我们将不胜感激。

所以基本上我想运行一个查询到我的 SQL 数据库，并存储返回的数据作为熊猫数据结构。

我有附加的代码查询。

我正在阅读关于熊猫的文档，但是我在识别查询的返回类型时遇到了问题。

我尝试打印查询结果，但它没有提供任何有用的信息。

谢谢！

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
)

因此，我有点想了解我的变量“ restotal”的格式/数据类型是什么，以及如何将其与 PANDAS 数据结构放在一起。

343333 次浏览
小开

resoverall是一个 sql 炼金术 ResultProxy 对象。您可以在 Sql 炼金术博士中了解更多，后者解释了使用引擎和连接的基本用法。这里重要的是，resoverall是字母喜欢。

熊猫喜欢用 dictlike 对象来创建它的数据结构，参见 在线文档

祝你在炼金术和熊猫方面好运。

小开

编辑2014-09-30:

熊猫现在有一个 read_sql函数。你肯定想用它来代替。

原答案:

在 SQLAlchemy 方面我无能为力——我总是根据需要使用 pyodbc、 MySQLdb 或者 Psyopg2。但是，当这样做时，一个像下面这样简单的函数往往适合我的需要:

import decimal


import pyodbc #just corrected a typo here
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
小开
最佳答案

下面是完成这项工作的最短代码:

from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()

你可以像保罗的回答那样，更花哨地分析类型。

小开

编辑: 2015年3月

如下所述，熊猫现在使用 SQLAlchemy来读取(Read _ sql)和插入(到 _ sql)数据库。下面这些应该可以

import pandas as pd


df = pd.read_sql(sql, cnxn)

回答: 通过 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()
小开

这个问题很老了，但我想补充一下我的看法。我把这个问题理解为“我想对我的 SQL 数据库运行一个查询，并将返回的数据存储为熊猫数据结构[ DataFrame ]。”

从代码来看，您似乎指的是 mysql 数据库，并假设您指的是熊猫 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。

小开

如果您使用的是 SQLAlchemy 的 ORM 而不是表达式语言，您可能会发现自己想要将 sqlalchemy.orm.query.Query类型的对象转换为 Pandas 数据帧。

最简洁的方法是从查询的语句属性获取生成的 SQL，然后使用 Pandas 的 read_sql()方法执行它。例如，从一个名为 query的 Query 对象开始:

df = pd.read_sql(query.statement, query.session.bind)
小开

像 Nathan 一样，我经常想将 sqltancy 或 sqlsoupQuery 的结果转储到 Panda 数据框中。我自己的解决办法是:

query = session.query(tbl.Field1, tbl.Field2)
DataFrame(query.all(), columns=[column['name'] for column in query.column_descriptions])
小开

离上一篇文章很久了，但也许对某些人有帮助..。

比保罗• H 短路:

my_dic = session.query(query.all())
my_df = pandas.DataFrame.from_dict(my_dic)
小开

这是最好的办法

db.execute(query) where db=db_class() #database class
mydata=[x for x in db.fetchall()]
df=pd.DataFrame(data=mydata)
小开

这是我的，以防你用的是“ 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
小开

如果结果类型为 结果集，应该先将其转换为字典，然后自动收集 DataFrame 列。

这对我的案子有用:

df = pd.DataFrame([dict(r) for r in resoverall])
小开

只需同时使用 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用于几个企业数据库(例如 SQLServer、 MySQL、 MariaDB、 IBM)。

小开

MySQL 连接器

对于那些使用 mysql 连接器的用户，可以从这段代码开始

参考文献:

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)
小开

1. 使用 MySQL-connect-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)
小开

Write _ frame 是 DEPRECATED。 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

还有一个办法。 PYODBC to Pandas-DataFrame 不工作-传递值的形状为(x，y) ，索引意味着(w，z)

至于熊猫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)
小开

这里有一个我喜欢的简单解决方案:

把你的数据库连接信息在一个 YAML 文件在一个安全的位置(不版本它在代码回购)。

---
host: 'hostname'
port: port_number_integer
database: 'databasename'
user: 'username'
password: 'password'

然后在字典中加载 conf，打开 db 连接，并在数据框中加载 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)

提交答案