Замечание. Вся трилогия (часть 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, добавить аналитические модули, импортировать данные из других подобных источников, соединить таблицы из разные источников, сделать анализ данных в разных разрезах)
Всем пис и насингфаунд фореве!
