Почему я получаю дополнительные escape-символы, когда вставляю символы юникода в базы данных sqlite3 с помощью Python 2.7?

Question

Я запрашиваю API и получаю блоб json со следующим значением:

{
    ...
    "Attribute" : "Some W\u00e9irdness", 
    ...
}

(Конечно, правильное значение - "Some Wéirdness")

Я добавляю это значение вместе с некоторыми другими вещами в список полей, которые я хочу добавить в свою базу данных sqlite3. Список выглядит так:

[None, 203, None, None, True, u'W\xe9irdness', None, u'Some', None, None, u'Some W\xe9irdness', None, u'Some W\xe9irdness', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]

Я заметил, что мы уже перенесли переход с \x00e9 на \xe9, и я не уверен, почему это так, но я надеялся, что это не имеет значения... это просто другая кодировка Unicode.

Прежде чем пытаться вставить в таблицу sqlite, я 'stringatize' список (см. Функцию ниже) и сделать его кортеж:

('', '203', '', '', 'True', 'W\xe9irdness', '', 'Some', '', '', 'Some W\xe9irdness', '', 'Some W\xe9irdness', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

Затем я делаю вставку:

my_tuple = tuple(val for val in my_utils.stringatize(my_list))

sql = "INSERT OR REPLACE INTO roster VALUES %s" % repr(my_tuple)

cur.execute(sql)

Когда я получаю его позже с помощью инструкции SELECT, в значение добавляется дополнительный символ экранирования (обратная косая черта):

u'Some W\\xe9irdness'

Во-первых, я уже знаю, что я не должен использовать интерполяцию строк с sqlite. Тем не менее, я не мог понять, как это сделать? - когда число полей в записи может меняться со временем, и я хочу, чтобы код был гибким, и мне не приходилось возвращаться и добавлять туда знаки вопроса, если я добавлю поля. (Если вы знаете лучший способ сделать это, я весь слух, но это, вероятно, для другого поста.)

Для устранения неполадок я печатаю отформатированный оператор SQL вставки и вижу только ОДНУ обратную косую черту:

INSERT OR REPLACE INTO roster VALUES ('', '203', '', '', 'True', 'W\xe9irdness', '', 'Some', '', '', 'Some W\xe9irdness', '', 'Some W\xe9irdness', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

Это выглядит так же, как в приведенном выше списке, поэтому я озадачен. Возможно, это интерпретируется как строка с обратной косой чертой, которую необходимо экранировать, а xe9 просто обрабатывается как текст ascii. Вот функция stringatize, которую я использую для подготовки списка к вставке:

def stringatize(cell_list, encoding = 'raw_unicode_escape', delete_quotes = False):
    """
    Converts every 'cell' in a 'row' (generally something extracted from
     a spreadsheet) to a unicode, then returns the list of cells (with all
     strings now, of course).
    """

    stringatized_list = []

    for cell in cell_list:
        if isinstance(cell, (datetime.datetime)):
            new = cell.strftime("%Y-%m-%dT%H:%M:%S")
        elif isinstance(cell, (datetime.date)):
            new = cell.strftime("%Y-%m-%d")
        elif isinstance(cell, (datetime.time)):
            new = cell.strftime("%H:%M:%S")
        elif isinstance(cell, (int, long)):
            new = str(cell)    
        elif isinstance(cell, (float)):    
            new = "%.2f" % cell
        elif cell == None:
            new = ""    
        else:                
            new = cell    

        if delete_quotes:    
            new = new.replace("\"","")   

        my_unicode = new.encode(encoding)    
        stringatized_list.append(my_unicode)

    return stringatized_list

Я ценю любые идеи, которые у вас есть для меня на этом фронте. Цель состоит в том, чтобы в конечном итоге выгрузить это значение в лист Excel, который работает с Unicode и поэтому должен правильно отображать это значение.

РЕДАКТИРОВАТЬ: В ответ на запрос @CL, я пытаюсь удалить строку 'кодировать' из моей функции stringatize.

Теперь это заканчивается следующим образом:

    #my_unicode = new.encode(encoding)
    my_unicode = new

    stringatized_list.append(my_unicode)

return stringatized_list

Новый sql выглядит следующим образом (и ниже приведена трассировка, которую я получаю, когда пытаюсь выполнить это):

INSERT OR REPLACE INTO roster VALUES ('', u'203', u'', u'', 'True', u'W\xe9irdness', '', u'Some', '', '', u'Some W\xe9irdness', '', u'Some W\xe9irdness', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

Traceback (most recent call last):
  File "test.py", line 80, in <module>
    my_call
  File redacted.py, line 102, in my_function
    cur.execute(sql)
sqlite3.OperationalError: near "'203'": syntax error

Я хотел бросить это число в строку. Я подозреваю, что это связано с repr(my_tuple), который я делаю, и u '' фактически больше не символизирует юникод.

Answer 1

"Some W\u00e9irdness"
"Some Wéirdness"

Являются одинаково допустимыми строковыми литеральными формами JSON с одинаковым значением, Some Wéirdness,

u'W\xe9irdness'

Я заметил, что мы уже перенесли переход с \x00e9 на \xe9, и я не уверен, почему это так, но я надеялся, что это не имеет значения... это просто другая кодировка Unicode.

Нет переключателя и нет кодировки, строка по-прежнему Some Wéirdness,

Вы только что напечатали строку из Python, и в строковых литералах Python есть \xNN форма, которой нет в JSON, сокращение для \u00NN,

my_tuple = tuple(val for val in my_utils.stringatize(my_list))
sql = "INSERT OR REPLACE INTO roster VALUES %s" % repr(my_tuple)
cur.execute(sql)

Не делай этого. Литерал кортежа Python, созданный repr совсем не тот же формат, что и список значений SQL. В частности, строковые литералы SQL не имеют какой-либо концепции обратной косой черты, поэтому \xE9 это обозначает é в строковом литерале Юникода Python, в SQL просто означает обратную косую черту, буквы x, E и число 9,

Несмотря на то, что существуют подходящие способы кодирования строки для размещения в строковом литерале SQL, следует избегать этого, потому что правильно понять это не просто, а ошибиться - проблема безопасности. Вместо этого забудьте "stringatizing" и просто передайте необработанные значения в базу данных в качестве параметров:

cur.execute(
    'INSERT OR REPLACE INTO roster VALUES (?, ?, ?, ?, ....)',
    my_list
)