build_training_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "22e302a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting connections...\n",
      "Connected?\n",
      "Retrieved data...\n",
      "Insert row count: 0\n",
      "Insert row count: 1\n",
      "Insert row count: 2\n",
      "Insert row count: 3\n",
      "Insert row count: 4\n",
      "Insert row count: 5\n",
      "Insert row count: 6\n",
      "Insert row count: 7\n",
      "Insert row count: 8\n",
      "Insert row count: 9\n",
      "Insert row count: 10\n",
      "Insert row count: 11\n",
      "Insert row count: 12\n",
      "Insert row count: 13\n",
      "Insert row count: 14\n",
      "Insert row count: 15\n",
      "Insert row count: 16\n",
      "Insert row count: 17\n",
      "Insert row count: 18\n",
      "Insert row count: 19\n",
      "Insert row count: 20\n",
      "Insert row count: 21\n",
      "Insert row count: 22\n",
      "Insert row count: 23\n",
      "Insert row count: 24\n",
      "Insert row count: 25\n",
      "Insert row count: 26\n",
      "Insert row count: 27\n",
      "Insert row count: 28\n",
      "Insert row count: 29\n",
      "Insert row count: 30\n",
      "Insert row count: 31\n",
      "Insert row count: 32\n",
      "Insert row count: 33\n",
      "Insert row count: 34\n",
      "Insert row count: 35\n",
      "Insert row count: 36\n",
      "Insert row count: 37\n",
      "Insert row count: 38\n",
      "Insert row count: 39\n",
      "Insert row count: 40\n",
      "Insert row count: 41\n",
      "Insert row count: 42\n",
      "Insert row count: 43\n",
      "Insert row count: 44\n",
      "Insert row count: 45\n",
      "Insert row count: 46\n",
      "Insert row count: 47\n",
      "Insert row count: 48\n",
      "Insert row count: 49\n",
      "Insert row count: 50\n",
      "Insert row count: 51\n",
      "Insert row count: 52\n",
      "Insert row count: 53\n",
      "Insert row count: 54\n",
      "Insert row count: 55\n",
      "Insert row count: 56\n",
      "Insert row count: 57\n",
      "Insert row count: 58\n",
      "Insert row count: 59\n",
      "Insert row count: 60\n",
      "Insert row count: 61\n",
      "Insert row count: 62\n",
      "Insert row count: 63\n",
      "Insert row count: 64\n",
      "Insert row count: 65\n",
      "Insert row count: 66\n",
      "Insert row count: 67\n",
      "Insert row count: 68\n",
      "Insert row count: 69\n",
      "Insert row count: 70\n",
      "Insert row count: 71\n",
      "Insert row count: 72\n",
      "Insert row count: 73\n",
      "Insert row count: 74\n",
      "Insert row count: 75\n",
      "Insert row count: 76\n",
      "Insert row count: 77\n",
      "Insert row count: 78\n",
      "Insert row count: 79\n",
      "Insert row count: 80\n",
      "Insert row count: 81\n",
      "Insert row count: 82\n",
      "Insert row count: 83\n",
      "Insert row count: 84\n",
      "Insert row count: 85\n",
      "Insert row count: 86\n",
      "Insert row count: 87\n",
      "Insert row count: 88\n",
      "Insert row count: 89\n",
      "Insert row count: 90\n",
      "Insert row count: 91\n",
      "Insert row count: 92\n",
      "Insert row count: 93\n",
      "Insert row count: 94\n",
      "Insert row count: 95\n",
      "Insert row count: 96\n",
      "Insert row count: 97\n",
      "Insert row count: 98\n",
      "Insert row count: 99\n",
      "Insert row count: 100\n",
      "Insert row count: 101\n",
      "Insert row count: 102\n",
      "Insert row count: 103\n",
      "Insert row count: 104\n",
      "Insert row count: 105\n",
      "Insert row count: 106\n",
      "Insert row count: 107\n",
      "Insert row count: 108\n",
      "Insert row count: 109\n",
      "Insert row count: 110\n",
      "Insert row count: 111\n",
      "Insert row count: 112\n",
      "Insert row count: 113\n",
      "Insert row count: 114\n",
      "Insert row count: 115\n",
      "Insert row count: 116\n",
      "Insert row count: 117\n",
      "Insert row count: 118\n",
      "Insert row count: 119\n",
      "Insert row count: 120\n",
      "Insert row count: 121\n",
      "Insert row count: 122\n",
      "Insert row count: 123\n",
      "Insert row count: 124\n",
      "Insert row count: 125\n",
      "Insert row count: 126\n",
      "Insert row count: 127\n",
      "Insert row count: 128\n",
      "Insert row count: 129\n",
      "Insert row count: 130\n",
      "Insert row count: 131\n",
      "Insert row count: 132\n",
      "Insert row count: 133\n",
      "Insert row count: 134\n",
      "Insert row count: 135\n",
      "Insert row count: 136\n",
      "Insert row count: 137\n",
      "Insert row count: 138\n",
      "Insert row count: 139\n",
      "Insert row count: 140\n",
      "Insert row count: 141\n",
      "Insert row count: 142\n",
      "Insert row count: 143\n",
      "Insert row count: 144\n",
      "Insert row count: 145\n",
      "Insert row count: 146\n",
      "Insert row count: 147\n",
      "Insert row count: 148\n",
      "Insert row count: 149\n",
      "Insert row count: 150\n",
      "Insert row count: 151\n",
      "Insert row count: 152\n",
      "Insert row count: 153\n",
      "Insert row count: 154\n",
      "Insert row count: 155\n",
      "Insert row count: 156\n",
      "Insert row count: 157\n",
      "Insert row count: 158\n",
      "Insert row count: 159\n",
      "Insert row count: 160\n",
      "Insert row count: 161\n",
      "Insert row count: 162\n",
      "Insert row count: 163\n",
      "Insert row count: 164\n",
      "Insert row count: 165\n",
      "Insert row count: 166\n",
      "Insert row count: 167\n",
      "Insert row count: 168\n",
      "Insert row count: 169\n",
      "Insert row count: 170\n",
      "Insert row count: 171\n",
      "Insert row count: 172\n",
      "Insert row count: 173\n",
      "Insert row count: 174\n",
      "Insert row count: 175\n",
      "Insert row count: 176\n",
      "Insert row count: 177\n",
      "Insert row count: 178\n",
      "Insert row count: 179\n",
      "Insert row count: 180\n",
      "Insert row count: 181\n",
      "Insert row count: 182\n",
      "Insert row count: 183\n",
      "Insert row count: 184\n",
      "Insert row count: 185\n",
      "Insert row count: 186\n",
      "Insert row count: 187\n",
      "Insert row count: 188\n",
      "Insert row count: 189\n",
      "Insert row count: 190\n",
      "Insert row count: 191\n",
      "Insert row count: 192\n",
      "Insert row count: 193\n",
      "Insert row count: 194\n",
      "Insert row count: 195\n",
      "Insert row count: 196\n",
      "Insert row count: 197\n",
      "Insert row count: 198\n",
      "Insert row count: 199\n",
      "Insert row count: 200\n",
      "Insert row count: 201\n",
      "Insert row count: 202\n",
      "Insert row count: 203\n",
      "Insert row count: 204\n",
      "Insert row count: 205\n",
      "Insert row count: 206\n",
      "Insert row count: 207\n",
      "Insert row count: 208\n",
      "Insert row count: 209\n",
      "Insert row count: 210\n",
      "Insert row count: 211\n",
      "Insert row count: 212\n",
      "Insert row count: 213\n",
      "Insert row count: 214\n",
      "Insert row count: 215\n",
      "Insert row count: 216\n",
      "Insert row count: 217\n",
      "Insert row count: 218\n",
      "Insert row count: 219\n",
      "Insert row count: 220\n",
      "Insert row count: 221\n",
      "Insert row count: 222\n",
      "Insert row count: 223\n",
      "Insert row count: 224\n",
      "Insert row count: 225\n",
      "Insert row count: 226\n",
      "Insert row count: 227\n",
      "Insert row count: 228\n",
      "Insert row count: 229\n",
      "Insert row count: 230\n",
      "Insert row count: 231\n",
      "Insert row count: 232\n",
      "Insert row count: 233\n",
      "Insert row count: 234\n",
      "Insert row count: 235\n",
      "Insert row count: 236\n",
      "Insert row count: 237\n",
      "Insert row count: 238\n",
      "Insert row count: 239\n",
      "Insert row count: 240\n",
      "Insert row count: 241\n",
      "Insert row count: 242\n",
      "Insert row count: 243\n",
      "Insert row count: 244\n",
      "Insert row count: 245\n",
      "Insert row count: 246\n",
      "Insert row count: 247\n",
      "Insert row count: 248\n",
      "Insert row count: 249\n",
      "Insert row count: 250\n",
      "Insert row count: 251\n",
      "Insert row count: 252\n",
      "Insert row count: 253\n",
      "Insert row count: 254\n",
      "Insert row count: 255\n",
      "Insert row count: 256\n",
      "Insert row count: 257\n",
      "Insert row count: 258\n",
      "Insert row count: 259\n",
      "Insert row count: 260\n",
      "Insert row count: 261\n",
      "Insert row count: 262\n",
      "Insert row count: 263\n",
      "Insert row count: 264\n",
      "Insert row count: 265\n",
      "Insert row count: 266\n",
      "Insert row count: 267\n",
      "Insert row count: 268\n",
      "Insert row count: 269\n",
      "Insert row count: 270\n",
      "Insert row count: 271\n",
      "Insert row count: 272\n",
      "Insert row count: 273\n",
      "Insert row count: 274\n",
      "Insert row count: 275\n",
      "Insert row count: 276\n",
      "Insert row count: 277\n",
      "Insert row count: 278\n",
      "Insert row count: 279\n",
      "Insert row count: 280\n",
      "Insert row count: 281\n",
      "Insert row count: 282\n",
      "Insert row count: 283\n",
      "Insert row count: 284\n",
      "Insert row count: 285\n",
      "Insert row count: 286\n",
      "Insert row count: 287\n",
      "Insert row count: 288\n",
      "Insert row count: 289\n",
      "Insert row count: 290\n",
      "Insert row count: 291\n",
      "Insert row count: 292\n",
      "Insert row count: 293\n",
      "Insert row count: 294\n",
      "Insert row count: 295\n",
      "Insert row count: 296\n",
      "Insert row count: 297\n",
      "Insert row count: 298\n",
      "Insert row count: 299\n",
      "Insert row count: 300\n",
      "Insert row count: 301\n",
      "Insert row count: 302\n",
      "Insert row count: 303\n",
      "Insert row count: 304\n",
      "Insert row count: 305\n",
      "Insert row count: 306\n",
      "Insert row count: 307\n",
      "Insert row count: 308\n",
      "Insert row count: 309\n",
      "Insert row count: 310\n",
      "Insert row count: 311\n",
      "Insert row count: 312\n",
      "Insert row count: 313\n",
      "Insert row count: 314\n",
      "Insert row count: 315\n",
      "Insert row count: 316\n",
      "Insert row count: 317\n",
      "Insert row count: 318\n",
      "Insert row count: 319\n",
      "Insert row count: 320\n",
      "Insert row count: 321\n",
      "Insert row count: 322\n",
      "Insert row count: 323\n",
      "Insert row count: 324\n",
      "Insert row count: 325\n",
      "Insert row count: 326\n",
      "Insert row count: 327\n",
      "Insert row count: 328\n",
      "Insert row count: 329\n",
      "Insert row count: 330\n",
      "Insert row count: 331\n",
      "Insert row count: 332\n",
      "Insert row count: 333\n",
      "Insert row count: 334\n",
      "Insert row count: 335\n",
      "Insert row count: 336\n",
      "Insert row count: 337\n",
      "Insert row count: 338\n",
      "Insert row count: 339\n",
      "Insert row count: 340\n",
      "Insert row count: 341\n",
      "Insert row count: 342\n",
      "Insert row count: 343\n",
      "Insert row count: 344\n",
      "Insert row count: 345\n",
      "Insert row count: 346\n",
      "Insert row count: 347\n",
      "Insert row count: 348\n",
      "Insert row count: 349\n",
      "Insert row count: 350\n",
      "Insert row count: 351\n",
      "Insert row count: 352\n",
      "Insert row count: 353\n",
      "Insert row count: 354\n",
      "Insert row count: 355\n",
      "Insert row count: 356\n",
      "Insert row count: 357\n",
      "Insert row count: 358\n",
      "Insert row count: 359\n",
      "Insert row count: 360\n",
      "Insert row count: 361\n",
      "Insert row count: 362\n",
      "Insert row count: 363\n",
      "Insert row count: 364\n",
      "Insert row count: 365\n",
      "Insert row count: 366\n",
      "Insert row count: 367\n",
      "Insert row count: 368\n",
      "Insert row count: 369\n",
      "Insert row count: 370\n",
      "Insert row count: 371\n",
      "Insert row count: 372\n",
      "Insert row count: 373\n",
      "Insert row count: 374\n",
      "Insert row count: 375\n",
      "Insert row count: 376\n",
      "Insert row count: 377\n",
      "Insert row count: 378\n",
      "Insert row count: 379\n",
      "Insert row count: 380\n",
      "Insert row count: 381\n",
      "Insert row count: 382\n",
      "Insert row count: 383\n",
      "Insert row count: 384\n",
      "Insert row count: 385\n",
      "Insert row count: 386\n",
      "Insert row count: 387\n",
      "Insert row count: 388\n",
      "Insert row count: 389\n",
      "Insert row count: 390\n",
      "Insert row count: 391\n",
      "Insert row count: 392\n",
      "Insert row count: 393\n",
      "Insert row count: 394\n",
      "Insert row count: 395\n",
      "Insert row count: 396\n",
      "Insert row count: 397\n",
      "Insert row count: 398\n",
      "Insert row count: 399\n",
      "Insert row count: 400\n",
      "Insert row count: 401\n",
      "Insert row count: 402\n",
      "Insert row count: 403\n",
      "Insert row count: 404\n",
      "Insert row count: 405\n",
      "Insert row count: 406\n",
      "Insert row count: 407\n",
      "Insert row count: 408\n",
      "Insert row count: 409\n",
      "Insert row count: 410\n",
      "Insert row count: 411\n",
      "Insert row count: 412\n",
      "Insert row count: 413\n",
      "Insert row count: 414\n",
      "Insert row count: 415\n",
      "Insert row count: 416\n",
      "Insert row count: 417\n",
      "Insert row count: 418\n",
      "Insert row count: 419\n",
      "Insert row count: 420\n",
      "Insert row count: 421\n",
      "Insert row count: 422\n",
      "Insert row count: 423\n",
      "Insert row count: 424\n",
      "Insert row count: 425\n",
      "Insert row count: 426\n",
      "Insert row count: 427\n",
      "Insert row count: 428\n",
      "Insert row count: 429\n",
      "Insert row count: 430\n",
      "Insert row count: 431\n",
      "Insert row count: 432\n",
      "Insert row count: 433\n",
      "Insert row count: 434\n",
      "Insert row count: 435\n",
      "Insert row count: 436\n",
      "Insert row count: 437\n",
      "Insert row count: 438\n",
      "Insert row count: 439\n",
      "Insert row count: 440\n",
      "Insert row count: 441\n",
      "Insert row count: 442\n",
      "Insert row count: 443\n",
      "Insert row count: 444\n",
      "Insert row count: 445\n",
      "Insert row count: 446\n",
      "Insert row count: 447\n",
      "Insert row count: 448\n",
      "Insert row count: 449\n",
      "Insert row count: 450\n",
      "Insert row count: 451\n",
      "Insert row count: 452\n",
      "Insert row count: 453\n",
      "Insert row count: 454\n",
      "Insert row count: 455\n",
      "Insert row count: 456\n",
      "Insert row count: 457\n",
      "Insert row count: 458\n",
      "Insert row count: 459\n",
      "Insert row count: 460\n",
      "Insert row count: 461\n",
      "Insert row count: 462\n",
      "Insert row count: 463\n",
      "Insert row count: 464\n",
      "Insert row count: 465\n",
      "Insert row count: 466\n",
      "Insert row count: 467\n",
      "Insert row count: 468\n",
      "Insert row count: 469\n",
      "Insert row count: 470\n",
      "Insert row count: 471\n",
      "Insert row count: 472\n",
      "Insert row count: 473\n",
      "Insert row count: 474\n",
      "Insert row count: 475\n",
      "Insert row count: 476\n",
      "Insert row count: 477\n",
      "Insert row count: 478\n",
      "Insert row count: 479\n",
      "Insert row count: 480\n",
      "Insert row count: 481\n",
      "Insert row count: 482\n",
      "Insert row count: 483\n",
      "Insert row count: 484\n",
      "Insert row count: 485\n",
      "Insert row count: 486\n",
      "Insert row count: 487\n",
      "Insert row count: 488\n",
      "Insert row count: 489\n",
      "Insert row count: 490\n",
      "Insert row count: 491\n",
      "Insert row count: 492\n",
      "Insert row count: 493\n",
      "Insert row count: 494\n",
      "Insert row count: 495\n",
      "Insert row count: 496\n",
      "Insert row count: 497\n",
      "Insert row count: 498\n",
      "Insert row count: 499\n",
      "Insert row count: 500\n",
      "Insert row count: 501\n",
      "Insert row count: 502\n",
      "Insert row count: 503\n",
      "Insert row count: 504\n",
      "Insert row count: 505\n",
      "Insert row count: 506\n",
      "Insert row count: 507\n",
      "Insert row count: 508\n",
      "Insert row count: 509\n",
      "Insert row count: 510\n",
      "Insert row count: 511\n",
      "Insert row count: 512\n",
      "Insert row count: 513\n",
      "Insert row count: 514\n",
      "Insert row count: 515\n",
      "Insert row count: 516\n",
      "Insert row count: 517\n",
      "Insert row count: 518\n",
      "Insert row count: 519\n",
      "Insert row count: 520\n",
      "Insert row count: 521\n",
      "Insert row count: 522\n",
      "Insert row count: 523\n",
      "Insert row count: 524\n",
      "Insert row count: 525\n",
      "Insert row count: 526\n",
      "Insert row count: 527\n",
      "Insert row count: 528\n",
      "Insert row count: 529\n",
      "Insert row count: 530\n",
      "Insert row count: 531\n",
      "Insert row count: 532\n",
      "Insert row count: 533\n",
      "Insert row count: 534\n",
      "Insert row count: 535\n",
      "Insert row count: 536\n",
      "Insert row count: 537\n",
      "Insert row count: 538\n",
      "Insert row count: 539\n",
      "Insert row count: 540\n",
      "Insert row count: 541\n",
      "Insert row count: 542\n",
      "Insert row count: 543\n",
      "Insert row count: 544\n",
      "Insert row count: 545\n",
      "Insert row count: 546\n",
      "Insert row count: 547\n",
      "Insert row count: 548\n",
      "Insert row count: 549\n",
      "Insert row count: 550\n",
      "Insert row count: 551\n",
      "Insert row count: 552\n",
      "Insert row count: 553\n",
      "Insert row count: 554\n",
      "Insert row count: 555\n",
      "Insert row count: 556\n",
      "Insert row count: 557\n",
      "Insert row count: 558\n",
      "Insert row count: 559\n",
      "Insert row count: 560\n",
      "Insert row count: 561\n",
      "Insert row count: 562\n",
      "Insert row count: 563\n",
      "Insert row count: 564\n",
      "Insert row count: 565\n",
      "Insert row count: 566\n",
      "Insert row count: 567\n",
      "Insert row count: 568\n",
      "Insert row count: 569\n",
      "Insert row count: 570\n",
      "Insert row count: 571\n",
      "Insert row count: 572\n",
      "Insert row count: 573\n",
      "Insert row count: 574\n",
      "Insert row count: 575\n",
      "Insert row count: 576\n",
      "Insert row count: 577\n",
      "Insert row count: 578\n",
      "Insert row count: 579\n",
      "Insert row count: 580\n",
      "Insert row count: 581\n",
      "Insert row count: 582\n",
      "Insert row count: 583\n",
      "Insert row count: 584\n",
      "Insert row count: 585\n",
      "Insert row count: 586\n",
      "Insert row count: 587\n",
      "Insert row count: 588\n",
      "Insert row count: 589\n",
      "Insert row count: 590\n",
      "Insert row count: 591\n",
      "Insert row count: 592\n",
      "Insert row count: 593\n",
      "Insert row count: 594\n",
      "Insert row count: 595\n",
      "Insert row count: 596\n",
      "Insert row count: 597\n",
      "Insert row count: 598\n",
      "Insert row count: 599\n",
      "Insert row count: 600\n",
      "Insert row count: 601\n",
      "Insert row count: 602\n",
      "Insert row count: 603\n",
      "Insert row count: 604\n",
      "Insert row count: 605\n",
      "Insert row count: 606\n",
      "Insert row count: 607\n",
      "Insert row count: 608\n",
      "Insert row count: 609\n",
      "Insert row count: 610\n",
      "Insert row count: 611\n",
      "Insert row count: 612\n",
      "Insert row count: 613\n",
      "Insert row count: 614\n",
      "Insert row count: 615\n",
      "Insert row count: 616\n",
      "Insert row count: 617\n",
      "Insert row count: 618\n",
      "Insert row count: 619\n",
      "Insert row count: 620\n",
      "Insert row count: 621\n",
      "Insert row count: 622\n",
      "Insert row count: 623\n",
      "Insert row count: 624\n",
      "Insert row count: 625\n",
      "Insert row count: 626\n",
      "Insert row count: 627\n",
      "Insert row count: 628\n",
      "Insert row count: 629\n",
      "Insert row count: 630\n",
      "Insert row count: 631\n",
      "Insert row count: 632\n",
      "Insert row count: 633\n",
      "Insert row count: 634\n",
      "Insert row count: 635\n",
      "Insert row count: 636\n",
      "Insert row count: 637\n",
      "Insert row count: 638\n",
      "Insert row count: 639\n",
      "Insert row count: 640\n",
      "Insert row count: 641\n",
      "Insert row count: 642\n",
      "Insert row count: 643\n",
      "Insert row count: 644\n",
      "Insert row count: 645\n",
      "Insert row count: 646\n",
      "Insert row count: 647\n",
      "Insert row count: 648\n",
      "Insert row count: 649\n",
      "Insert row count: 650\n",
      "Insert row count: 651\n",
      "Insert row count: 652\n",
      "Insert row count: 653\n",
      "Insert row count: 654\n",
      "Insert row count: 655\n",
      "Insert row count: 656\n",
      "Insert row count: 657\n",
      "Insert row count: 658\n",
      "Insert row count: 659\n",
      "Insert row count: 660\n",
      "Insert row count: 661\n",
      "Insert row count: 662\n",
      "Insert row count: 663\n",
      "Insert row count: 664\n",
      "Insert row count: 665\n",
      "Insert row count: 666\n",
      "Insert row count: 667\n",
      "Insert row count: 668\n",
      "Insert row count: 669\n",
      "Insert row count: 670\n",
      "Insert row count: 671\n",
      "Insert row count: 672\n",
      "Insert row count: 673\n",
      "Insert row count: 674\n",
      "Insert row count: 675\n",
      "Insert row count: 676\n",
      "Insert row count: 677\n",
      "Insert row count: 678\n",
      "Insert row count: 679\n",
      "Insert row count: 680\n",
      "Insert row count: 681\n",
      "Insert row count: 682\n",
      "Insert row count: 683\n",
      "Insert row count: 684\n",
      "Insert row count: 685\n",
      "Insert row count: 686\n",
      "Insert row count: 687\n",
      "Insert row count: 688\n",
      "Insert row count: 689\n",
      "Insert row count: 690\n",
      "Insert row count: 691\n",
      "Insert row count: 692\n",
      "Insert row count: 693\n",
      "Insert row count: 694\n",
      "Insert row count: 695\n",
      "Insert row count: 696\n",
      "Insert row count: 697\n",
      "Insert row count: 698\n",
      "Insert row count: 699\n",
      "Insert row count: 700\n",
      "Insert row count: 701\n",
      "Insert row count: 702\n",
      "Insert row count: 703\n",
      "finished\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "import csv\n",
    "\n",
    "from psycopg2 import connect, extras\n",
    "\n",
    "from connectors.postgres_connector import PostgresConnector\n",
    "from feeds.postgres_feed import PostgresFeed\n",
    "\n",
    "from manglers.mangle_org_name import mangle_org_name\n",
    "from manglers.mangle_url import mangle_url\n",
    "from manglers.tld_swap_prob_dict import tld_swap_prob_dict\n",
    "\n",
    "# connection details for source data table\n",
    "source_host = 'silobuster-db-do-user-12298230-0.b.db.ondigitalocean.com'\n",
    "source_user = 'jameyc'\n",
    "source_passwd = 'UXZSXXXSFZeU8XKw'\n",
    "source_db = 'defaultdb'\n",
    "source_port = 25060\n",
    "\n",
    "# connection details for training data table\n",
    "training_host = 'silobuster-db-do-user-12298230-0.b.db.ondigitalocean.com'\n",
    "training_user = 'jameyc'\n",
    "training_passwd = 'UXZSXXXSFZeU8XKw'\n",
    "training_db = 'jameycdb'\n",
    "training_port = 25060\n",
    "training_table = 'organizations_normalized'\n",
    "\n",
    "csv_output = 'training_set1.csv'\n",
    "\n",
    "# some of below probabilities are rough estimates based on organization table\n",
    "# e.g. around 3% of non-blank URLs in organization table are missing scheme\n",
    "url_mangling_probs_dict = {\n",
    "    'www_remove_prob': .22, \n",
    "    'scheme_remove_prob': .03, \n",
    "    'remove_s_from_https_prob': .68,\n",
    "    'append_extra_slash_prob': .35,\n",
    "    # 'change_domain_ext_prob': .05, # arbitrary probability\n",
    "    'mispell_remove_char_prob': .02, # arbitrary probability\n",
    "    'mispell_replace_char_prob': .02, # arbitrary probability\n",
    "    'mispell_null_url_prob': .02 # arbitrary probability\n",
    "}\n",
    "\n",
    "print ('Starting connections...')\n",
    "source_conn = connect(\n",
    "        database=source_db,\n",
    "        user=source_user,\n",
    "        password=source_passwd,\n",
    "        host=source_host,\n",
    "        port=source_port\n",
    "    )\n",
    "\n",
    "training_conn = connect(\n",
    "        database=training_db,\n",
    "        user=training_user,\n",
    "        password=training_passwd,\n",
    "        host=training_host,\n",
    "        port=training_port\n",
    "    )\n",
    "\n",
    "select_qry = \"select t1.name, t1.description, t1.url, t3.address_1, t3.address_2, t3.city, t3.region, t3.state_province, t3.postal_code, t3.country, t3.type from organization t1 left join location t2 on t1.id = t2.organization_id left join address t3 on t3.location_id = t2.id\"\n",
    "insert_qry = f\"INSERT INTO {training_table} (name, description, url, address_1, address_2, city, state_province, postal_code, country, type, region) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id\"\n",
    "insert_dup_qry = f\"INSERT INTO {training_table} (name, description, url, address_1, address_2, city, state_province, postal_code, country, type, region, duplicate_id, duplicate_type, training_set) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)\"\n",
    "\n",
    "print (\"Connected?\")\n",
    "dups = []\n",
    "\n",
    "# Data keys for mangling\n",
    "address_keys = []\n",
    "with open('./helper_data/street_suffixes.csv', 'r') as suffixes:\n",
    "    csv_reader = csv.reader(suffixes)\n",
    "    for row in csv_reader:\n",
    "        row_clean = [suffix for suffix in row if suffix] # removing empty strings\n",
    "        address_keys.append(row_clean)\n",
    "\n",
    "state_keys = list()\n",
    "state_keys.append(['wa', 'wash', 'washington'])\n",
    "\n",
    "with source_conn.cursor(cursor_factory=extras.RealDictCursor) as source_cur:\n",
    "    source_cur.execute(select_qry)\n",
    "    data = source_cur.fetchall()\n",
    "    print (\"Retrieved data...\")\n",
    "    \n",
    "    dup_address = random.randint(1,3)\n",
    "    dup_blank_stuff = random.randint(1,2)\n",
    "    \n",
    "    for count, row in enumerate(data):\n",
    "        \n",
    "        print (f\"Insert row count: {count}\")\n",
    "\n",
    "                \n",
    "        name = row['name'].strip().lower()\n",
    "        try:\n",
    "            desc = row['description'].strip().lower()\n",
    "        except:\n",
    "            desc = ''\n",
    "        try:\n",
    "            url = row['url'].strip().lower()\n",
    "        except:\n",
    "            url = ''\n",
    "        try:\n",
    "            address_1 = row['address_1'].strip().lower()\n",
    "        except:\n",
    "            address_1 = ''\n",
    "        try:\n",
    "            address_2 = row['address_2'].strip().lower()\n",
    "        except:\n",
    "            address_2 = ''\n",
    "        try:\n",
    "            city = row['city'].strip().lower()\n",
    "        except:\n",
    "            city = ''\n",
    "        try:\n",
    "            region = row['region'].strip().lower()\n",
    "        except:\n",
    "            region = ''\n",
    "        try:\n",
    "            state = row['state_province'].strip().lower()\n",
    "        except:\n",
    "            state = ''\n",
    "        try:\n",
    "            postal = row['postal_code'].strip().lower()\n",
    "        except:\n",
    "            postal = ''\n",
    "        try:\n",
    "            country = row['country'].strip().lower()\n",
    "        except:\n",
    "            country = ''\n",
    "        try:\n",
    "            type_row = row['type'].strip().lower()\n",
    "        except:\n",
    "            type_row = ''\n",
    "        \n",
    "        # Write the rows to the normalized table\n",
    "        with training_conn.cursor() as training_cur:\n",
    "            training_cur.execute(insert_qry, [\n",
    "                    name, \n",
    "                    desc, \n",
    "                    url, \n",
    "                    address_1, \n",
    "                    address_2, \n",
    "                    city, \n",
    "                    state, \n",
    "                    postal,\n",
    "                    country, \n",
    "                    type_row,\n",
    "                    region, \n",
    "\n",
    "            ])\n",
    "            insert_id = training_cur.fetchone()[0]\n",
    "            training_conn.commit()\n",
    "        \n",
    "        \n",
    "        with open(csv_output, 'a') as csvfile:\n",
    "            w = csv.writer(csvfile, delimiter=',', quoting=csv.QUOTE_MINIMAL)\n",
    "            w.writerow([\n",
    "                name,\n",
    "                desc,\n",
    "                url,\n",
    "                address_1,\n",
    "                address_2,\n",
    "                city,\n",
    "                state,\n",
    "                postal,\n",
    "                country,\n",
    "                type_row,\n",
    "                region,\n",
    "                '',\n",
    "                '',\n",
    "                0,\n",
    "            ])\n",
    "\n",
    "        #create a duplicate/mangled name\n",
    "        mangled_name = mangle_org_name(\n",
    "            name, \n",
    "            remove_prob = 0.02,\n",
    "            replace_prob = 0.02,\n",
    "            null_prob = 0.02\n",
    "        )\n",
    "\n",
    "        duplicate_type = []\n",
    "        if mangled_name != name:\n",
    "            duplicate_type.append('mangled_name')\n",
    "\n",
    "        mangled_url = mangle_url(url, url_mangling_probs_dict, tld_swap_prob_dict)\n",
    "        if mangled_url != url:\n",
    "            duplicate_type.append('mangled_url')\n",
    "\n",
    "        # initializing mangled fields as the original values - this will allow us to identify if these fields were mangled later on\n",
    "        # when deciding if we need to insert another \"duplicate\" row\n",
    "        mangled_address_1 = address_1\n",
    "        mangled_desc = desc\n",
    "        mangled_region = region\n",
    "        mangled_country = country\n",
    "\n",
    "        #create a duplicate address\n",
    "        if dup_address == count:\n",
    "            dup_address = random.randint(1, 3) + count\n",
    "            duplicate_type.append('street_dup')\n",
    "            lst = address_1.split(' ')\n",
    "            for a in address_keys:\n",
    "                if lst[len(lst)-1] in a:\n",
    "                    new_street = a[random.randint(0, len(a)-1)]\n",
    "                    del lst[len(lst)-1]\n",
    "                    lst.append(new_street)\n",
    "                    break\n",
    "                    \n",
    "            mangled_address_1 = ' '.join(lst)\n",
    "            if count // dup_blank_stuff == 0:\n",
    "                duplicate_type.append('blanked')\n",
    "                mangled_desc = ''\n",
    "                mangled_region = ''\n",
    "                mangled_country = ''\n",
    "\n",
    "        # if any of the fields were mangled, create a duplicate row in database and in training set CSV file\n",
    "        if mangled_name != name or mangled_address_1 != address_1 or mangled_desc != desc or mangled_url != url or mangled_region != region or mangled_country != country:  \n",
    "            dup_row = {\n",
    "                'name': mangled_name,\n",
    "                'description': mangled_desc,\n",
    "                'url': mangled_url,\n",
    "                'address_1': mangled_address_1,\n",
    "                'address_2': address_2,\n",
    "                'city': city,\n",
    "                'region': mangled_region,\n",
    "                'state': state,\n",
    "                'postal': postal,\n",
    "                'country': mangled_country,\n",
    "                'duplicate_id': insert_id,\n",
    "                'duplicate_type': duplicate_type,\n",
    "                'training_set': 1\n",
    "            }\n",
    "\n",
    "            # name, description, url, address_1, address_2, city, state_province, postal_code, country, type, region, duplicate_id, duplicate_type, training_set\n",
    "            \n",
    "            with training_conn.cursor() as dup1_cur:\n",
    "                dup1_cur.execute(insert_dup_qry, [\n",
    "                        mangled_name,\n",
    "                        mangled_desc,\n",
    "                        mangled_url,\n",
    "                        mangled_address_1,\n",
    "                        address_2,\n",
    "                        city,\n",
    "                        state,\n",
    "                        postal,\n",
    "                        mangled_country,\n",
    "                        type_row,\n",
    "                        mangled_region,\n",
    "                        insert_id,\n",
    "                        '|'.join(duplicate_type),\n",
    "                        1,\n",
    "                    ])\n",
    "                \n",
    "    \n",
    "\n",
    "            with open(csv_output, 'a') as csvfile:\n",
    "                w = csv.writer(csvfile, delimiter=',', quoting=csv.QUOTE_MINIMAL)\n",
    "                w.writerow([\n",
    "                    mangled_name,\n",
    "                    mangled_desc,\n",
    "                    mangled_url,\n",
    "                    mangled_address_1,\n",
    "                    address_2,\n",
    "                    city,\n",
    "                    state,\n",
    "                    postal,\n",
    "                    mangled_country,\n",
    "                    type_row,\n",
    "                    mangled_region,\n",
    "                    insert_id,\n",
    "                    '|'.join(duplicate_type),\n",
    "                    1,\n",
    "                ])\n",
    "                \n",
    " \n",
    "print ('finished')           \n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5085c4a3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 64-bit",
   "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"
  },
  "vscode": {
   "interpreter": {
    "hash": "81794d4967e6c3204c66dcd87b604927b115b27c00565d3d43f05ba2f3a2cb0d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}