Python to group SQL data

Question

I used to read the data from CSV file, while I just imported all CSV data in SQL database, but I have difficulty in extracting data using Python from SQL.

My original code of read CSV is like this:

import pandas as pd
stock_data = pd.read_csv(filepath_or_buffer='stock_data_w.csv', parse_dates=[u'date'], encoding='gbk')
stock_data[u'change_weekly'] = stock_data.groupby(u'code')[u'change'].shift(-1)

Now I want to read data from SQL, here is my code, but it doesn't work and I am not sure how to sort it out:

import pandas as pd
import MySQLdb

db = MySQLdb.connect(host='localhost', user='root', passwd='232323', db='test', port=3306)
cur = db.cursor()
cur.execute("SELECT * FROM stock_data_w")

stock_data = pd.DataFrame(data=cur.fetchall(),  columns=[i[0] for i in cur.description])
stock_data[u'change_weekly'] = stock_data.groupby(u'code')[u'change'].shift(-1)

the error is: "raise PandasError('DataFrame constructor not properly called!') pandas.core.common.PandasError: DataFrame constructor not properly called!"

DataFrame is available now. Go to IDE and use print stock_data.head() to see the data and stock_data.columns to understand what you have in data — WoodChopper
– WoodChopper, Commented Dec 7, 2015 at 4:56

WoodChopper · Accepted Answer · 2016-02-13 19:51:01Z

3

Use below way to convert your cursor object to crate data frame.

stock_data = pd.DataFrame(data=cursor.fetchall(), index=None,
                         columns=cursor.keys())
print stock_data

In mysqldb, columns=[i[0] for i in cursor.description]

or

Make your connection with alchemy and use,

stock_data = pd.read_sql("SELECT * from stock_data_w",
                                      con= cnx,parse_dates=['date'])

I'm not sure whether mysql.connector is supported in pandas read_sql(). You can give a try and let us know :)

edited Feb 13, 2016 at 19:51

answered Dec 6, 2015 at 17:08

WoodChopper

4,4056 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Yi Huang Over a year ago

Hi WoodChopper, thanks very much for your reply! I tried both, the first replied an AttributeError: 'MySQLCursor' object has no attribute 'keys'. The second doesnt work either. I also installed MySQLdb, how can I group data under MySQLdb please? Thanks

WoodChopper Over a year ago

I'm sure mysqldb cursor will have keys.

WoodChopper Over a year ago

Hi use columns=[i[0] for i in cursor.description] in mysqldb

Yi Huang Over a year ago

Thanks, Thanks, I changed the code with importing MySQLdb. Columns is ok now, but parse_dates has prob, : (... I have updated the post, you can see my code and error above.

WoodChopper Over a year ago

Hi remove that parse_dates=['date'] it will infer that as date column

|

Collectives™ on Stack Overflow

Python to group SQL data

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related