WORKING WITH WEB API and exporting results to sql db

Python Help
1

i have a URL and and also the api key to get data from an external website
however i need to provide 2 parameters to extract the data (Date, EMPLOYEE).
Once i enter the parameter i should get all the data i need in this format (name,account_number,date,employee).
After this i would like to export the results into an already created sql table ( same headers as above)
can someone please assist

this is what i did so far

import json
import requests

api_token = ‘’
url’’

headers {‘authorization’: 'Bearer {0}.format(api_ken}
r= requests.get(url,headers=headers)

print(r.text)

2

So for the first part, requests does allow you to pass URL params, e.g.

my_params = {'Date': '2020-07-21', 'Employee': 12}
r = requests.get(url, headers=headers, params=my_params)

See the requests docs for more info: https://requests.readthedocs.io/en/master/user/quickstart/#passing-parameters-in-urls

Once you have got the data, there are several libraries which can help you write the data back to a SQL database. My preference is to use a package implementing the python DB API for a given SQL language. For example, for MS SQL Server this would be pyodbc.

Some example code to insert using pyodbc would be:

import pyodbc
conn = pyodbc.connect(YOUR_DATABASE_CONNECTION_STRING)
cursor = conn.cursor()

cursor.execute(
    """
    insert into your_table_name(name,account_number,date,employee) 
    values (?, ?, ?, ?)
    """, 
    'John Smith', 12345, '2020-07-21', 12
)
conn.commit()

You can see more in the pyodbc docs (https://github.com/mkleehammer/pyodbc/wiki/Getting-started).

If you are using a different SQL language, you will need to use a different library (e.g. psycopg2 for PostgreSQL), but the code should be almost identical as all of these libraries implement the same functions for interaction with databases (see https://www.python.org/dev/peps/pep-0249/)