Андрей Иванов

import requests
import os
import urllib3
import csv
import sqlite3


source = r'https://rossvyaz.gov.ru/data/'
abc3 = 'ABC-3xx.csv'
abc4 = 'ABC-4xx.csv'
abc8 = 'ABC-8xx.csv'
def9 = 'DEF-9xx.csv'
path = os.getcwd() + '\\NP\\'
db = path + "np.db"
try:
    os.mkdir(path)
except OSError:
    pass

file_list = [abc3, abc4, abc8, def9]


table = "CREATE TABLE numbering_plan(prefix INT, begin INT, end INT, capacity INT, operator TEXT, region TEXT);"
with sqlite3.connect(db) as connection:
    cursor = connection.cursor()
    cursor.execute(table)


for file in file_list:
    urllib3.disable_warnings()
    r = requests.get(source + file, verify=False)
    open(path + file, 'wb').write(r.content)
    with open(path + file, 'r', encoding='utf-8') as f:
        dr = csv.DictReader(f, delimiter=';', quoting=csv.QUOTE_NONE)
        to_db = [(i['АВС/ DEF'], i['От'], i['До'], i['Емкость'], i['Оператор'], i['Регион']) for i in dr]
        with sqlite3.connect(db) as connection:
            cursor = connection.cursor()
            cursor.executemany("INSERT INTO numbering_plan (prefix, begin, end, capacity, operator, region) "
                               "VALUES (?, ?, ?, ?, ?, ?);", to_db)

import sqlite3
import os


path = os.getcwd() + '\\NP\\'
db = path + "np.db"

num = 1

while num:
    num = input('Введите номер в формате ABD/DEАххх ')
    prefix = num[:3]
    number = num[3:]
    search_query = 'SELECT * FROM numbering_plan WHERE prefix=? AND ? BETWEEN begin AND end;'

    with sqlite3.connect(db) as connection:
        cursor = connection.cursor()
        cursor.execute(search_query, (prefix, number))
        result = cursor.fetchall()
    print(result)