如何在Python和Postgres中处理批量数据库导入中的重音字符
在运行一个用Python写的批量导入脚本（openblock）时，我遇到了一个问题，出现了一个无效的字节序列，编码为“UTF8”：0xca4e，这个错误是因为一个带重音符的字符。
显示出来的内容是：
GRAND-CH?NE, COUR DU
但实际上应该是“GRAND-CHÊNE, COUR DU”。
我该怎么处理这个问题呢？理想情况下，我想保留这个带重音符的字符。我怀疑我需要以某种方式对它进行编码。
编辑：这里的“?”实际上应该是“Ê”。另外要注意，这个变量是来自一个ESRI Shapefile。当我尝试davidcrow的解决方案时，出现了“Unicode不支持”的错误，因为没有重音符的字符串可能已经是Unicode字符串了。
这是我使用的ESRIImporter代码：
from django.contrib.gis.gdal import DataSource
class EsriImporter(object):
def __init__(self, shapefile, city=None, layer_id=0):
print >> sys.stderr, 'Opening %s' % shapefile
ds = DataSource(shapefile)
self.layer = ds[layer_id]
self.city = "OTTAWA" #city and city or Metro.objects.get_current().name
self.fcc_pat = re.compile('^(' + '|'.join(VALID_FCC_PREFIXES) + ')\d$')
def save(self, verbose=False):
alt_names_suff = ('',)
num_created = 0
for i, feature in enumerate(self.layer):
#if not self.fcc_pat.search(feature.get('FCC')):
# continue
parent_id = None
fields = {}
for esri_fieldname, block_fieldname in FIELD_MAP.items():
value = feature.get(esri_fieldname)
#print >> sys.stderr, 'Looking at %s' % esri_fieldname
if isinstance(value, basestring):
value = value.upper()
elif isinstance(value, int) and value == 0:
value = None
fields[block_fieldname] = value
if not ((fields['left_from_num'] and fields['left_to_num']) or
(fields['right_from_num'] and fields['right_to_num'])):
continue
# Sometimes the "from" number is greater than the "to"
# number in the source data, so we swap them into proper
# ordering
for side in ('left', 'right'):
from_key, to_key = '%s_from_num' % side, '%s_to_num' % side
if fields[from_key] > fields[to_key]:
fields[from_key], fields[to_key] = fields[to_key], fields[from_key]
if feature.geom.geom_name != 'LINESTRING':
continue
for suffix in alt_names_suff:
name_fields = {}
for esri_fieldname, block_fieldname in NAME_FIELD_MAP.items():
key = esri_fieldname + suffix
name_fields[block_fieldname] = feature.get(key).upper()
#if block_fieldname == 'postdir':
#print >> sys.stderr, 'Postdir block %s' % name_fields[block_fieldname]
if not name_fields['street']:
continue
# Skip blocks with bare number street names and no suffix / type
if not name_fields['suffix'] and re.search('^\d+$', name_fields['street']):
continue
fields.update(name_fields)
block = Block(**fields)
block.geom = feature.geom.geos
print repr(fields['street'])
print >> sys.stderr, 'Looking at block %s' % unicode(fields['street'], errors='replace' )
street_name, block_name = make_pretty_name(
fields['left_from_num'],
fields['left_to_num'],
fields['right_from_num'],
fields['right_to_num'],
'',
fields['street'],
fields['suffix'],
fields['postdir']
)
block.pretty_name = unicode(block_name)
#print >> sys.stderr, 'Looking at block pretty name %s' % fields['street']
block.street_pretty_name = street_name
block.street_slug = slugify(' '.join((unicode(fields['street'], errors='replace' ), fields['suffix'])))
block.save()
if parent_id is None:
parent_id = block.id
else:
block.parent_id = parent_id
block.save()
num_created += 1
if verbose:
print >> sys.stderr, 'Created block %s' % block
return num_created
输出：
'GRAND-CH\xcaNE, COUR DU'
Looking at block GRAND-CH�NE, COUR DU
Traceback (most recent call last):
File "../blocks_ottawa.py", line 144, in <module>
sys.exit(main())
File "../blocks_ottawa.py", line 139, in main
num_created = esri.save(options.verbose)
File "../blocks_ottawa.py", line 114, in save
block.save()
File "/home/chris/openblock/src/django/django/db/models/base.py", line 434, in save
self.save_base(using=using, force_insert=force_insert, force_update=force_update)
File "/home/chris/openblock/src/django/django/db/models/base.py", line 527, in save_base
result = manager._insert(values, return_id=update_pk, using=using)
File "/home/chris/openblock/src/django/django/db/models/manager.py", line 195, in _insert
return insert_query(self.model, values, **kwargs)
File "/home/chris/openblock/src/django/django/db/models/query.py", line 1479, in insert_query
return query.get_compiler(using=using).execute_sql(return_id)
File "/home/chris/openblock/src/django/django/db/models/sql/compiler.py", line 783, in execute_sql
cursor = super(SQLInsertCompiler, self).execute_sql(None)
File "/home/chris/openblock/src/django/django/db/models/sql/compiler.py", line 727, in execute_sql
cursor.execute(sql, params)
File "/home/chris/openblock/src/django/django/db/backends/util.py", line 15, in execute
return self.cursor.execute(sql, params)
File "/home/chris/openblock/src/django/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
return self.cursor.execute(query, args)
django.db.utils.DatabaseError: invalid byte sequence for encoding "UTF8": 0xca4e
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
3 个回答
-3
你可以试试下面这个：
uString = unicode(item.field, "utf-8")
想了解更多关于Unicode和Python的内容，可以查看这个链接：http://evanjones.ca/python-utf8.html。
1
看起来数据没有以UTF-8格式发送……所以你需要检查一下数据库会话中的client_encoding参数，确保它和你的数据匹配，或者在用Python读取文件时把它转换成UTF-8/Unicode格式。
你可以通过使用“SET client_encoding = 'ISO-8859-1'”或者类似的命令来改变数据库会话的客户端编码。不过，0xca在Latin1编码中并不是带重音的E，所以我不太确定你的文件使用的是什么字符编码？
3
请提供更多信息。你使用的是哪个平台 - Windows / Linux / 其他？
你用的Python版本是什么？
如果你在Windows上运行，编码方式更可能是 cp1252 或类似的，而不是 ISO-8859-1。肯定不是 UTF-8。
你需要做的是： (1) 找出你的输入数据是用什么编码的。试试 cp1252；这通常是个常见问题。 (2) 将你的数据解码成unicode (3) 再编码成UTF-8。
你是怎么从你的ESRI shapefile中提取数据的？请展示你的代码。把完整的错误追踪信息和错误消息也展示出来。为了避免视觉上的问题（是E-grave还是E-acute！），请使用 print repr(the_suspect_data)，然后把结果复制粘贴到你问题的编辑中。别用太多粗体字。