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

Answer 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'])) 

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

Answer 2

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.

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