Я реализовал скрипт python, чтобы разделить миллионы документов (сгенерированных веб-приложением .NET и все содержимое которых находится в одном каталоге) на подпапки с этой схемой: год/месяц/партия, поскольку все задачи этих документов родом были первоначально разделены на партии. Мои сценарии Python выполняют запросы к SQL Server 2014, который содержит все данные, необходимые для каждого документа, в частности, месяц и год, в котором он был создан. Затем он использует модуль shutil
для перемещения PDF. Итак, сначала я выполняю первый запрос, чтобы получить список пакетов за данный месяц и год:
queryBatches = '''SELECT DISTINCT IDBATCH
FROM [DBNAME].[dbo].[WORKS]
WHERE YEAR(DATETIMEWORK)={} AND MONTH(DATETIMEWORK)={}'''.format(year, month)
Затем я выполняю:
for batch in batches:
query = '''SELECT IDWORK, IDBATCH, NAMEDOCUMENT
FROM [DBNAME].[dbo].[WORKS]
WHERE NAMEDOCUMENTI IS NOT NULL and
NAMEDOCUMENT not like '/%/%/%/%.pdf' and
YEAR(DATETIMEWORK)={} and
MONTH(DATETIMEWORK)={} and
IDBATCH={}'''.format(year,month,batch[0])
чьи записи собираются в курсор, согласно документации по использованию PYMSSQL. Итак, я продолжаю:
IDWorksUpdate = []
row = cursor.fetchone()
while row:
if moveDocument(...):
IDWorksUpdate.append(row[0])
row = cursor.fetchone()
Наконец, когда цикл закончился, в IDWorksUpdate
у меня есть все ПК WORKS, чьи документы удалось правильно переместить в подпапку. Итак, я закрываю курсор и соединение и создаю новые экземпляры. В итоге я выполняю:
subquery = '('+', '.join(str(x) for x in IDWorksUpdate)+')'
query = '''UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT = \'/{}/{}/{}/\'+NAMEDOCUMENT WHERE IDWORK IN {}'''.format(year,month,idbatch,subquery)
newConn = pymssql.connect(server='localhost', database='DBNAME')
newCursor = newConn.cursor()
try:
newCursor.execute(query)
newConn.commit()
except:
newConn.rollback()
log.write('Error on updating documents names in database of works {}/{} of batch {}'.format(year,month,idbatch))
finally:
newCursor.close()
del newCursor
newConn.close()
Сегодня утром я вижу, что только для нескольких пакетов запрос на обновление не выполнялся в базе данных, даже если документы были правильно перемещены в подкаталоги. В этом пакете было более 55000 документов, которые нужно было переместить, так что, возможно, IDWorksUpdate
переполнился, и это помогло создать этот окончательный запрос на обновление? Я думал, что 55000 не такой уж большой список целых чисел. Проблема в том, что в PYMSSQL у нас не может быть более одного соединения/курсора одновременно с одной и той же базой данных, поэтому я не могу обновлять записи при перемещении соответствующих файлов. Поэтому я подумал создать список ПК работ, документы которых были правильно перемещены, и, наконец, обновить их новым соединением/курсором. Что могло случиться? Я делаю это неправильно?
ОБНОВЛЕНИЕ
Я только что написал простой скрипт для воспроизведения запроса, который будет выполняться для обновления записей, и это ошибка, которую я получаю от SQL Server:
У обработчика запросов закончились внутренние ресурсы, и он не смог создать план запроса. Это редкое событие, ожидаемое только для чрезвычайно сложных запросов или запросов, которые ссылаются на очень большое количество таблиц или разделов. Пожалуйста, упростите запрос. Если вы считаете, что получили это сообщение по ошибке, обратитесь в службу поддержки клиентов для получения дополнительной информации.
Это запрос:
UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT = '/2016/12/1484/'+NAMEDOCUMENT WHERE IDWORK IN (list of 55157 PKs)
Дело в том, что таблица очень большая (около 14 миллионов записей). Но мне нужен этот список ПК, потому что только задачи, документ которых был правильно обработан и перемещен, могут быть обновлены. Я не могу просто запустить:
UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT = '/2016/12/1484/'+NAMEDOCUMENT WHERE YEAR(DATETIMEWORK)=2016 and
MONTH(DATETIMEWORK)=12 and IDBATCH=1484
Это связано с тем, что наш сервер был атакован криптоблокировщиком, и я должен обрабатывать и перемещать только те документы, которые все еще существуют, ожидая освобождения других. Должен ли я разделить эту строку на подсписки? Как?
ОБНОВЛЕНИЕ 2
Похоже, что решением может быть следующее: я разбиваю список PK на фрагменты по 10000 (полностью экспериментальное число), а затем выполняю столько запросов, сколько фрагментов, каждый из которых содержит фрагмент в качестве подзапроса.
def updateDB(listID, y, m, b, log):
newConn = pymssql.connect(server='localhost', database='DBNAME')
newCursor = newConn.cursor()
if len(listID) <= 10000:
subquery = '('+', '.join(str(x) for x in listID)+')'
query = '''UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT= \'/{}/{}/{}/\'+NAMEDOCUMENT WHERE IDWORKIN {}'''.format(y,m,b,subquery)
try:
newCursor.execute(query)
newConn.commit()
except:
newConn.rollback()
log.write('...')
log.write('\n\n')
finally:
newCursor.close()
del newCursor
newConn.close()
else:
chunksPK = [listID[i:i + 10000] for i in xrange(0, len(listID), 10000)]
for sublistPK in chunksPK:
subquery = '('+', '.join(str(x) for x in sublistPK)+')'
query = '''UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT= \'/{}/{}/{}/\'+NAMEDOCUMENT WHERE IDWORK IN {}'''.format(y,m,b,subquery)
try:
newCursor.execute(query)
newConn.commit()
except:
newConn.rollback()
log.write('Could not execute partial {}'.format(query))
log.write('\n\n')
newCursor.close()
del newCursor
newConn.close()
Может ли это быть хорошим/безопасным решением?