Поступила задача: "Выгрузить товары из базы данных Microinvest на платформу 1С-Битрикс". В связи с раздельной разработкой задача была разделена на два этапа:
Получить нужные данные в виде excel таблицы
Настроить импорт из файла excel на сайт
В первой части я использовал pyodbc для работы с базой данных MS SQL Server, ftplib для работы с FTP сервером и configparser для работы с конфигурационными файлами. С этими инструментами я смог автоматизировать весь процесс: от извлечения данных из базы данных до загрузки файла Excel на FTP сервер.
В этой статье я расскажу вам, как я это сделал, шаг за шагом. Надеюсь, это поможет вам, если вы столкнетесь с подобной или похожей задачей. Итак, давайте начнем!
Часть 1: Выгрузка данных из MS SQL Server в Excel на FTP с использованием Python
Подключение к MS SQL Server с использованием Python
Первым делом, нам нужно подключиться к нашему серверу MS SQL. Для этого мы будем использовать библиотеку pyodbc, которая позволяет Python взаимодействовать с базами данных через ODBC.
import pyodbc
import configparser
Перед тем как подключиться к серверу, нам нужно получить настройки подключения. Это можно сделать с помощью библиотеки configparser, которая позволяет работать с конфигурационными файлами. В нашем случае, мы будем использовать файл config.ini.
config = configparser.ConfigParser()
config.read('config.ini')
sql_server = config.get('SQL', 'server')
sql_database = config.get('SQL', 'database')
sql_username = config.get('SQL', 'username')
sql_password = config.get('SQL', 'password')
Теперь, когда у нас есть все необходимые настройки, мы можем подключиться к серверу:
conn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={sql_server};DATABASE={sql_database};UID={sql_username};PWD={sql_password}')
И вот так просто мы подключились к нашему серверу MS SQL с использованием Python!
Для дальнейшей конфигурации настроек подключения к базе данных и FTP создадим файл config.ini. Добавим код чтобы программа создала его автоматически и попросила пользователя обновить информацию перед следующим запуском.
if not os.path.exists('config.ini'):
config = configparser.ConfigParser()
config['SQL'] = {
'server': 'localhost',
'database': '',
'username': 'sa',
'password': 'Micr0!nvest'
}
config['FTP'] = {
'server': '',
'username': '',
'password': ''
}
with open('config.ini', 'w') as configfile:
config.write(configfile)
print("Файл config.ini создан. Пожалуйста, обновите его с правильной информацией и запустите программу снова.")
input("Нажмите Enter, чтобы закрыть программу...")
os._exit(1)
Извлечение данных из базы данных Microinvest
Теперь, когда мы подключились к серверу, мы можем начать извлекать данные. Для этого нам понадобится SQL запрос. Вот пример такого запроса:
WITH RecursiveGroups AS (
SELECT id, name, code, CAST(name AS NVARCHAR(MAX)) AS path,
CASE WHEN LEN(code) = 3 THEN name ELSE NULL END AS [group],
CASE WHEN LEN(code) = 6 THEN name ELSE NULL END AS subgroup1,
CASE WHEN LEN(code) = 9 THEN name ELSE NULL END AS subgroup2,
CASE WHEN LEN(code) = 12 THEN name ELSE NULL END AS subgroup3,
CASE WHEN LEN(code) = 15 THEN name ELSE NULL END AS subgroup4,
CASE WHEN LEN(code) = 18 THEN name ELSE NULL END AS subgroup5
FROM goodsgroups
WHERE LEN(code) = 3 AND code != '-1'
UNION ALL
SELECT g.id, g.name, g.code, CAST(rg.path + '/' + g.name AS NVARCHAR(MAX)),
CASE WHEN LEN(g.code) = 3 THEN g.name ELSE rg.[group] END,
CASE WHEN LEN(g.code) = 6 THEN g.name ELSE rg.subgroup1 END,
CASE WHEN LEN(g.code) = 9 THEN g.name ELSE rg.subgroup2 END,
CASE WHEN LEN(g.code) = 12 THEN g.name ELSE rg.subgroup3 END,
CASE WHEN LEN(g.code) = 15 THEN g.name ELSE rg.subgroup4 END,
CASE WHEN LEN(g.code) = 18 THEN g.name ELSE rg.subgroup5 END
FROM goodsgroups g
INNER JOIN RecursiveGroups rg ON LEFT(g.code, LEN(g.code) - 3) = rg.code
WHERE g.code != '-1'
)
SELECT
g.ID as [ID товара],
g.code AS [Идентификатор],
g.name AS [Название],
g.Description as [Описание],
g.Description as [Краткое описание],
g.PriceOut2 as [Цена],
CASE WHEN g.IsVeryUsed = -1 THEN 'Да' ELSE 'Нет' END as [Популярный],
CASE WHEN SUM(s.qtty) > 0 THEN 'Да' ELSE 'Нет' END as [В наличии],
SUM(s.qtty) AS [Кол-во],
g.Measure1 AS [Еденица измерения],
rg.[group] AS [Группа],
rg.subgroup1 AS [Подгруппа 1],
rg.subgroup2 AS [Подгруппа 2],
rg.subgroup3 AS [Подгруппа 3],
rg.subgroup4 AS [Подгруппа 4],
rg.subgroup5 AS [Подгруппа 5]
FROM goods g
INNER JOIN store s ON g.id = s.goodid
INNER JOIN RecursiveGroups rg ON g.groupid = rg.id
where g.Deleted = 0
GROUP BY g.id, g.name, g.code, g.IsVeryUsed, g.Description, g.PriceOut2, g.Measure1, rg.path, rg.[group], rg.subgroup1, rg.subgroup2, rg.subgroup3, rg.subgroup4, rg.subgroup5
Order by [Идентификатор]
Этот запрос извлекает все необходимые данные о товарах из базы данных.
Стоит уточнить что для нашей задачи вывод подгрупп понадобился в уникальных столбцах, но существует вариант компактнее.
Альтернативный метод
Путь до группы товара можно разместить в одном столбце через разделитель.
WITH RecursiveGroups AS (
SELECT id, name, code, CAST(name AS NVARCHAR(MAX)) AS path
FROM goodsgroups
WHERE LEN(code) = 3
UNION ALL
SELECT g.id, g.name, g.code, CAST(rg.path + ' / ' + g.name AS NVARCHAR(MAX))
FROM goodsgroups g
INNER JOIN RecursiveGroups rg ON LEFT(g.code, LEN(g.code) - 3) = rg.code
WHERE g.code != '-1'
)
SELECT
g.ID as [ID товара],
g.code AS [Идентификатор],
g.name AS [Название],
g.Description as [Описание],
g.PriceOut2 as [Цена],
CASE WHEN g.IsVeryUsed = -1 THEN 'Да' ELSE 'Нет' END as [Популярный],
CASE WHEN SUM(s.qtty) > 0 THEN 'Да' ELSE 'Нет' END as [В наличии],
SUM(s.qtty) AS [Кол-во],
g.Measure1 AS [Еденица измерения],
rg.path AS [Группа]
FROM goods g
INNER JOIN store s ON g.id = s.goodid
INNER JOIN RecursiveGroups rg ON g.groupid = rg.id
where g.Deleted = 0
GROUP BY g.id, g.name, g.code, g.IsVeryUsed, g.Description, g.PriceOut2, g.Measure1, rg.path
Order by [Идентификатор]
После того как мы определили наш SQL запрос, мы можем использовать его для извлечения данных из базы данных с помощью функции read_sql из библиотеки pandas:
df = pd.read_sql(sql_query, conn)
Однако, перед тем как мы сможем использовать эти данные, нам нужно убедиться, что они не содержат никаких недопустимых символов, которые могут нарушить формат Excel. Для этого мы можем использовать следующую функцию:
def remove_illegal_chars(val):
if isinstance(val, str):
return ''.join(ch for ch in val if ord(ch) >= 32)
return val
df = df.applymap(remove_illegal_chars)
Эта функция проходит по каждому значению в нашем наборе данных и удаляет все недопустимые символы.
Теперь у нас есть все данные, которые нам нужны, и мы готовы перейти к следующему шагу - созданию файла Excel.
Создание файла Excel с использованием Python
После того как мы извлекли все необходимые данные, мы можем сохранить их в файл Excel. Для этого мы будем использовать метод to_excel из библиотеки pandas, которая уже была использована для работы с данными.
df.to_excel('output.xlsx', index=False)
Эта строка кода создает файл Excel с именем output.xlsx и записывает в него все данные из нашего DataFrame df.
Параметр index=False говорит pandas не сохранять индексы DataFrame в файл Excel. Если вы установите index=True или просто опустите этот параметр (по умолчанию он равен True), pandas сохранит индексы DataFrame вместе с данными. В большинстве случаев это не нужно, поэтому мы устанавливаем index=False.
Теперь у нас есть файл Excel со всеми данными, которые мы извлекли из базы данных.
Загрузка файла Excel на FTP сервер с использованием Python:
После того как мы создали файл Excel, последний шаг - это загрузить его на FTP сервер. Для этого мы будем использовать библиотеку ftplib, которая позволяет Python взаимодействовать с FTP серверами.
Сначала нам нужно получить настройки подключения к FTP серверу из нашего файла config.ini:
ftp_server = config.get('FTP', 'server')
ftp_username = config.get('FTP', 'username')
ftp_password = config.get('FTP', 'password')
Теперь мы можем подключиться к FTP серверу и загрузить наш файл:
from ftplib import FTP
ftp = FTP(ftp_server)
ftp.login(user=ftp_username, passwd=ftp_password)
with open('output.xlsx', 'rb') as fp:
ftp.storbinary('STOR %s' % 'output.xlsx', fp)
ftp.quit()
Этот код подключается к FTP серверу, открывает наш файл Excel в режиме чтения бинарных данных ('rb'), загружает файл на сервер с помощью метода storbinary, а затем закрывает соединение с FTP сервером.
И вот так просто мы загрузили наш файл Excel на FTP сервер с использованием Python!
Теперь у нас есть автоматизированный процесс, который извлекает данные из базы данных Microinvest, сохраняет их в файл Excel и загружает этот файл на FTP сервер.
Для работы приложения по расписанию достаточно работы планировщика задач встроенного в Windows.
Часть 2: Импорт товаров на сайт Битрикса из Excel
Импорт товаров был необходим для интернет-магазина "Рыбное место" (https://rybnoemesto05.ru/), который функционирует на платформе 1С-Битрикс. Для реализации этой задачи мы использовали многофункциональный модуль Экспорт/Импорт (https://marketplace.1c-bitrix.ru/solutions/esol.importexportexcel/) с простыми настройками.
В процессе импорта мы указываем ссылку на полученный файл из экспорта в настройках модуля, выполняем небольшие настройки и переходим ко второму этапу.
На втором этапе мы устанавливаем соответствие между полями в файле-экспорте и полями на сайте, куда нужно загрузить информацию. На этом этапе предоставляются широкие возможности для преобразования исходных данных. Одним из таких преобразований, которое нам понадобилось, было решение проблемы загрузки изображений товаров.
Поскольку в файле-экспорте отсутствовали изображения товаров или ссылки на них, мы решили выгрузить дополнительное поле "Код товара" и применить правило преобразования: если поле "Код" не пустое, то заменить значение этой ячейки на https://rybnoemesto05.ru/docs/productImg/#VAL#.jpg, где #VAL# представляет текущее значение ячейки "Код". Мы создали папку docs/productImg на хостинге и загрузили туда изображения, при этом наименование изображений должно было совпадать с кодом товара.
На этом всё. Запускаем импорт, ждем несколько минут, и 15 категорий вместе с 1124 товарами успешно загружены на наш сайт.
