Python MySQLdb上传UnicodeEncodeError

Question

我有一个问题，我可以将 CSV 文件上传到 MySQL，但随后发生了一些事情，我得到了一个编码错误。有人可以检查我的代码并告诉我哪里出了问题吗？我是新手。

以下代码段是我编写将要上传的 CSV 文件的方式，数据是使用 MDN 工具 (mdb-export) 从 MDB 文件中提取的:

    tableIndex  = 1
    for tName in tableNames:
        fileName = os.path.join(csvPath, os.path.basename(mdb).split('.')[0] + '_' + tName + '.csv')

        try:
            p = subprocess.Popen(["mdb-export", "-H", mdb, tName], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            tableContent, error = p.communicate()

            if(p.returncode != 0):
                _logger.error('[%3d] Export Subprocess %d %s' % (tID, p.returncode, tableContent))
                SendMdbError(tID, mdb, _logger, 'ALERT: Export Subprocess')
                return(['', False])
            if(error):
                _logger.error('[%3d] Export Communicate %d %s' % (tID, p.returncode, error.strip()))
                SendMdbError(tID, mdb, _logger, 'ALERT: Export Communicate')
                return(['', False])

        except Exception as ex:
            _logger.exception('[%3d] Export Error' % tID)
            SendMdbError(tID, mdb, _logger, 'ALERT: Export Exception')
            return(['', False])
        except:
            _logger.exception('[%3d] Export Unexpected' % tID)
            SendMdbError(tID, mdb, _logger, 'ALERT: Export Unexpected')
            return(['', False])

        # If no data, no need for corresponding SQL
        if(len(tableContent) == 0):
            emptyTables.append(tName)

        # If data exists, dump data
        else:
            # Add the 'DriveTest' to the data to upload
            tableContent = tableContent.split('\n')

            tableContent = [dt + ',' + line for line in tableContent if(line)]
            tableContent = '\n'.join(tableContent)

            try:
                with open(fileName, 'wb') as f:
                    f.write(tableContent)

                    if(_VERBOSITY):
                        _logger.debug('[%3d] %3d - Write CSV SIZE[%8d] FILE: %s' %(tID, tableIndex, len(tableContent.split('\n')), fileName))
                        tableIndex += 1

            except IOError as err:
                _logger.exception('[%3d] Write IOError: %s' % (tID, str(err)))
                SendMdbError(tID, mdb, _logger, 'ALERT: Write IOError')
                return(['', False])
            except Exception as ex:
                _logger.exception('[%3d] Write Exception' % tID)
                SendMdbError(tID, mdb, _logger, 'ALERT: Write Exception')
                return(['', False])
            except:
                _logger.exception('[%3d] Write Unexpected: %s' % tID)
                SendMdbError(tID, mdb, _logger, 'ALERT: Write Unexpected')
                return(['', False])

下面是我上传CSV文件的地方，这里是我得到错误的地方:

    # Upload the data
    tableIndex = 0
    for table in tableDDL:
        try:

            with warnings.catch_warnings(record=True) as war:

                _logger.info('[%3d] %3d Going up... %s' %(tID, tableIndex+1, os.path.basename(mdb).split('.')[0] + '_' + table))

                _sqlLock[tableIndex].acquire()
                #self.cursor.execute(tableDDL[table])
                self.cursor.execute(tableULD[table])
                self.conn.commit()
                _sqlLock[tableIndex].release()

                if(war):
                    #if(_VERBOSITY): print('[%3d] %3d WARNINGS[%3d] %s' % (tID, tableIndex+1, len(war), os.path.basename(mdb).split('.')[0] + '_' + table))
                    _logger.warning('[%3d] %3d WARNINGS[%3d] %s' % (tID, tableIndex+1, len(war), os.path.basename(mdb).split('.')[0] + '_' + table))
                    for w in war:
                        _logger.warning('[%3d] %s' % (tID, w.message))

                #if(_VERBOSITY): print('[%3d] %3d Uploaded %s' % (tID, tableIndex+1, os.path.basename(mdb).split('.')[0] + '_' + table))
                _logger.info('[%3d] %3d Uploaded %s' % (tID, tableIndex+1, os.path.basename(mdb).split('.')[0] + '_' + table))
                tableIndex += 1

                # Remove the uploaded CSV file
                try:
                    os.remove(csvFiles[table]+'.csv')
                    _logger.info('[%3d] Removed CVS file: %s' % (tID, csvFiles[table]+'.csv'))
                except OSError:
                    pass

        except (MySQLdb.InternalError, MySQLdb.NotSupportedError) as err:
            _logger.error('[%3d] %3d Internal: %s %s' % (tID, tableIndex+1, err, sys.exc_info()[0]))
            self.conn.rollback()
            self.Disconnect(tID, _logger, _VERBOSITY, _DEBUG)
            return(False)
        except MySQLdb.OperationalError as err:
            _logger.error('[%3d] %3d OperationalError: %s' % (tID, tableIndex+1, sys.exc_info()[0]))
            _logger.error(err)
            self.conn.rollback()
            self.Disconnect(tID, _logger, _VERBOSITY, _DEBUG)
            return(False)
        except MySQLdb.ProgrammingError as err:
            _logger.error('[%3d] %3d ProgrammingError: %s' % (tID, tableIndex+1, sys.exc_info()[0]))
            _logger.error(err)
            self.conn.rollback()
            self.Disconnect(tID, _logger, _VERBOSITY, _DEBUG)
            return(False)
        except MySQLdb.Error as err:
            _logger.error('[%3d] %3d QUERY: %s %s' % (tID, tableIndex+1, err, sys.exc_info()[0]))
            self.conn.rollback()
            self.Disconnect(tID, _logger, _VERBOSITY, _DEBUG)
            return(False)
        except Exception as err:
            _logger.error('[%3d] %3d Exception: %s %s' % (tID, tableIndex+1, err, sys.exc_info()[0]))
            #self.conn.rollback()
            #self.Disconnect(tID, _logger, _VERBOSITY, _DEBUG)
            #return(False)
            pass
        except:
            _logger.error('[%3d] %3d Other: %s' % (tID, tableIndex+1, sys.exc_info()[0]))
            self.conn.rollback()
            self.Disconnect(tID, _logger, _VERBOSITY, _DEBUG)
            return(False)

我得到的错误如下:

2015-06-13 19:42:21,743 __main__ -    ERROR - [  1]   1 Exception: 'ascii' codec can't encode character u'\xb4' in position 40: ordinal not in range(128) <type 'exceptions.UnicodeEncodeError'>
2015-06-13 19:42:30,962 __main__ -    ERROR - [  1]   1 Exception: 'ascii' codec can't encode character u'\xb4' in position 27: ordinal not in range(128) <type 'exceptions.UnicodeEncodeError'>

我注意到给定的数据已上传，但不确定是否所有行都已上传。

谢谢!

Answer 1

在将 csv 放入 DB s.decode('UTF-8') 之前以及从 DB s.encode('UTF-8') 中尝试>

我为 SQLite 做了它并且工作正常。