How to store API respond in MySQL database using Python?

Question

I am really new to Python, MySQL and API development. So, I have a created a simple request from scheduling calendar using postman (After hours of online research). I was able create a MySQL database on localhost. I was able to insert data using Python, but I want to store the API response not manually. Please look at my code and I would really appreciate if any body could help me on this.

Request Call using Python

    import requests

    url = "https://shedul.com/api/v1/appointments"
    querystring = {"minDate":"02/01/2019","maxDate":"02/08/2019"}

    payload = ""
    headers = {
        'Authorization': "Basic ************************"}

    response = requests.request("GET", url, data=payload, headers=headers, params=querystring)
    print(response.json())

I will get a response something like this (I will so many request and more fields but I need those few fields),

[
    {
        "id": 2649,
        "firstName": "Walikada",
        "lastName": "Sumana",
        "phone": "7894561452",
        "email": "[email protected]",
        "date": "February 8, 2019",
        "time": "2:00pm",
        "endTime": "3:00pm",
        "dateCreated": "February 5, 2019",
        "datetimeCreated": "2019-02-05T15:57:13-0600",
        "datetime": "2019-02-08T14:00:00-0700",
    },
    {
        "id": 264693950,
        "firstName": "Wade",
        "lastName": "Gulikapupusa",
        "phone": "789691985",
        "email": "[email protected]",
        "date": "February 8, 2019",
        "time": "2:00pm",
        "endTime": "2:20pm",
        "dateCreated": "February 4, 2019",
        "datetimeCreated": "2019-02-04T15:05:07-0600",
        "datetime": "2019-02-08T14:00:00-0600",
     }
]

Code that I found to connect MySQl database,

import mysql.connector

connection = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="admin",
              database="techbrandtest"
            )
mycursor = connection.cursor()

sqlcode = """INSERT INTO techbrandtest.acuity 
                (
                    id, 
                    firstName, 
                    lastName,
                    phone,
                    email,
                    date,
                    time,
                    endTime,
                    dateCreated,
                    datetimeCreated,
                    datetime                     

                ) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

insertvalues = ('546606', 'Suneth', 'Tharinda', '8016381256', '[email protected]', '2019-02-02', '', '','2019-02-02','2019-02-02 23:59:59', '2019-02-05 23:59:59')

mycursor.execute(sqlcode, insertvalues)
connection.commit()
print(mycursor.rowcount, "was inserted.")

I want to make it one code and store every response on MySQL database. I am not sure this is too much to ask, but any help will be highly appreciated.

Thank you so much for your time

Susie Queue · Accepted Answer · 2019-02-05 23:36:24Z

1

If I understand your question properly, then you should be able to achieve this by simply using a for loop.

e.g.,

    for i in response:
        mycursor.execute(sqlcode, (i['id'],i['firstName']))

answered Feb 5, 2019 at 23:36

Susie Queue

361 silver badge3 bronze badges

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

1 Comment

Suneth Over a year ago

Hey Susie, Again I am really new to python, how do you insert in to SQL? Thank you

Bohdan Turani · Accepted Answer · 2019-02-05 23:44:30Z

1

In Python you have very useful tool for working with databases. Its called SQLAlchemy. With its help you don’t need to write raw SQL, you can use database tables as Python objects. Though you will need to learn its API.

Respond can be stored as JSON field in database or you can make additional table for it(in case responds look same all the time). You can save it before sending respond and it will be saving “automatically”.

If you have further questions or my answer does not contain what you need, than just comment this answer. Have a good day.

answered Feb 5, 2019 at 23:44

Bohdan Turani

197 bronze badges

1 Comment

Suneth Over a year ago

Hey Bohdan Turani, I will look into that option. Thank you for the information.

Collectives™ on Stack Overflow

How to store API respond in MySQL database using Python?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related