我正在从谷歌表中读取查询作为文本,该表在下面作为“str1”传递。这是我的代码:
# get query string from google sheets
# establish database connection
cursor = conn.cursor()
cursor.execute((str1))
results1 = cursor.fetchall()
cursor.close()
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
for row in results1:
ws.append(row)
此时,我收到标题所示的错误:
File "<stdin>", line 2, in <module>
File "/Library/Python/2.7/site-packages/openpyxl/worksheet/worksheet.py", line 790, in append
cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
File "/Library/Python/2.7/site-packages/openpyxl/cell/cell.py", line 114, in __init__
self.value = value
File "/Library/Python/2.7/site-packages/openpyxl/cell/cell.py", line 294, in value
self._bind_value(value)
File "/Library/Python/2.7/site-packages/openpyxl/cell/cell.py", line 191, in _bind_value
value = self.check_string(value)
File "/Library/Python/2.7/site-packages/openpyxl/cell/cell.py", line 150, in check_string
value = unicode(value, self.encoding)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x92 in position 48: invalid start byte
数据与作者/文章信息有关(我们是出版商)。它包含内容 ID、站点代码、署名、作者、Facebook 广告链接和拉取日期/时间
这是包含错误的一行数据的示例:
(1693279, 'CPD', 'Morgan Dietrich', "20 Intuitive People Share Their 'Something Doesn\x92t Feel Right' Story That Turned Out To Be True", 'business.facebook.com/550634765042035/posts/…;, datetime.datetime(2017, 11, 29, 20, 49, 24))
我已经阅读了很多有关此错误的问题,但无法找到解决方案。查询的结果 (results1) - 成功运行 - 是一个像这样的元组:
( (query result ro1/col1, query result ro1/col2, query result ro1/col3),
(query result ro2/col1, query result ro2/col2, query result ro2/col3), ... etc... )
我试过 .encode/.decode 但它们似乎不适用于元组。我试过过滤坏字符,但没有奏效。
我到底该如何解决这个问题?这些与 utf8 相关的错误在过去曾让我感到非常烦恼,尽管我阅读了所有内容,但似乎仍然相当混乱。
失败的行是
(1693279,
'CPD',
'Morgan Dietrich',
"20 Intuitive People Share Their 'Something Doesn\x92t Feel Right' Story That Turned Out To Be True",
'https://business.facebook.com/550634765042035/posts/1223000787805426',
datetime.datetime(2017, 11, 29, 20, 49, 24))
您有包含非 ASCII 和非 UTF8 数据的字节串数据。您要么必须将数据库配置为为第 4 列返回 Unicode 字符串,要么手动解码。
如果将数据解码为 Windows 代码页 1252(或同一范围内的许多其他 Windows 代码页,但 1252 最有可能用于其他英文文本),则 0x92 字节是U+2019 右单引号代码点,因此您可以试试吧:
for row in results1:
row = list(row) # convert to list for easier mutation
row[3] = row[3].decode('cp1252')
ws.append(row)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句