Skip to content

Instantly share code, notes, and snippets.

@srinivas946

srinivas946/Interact_CSV_with_DB.ipynb

Created February 23, 2020 19:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save srinivas946/c8fec674a861c024b57e17107c4fe3c6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save srinivas946/c8fec674a861c024b57e17107c4fe3c6 to your computer and use it in GitHub Desktop.
Download ZIP
Interact with MySQL database to store the Logs Information which holds by CSV file.
Display the source blob
Display the rendered blob
Raw
Interact_CSV_with_DB.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import csv # load csv module to handle csv data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<b>create logic for pivot table</b>\n",
"<p>Download sample Information file - <a href=\"https://drive.google.com/open?id=1EinQJ7Z3WAKWqZvUsEZJXg_HlJ_On0J4\">link</a></p>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"with open('pivot_table.csv', 'r') as csvfile:\n",
" csv_reader = csv.DictReader(csvfile)\n",
" for row in csv_reader:\n",
" print(row)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"source_set, data_list = set(), []\n",
"with open('pivot_table.csv', 'r') as csvfile:\n",
" csv_reader = csv.DictReader(csvfile)\n",
" for row in csv_reader:\n",
" source_set.add(row['Source IP'])\n",
" data_list.append(row['Source IP'])\n",
"pivot_data = {}\n",
"for ip in source_set:\n",
" repeat_count = 0\n",
" for data in data_list:\n",
" if ip == data: repeat_count += 1\n",
" pivot_data[ip] = repeat_count\n",
"pivot_data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<b>Interact with mysql database</b>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<li>Interact with mysql database using python module - <a href=\"https://pymysql.readthedocs.io/en/latest/\">PyMySQL</a></li>\n",
"<li>Install this package using command - pip install PyMySQL</li>\n",
"<li>For this tutorial we can only create a table and insert the data into it</li>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import pymysql # load module to handle database\n",
"\n",
"# Open database connection\n",
"db = pymysql.connect(host=\"localhost\", user=\"testuser\", password=\"test123\",db=\"TESTDB\" )\n",
"\n",
"# prepare a cursor object using cursor() method\n",
"cursor = db.cursor()\n",
"\n",
"# Prepare SQL query to INSERT a record into the database.\n",
"sql = \"\"\"INSERT INTO EMPLOYEE(SOURCE_IP,\n",
" SOURCE_PORT, DESTINATION_IP, DESTINATION_PORT)\n",
" VALUES ('1.2.34.5', '1234', 10.0.0.2, '80')\"\"\"\n",
"try:\n",
" # Execute the SQL command\n",
" cursor.execute(sql)\n",
" # Commit your changes in the database\n",
" db.commit()\n",
"except:\n",
" # Rollback in case there is any error\n",
" db.rollback()\n",
"\n",
"# disconnect from server\n",
"db.close()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<b>Real Time Scenarion Script</b>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import csv\n",
"import pymysql\n",
"\n",
"# ===============================================\n",
"# CREATE CLASS TO STORE CSV DATA INTO DATABASE\n",
"# ===============================================\n",
"class CSV_DB:\n",
" \n",
" # ===================================\n",
" # READ FILE PATH TO LOAD CSV DATA\n",
" # ===================================\n",
" def __init__(self, read_file_path, database_host, database_user, database_password, database_name):\n",
" self._read_file_path = read_file_path\n",
" self._database_host = database_host\n",
" self._database_user = database_user\n",
" self._database_password = database_password\n",
" self._database_name = database_name\n",
" \n",
" # =================================\n",
" # READ CSV FILE TO EXTRACT DATA\n",
" # =================================\n",
" def read_csv_file(self):\n",
" source_set, data_list = set(), []\n",
" with open(self._read_file_path, 'r') as csvfile:\n",
" csv_reader = csv.DictReader(csvfile)\n",
" for row in csv_reader:\n",
" source_set.add(row['Source IP'])\n",
" data_list.append(row['Source IP'])\n",
" pivot_data = {}\n",
" for ip in source_set:\n",
" repeat_count = 0\n",
" for data in data_list:\n",
" if ip == data: repeat_count += 1\n",
" pivot_data[ip] = repeat_count\n",
" return pivot_data\n",
" \n",
" # =======================================================\n",
" # INTERACT WITH MYSQL DATABASE TO STORE CSV INFORMATION\n",
" # =======================================================\n",
" def connect_database(self):\n",
" # Open database connection\n",
" db = pymysql.connect(host=self._database_host, user=self._database_user, \n",
" password=self._database_password, db=self._database_name)\n",
" cursor = db.cursor()\n",
" for key, val in pivot_data.items():\n",
" sql = f\"INSERT INTO EMPLOYEE(SOURCE_IP,\n",
" SOURCE_PORT, DESTINATION_IP, DESTINATION_PORT)\n",
" VALUES {(key, data)}\"\n",
" try:\n",
" # Execute the SQL command\n",
" cursor.execute(sql)\n",
" # Commit your changes in the database\n",
" db.commit()\n",
" except:\n",
" # Rollback in case there is any error\n",
" db.rollback()\n",
"\n",
" # disconnect from server\n",
" db.close()\n",
" \n",
"# =====================================\n",
"# PROGRAM EXECUTION STARTS FROM HERE\n",
"# =====================================\n",
"\n",
"# CREATE OBJECT TO CSV_DB CLASS\n",
"cb = CSV_DB(read_file_path='pivot_table.csv', database_host='your_db_host',\n",
" database_user='your_db_username', database_password='your_db_password',\n",
" database_name='your_db_name')\n",
"\n",
"# READ CSV FILE INFORMATION\n",
"pivot_data = cb.read_csv_file()\n",
"\n",
"# INSETING PIVOT DATA INTO DATABASE\n",
"cb.connect_database()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<b>Learn more Real Time Scenarios related to CSV - <a href=\"https://cybersecpy.in/handle-csv-files-using-python/\">cybersecpy</a></b>"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"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.8.1"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment