Hi there,
Firstly, hello to everyone, this is my first post in this group and have
just started looking/using SQLAlchemy for a project and have a hopefully
very easy to answer question.
I am taking in CSV files into a Pandas Dataframe and then grabbing a dict
of each row to then insert via SqlAlchemy using the automap functionality
to a data table:
My main issue is that some of the field types are obviously numpy specific
and there aren't a direct translator to a mysql type.
So for instance I have a row like this:
Loading symbol data
creating DB connection...
created engine:
Engine(mysql+mysqlconnector://eodb_user:***@localhost:3306/eodb)
index - 1984-01-03 00:00:00
[unadjClose]25.625 - <type 'numpy.float64'>
[adjClose]3.203125 - <type 'numpy.float64'>
[unadjLow]24.375 - <type 'numpy.float64'>
[unadjHigh]26.125 - <type 'numpy.float64'>
[unadjOpen]24.375 - <type 'numpy.float64'>
[sid]5902.0 - <type 'numpy.float64'>
[date]1984-01-03 00:00:00 - <class 'pandas.tslib.Timestamp'>
[unadjVolume]6705.0 - <type 'numpy.float64'>
[splitRatio]None - <type 'NoneType'>
and when it tries to load this into mysql via sqlalchemy I get this error:
ProgrammingError: (ProgrammingError) Failed processing pyformat-parameters;
Python 'float64' cannot be converted to a MySQL type 'INSERT INTO data
(sid, symbol, date, `unadjOpen`, `unadjHigh`, `unadjLow`, `unadjClose`,
`unadjVolume`, `adjClose`, `splitRatio`) VALUES (%(sid)s, %(symbol)s,
%(date)s, %(unadjOpen)s, %(unadjHigh)s, %(unadjLow)s, %(unadjClose)s,
%(unadjVolume)s, %(adjClose)s, %(splitRatio)s)' {'unadjClose': 25.625,
'adjClose': 3.203125, 'unadjLow': 24.375, 'unadjHigh': 26.125, 'unadjOpen':
24.375, 'sid': 5902.0, 'date': datetime.datetime(1984, 1, 3, 0, 0),
'unadjVolume': 6705.0, 'splitRatio': None, 'symbol': None}
the snipped code is like this:
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker, scoped_session
Base = automap_base()
DBSession = scoped_session(sessionmaker())
....
... set up connection etc into self.engine ...
self.engine = create_engine("{0}://{1}:{2}@{3}{4}/{5}".format(engineType,
user, password, host, port, schema), echo=False)
DBSession.configure(bind=self.engine, autoflush=True, expire_on_commit=True)
Base.prepare(self.engine, reflect=True)
def loadSymbolData(self, ret, append=True, debug=False):
print("Loading symbol data")
Data = Base.classes.data
...
DBSession.add(Data(**rd)) <- rd is my row dict
...
DBSession.commit()
How do I register new types to the engine to do this translation
automagically? Coming from a java background and using hibernate and
explicit mapping annotations you can wire in similar things to a
TypeDecorator but I'm unsure how to do that when you are using the automap
features.
I had initially tried in sqlsoup and fell upon the same issues, I can add
data like strings and timestamps after converting them to py_datetime or
plain python objects but I would like something a little more pythonic and
less verbose in processing.
Hope someone can shed some light on a possible solution.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
