mysql float64,'numpy.float64'对象没有属性'translate'将值插入Python中的Mysql

import dataset

db = dataset.connect(....)

table = db[...]

When I try to insert some value into Mysql table, this error occurred.

Sample Value I am inserting to the table:

print("Buy", ticker, price, date, OType, OSize)

Buy AAPL 93.4357142857 2016-05-12 Market 200

data = dict(Order_Side='Buy',Ticker=ticker, Price=price,

Order_Date= date, Order_Type = OType, Volume = OSize )

table.insert(data)

error message:

Traceback (most recent call last):

File "", line 1, in

runfile('C:/Users/SwigelUser/Documents/GitHub/Trading/Strategies/strat_MA_ExecTest.py', wdir='C:/Users/SwigelUser/Documents/GitHub/Trading/Strategies')

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 880, in runfile

execfile(filename, namespace)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 102, in execfile

exec(compile(f.read(), filename, 'exec'), namespace)

File "C:/Users/SwigelUser/Documents/GitHub/Trading/Strategies/strat_MA_ExecTest.py", line 69, in

MA_Stra(start_length=7,end_length=10,start_date=date(2016,5,12),end_date=date(2016,6,18))

File "C:/Users/SwigelUser/Documents/GitHub/Trading/Strategies/strat_MA_ExecTest.py", line 66, in MA_Stra

table.insert(data1)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\dataset\persistence\table.py", line 87, in insert

res = self.database.executable.execute(self.table.insert(row))

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 945, in execute

return meth(self, multiparams, params)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 263, in _execute_on_connection

return connection._execute_clauseelement(self, multiparams, params)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1053, in _execute_clauseelement

compiled_sql, distilled_params

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context

context)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in _handle_dbapi_exception

util.reraise(*exc_info)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise

raise value

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context

context)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute

cursor.execute(statement, parameters)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\cursors.py", line 164, in execute

query = self.mogrify(query, args)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\cursors.py", line 143, in mogrify

query = query % self._escape_args(args, conn)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\cursors.py", line 123, in _escape_args

return dict((key, conn.literal(val)) for (key, val) in args.items())

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\cursors.py", line 123, in

return dict((key, conn.literal(val)) for (key, val) in args.items())

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\connections.py", line 821, in literal

return self.escape(obj, self.encoders)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\connections.py", line 814, in escape

return escape_item(obj, self.charset, mapping=mapping)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\converters.py", line 27, in escape_item

val = encoder(val, mapping)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\converters.py", line 110, in escape_unicode

return u"'%s'" % _escape_unicode(value)

File "C:\Users\SwigelUser\Anaconda3\lib\site-packages\pymysql\converters.py", line 73, in _escape_unicode

return value.translate(_escape_table)

AttributeError: 'numpy.float64' object has no attribute 'translate'

What have caused this error? How could I solve this.

It seems like I entered too much code here,thus I will have to type lots of none sense in order to submit.

my price and date variable comes from a dataframe:

for i in range(len(All_Tickers)-1-len(Current_Date),len(All_Tickers)-1):

price = All_Tickers[str(length) + 'day_MA'][i]

date = All_Tickers['Date'][i+1]

According to the answer below, the problem should be the type of price is np.float64, how can I convert this variable to type float?

解决方案

Your library tries to format the provided arguments to a format MySQL will understand. To do so, it checks the type of each argument, to determine how the input should be formatted.

However, since your lib doesn't knows numpy.float64, it fallbacks to a default encoder, which happens to be one for strings (unicode). Here is the relevent piece of code.

def escape_item(val, charset, mapping=None):

if mapping is None:

mapping = encoders

encoder = mapping.get(type(val))

# Fallback to default when no encoder found

if not encoder:

try:

encoder = mapping[text_type]

except KeyError:

raise TypeError("no default type converter defined")

if encoder in (escape_dict, escape_sequence):

val = encoder(val, charset, mapping)

else:

val = encoder(val, mapping)

return val

This encoder, assuming the input is indeed a string, tries to call the translate() method on this string. But, since this method isn't defined for float64, you get this error.

You should try to convert your float64 to a regular float.

Or, you can create your own encoder, and add it in the encoders dict used as the default mapping of python types to encoder. If you're going to use a lot this lib with float64, it may be worth doing.