Untitled12.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c2aa727e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymysql\n",
    "import datetime\n",
    "    # from datetime import datetime\n",
    "def create_regex_for_ip():\n",
    "    ip=[\"192.168.5.51\",\"45.112.178.18\"]\n",
    "\n",
    "    startip=\"192.168.21.202\"\n",
    "\n",
    "    endip=\"192.168.21.212\"\n",
    "\n",
    "    customername=\"Sattrix\"\n",
    "\n",
    "    csv_type=\"IP_Range\"\n",
    "\n",
    "    data = pd.DataFrame()\n",
    "\n",
    "    today_date = datetime.datetime.now()\n",
    "\n",
    "    tdate = today_date.strftime(\"%d-%m-%Y %H:%M:%S\")\n",
    "\n",
    "    if csv_type==\"Indvidual_Ip\":\n",
    "\n",
    "        for i in ip:\n",
    "\n",
    "            i=i.strip()\n",
    "\n",
    "            cur=db.cursor()\n",
    "\n",
    "            query='''select CustomerName from subnetMapping where IP_Subnet='{}' '''.format(i)\n",
    "\n",
    "            cur.execute(query)\n",
    "\n",
    "            ip_available=cur.fetchall()\n",
    "\n",
    "            pattern='''^{}$'''.format(i)\n",
    "\n",
    "            dataframe = [{\n",
    "\n",
    "                    \"Department_Name\": \"Python\",\n",
    "\n",
    "                    \"Modification_Date\":tdate,\n",
    "\n",
    "                    \"End_IP\": i,\n",
    "\n",
    "                    \"IP_Subnet\": '',\n",
    "\n",
    "                    \"Start_IP\": i,\n",
    "\n",
    "                    \"CustomerName\":customername ,\n",
    "\n",
    "                    \"regex\": pattern\n",
    "\n",
    "                }]\n",
    "\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "    if csv_type==\"IP_Subnet\":\n",
    "\n",
    "        for i in ip:\n",
    "\n",
    "            i=i.strip()\n",
    "\n",
    "            ip_subnet = i.rfind('.')\n",
    "\n",
    "            slash = i.rfind('/')\n",
    "\n",
    "            subnet_end = int(i[(slash) + 1:])\n",
    "\n",
    "            cur=db.cursor()\n",
    "\n",
    "            query='''select CustomerName from subnetMapping where IP_Subnet='{}' '''.format(i)\n",
    "\n",
    "            cur.execute(query)\n",
    "\n",
    "            ip_available=cur.fetchall()\n",
    "\n",
    "            # print(\"************************************************\",i,\"++++\",ip_available)\n",
    "\n",
    "        #     if not ip_available:\n",
    "\n",
    "            if subnet_end == 24:\n",
    "\n",
    "                pattern = '''^{}.(?:[1-9]|[1-9][0-9]|[1][0-9][0-9]|[2][0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [{\n",
    "\n",
    "                    \"Department_Name\": \"Python\",\n",
    "\n",
    "                    \"Modification_Date\":tdate,\n",
    "\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                    \"IP_Subnet\": i,\n",
    "\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.0',\n",
    "\n",
    "                    \"CustomerName\":customername ,\n",
    "\n",
    "                    \"regex\": pattern\n",
    "\n",
    "                }]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "            if subnet_end == 25:\n",
    "\n",
    "                pattern = '''^{}.(?:12[8-9]|1[3-9][0-9]|2[0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [\n",
    "\n",
    "                    {\n",
    "\n",
    "                        \"Department_Name\": \"Python\",\n",
    "\n",
    "                        \"Modification_Date\": tdate,\n",
    "\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                        \"IP_Subnet\": i,\n",
    "\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.128',\n",
    "\n",
    "                        \"CustomerName\": customername,\n",
    "\n",
    "                        \"regex\": pattern\n",
    "\n",
    "                    }\n",
    "\n",
    "                ]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "            if subnet_end == 26:\n",
    "\n",
    "                pattern = '''^{}.(?:19[2-9]|2[0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [\n",
    "\n",
    "                    {\n",
    "\n",
    "                        \"Department_Name\": \"Python\",\n",
    "\n",
    "                        \"Modification_Date\": tdate,\n",
    "\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                        \"IP_Subnet\": i,\n",
    "\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.192',\n",
    "\n",
    "                        \"CustomerName\": customername,\n",
    "\n",
    "                        \"regex\": pattern\n",
    "\n",
    "                    }\n",
    "\n",
    "                ]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "            if subnet_end == 27:\n",
    "\n",
    "                pattern = '''^{}.(?:22[4-9]|2[3-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [\n",
    "\n",
    "                    {\n",
    "\n",
    "                        \"Department_Name\": \"Python\",\n",
    "\n",
    "                        \"Modification_Date\": tdate,\n",
    "\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                        \"IP_Subnet\": i,\n",
    "\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.224',\n",
    "\n",
    "                        \"CustomerName\":customername,\n",
    "\n",
    "                        \"regex\": pattern\n",
    "\n",
    "                    }\n",
    "\n",
    "                ]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "            if subnet_end == 28:\n",
    "\n",
    "                pattern = '''^{}.(?:[2][4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [\n",
    "\n",
    "                    {\n",
    "\n",
    "                        \"Department_Name\": \"Python\",\n",
    "\n",
    "                        \"Modification_Date\": tdate,\n",
    "\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                        \"IP_Subnet\": i,\n",
    "\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.240',\n",
    "\n",
    "                        \"CustomerName\": customername,\n",
    "\n",
    "                        \"regex\": pattern\n",
    "\n",
    "                    }\n",
    "\n",
    "                ]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "            if subnet_end == 29:\n",
    "\n",
    "                pattern = '''^{}.(?:[2][4][8-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [\n",
    "\n",
    "                    {\n",
    "\n",
    "                        \"Department_Name\": \"Python\",\n",
    "\n",
    "                        \"Modification_Date\": tdate,\n",
    "\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                        \"IP_Subnet\": i,\n",
    "\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.248',\n",
    "\n",
    "                        \"CustomerName\": customername,\n",
    "\n",
    "                        \"regex\": pattern\n",
    "\n",
    "                    }\n",
    "\n",
    "                ]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "            if subnet_end == 30:\n",
    "\n",
    "                pattern = '''^{}.(?:25[2-4])$'''.format(i[:ip_subnet])\n",
    "\n",
    "                dataframe = [\n",
    "\n",
    "                    {\n",
    "\n",
    "                        \"Department_Name\": \"Python\",\n",
    "\n",
    "                        \"Modification_Date\": tdate,\n",
    "\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "\n",
    "                        \"IP_Subnet\": i,\n",
    "\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.252',\n",
    "\n",
    "                        \"CustomerName\": customername,\n",
    "\n",
    "                        \"regex\": pattern\n",
    "\n",
    "                    }\n",
    "\n",
    "                ]\n",
    "\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "\n",
    "\n",
    "    if csv_type==\"IP_Range\":\n",
    "\n",
    "        ipstart = startip.rfind('.')\n",
    "\n",
    "        ipstartvalue=startip[(ipstart+1):]\n",
    "\n",
    "        ipend=endip.rfind('.')\n",
    "\n",
    "        ipendvalue=endip[(ipstart+1):]\n",
    "\n",
    "        if int(ipstartvalue)<10:\n",
    "\n",
    "            if int(ipendvalue)<10:\n",
    "\n",
    "                pattern='''^{}.(?:[{}-{}])$'''.format(startip[:ipstart],ipstartvalue,ipendvalue)\n",
    "\n",
    "                print(\"0\",pattern)\n",
    "\n",
    "            if int(ipendvalue)>=10 and int(ipendvalue)<100:\n",
    "\n",
    "                pattern='''^{}.(?:[{}-9]|[1-{}][0-9]|[{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue,(int(ipendvalue[0])-1),ipendvalue[0],ipendvalue[1])\n",
    "\n",
    "                return(\"1\",pattern)\n",
    "\n",
    "            if int(ipendvalue)>=100 and int(ipendvalue)<200:\n",
    "\n",
    "                pattern='''^{}.(?:[{}-9]|[1][0-{}][0-9]|[{}{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue,(int(ipendvalue[1])-1),ipendvalue[0],ipendvalue[1],ipendvalue[2])\n",
    "\n",
    "                return(\"2--\" ,pattern)\n",
    "\n",
    "            if int(ipendvalue)>=200:\n",
    "\n",
    "                pattern='''^{}.(?:[{}-9]|[1][0-9][0-9]|[2][0-{}][0-9]|[{}{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue,(int(ipendvalue[1])-1),ipendvalue[0],ipendvalue[1],ipendvalue[2])\n",
    "\n",
    "                return(\"3--\" ,pattern)\n",
    "\n",
    "        if int(ipstartvalue)>=10 and int(ipstartvalue)<100:\n",
    "\n",
    "            if int(ipendvalue)>=10 and int(ipendvalue)<100:\n",
    "\n",
    "    #             pattern='''^{}.(?:[{}-{}][0-9]|[1-{}])$'''.format(startip[:ipstart],(int(ipendvalue[0])-1),ipstartvalue,(int(ipendvalue[0])-1),ipendvalue[0],ipendvalue[1])\n",
    "\n",
    "                pattern='''^{}.(?:[{}][{}-9]|[{}-{}][0-9]|[{}][0-{}])'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],(int(ipstartvalue[0])+1),(int(ipendvalue[0])-1),ipendvalue[0],ipendvalue[1])\n",
    "\n",
    "                return(\"+++++++++++++++++++\",pattern)\n",
    "\n",
    "            if int(ipendvalue)>=100 and int(ipendvalue)<200:\n",
    "\n",
    "                pattern='''^{}.(?:[{}][{}-9]|[{}][{}-{}][0-9]|[1{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],ipendvalue[0],(int(ipstartvalue[1])+1), (int(ipendvalue[1])-1),ipendvalue[1],ipendvalue[-1])\n",
    "\n",
    "                return(\"5--\" ,pattern)\n",
    "\n",
    "            if int(ipendvalue)>=200:\n",
    "\n",
    "    #             pattern='''^{}.(?:[{}-9]|[1][0-9][0-9]|[2][0-{}][0-9]|[{}{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue,(int(ipendvalue[1])-1),ipendvalue[0],ipendvalue[1],ipendvalue[2])\n",
    "\n",
    "                pattern='''^{}.(?:[{}][{}-9]|[6-9][0-9]|[2][0-{}][0-9]|[{}{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue[0],int(ipstartvalue[1]),(int(ipendvalue[1])-1),ipendvalue[-1],ipendvalue[1],ipendvalue[-1])\n",
    "\n",
    "                return(\"6--\" ,pattern)\n",
    "\n",
    "        if int(ipstartvalue)>=100 and int(ipstartvalue)<200:\n",
    "\n",
    "            if int(ipendvalue)>=100 and int(ipendvalue)<200:\n",
    "\n",
    "                pattern='''^{}.(?:[{}{}][{}-{}]|[{}][{}-{}][0-9]|[{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],ipstartvalue[-1],ipstartvalue[0],ipstartvalue[0],(int(ipstartvalue[1])+1),(int(ipendvalue[1])-1),ipendvalue[:2],ipendvalue[-1])\n",
    "\n",
    "                return(\"7--\" ,pattern)\n",
    "\n",
    "            if int(ipendvalue)>=200:\n",
    "\n",
    "    #             pattern='''^{}.(?:[{}{}][{}-{}]|[{}][{}-{}][0-9]|[{}][0-{}])'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],ipstartvalue[-1],ipstartvalue[0],ipstartvalue[0],(int(ipstartvalue[1])+1),(int(ipendvalue[1])-1),ipendvalue[:2],ipendvalue[-1])\n",
    "                pattern='''^{}.(?:[{}{}][{}-9]|[1][{}-9][0-9]|[2][0-{}][0-9]|[{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],ipstartvalue[2],(int(ipstartvalue[1])+1),(int(ipendvalue[1])-1),ipendvalue[:2],ipendvalue[2])\n",
    "                return(\"8--\" ,pattern)\n",
    "\n",
    "        if int(ipstartvalue)>=200:\n",
    "            pattern='''^{}.(?:[{}][{}][{}-9]|[2][{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],ipstartvalue[2],int(ipendvalue[1]),ipendvalue[2])\n",
    "            return(\"9--\",pattern)\n",
    "    # data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9101b7f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('9--', '^192.168.21.(?:[2][0][2-9]|[2][1][0-2])$')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_regex_for_ip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "44ba2393",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'^192.168.21.(?:[15][2-9]|[1][6-9][0-9]|[2][0-0][0-9]|[21][0-2])$'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern='''^{}.(?:[{}{}][{}-9]|[1][{}-9][0-9]|[2][0-{}][0-9]|[{}][0-{}])$'''.format(startip[:ipstart],ipstartvalue[0],ipstartvalue[1],ipstartvalue[2],(int(ipstartvalue[1])+1),(int(ipendvalue[1])-1),ipendvalue[:2],ipendvalue[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "7d1d153d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int(ipendvalue[1])-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6b2b4f90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'192.168.21.212'"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "endip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "edb327d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'202'"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ipstartvalue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7cc92b6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}