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

Question 1

如果对这个问题有任何帮助，我们将不胜感激。

所以基本上我想在我的SQL数据库中运行一个查询，并将返回的数据存储为Pandas数据结构。

我已经附上了查询的代码。

我正在阅读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数据结构中。

Question 2


          
           这里有最短的代码，可以完成这项工作。
          
          from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()
你可以像保罗的回答那样，更大胆地去解析类型。

Question 3


          
           
            
             编辑：Mar.2015
            
           
           
            如下所述，pandas现在使用
            
             SQLAlchemy
            
            读取(
            
             read_sql
            
            )并插入到(
            
             to_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()

Question 4


          
           
            
             
              如果你使用SQLAlchemy的ORM而不是表达式语言，你可能会发现自己想把一个
              
               sqlalchemy.orm.query.Query
              
              类型的对象转换为一个Pandas数据框架。
             
             
              最干净的方法是从查询的语句属性中获取生成的SQL，然后用pandas的
              
               read_sql()
              
              方法执行它。例如，从一个叫做
              
               query
              
              的查询对象开始。
             
             df = pd.read_sql(query.statement, query.session.bind)

Question 5


          
           
            
             
              
               Edit 2014-09-30:
              
              
               pandas现在有一个
               
                read_sql
               
               函数。你肯定想用它来代替。
              
              
               Original answer:
              
              
               在SQLAlchemy方面我无法帮助你 -- 我总是根据需要使用pyodbc、MySQLdb或psychopg2。但在这样做时，像下面这样简单的函数往往能满足我的需要。
              
              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

Question 6


          
           
            
             
              
               
                MySQL Connector
               
               
                对于那些使用mysql连接器的人，你可以使用这个代码作为开始。(感谢@Daniel Velkov)
               
               
                Used refs:
               
               
                
                 Querying Data Using Connector/Python
                
               
               
                
                 Connecting to MYSQL with Python in 3 steps
                
               
               # 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())

Question 7


          
           
            
             
              
               
                1. Using 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. Using 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)

Question 8


          
           
            
             
              
               
                
                 
                  Here's the code I use. Hope this helps.
                 
                 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)

Question 9


          
           
            
             
              
               
                
                 
                  
                   这是对你的问题一个简短而干脆的回答。
                  
                  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)

Question 10


          
           
            
             
              
               
                
                 
                  
                   
                    像Nathan一样，我经常想把sqlalchemy或sqlsoup查询的结果转储到Pandas数据框中。  我自己对此的解决方案是
                   
                   query = session.query(tbl.Field1, tbl.Field2)
DataFrame(query.all(), columns=[column['name'] for column in query.column_descriptions])

Question 11


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      resoverall
                     
                     是一个sqlchemy ResultProxy对象。你可以在
                     
                      resoverall
                     
                     中阅读更多关于它的信息。
                     
                      sqlalchemy docs
                     
                     后者解释了使用引擎和连接的基本用法。这里重要的是，
                     
                      resoverall
                     
                     是像这样的口令。
                    
                    
                     Pandas喜欢像对象一样的口令来创建它的数据结构，见
                     
                      online docs
                     
                    
                    
                     祝你在sqlalchemy和pandas方面好运。

Question 12


          
           
            
             
              
               
                
                 
                  
                   
                    
                     只需同时使用
                     
                      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))
我曾在多个企业数据库（如SQL Server、MySQL、MariaDB、IBM）中使用过pyodbc。

Question 13


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      这个问题是老问题了，但我想增加我的两点意见。我把这个问题理解为 "我想对我的[我的]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。

Question 14


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        距离上一次发帖已经很久了，但也许对某人有帮助......。
                       
                       
                        比Paul H.做空的方式。
                       
                       my_dic = session.query(query.all())
my_df = pandas.DataFrame.from_dict(my_dic)

Question 15


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        
                         这里是我的。如果你使用的是 "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

Question 16


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        
                         
                          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)
Prior to 0.12, you could do:
import pandas
from pandas.io.sql import read_frame
import pyodbc
sql = 'select * from table'
cnn = pyodbc.connect(...)
data = read_frame(sql, cnn)

Question 17


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        
                         
                          
                           
                            我最好的方法是这样做
                           
                           db.execute(query) where db=db_class() #database class
    mydata=[x for x in db.fetchall()]
    df=pd.DataFrame(data=mydata)

Question 18


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        
                         
                          
                           
                            
                             If the result type is
                             
                              ResultSet
                             
                             你应该先把它转换为字典。然后将
                             
                              数据框架列
                             
                             将被自动收集。
                            
                            
                             这在我的案例中是可行的。
                            
                            df = pd.DataFrame([dict(r) for r in resoverall])

Question 19


          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        
                         
                          
                           
                            
                             
                              这里有一个我喜欢的简单解决方案。
                             
                             
                              把你的数据库连接信息放在一个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