How to convert SQL Query result to PANDAS Data Structure

Question

Any help on this problem will be greatly appreciated   So basically I want to run a query to my SQL database and store the returned data as Pandas data structure   I have attached code for query   I am reading the documentation on Pandas  but I have problem to identify the return type of my query   I tried to print the query result  but it doesn t give any useful information   Thanks       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    So I sort of want to understand what s the format datatype of my variable  resoverall  and how to put it with PANDAS data structure

User · Answer

This is a short and crisp answer to your problem   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

User · Answer

Edit 2014-09-30   pandas now has a read sql function  You definitely want to use that instead   Original answer   I can t help you with SQLAlchemy -- I always use pyodbc  MySQLdb  or psychopg2 as needed  But when doing so  a function as simple as the one below tends to suit my needs   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

User · Answer

This question is old  but I wanted to add my two-cents  I read the question as   I want to run a query to my  my SQL database and store the returned data as Pandas data structure  DataFrame     From the code it looks like you mean mysql database and assume you mean pandas DataFrame   import MySQLdb as mdb import pandas io sql as sql from pandas import    conn   mdb connect   lt server gt     lt user gt     lt pass gt     lt db gt     df   sql read frame   lt query gt    conn    For example   conn   mdb connect  localhost   myname   mypass   testdb    df   sql read frame  select   from testTable   conn    This will import all rows of testTable into a DataFrame

User · Answer

Like Nathan  I often want to dump the results of a sqlalchemy or sqlsoup Query into a Pandas data frame   My own solution for this is   query   session query tbl Field1  tbl Field2  DataFrame query all    columns  column  name   for column in query column descriptions

User · Answer

pandas io sql write frame is DEPRECATED  https   pandas pydata org pandas-docs version 0 15 2 generated pandas io sql write frame html  Should change to use pandas DataFrame to sql https   pandas pydata org pandas-docs stable reference api pandas DataFrame to sql html  There is another solution  PYODBC to Pandas - DataFrame not working - Shape of passed values is  x y   indices imply  w z   As of Pandas 0 12  I believe  you can do   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

User · Answer

best way I do this  db execute query  where db db class    database class     mydata  x for x in db fetchall        df pd DataFrame data mydata

User · Answer

Edit  Mar  2015  As noted below  pandas now uses SQLAlchemy to both read from  read sql  and insert into  to sql  a database  The following should work  import pandas as pd  df   pd read sql sql  cnxn    Previous answer  Via mikebmassey from a similar question  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

User · Answer

Simply use pandas and pyodbc together  You ll have to modify your connection string  connstr  according to your database specifications   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     I ve used pyodbc with several enterprise databases  e g  SQL Server  MySQL  MariaDB  IBM

User · Answer

If you are using SQLAlchemy s ORM rather than the expression language  you might find yourself wanting to convert an object of type sqlalchemy orm query Query to a Pandas data frame    The cleanest approach is to get the generated SQL from the query s statement attribute  and then execute it with pandas s read sql   method  E g   starting with a Query object called query   df   pd read sql query statement  query session bind

User · Answer

Here is mine  Just in case if you are using  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

User · Answer

MySQL Connector  For those that works with the mysql connector you can use this code as a start   Thanks to  Daniel Velkov   Used refs    Querying Data Using Connector Python Connecting to MYSQL with Python in 3 steps     import pandas as pd import mysql connector    Setup MySQL connection db   mysql connector connect      host   lt IP gt                   your host  usually localhost     user   lt USER gt                 your username     password   lt PASS gt             your password     database   lt DATABASE gt         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  lt TABLE gt       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

User · Answer

resoverall is a sqlalchemy ResultProxy object  You can read more about it in the sqlalchemy docs  the latter explains basic usage of working with Engines and Connections  Important here is that resoverall is dict like   Pandas likes dict like objects to create its data structures  see the online docs  Good luck with sqlalchemy and pandas

User · Answer

If the result type is ResultSet  you should convert it to dictionary first  Then the DataFrame columns will be collected automatically   This works on my case   df   pd DataFrame  dict r  for r in resoverall

User · Answer

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

User · Answer

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

User · Answer

Long time from last post but maybe it helps someone     Shorted way than Paul H   my dic   session query query all    my df   pandas DataFrame from dict my dic

User · Answer

Here is a simple solution I like  Put your DB connection info in a YAML file in a secure location  do not version it in the code repo   --- host   hostname  port  port number integer database   databasename  user   username  password   password   Then load the conf in a dictionary  open the db connection and load the result set of the SQL query in a data frame  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

User · Answer

Here s the shortest code that will do the job   from pandas import DataFrame df   DataFrame resoverall fetchall    df columns   resoverall keys     You can go fancier and parse the types as in Paul s answer

[python] How to convert SQL Query result to PANDAS Data Structure?

Examples related to python

Examples related to mysql

Examples related to data-structures

Examples related to pandas