0

I want to send the output to a postgres database directly! is that possible of i have to convert it first to another format! any idea!! 

curl -sG https://peeringdb.com/api/netixlan --data-urlencode asn=58717 \
  --data-urlencode fields=ix_id,ipaddr4,ipaddr6,speed | jq -c '.data[]'

The output is:

{"ix_id":215,"speed":1000,"ipaddr4":"198.32.167.102","ipaddr6":null} {"ix_id":158,"speed":10000,"ipaddr4":"27.111.228.8","ipaddr6":"2001:de8:4::5:8717:1"} {"ix_id":158,"speed":10000,"ipaddr4":"27.111.229.92","ipaddr6":"2001:de8:4::5:8717:2"} {"ix_id":429,"speed":10000,"ipaddr4":"103.16.102.35","ipaddr6":"2001:de8:12:100::35"}

Drik Drik
  • 71
  • 1
  • 1
  • 9
  • Do you know how to insert data into postgres? – Michael Cox Sep 25 '18 at 20:13
  • yes! I use insert method. i can send the output to a text file and than send it back to database but i want to send it directly to a database. i have tried json but my curl has | jq -c '.data[]' and not easy for me convert it – Drik Drik Sep 25 '18 at 20:17
  • I think you can do what you wish to do with the [requests](http://docs.python-requests.org/en/master/) library. – IMCoins Sep 25 '18 at 20:19

1 Answers1

0

You could try using Pandas and sqlalchemy:

import pandas as pd
from sqlalchemy import create_engine

#Define your connection to the DB:
engine = create_engine('postgresql://username:password@servername:port/dbname')

#Convert your json to a Pandas Dataframe:
df = pd.read_json(json_file) # Your json goes here, 
     #or just create a dataframe from the curl dictionary output

#Sends the DataFrame to postgres:
df.to_sql('tablename', engine, if_exists='replace', schema='public')
fabio.avigo
  • 308
  • 1
  • 13
  • is that what you mean!! df = pd.read_json(curl -sG https://peeringdb.com/api/netixlan --data-urlencode asn=58717 \ --data-urlencode fields=ix_id,ipaddr4,ipaddr6,speed | jq -c '.data[]') – Drik Drik Sep 25 '18 at 20:44
  • I meant that you should output your curl command to a string (let's say json_str), and then do df = pd.read_json(json_str). You could chain the commands, but I'm not entirely familiar with the specifics. – fabio.avigo Sep 25 '18 at 20:54
  • that is actually my problem! i cant convert it to json_str. because the curl has extra parameters like | jq -c '.data[]' – Drik Drik Sep 25 '18 at 21:01