sqlite - Python 3.4 - How to transform a Pandas Data Panel (not frame) to a mySQL database? -

i trying organize financial data in 'multidimensional' sql database can, @ later stage, take slices needed across time or asset (or attribute such 'close_price').

pandas panel.to_sql seemed nice way (albeit lack of detailed documentation on data panels specifically), , have managed store data in pandas data panel:

http://pandas.pydata.org/pandas-docs/dev/generated/pandas.panel.to_sql.html

<class 'pandas.core.panel.panel'> dimensions: 1322 (items) x 2717 (major_axis) x 15 (minor_axis) items axis: 0 1321 major_axis axis: 2004-01-02 00:00:00 2014-12-24 00:00:00 minor_axis axis: open name 

in case items security ids (or stock symbols), major_axis dates, , minor_axis hold various attributes (prices etc.)

i tried (assume 'dp' name of data panel , 'path' target path database):

from sqlalchemy import create_engine import sqlalchemy  engine = create_engine('sqlite:///'+ path) dp.to_sql(name = 'equities_data', con = engine, flavor = 'sqlite') 

this flagged error dtypes not recognized - , noticed .to_sql takes additional argument dtypes, dictionary maps pythonic types sqlalchemy types. fine did this:

attributes = list(dp.minor_axis) values = [sqlalchemy.types.string etc.. ] (populated loop) dtype = dict(zip(attributes, values)) 

this still did not solve problem transforming full datapanel did manage allow me transform dataframe slice .db file.

i.e.: dp[0].to_sql(name = 'equities_data', con = engine, flavor = 'sqlite', dtype = dtype, index = dp.major_axis)

so guess question is: has managed sort of thing work in past? possible? easier ways? ears.

otherwise, if loop create 2d slices of panel databases there way combine these in sql form 3d-like object can slice through want?

thanks in advance.

** edit **

taking deeper pandas.io.sql files seems panel.to_sql method limited dataframes. little bit misleading.

question is: there easier way achieve aggregating data in 3d object in sql?

thank

weird.. think might have found way achieve relatively close goal accident. share in case helpful else out there!

start defining single engine / connection sqlite:

from sqlalchemy import create_engine engine = create_engine('sqlite:///'+ path + 'equities_data.db') 

then loop each dataframe in datapanel , create mini datatables (in big equities_data.db , i.e. many datatables in database):

for j in range(len(list(dp.items))):      dp[j].to_sql(name = dp[j].symbol[0], con = engine, flavor = 'sqlite', dtype = dtype) 

in case each small database named stock's symbol.

the end result database has several datatables inside it. not perfect 3d structure can access items in 1 direction - believe attributes can linked across datatables can slice in other ways (sql newbie here).