Основная идея работы скрипта – генерация запросов с учетом различных параметров и последовательное их выполнение для вставки данных в существующую таблицу или выгрузка данных в файл.
Сама программа состоит из 3 частей:
1) Соединение c базой данных.
2)Определение варьируемых параметров.
3)Выполнение запросов к базе (структура построения запросов позволяет выполнять запросы последовательно или параллельно, что позволяет управлять скоростью загрузки/выгрузки данных с сервера).
Соединение с БД определяется фабрикой, в которой содержатся параметры соединения с определенным сервером и определены ссылки на классы для работы с БД.
db = DatabaseFactory().build('*наименование сервера*')Сами объекты для работы с БД содержат 3 метода:
▪
collect– запускает запрос с помощью метода read_sql библиотеки pandas и возвращает DataFrame, содержащий результат выполненного запроса;▪
execute– запускает запросы типа CREATE, UPDATE, DELETE\TRUNCATE\DROP;▪
execute_many – используется в основном для загрузки данных внутрь БД. Сама загрузка производится с помощью BULK вставки.db.collect('select top 100 * from table')
db.execute('insert into table select * from another_table')
db. execute_many ('insert into from table (id, name, age) values (?,?,?)', [1,’Jhon’, 25])Далее пользователь может задать параметры запроса с помощью метода
add_var класса SqlContext. Данный метод принимает 4 параметра: наименование колонки, значения данной переменной, условие (=, <=, >=, between и т.п.) и разделитель (под разделителем понимаются команды AND и OR).context = SqlContext()
context.add_var('col_name’, [1,2,3,4,5], separator='AND', condition='=')
context.add_var('col_name_1’, [[‘a’,’b’,’v’], [‘a1’,’b2’,’v3’],] , separator='AND', condition='in')
В случае определения нескольких параметров одновременно, в запросе они будут варьироваться по следующему правилу: сначала варьируются те параметры, которые были заданы в последнюю очередь. Если все вариации последнего параметра будут пройдены, то берутся следующее значения параметра выше и вновь перебираются все вариации последнего параметра. Так продолжается до тех пор, пока не переберутся все возможные комбинации заданных параметров.
После того, как мы определили варьируемые параметры необходимо задать сам sql запрос. Для этого создаем объект
SqlBuilder и вызываем метод custom_sql внутрь которого помещаем сам запрос:builder = SqlBuilder()
builder.custom_sql('''
INSERT INTO insertable_table
SELECT
*
FROM table
WHERE 1=1
AND col1 in (1, 2,10,98,34)
AND col2 = 9
AND col3 between ‘20200101’ and ‘20200201’
''')
или можно воспользоваться встроенными в объект методами для генерации sql (select, insert_into, create_table и т.д.):builder = SqlBuilder()
builder.select([‘col1’, ‘col2’, ‘col3’]).from(‘table’)Для запуска скрипта необходимо создать объект класса SqlGenerator, объекты SqlBuilder и SqlContext и с помощью цикла запустить обработку запроса (в качестве примера был взят вариант последовательного исполнения запроса):
generator = SqlGenerator(builder, context)
for sql in tqdm(generator.generate()):
t = time.time()
db.execute(sql)
print('Итоговое время работы запроса: ' + str(time.time()-t))В итоге данный скрипт позволяет значительно сократить трудозатраты и время на выполнение рутинных запросов, чем я неоднократно пользовался в своей работе.
Весь исходный код опубликован на github.
@sqlhub