Escape SQL "LIKE" значение для Postgres с psycopg2

Ответ 1

Да, это настоящий беспорядок. Оба MySQL и PostgreSQL используют для этого по умолчанию обратную косую черту. Это ужасная боль, если вы снова избегаете строки с помощью обратных косых черт вместо использования параметризации, а также неверны в соответствии с ANSI SQL: 1992, в котором говорится, что по умолчанию нет лишних символов escape поверх нормального экранирования строки и следовательно, нет возможности включить литерал % или _.

Я бы предположил, что простой метод обратной косой черты также ошибочен, если вы отключите обратные слэши-экраны (которые сами не соответствуют ANSI SQL), используя NO_BACKSLASH_ESCAPE sql_mode в MySQL или standard_conforming_strings conf в PostgreSQL ( которые разработчики PostgreSQL угрожают сделать для нескольких версий сейчас).

Единственное реальное решение - использовать малоизвестный синтаксис LIKE...ESCAPE для указания явного escape-символа для LIKE -pattern. Это используется вместо обратного слэша в MySQL и PostgreSQL, что делает их совместимыми с тем, что делают все остальные, и дает гарантированный способ включения внеполосных символов. Например, с знаком = в качестве escape:

# look for term anywhere within title
term= term.replace('=', '==').replace('%', '=%').replace('_', '=_')
sql= "SELECT * FROM things WHERE description LIKE %(like)s ESCAPE '='"
cursor.execute(sql, dict(like= '%'+term+'%'))

Это работает с базами данных PostgreSQL, MySQL и ANSI SQL (по модулю, который, конечно же, изменяется на разных модулях db).

По-прежнему может возникнуть проблема с MS SQL Server/Sybase, которая, по-видимому, также допускает [a-z] -символьные группы символов в выражениях LIKE. В этом случае вы также захотите избежать символа [ с .replace('[', '=['). Однако, согласно ANSI SQL, экранирование символов, которые не требуют экранирования, недействительно! (Argh!) Поэтому, хотя он, вероятно, все еще будет работать через настоящие СУБД, вы все равно не будете ANSI-совместимым. Вздох...

Ответ 2

Вы также можете взглянуть на эту проблему под другим углом. Что ты хочешь? Вам нужен запрос, который для любого строкового аргумента выполняет LIKE, добавляя "%" к аргументу. Хороший способ выразить это, не прибегая к функциям и расширениям psycopg2, может быть:

sql = "... WHERE ... LIKE %(myvalue)s||'%'"
cursor.execute(sql, { 'myvalue': '20% of all'})

Ответ 3

Вместо того, чтобы избегать символа процента, вместо этого вы можете использовать реализацию регулярного выражения PostgreSQL.

Например, следующий запрос к системным каталогам предоставит список активных запросов, которые не относятся к подсистеме автовыпускания:

SELECT procpid, current_query FROM pg_stat_activity
WHERE (CURRENT_TIMESTAMP - query_start) >= '%s minute'::interval
AND current_query !~ '^autovacuum' ORDER BY (CURRENT_TIMESTAMP - query_start) DESC;

Поскольку этот синтаксис запроса не использует ключевое слово "LIKE", вы можете делать то, что хотите... и не мутить воды по отношению к python и psycopg2.

Ответ 4

Мне удалось избежать % с помощью %% в операнде LIKE.

sql_query = "select * from mytable where website like '%%.com'"
cursor.fetchall(sql_query)

Ответ 5

Интересно, действительно ли все это необходимо. Я использую psycopg2 и просто могу использовать:

data_dict['like'] = psycopg2.Binary('%'+ match_string +'%')
cursor.execute("SELECT * FROM some_table WHERE description ILIKE %(like)s;", data_dict)

Ответ 6

Не удалось найти встроенную функцию, написанную мной, довольно просто:

def escape_sql_like(s):
    return s.replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_')

Ответ 7

Вы можете создать подклассы Like str и зарегистрировать адаптер для него, чтобы преобразовать его в правильном синтаксисе (например, используя escape_sql_like(), который вы написали).

Ответ 8

Я сделал некоторые изменения в приведенном выше коде, чтобы сделать следующее:

def escape_sql_like(SQL):
    return SQL.replace("'%", 'PERCENTLEFT').replace("%'", 'PERCENTRIGHT')

def reescape_sql_like(SQL):
    return SQL.replace('PERCENTLEFT', "'%").replace('PERCENTRIGHT', "%'")

SQL = "SELECT blah LIKE '%OUCH%' FROM blah_tbl ... "
SQL = escape_sql_like(SQL)
tmpData = (LastDate,)
SQL = cur.mogrify(SQL, tmpData)
SQL = reescape_sql_like(SQL)
cur.execute(SQL)

Ответ 9

Я нашел лучший взломать. Просто добавьте "%" к вашему поисковому запросу.

con, queryset_list = psycopg2.connect(**self.config), None
cur = con.cursor(cursor_factory=RealDictCursor)
query = "SELECT * "
query += " FROM questions WHERE  body LIKE %s OR title LIKE %s  "
query += " ORDER BY questions.created_at"
cur.execute(query, ('%'+self.q+'%', '%'+self.q+'%'))