python - mysql和python的编码问题

Question

我有 python 代码，可以从更大的学校提取数据并将其存储在我们的服务器上。一切都很顺利，直到它到达 Á 角色为止。我的数据库以 UTF-8 编码，一般代码将上述字符存储为:xc1 在数据库中。我附上了 api 生成的 XML 文件。

import schoolslib, MySQLdb

cities = schoolslib.getcitylist("ca")

for city in cities:
schools = schoolslib.getschoolstest(city)
data = ['gsId', 'name', 'type', 'gradeRange', 'enrollment', 'gsRating', 'parentRating', 'city', 'state', 'districtId', 'district', 'districtNCESId', 'address', 'phone', 'fax', 'website', 'ncesId', 'lat', 'lon', 'overviewLink', 'ratingsLink', 'reviewsLink', 'schoolStatsLink']
db = MySQLdb.connect(host="localhost", user="schools", passwd="", db="schoolinfo")
cursor = db.cursor()
schooldata = [0]*23
for school in schools.iter('school'):
    for x in range(0, 23):
        schooldata[x] = school.find(data[x])
        if (schooldata[x] == None) or (schooldata[x].text == None) :
            schooldata[x] = ""
        else:
            schooldata[x] = schooldata[x].text
            schooldata[x] = schooldata[x].encode('utf-8', "ignore")

    cursor.execute("INSERT INTO school (gsId,name,type,gradeRange,enrollment,gsRating,parentRating,city,state,districtId,district,districtNCESId,address,phone,fax,website,ncesId,lat,lon,overviewLink,ratingsLink,reviewsLink,schoolStatsLink) VALUES %r;" % (tuple(schooldata),))
db.commit()

XML 文件: 阿尼莫·英格尔伍德特许高中

Answer 1

当您 connect() 到数据库时，在建立 MySQL 连接时分配 charset 和 use_unicode 参数。

db = MySQLdb.connect(host="localhost", 
                     user="schools", 
                     passwd="", 
                     db="schoolinfo",
                     charset = "utf8",
                     use_unicode =True)