Trying to get json data to be parsed into an SQL server

Question

By using python, I have been trying to get data from an online API and parsing it into a MySQL server. However, I keep running into different errors. One the errors has been

Exception has occurred: AttributeError 'str' object has no attribute 'get' File "C:\Users\bazoo\ShortPy\ShortPy.py", line 19, in <module> title=comic.get("title")"

Any help is appreciated.

Code:

from sqlite3.dbapi2 import Connection, Cursor 
import requests
import sqlite3
import json
import pandas as pd
import pyodbc
import MySQLdb
import mysql.connector
import pymysql
from requests.models import Response

response_API= requests.get('https://api.shortboxed.com/comics/v1/new')
data=response_API.json()
comics_in_box=json.dumps(data)
conn = pymysql.connect(host ="", user ="", password = "", db ="")
cursor=conn.cursor()

for comic in comics_in_box:
    title=comic.get("title")
    publisher=comic.get("publisher")
    price=comic.get("price")
    diamond_id=comic.get("diamond_id")
    cursor.execute("insert into Shortbox (title, publisher, price, diamond_id) value (%s, %s, %s, %s)", (title, publisher, price, diamond_id))
conn.commit()
conn.close()

Exception has occurred: AttributeError 'str' object has no attribute 'get' File "C:\Users\bazoo\ShortPy\ShortPy.py", line 19, in <module> title=comic.get("title") — Patrick Wood
– Patrick Wood, Commented Dec 10, 2021 at 12:35
yea, json.dumps makes a string out of an object so you just probably need to not do comics_in_box=json.dumps(data) and say comics_in_box = data instead — Ezenhis
– Ezenhis, Commented Dec 10, 2021 at 12:44

Kris · Accepted Answer · 2021-12-10 12:44:15Z

1

In your code comics_in_box is a JSON string and not a dict object as you are dumping it already. You cannot expect to parse a string like a dict

I made some changes to correct your code.

response_API= requests.get('https://api.shortboxed.com/comics/v1/new')
# Use the JSON object as is
comics_in_box=response_API.json()
conn = pymysql.connect(host ="", user ="", password = "", db ="")
cursor=conn.cursor()

#The respons contains comics as the key to the array
for comic in comics_in_box.get("comics"):
    title=comic.get("title")
    publisher=comic.get("publisher")
    price=comic.get("price")
    diamond_id=comic.get("diamond_id")
    cursor.execute("insert into Shortbox (title, publisher, price, diamond_id) value (%s, %s, %s, %s)", (title, publisher, price, diamond_id))
conn.commit()
conn.close()

answered Dec 10, 2021 at 12:44

Kris

8,8984 gold badges45 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fasika · Accepted Answer · 2021-12-15 15:29:28Z

0

You can load the json directly to a pandas dataframe and write that to the server via to_sql method.

The dataframe will look like

Use the code as follows to load that df via sqlalchemy to the db

import requests
import json
from pandas.io.json import json_normalize
from sqlalchemy import create_engine

response_API= requests.get('https://api.shortboxed.com/comics/v1/new')
data = json.loads(response_API.content)
df = json_normalize(data, record_path=['comics'])
engine = create_engine("mysql://user:password@localhost:port/db")
df.to_sql(name='Shortbox', schema='dbo', con=engine, if_exists='append', index=False)

answered Dec 15, 2021 at 15:29

Fasika

463 bronze badges

Collectives™ on Stack Overflow

Trying to get json data to be parsed into an SQL server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related