Примечание. Как и первая часть эта тоже для совсем маленьких кодеров-велосипедостроителей на Питоне. Для прожженных кодеров будет скучно. Изначально хотели внести исправления сразу в первую статью по мере нахождения ошибок, но после некоторого раздумия решили, что это неудобно. Ошибки исчезнут совсем, а именно ошибки приносят максимальную пользу для начинающего кодера. А посему ошибки оставляем в первой части, а в этой начинаем от них избавляться.
Начало
Сначала создадим в основном проекте новую папку, куда будем складывать весь код из этой статьи. Заходим в командную строку виндовс под админом:
D:\2021_8_16_oborot>mkdir part2
Далее идем по порядку. Мы хотели проверить соответствуют ли ВСЕ исходные xml-файлы своему xsd описанию.
Проверяем все исходные xml файлы
Что делаем? Сравниваем все файлы из исходной директории с xml-файлами (.../ish_unziped) с xsd схемой. Если все гуд записываем его имя в текстовый файл для хороших файлов, иначе к плохим.
#D:\2021_8_16_oborot\part2\14.09.2021_check_all_xml_files_in_dir.py
import lxml
from lxml import etree
import os
import xml.etree.ElementTree as Xet
def s_errors():
path = 'D:/2021_8_16_oborot/ish_unziped/'
filelist = os.listdir(path)
enu = 0
mis = 0
with open('D:/2021_8_16_oborot/part2/xml_not_valid_errors_2.txt', 'a+', encoding='utf-8') as ef:
with open('D:/2021_8_16_oborot/part2/xml_is_valid_2.txt', 'a+', encoding='utf-8') as va_f:
for t in filelist:
enu = enu + 1
with open('D:/2021_8_16_oborot/ish_unziped/' + t, 'r', encoding='utf-8') as ft:
xml_file = lxml.etree.parse(ft)
xml_validator = lxml.etree.XMLSchema(file='D:/2021_8_16_oborot/structure-20180110.xsd')
is_valid = xml_validator.validate(xml_file)
if is_valid == True:
enu1 = str(enu)
va_f.write(enu1)
va_f.write(':::')
va_f.write(t)
va_f.write('|')
va_f.write('file_matches_xsd')
va_f.write('\n')
else:
enu1 = str(enu)
mis = mis + 1
mis_str = str(mis)
ef.write(enu1)
ef.write(':::')
ef.write(t)
ef.write('the_file_does_not_match_the_xsd_schema')
ef.write('\n')
if __name__ == '__main__':
s_errors()Запустили. Получаем результат: "плохой" файл пустой, в хорошем все 12060 +1 файл (один тестовый файл мы же добавляли!!!), итого 12061 гуд файл.
Вид записей такой (последние 5 строчек)
12057:::VO_OTKRDAN5_9965_9965_20210729_ffdf4a61-dc58-451e-8c76-46d63b534694.xml|file_matches_xsd
12058:::VO_OTKRDAN5_9965_9965_20210729_ffe07802-f596-46a2-aff3-24293c5162c0.xml|file_matches_xsd
12059:::VO_OTKRDAN5_9965_9965_20210729_ffe103cb-571e-4c05-9395-6d97814e6100.xml|file_matches_xsd
12060:::VO_OTKRDAN5_9965_9965_20210729_ffe3f7a0-c0db-4c6a-a231-98a429c51314.xml|file_matches_xsd
12061:::VO_OTKRDAN5_9965_9965_20210729_ffec8ca1-4e5d-42ea-ad3e-2ac45146da0d.xml|file_matches_xsdПримечание. Как оказалось позже этот метод проверки не защищает от наличия "битых" данных, которые могут испортить в дальнейшем нормальный импорт в csv и далее в таблицу sqlite.
Пишем импорт xml в csv и далее в sqlite на Питоне
В первой части мы сильно торопились и не стали выяснять что мы там не то навелосипедили в Питоне во время попытки импорта csv в sqlite, а просто 'перепрыгнули' дальше к финишу. Исправляемся. В качестве разделителя используем символ пайп-pipe (который в свое время Фигурнов перевел, как 'символ водопровода'): "|". Подумали (как позже оказалось зря), что вряд-ли такой символ может встретиться в исходных данных. Как говорится "никогда не говори 'никогда'. По хорошему, конечно, это надо было бы проверить программно, но иногда возникает лень в самых неподходящих моментах. Почему не стали использовать в качестве разделителя обычно используемую запятую (",")? Сначала попробовали, но оказалось, что в именах ЮЛ в данной базе данных достаточно много запятых присутствует изначально. И при импорте в sqlite эти запятые в данных воспринимались тоже как разделители и ломали весь импорт. Потому будет пайп в качестве разделителя. Сама схема преобразований данных осталась старой, как в первой части. Сначала трансформируем xml в csv, а потом csv импортируем в sqlite. Только разделитель другой. Сначала протестируем код на одиночных файлах. Если что-то пойдет не так - быстро можем исправить код и ошибки.
Тестовый импорт xml в csv
Написали импорт одного файла из xml в csv. Запустили.
#D:\2021_8_16_oborot\part2\20.10.2021_mini_test_one_file_xml_to_csv_with_new_delimiter.py
import xml.etree.ElementTree as Xet
f = open('18_your_csv_file.csv', 'w', encoding='utf-8')
xmlparse = Xet.parse('D:/2021_8_16_oborot/ish_unziped/test1.xml')
root = xmlparse.getroot()
for i in root:
for i2 in i.findall('СведНП'):
f.writelines(i2.attrib['НаимОрг'])
f.write('|')
f.writelines(i2.attrib['ИННЮЛ'])
f.write('|')
#f.write('\n')
for i2 in i.findall('СведДохРасх'):
f.writelines(i2.attrib['СумДоход'])
f.write('|')
f.writelines(i2.attrib['СумРасход'])
f.write('\n')
f.close()Результат норм, такой:
ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "ФИБРОТЕК"|7816394401|0.00|0.00
ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "ИЗДАТЕЛЬСКИЙ ДОМ "ТВЕРСКАЯ ЖИЗНЬ"|6901026686|0.00|0.00
ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "МРАВ"|7726503712|20800000.00|14102000.00
ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "АГРОРЕСУРС"|6901026703|470000.00|461000.00Тестируем импорт csv в table sqlite
Далее то же самое тест получившегося одного (маленького) csv файла в таблицу sqlite.
#D:\2021_8_16_oborot\part2\25.10.2021_mini_test_csv_to_sqlite_with_new_delimiter.py
import sqlite3
import csv
import os
def create_db_table():
conn = sqlite3.connect('D:/2021_8_16_oborot/UL11.db')
c = conn.cursor()
# Create table
c.execute('''CREATE TABLE IF NOT EXISTS oborot_2019_fns12
(name_UL text, inn_UL int, oborot_2019 real, rashod_2019 real)''') # ??? real==>>int???
#commit the changes to db
conn.commit()
#close the connection
conn.close()
def import_one_file_csv_to_sqlite():
con = sqlite3.connect('D:/2021_8_16_oborot/UL11.db')
cur = con.cursor()
with open('D:/2021_8_16_oborot/part2/19_your_csv_file.csv', 'r', encoding='utf-8') as f_open_csv:
rows = csv.reader(f_open_csv, delimiter="|")
for row in rows:
cur.execute('INSERT INTO oborot_2019_fns12 VALUES (?, ?, ?, ?)', row)
con.commit()
con.close()
if __name__ == '__main__':
create_db_table()
import_one_file_csv_to_sqlite()Все гуд, данные записываются корректно. Данные в своих полях, не лезут в чужие.
Импорт всех xml в csv2 директорию
Теперь делаем тоже самое, но читаем все файлы из директории xml (.../ish_unziped) в директорию csv. Чтобы не путаться с директорией из первой части создадим новую директорию под csv файлы из текущей (второй части).
Назовем ее csv2
И теперь полный код, который импортирует данные из директории xml в директорию csv2 (с нашим новым разделителем)
# D:\2021_8_16_oborot\part2\2021.10.20_dir_xml_to_dir_csv_with_check_name_cell.py
import os
import xml.etree.ElementTree as Xet
def parse_dii():
path = 'D:/2021_8_16_oborot/ish_unziped/'
fileList = os.listdir(path)
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/csv2/' + base + '.csv'), 'w+', encoding='utf-8') as f:
xmlparse = Xet.parse('D:/2021_8_16_oborot/ish_unziped/' + t)
root = xmlparse.getroot()
for i in root:
for i2 in i.findall('СведНП'):
f.writelines(i2.attrib['НаимОрг'])
f.write('|')
f.writelines(i2.attrib['ИННЮЛ'])
f.write('|')
for i2 in i.findall('СведДохРасх'):
f.writelines(i2.attrib['СумДоход'])
f.write('|')
f.writelines(i2.attrib['СумРасход'])
f.write('\n')
if __name__ == '__main__':
parse_dii()Импорт всех csv в sqlite
Ну, а теперь в цикле открываем все получившиеся csv файлы из директории csv2 и записываем данные в таблицу SQLite.
#D:\2021_8_16_oborot\part2\20.10.2021_import_dir_csv_to_sqlite_with_new_delimiter.py
import sqlite3
import csv
import os
def create_db_table():
conn = sqlite3.connect('D:/2021_8_16_oborot/UL12.db')
c = conn.cursor()
# Create table
c.execute('''CREATE TABLE IF NOT EXISTS oborot_2019_fns13
(name_UL text, inn_UL int, oborot_2019 real, rashod_2019 real)''') # ??? real==>>int???
#commit the changes to db
conn.commit()
#close the connection
conn.close()
def import_csv_to_sqlite():
con = sqlite3.connect('D:/2021_8_16_oborot/UL12.db')
cur = con.cursor()
path_D = 'D:/2021_8_16_oborot/csv2'
file_List = os.listdir(path_D)
for x in file_List:
with open('D:/2021_8_16_oborot/csv2/'+x, 'r', encoding='utf-8') as f_in:
rows = csv.reader(f_in, delimiter="|")
for row in rows:
cur.execute('INSERT INTO oborot_2019_fns13 VALUES (?, ?, ?, ?)', row)
con.commit()
con.close()
if __name__ == '__main__':
create_db_table()
import_csv_to_sqlite()И вот тут нас ожидает БОЛЬШОЙ СЮРПРИЗ! Код вылетает с неприятным предупреждением: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied.
Что в переводе на русский означает примерно следующее: "программная ошибка sqlite3: Поставляется неправильное число привязок. В текущем операторе используется 4 (четыре), а предоставлено 1(одно).
Вообще непонятно, почему это предоставлено 1(одно) значение, мы же даем 4 значения для 4 колонок в sqlite?
И да, постойте, но мы же только что проверяли код и делали подобное преобразование для одного файла. Тот же самый код (за исключением итерации по директории) и там все было гуд. Как такое может быть? Надо заметить, что у нас гугление и понимание, что же это такое за ошибка заняло достаточно много времени. А все оказалось просто до невозможности.
А теперь очень хороший учебный вопрос для правильных велосипедостроителей: где же тут ошибка и как от нее избавиться? Есть идеи? Решение приведем в следующей части.
И как говорят в конце правильных фильмов: "Продолжение следует..."
P.S. Большая просьба (насколько это возможно) для догадавшихся или знающих в чем причина ошибки: пишите решение мне в личку. А то остальным велосипедостроителям будет не интересно гуглить, думать и решать задачку, когда решение уже известно.
Всем добра!
С уважением, Ваш nasingfaund.
Ссылка на Часть1. "Парсим базу юриков ФНС (велосипедостроение с xml, csv, SQLite и Питоном)"
Комментарии (2)
nasingfaund Автор
11.11.2021 08:32Ну, можно, конечно и так, наверное. У нас задачи не стояло использовать все, что есть. Задачу решили, повелосипедили по-полной. Идем дальше.
PbIXTOP
Странно конечно смотрится — для экспорта в csv используем прямую запись, а для импорта используем уже готовую библиотеку.
Сам когда пишу, сохраняю обычно используя pandas с его read_csv-to_csv проблем с экранированием и переносом строк не возникает. Причем сохраненный таким образом файл даже отлично открывается в LibreOffice. MS Excel к сожалению не поддерживает нормально переносы строк в csv