Вопрос или проблема
Я хочу распарсить данные из xml, но когда я пытаюсь вставить эти данные в базу данных SQL через db_cursor.execute, я получаю ошибку, потому что у меня есть некоторые данные с одинарными и двойными кавычками в одном столбце:
import re
import os
import pyodbc
import xml.etree.ElementTree as ET
login_db = '\*\*\*\*\*'
passwd_db ='\*\*\*\*\*\*\*'
db_str = ("Driver={PostgreSQL UNICODE(x64)};"
"Server=\*\*\*\*\*\*\*;"
"Port=5432;"
"Database=inventory_UAT;"
"Uid=%s;"
"Pwd=%s") % (login_db, passwd_db)
db_cnxn = pyodbc.connect(db_str,autocommit=True)
db_cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
db_cnxn.setencoding(encoding='utf-8')
db_cursor = db_cnxn.cursor()
import xml.etree.ElementTree as ET
tree1 = ET.parse(r"C:\\Mine\\diverts.xml")
root1 = tree1.getroot()
for div in root1.findall('{urn:activemq:core}divert'):
source = div.find('{urn:activemq:core}address').text
target = div.find('{urn:activemq:core}forwarding-address').text
name = div.attrib\['name'\]
filtr = ""
for i in div.findall('{urn:activemq:core}filter'):
filtr = i.attrib\['string'\]
qry = (("""INSERT INTO inventory.artemis_divert_inventory
(divert_name,from_address,to_address,filter,instance_type,last_update)
VALUES ('%s','%s','%s','%s','CORP-UAT',NOW());""")
%(name, source, target, filtr))
\# print(qry)
db_cursor.execute(qry)
Вот ошибка:
pyodbc.ProgrammingError: (‘42601’, ‘[42601] ERROR: syntax error at or near “KondorSDEvent_uat1”;\nОшибка при выполнении запроса (1) (SQLExecDirectW)’)
Позвольте мне добавить некоторые из этих данных в столбец фильтра:
_AMQ_ORIG_ADDRESS is null and (MessageType=”CommodityReset” or (MessageType=”Product” and ProductTy
pe=”Commodity”
Publisher IN (‘KondorSDEvent_uat1′,’AdstisSDEvent_uat1’)
Я вообще не знаю, как решить эту проблему
Ответ или решение
Ошибка, с которой вы столкнулись, связана с синтаксисом SQL, вызванным неэкранированными одинарными и двойными кавычками в строке, которая вставляется в базу данных PostgreSQL. Как правило, такие ошибки возникают, если строки, содержащие специальные символы, не обрабатываются должным образом перед выполнением SQL-запросов.
Причина проблемы
В вашем коде динамически создается SQL-запрос, который включает значения, полученные из XML-данных. Когда эти значения содержат одинарные кавычки, они нарушают синтаксис SQL. Например, строка:
Publisher IN ('KondorSDEvent_uat1','AdstisSDEvent_uat1')Если filtr содержит подобную строку с кавычками, SQL-запрос становится недопустимым.
Рекомендации по решению проблемы
1. Использование параметризованных запросов
Наиболее безопасный и правильный подход — использовать параметризованные запросы. Это не только помогает избежать проблем с синтаксисом, но и повышает безопасность за счет предотвращения SQL-инъекций. Вместо формирования запроса в виде форматированной строки, используйте вопросительные знаки в качестве плейсхолдеров:
qry = """INSERT INTO inventory.artemis_divert_inventory
(divert_name, from_address, to_address, filter, instance_type, last_update)
VALUES (?, ?, ?, ?, 'CORP-UAT', NOW());"""
params = (name, source, target, filtr)
db_cursor.execute(qry, params)2. Экранирование строк
Если по какой-то причине не удается использовать параметризованные запросы, необходимо экранировать одинарные кавычки в строках, заменяя их на две одинарные кавычки:
filtr = filtr.replace("'", "''")Однако выполнение этого подхода менее предпочтительно, поскольку оно может быть подвержено ошибкам, особенно в случае более сложных строк.
3. Проверка корректности данных
Также имеет смысл проверять данные, которые вы извлекаете из XML, и убедиться, что они не содержат неподходящих символов, если вы не контролируете входные данные. Например, вы можете добавить логику для обработки или фильтрации данных перед их вставкой в базу данных.
Пример исправленного кода
Вот пример вашего кода с использованием параметризованных запросов:
import pyodbc
import xml.etree.ElementTree as ET
login_db = '*****'
passwd_db = '******'
db_str = ("Driver={PostgreSQL UNICODE(x64)};"
"Server=********;"
"Port=5432;"
"Database=inventory_UAT;"
"Uid=%s;"
"Pwd=%s") % (login_db, passwd_db)
db_cnxn = pyodbc.connect(db_str, autocommit=True)
db_cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
db_cnxn.setencoding(encoding='utf-8')
db_cursor = db_cnxn.cursor()
tree1 = ET.parse(r"C:\\Mine\\diverts.xml")
root1 = tree1.getroot()
for div in root1.findall('{urn:activemq:core}divert'):
source = div.find('{urn:activemq:core}address').text
target = div.find('{urn:activemq:core}forwarding-address').text
name = div.attrib['name']
filtr = ""
for i in div.findall('{urn:activemq:core}filter'):
filtr = i.attrib['string']
# Параметризованный запрос
qry = """INSERT INTO inventory.artemis_divert_inventory
(divert_name, from_address, to_address, filter, instance_type, last_update)
VALUES (?, ?, ?, ?, 'CORP-UAT', NOW());"""
params = (name, source, target, filtr)
db_cursor.execute(qry, params)Заключение
Использование параметризованных запросов — это наилучший и наиболее безопасный способ обработки SQL-запросов в вашем приложении. Это позволяет избежать ошибок синтаксиса и снижает риск SQL-инъекций, что особенно важно для обеспечения безопасности ваших данных и систем.