Замечание. Вся трилогия (часть 1 тут, часть 2 тут) о велосипедостроении с sqlite, xml, csv только для совсем маленьких Питоньих кодеров. Не для крутых кодеров, они умрут от скуки в нашем опусе и ничего нового не увидят. В третьей части заканчиваем все, что начали ранее
Начинаем изыски причины и местонахождения ошибки
Итак: правильный ответ: ошибки в коде нет. Ну точнее ошибка возникает при работе кода из-за ошибки в данных, которые обрабатывает наш код.
Чтобы убедиться, что это так, добавим в код обработку исключений:
вместо:
for row in rows: cur.execute('INSERT INTO oborot_2019_fns13 VALUES (?, ?, ?, ?)', row)
запишем:
for row in rows: try: cur.execute('INSERT INTO oborot_2019_fns14 VALUES (?, ?, ?, ?)', row) except Exception as e: print(e.__class__)
Мы изменили совсем немного, что это нам дало? В случае возникновения исключения при работе кода мы выводим ее причину в терминал, в остальных случаях код успешно отрабатывает и записывает данные в таблицу sqlite.
Запускаем код. В терминале получаем 5 раз одну и ту же строчку:
<class 'sqlite3.ProgrammingError'>
Не очень информативно, но можно предположить, что причиной ошибки явился не наш код, а данные. Причем, вероятно ошибка возникает 5 раз. Но как понять, где именно в данных произошли эти пять ошибок. И какова их причина?
Идем дальше. Меняем код так:
вместо:
except Exception as e: print(e.__class__)
пишем:
except Exception as e: print(e.__class__, ':', e.args[0], '::', x, ':::', row)
Что это нам дает? Мы получаем имя файлов, в которых случились ошибки и как выглядит та строчка, где случилась ошибка.
Запускаем исправленный код.
Получаем вывод в терминале:
D:\2021_8_16_oborot>python d:/2021_8_16_oborot/part2/30.10.2021_check2_error_with_import_csv_dir_to_sqlite_new_delimi.py <class 'sqlite3.ProgrammingError'> : Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied. :: VO_OTKRDAN5_9965_9965_20210729_0773d892-d1e5-4000-a5e9-48f009a36802.csv ::: ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ '] <class 'sqlite3.ProgrammingError'> : Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied. :: VO_OTKRDAN5_9965_9965_20210729_58b559b1-b92a-4a72-aee7-a16a43341fec.csv ::: ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ'] <class 'sqlite3.ProgrammingError'> : Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied. :: VO_OTKRDAN5_9965_9965_20210729_a8e70040-cca9-4197-a402-55898861465b.csv ::: ['АКЦИОНЕРНОЕ ОБЩЕСТВО "НАУЧНО-ПРОИЗВОДСТВЕННОЕ'] <class 'sqlite3.ProgrammingError'> : Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied. :: VO_OTKRDAN5_9965_9965_20210729_cb1ffb92-e932-46b8-982f-5a2b01ec49fa.csv ::: ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ'] <class 'sqlite3.ProgrammingError'> : Incorrect number of bindings supplied. The current statement uses 4, and there are 5 supplied. :: VO_OTKRDAN5_9965_9965_20210729_e25030ca-21c1-4ebb-bfcd-8416a65c8e8d.csv ::: ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "ВЕДА ', ' ЮРИДИЧЕСКИЙ АУТСОРСИНГ"', '7729790649', '25185000.00', '24415000.00']
Отлично. Получили имена файлов. И 4 ошибки о том, что в исходниках csv одно поле в строчке, и если присмотреться это обрезанное имя какого-то ЮЛ, а пятая ошибка другая, мы видим, что по какой-то причине 4 значения для импорта в таблицу превратились в 5: имя ЮЛ разделилось на две части.
Отлично, идем дальше.
Перемещаем файлы с ошибками в отдельные директории
Мы получили имена файлов и чтобы было удобнее дальше искать причины ошибок скопируем файлы с ошибками в отдельную директорию 'csv_err'.
Кодим, перемещаем:
Сразу сделаем то же самое и для этих же файлов, но xml из директории 'ish_unziped'. Если помните названия файлов csv и xml у нас совпадают, только расширение нужно поменять. Раз в csv есть ошибки, то скорее всего эта же ошибка была изначально и xml. Есть еще, конечно, ненулевой вариант, что причина ошибки не xml, а свойства прокладки между стулом и клавой (но очень хочется надеяться, что это не так, и в результате поиска ошибок мы не выйдем сами на себя).
import os import shutil def create_dir(dir_name): if not os.path.exists(dir_name): os.mkdir(dir_name) print("Directory " , dir_name , " Created ") else: print("Directory " , dir_name , " already exists") def copy_files_by_names(list_files_err): old_dir = 'D:/2021_8_16_oborot/ish_unziped/' new_dir = 'D:/2021_8_16_oborot/xml_err/' list_files_err = ['VO_OTKRDAN5_9965_9965_20210729_0773d892-d1e5-4000-a5e9-48f009a36802.xml', 'VO_OTKRDAN5_9965_9965_20210729_58b559b1-b92a-4a72-aee7-a16a43341fec.xml', 'VO_OTKRDAN5_9965_9965_20210729_a8e70040-cca9-4197-a402-55898861465b.xml', 'VO_OTKRDAN5_9965_9965_20210729_cb1ffb92-e932-46b8-982f-5a2b01ec49fa.xml', 'VO_OTKRDAN5_9965_9965_20210729_e25030ca-21c1-4ebb-bfcd-8416a65c8e8d.xml'] for t in list_files_err: with open(new_dir + t, 'a+', encoding='utf-8') as f: try: shutil.copy(old_dir+t, new_dir+t) print('All copy to new_dir:', t) except Exception as e: print('Somethin goes to bad:', e, ':', t) if __name__ == '__main__': dir_name = 'D:/2021_8_16_oborot/xml_err' create_dir(dir_name) copy_files_by_names(list_files_err)
Теперь искать ошибки стало удобнее в 5 файлах, чем в 12 тысячах.
Вскрытие покажет
В принципе можно пять файлов и просто открыть и посмотреть что с ними не так. Но раз уж мы закоренелые велосипедостроители и тут пишем код питонячий. Единственное мы не стали перебирать 5 файлов, а по-одному обрабатывали - искали место и причиную ошибки. Так как в принципе ошибка может быть разная, а торопиться в этот раз мы не хотим. Код для одного из файлов. Для поиска других ошибок нужно только поменять имя файла и запустить.
import sqlite3 import csv import os def create_db_table(): conn = sqlite3.connect('D:/2021_8_16_oborot/UL17.db') c = conn.cursor() c.execute('''CREATE TABLE IF NOT EXISTS oborot_2019_fns18 (name_UL text, inn_UL int, oborot_2019 real, rashod_2019 real)''') conn.commit() conn.close() def import_csv_to_sqlite(): con = sqlite3.connect('D:/2021_8_16_oborot/UL17.db') cur = con.cursor() with open('D:/2021_8_16_oborot/csv_err/VO_OTKRDAN5_9965_9965_20210729_e25030ca-21c1-4ebb-bfcd-8416a65c8e8d.csv', 'r', encoding='utf-8') as f_open_csv: rows = csv.reader(f_open_csv, delimiter="|") count = 0 for row in rows: try: cur.execute('INSERT INTO oborot_2019_fns18 VALUES (?, ?, ?, ?)', row) print(count, row, ':', 'good') except Exception as e: print(e.__class__, ':', e.args[0], ':::', row, ':', count, 'e25030ca-21c1-4ebb-bfcd-8416a65c8e8d.csv' ) print(count, row, ':', 'bad') finally: count += 1 con.commit() con.close() if __name__ == '__main__': create_db_table() import_csv_to_sqlite()
Вывод получаем такой (приведены только строчки с ошибкой и на одну выше и ниже):
7 ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "МЕРКУРИЙ РИЭЛТ"', '1655306626', '170000.00', '177000.00'] : good <class 'sqlite3.ProgrammingError'> : Incorrect number of bindings supplied. The current statement uses 4, and there are 5 supplied. ::: ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "ВЕДА ', ' ЮРИДИЧЕСКИЙ АУТСОРСИНГ"', '7729790649', '25185000.00', '24415000.00'] : 8 e25030ca-21c1-4ebb-bfcd-8416a65c8e8d.csv 8 ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "ВЕДА ', ' ЮРИДИЧЕСКИЙ АУТСОРСИНГ"', '7729790649', '25185000.00', '24415000.00'] : bad 9 ['ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "УТИТ"', '9204019863', '296000.00', '328000.00'] : good
Имя базы данных мы тоже поменяли, чтобы не мешать базу с ошибками с большой базой.
Копаем дальше
Отлично видим, что ошибка в данном случае в 8 строчке у записи с ИНН = '7729790649', так как вероятность низка, что у нас двоятся ИНН-ки (в конце, кстати, надо не забыть проверить это), то открываем наш xml файл (в директории 'xml_err') c этим же именем и смотрим на запись с ИНН = '7729790649'. Нас интересуют данные перед этим блоком там находится имя ЮЛ, которое неправильно обрабатывается. Находим, копируем в любой редактор (VScode, word_MS, блокнот).
Ну или прямо в том редакторе, где xml исходный смотрим.
Видим:
'<СведНП НаимОрг="ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "ВЕДА | ЮРИДИЧЕСКИЙ АУТСОРСИНГ"" ИННЮЛ="7729790649"/>'
Слегка нечитаемый вид.
Можно расшифровать онлайн, можно самим написать питоний код. Но сегодня у нас лень, поэтому мы просто вживую видим, что в записи присутствует знак '|', который мы использовали в качестве разделителя. То есть этот знак изначально присутствовал в данных и естественно при обработке xml разделил имя ЮЛ на две части и вместо 4 значений для 4 колонок получилось 5, которые не влазят в четыре.
Другие четыре ошибки можете сами найти по приведенному методу, ну или придумайте свой (расскажите в обсуде, интересно же!).
Но, можете поверить нам на слово, что в остальных файлах примерно такая же ошибка, но только вместо "знака трубопровода" - там знак перевода строки ('
'). Он по-другому испортил импорт. Вместо пяти значений с разделителями в одну строчку создал две строки, в одной части имени (одно значение) для 4 колонок, которое ломало импорт и следующая строка, где почти нормальные четыре значения для четырех колонок таблицы, но имя немного обрезанное на ту часть, которая была в первой строке, а именно у трех отрезана организационно-правовая форма 'ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ', а у четвертой отрезано часть имени: 'АКЦИОНЕРНОЕ ОБЩЕСТВО "НАУЧНО-ПРОИЗВОДСТВЕННОЕ')
Как исправить эти ошибки? Можно поправить код и проверять на присутствие-отсутствие этих плохих разделителей, переводчиков каретки, но мы сделали чистку данных руками. Удалили эти 5 ошибок из xml и записали подправленные xml файлы обратно в общую директорию. Затем переобработали (перезаписали) только эти файлы в директорию csv2. Затем импортировали обновленную директорию csv2 в sqlite.
Код перемещалки "улучшенных файлов" xml обратно в общую директорию 'ish_unziped'
import os import shutil def copy_files_by_names(): old_dir = 'D:/2021_8_16_oborot/xml_err/' new_dir = 'D:/2021_8_16_oborot/ish_unziped/' list_files_err = ['VO_OTKRDAN5_9965_9965_20210729_0773d892-d1e5-4000-a5e9-48f009a36802.xml', 'VO_OTKRDAN5_9965_9965_20210729_58b559b1-b92a-4a72-aee7-a16a43341fec.xml', 'VO_OTKRDAN5_9965_9965_20210729_a8e70040-cca9-4197-a402-55898861465b.xml', 'VO_OTKRDAN5_9965_9965_20210729_cb1ffb92-e932-46b8-982f-5a2b01ec49fa.xml', 'VO_OTKRDAN5_9965_9965_20210729_e25030ca-21c1-4ebb-bfcd-8416a65c8e8d.xml'] for t in list_files_err: with open(new_dir + t, 'a+', encoding='utf-8') as f: try: shutil.copy(old_dir+t, new_dir+t) print('All copy to new_dir:', t) except Exception as e: print('Somethin goes to bad:', e, ':', t) if __name__ == '__main__': copy_files_by_names()
Финишные вишенки
Окей, таблица получена.
Посчитаем сколько у нас получилось ИННок ЮЛ в таблице sqlite.
Если совсем без питона, а запросами SQL (например в любом редакторе sqlite), то так можем посчитать количество строчек в таблице:
SELECT count(*) FROM oborot_2019_fns20;
А так сосчитать какое количество ИНН_ЮЛ получается без двойников по ИНН.
SELECT COUNT(DISTINCT inn_UL) as count FROM oborot_2019_fns20;
Тоже самое на Питоне:
#D:\2021_8_16_oborot\part3\08.11.2021_count_rows_i_INN_UL_without_dublicates_sqlite.py import sqlite3 def count_rows_i_zdINN(): conn = sqlite3.connect('D:/2021_8_16_oborot/UL19.db') c = conn.cursor() c.execute("select count(*) from oborot_2019_fns20") results = c.fetchone() print('count_rows :', results[0]) c.execute("select count(distinct inn_UL) as count from oborot_2019_fns20") results = c.fetchone() print('count_inn_UL_without_dublicates :', results[0]) c.close() conn.close() if __name__ == '__main__': count_rows_i_zdINN()
Получили вывод терминала, который говорит, что количество дубликатов равно нулю.
count_rows : 2174433 count_inn_UL_without_dublicates : 2174433
Теперь убедимся, что в начальных данных и было такое количество ИНН_ЮЛ.
Выведем в 2 файла в один просто ИНН_ЮЛ, а во втором еще и пронумеруем каждую запись. Так же в терминал выводим результаты подсчета ИНН_ЮЛ. Код отрабатывает достаточно долго. Но нас, как истинных велосипедостроителей оное не должно пугать.
import os import xml.etree.ElementTree as Xet def parse_dii(): path = 'D:/2021_8_16_oborot/ish_unziped/' fileList = os.listdir(path) inn_count = 1 for t in fileList: with open('D:/2021_8_16_oborot/ish_unziped/' + t, 'r', encoding='utf-8') as ft: base = os.path.splitext(t)[0] with open('D:/2021_8_16_oborot/part2/3all_inn_ul.csv', 'a+', encoding='utf-8') as f: with open('D:/2021_8_16_oborot/part2/3all_inn_ul_with_count.csv', 'a+', encoding='utf-8') as f1: xmlparse = Xet.parse('D:/2021_8_16_oborot/ish_unziped/' + t) root = xmlparse.getroot() for i in root: for i2 in i.findall('СведНП'): try: f1.write(str(inn_count)) f1.write(':') f1.write(i2.attrib['ИННЮЛ']) f1.write('\n') f.write(i2.attrib['ИННЮЛ']) f.write('\n') except Exception as e: print('Else bad', e, ':', e.args[0], '::', inn_count) finally: print('inn_count :', inn_count) inn_count += 1 if __name__ == '__main__': parse_dii()
В итоге получаем в терминале:
inn_count : 2174433
А открыв записанные файлв в обеих видим в последней строчке тоже самое число: 2 174 433.
Подведем итоги нашего велосипедостроения:
1. Как нам кажется неплохо повелосипедили.
2. В сети не так много практических примеров подобного велосипедостроения (сугубо моя оценка и по отношению к Питону только).
3. С полученной таблицей уже можно наделать много других велосипедов (прикрутить GUI, добавить аналитические модули, импортировать данные из других подобных источников, соединить таблицы из разные источников, сделать анализ данных в разных разрезах)
Всем пис и насингфаунд фореве!
