I am struggling to figure out how to get my already made Json output and also create a csv file from that output. For more clarification, I have created a json file by using write, but I am unsure on how to also create a CSV file during the same process. Maybe I am just not inputting the CSV code in the correct area, or if Django needs some other form of method to process that request.
utils.py
import json
import os
import pandas as pd
from django.utils import timezone
from django.http import HttpResponse
from django.db.models import Q
from api.models import BlockedList
def get_ip_list_json() -> dict:
json_response = {
"version": "",
"description": "",
"objects": [
{
"name": "",
"id": "",
"description": "",
"ranges": ["2.2.2.2"],
},
],
}
group_map = {}
for ip in BlockedList.objects.filter(
Q(end_date__isnull=True) | Q(end_date__gte=timezone.now())
).select_related("group"):
group_id = str(ip.group.id)
if group_id not in group_map:
group_map[group_id] = {
"name": ip.group.name,
"id": ip.group.group_id,
"description": ip.group.description,
"ranges": [],
}
group_map[group_id]["ranges"].append(ip.address)
json_response["objects"] = list(group_map.values())
return json_response
def create_ip_file():
try:
base_dir = "C://Users/Steven/Desktop"
filename = "blocklist.json"
full_path = os.path.join(base_dir, filename)
with open(full_path, "w") as f:
f.write(json.dumps(get_ip_list_json(), indent=4))
except Exception as e:
print(e)
That is my above code where I create my Json output file, below is the code that I have tried, but am unsure as to how to fit it into the current process, or if I will have to go at this in a different direction.
with open("blocklist.json", enconding="utf-8") as inputfile:
df = pd.read_json(inputfile)
df.to_csv("test.csv", encoding="utf-8", index=False)
data = get_ip_list_json()
json_data = json.loads(data)
df = pd.DataFrame(json_data)
csv_file = "test.csv"
df.to_csv(csv_file, index=False)
with open("blocktest.json") as inputfile:
Data = json.loads(get_ip_list_json())
with open("test.csv", "w") as outfile:
f = csv.writer(outfile)
f.writerows(Data["Objects"])
I have tried implementing these three methods, but nothing seems to produce a csv file, I am not sure why. They also don't seem to be creating an error when that util file gets used, so the code is being read but no file is produced.
Edit: This is how I have used the solution below done by Nayem, just changed some parts to personalize but should give the same output. This change is just for anyone who wants to directly grab objects from the Models.py.
def create_ip_file():
try:
base_dir = "C://Users/Steven/Desktop"
filename = "blocklist.json"
csv_filename = "blocktest.csv"
full_path = os.path.join(base_dir, filename)
# This is where the Json File gets created using the get_ip_list_json
with open(full_path, "w") as f:
json_data = get_ip_list_json()
f.write(json.dumps(json_data, indent=4))
# Conversion of Json Data into DataFrame
blockedlist = BlockedList.objects.all()
data_for_csv = []
for obj in blockedlist:
data_for_csv.append({
"NetworkAddress": obj.address,
"Group": obj.group,
"Reason": obj.reason,
"Expiry": obj.end_date
})
# Using the DataFram from the pandas library
df = pd.DataFrame(data_for_csv)
# Creation of the CSV file
csv_full_path = os.path.join(base_dir, csv_filename)
df.to_csv(csv_full_path, index=False)
except Exception as e:
print(e)
Simple.
Generate the JSON data-> Write the JSON data to a file -> Convert the JSON data to a Pandas DataFrame -> Write the DataFrame to a CSV file.
import json
import os
import pandas as pd
from django.utils import timezone
from django.http import HttpResponse
from django.db.models import Q
from api.models import BlockedList
def get_ip_list_json() -> dict:
json_response = {
"version": "",
"description": "",
"objects": [
{
"name": "",
"id": "",
"description": "",
"ranges": ["2.2.2.2"],
},
],
}
group_map = {}
for ip in BlockedList.objects.filter(
Q(end_date__isnull=True) | Q(end_date__gte=timezone.now())
).select_related("group"):
group_id = str(ip.group.id)
if group_id not in group_map:
group_map[group_id] = {
"name": ip.group.name,
"id": ip.group.group_id,
"description": ip.group.description,
"ranges": [],
}
group_map[group_id]["ranges"].append(ip.address)
json_response["objects"] = list(group_map.values())
return json_response
def create_ip_file():
try:
base_dir = "C://Users/Steven/Desktop"
json_filename = "blocklist.json"
csv_filename = "blocklist.csv"
# Create JSON file
json_full_path = os.path.join(base_dir, json_filename)
with open(json_full_path, "w") as f:
json_data = get_ip_list_json()
f.write(json.dumps(json_data, indent=4))
# Convert JSON data to DataFrame
objects = json_data["objects"]
data_for_csv = []
for obj in objects:
for ip in obj["ranges"]:
data_for_csv.append({
"name": obj["name"],
"id": obj["id"],
"description": obj["description"],
"ip_range": ip
})
df = pd.DataFrame(data_for_csv)
# Create CSV file
csv_full_path = os.path.join(base_dir, csv_filename)
df.to_csv(csv_full_path, index=False)
except Exception as e:
print(e)