'numpy.float64'对象没有属性'translate'将值插入Python中的Mysql [英] 'numpy.float64' object has no attribute 'translate' Inserting value to Mysql in Python

问题描述

import dataset
db = dataset.connect(....)
table = db[...]

当我尝试将一些值插入Mysql表时，发生此错误.

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)

错误消息:

Traceback (most recent call last):

  File "<ipython-input-3-b7ab0c98f72f>", line 1, in <module>
    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 <module>
    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 <genexpr>
    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]

根据下面的答案，问题应该是价格类型为np.float64，如何将这个变量转换为float类型?

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?

推荐答案

您的库尝试将提供的参数格式化为MySQL可以理解的格式.为此，它将检查每个参数的类型，以确定应如何格式化输入.

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.

但是，由于您的lib不知道numpy.float64，因此它将回退到默认编码器，该编码器恰好是 strings (unicode)的编码器. 这是相关的代码段.

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

假设输入确实是一个字符串，则此编码器尝试在此字符串上调用translate()方法.但是，由于未为float64定义此方法，因此会出现此错误.

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.

您应该尝试将float64转换为常规float.

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

或者，您可以创建自己的编码器，并将其添加到encoders字典中，该字典用作python类型到编码器的默认映射.如果要在float64上大量使用此lib，则可能值得这样做.

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.

这篇关于'numpy.float64'对象没有属性'translate'将值插入Python中的Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！