Web api results to sql server table

1

Just wanted to see if it is possible to access a web api from sql then populate sql tables? If so please provide steps .

2

hi

hope this link helps :slight_smile:

3

is this an in house api or an external api (external to your network)

4

External

5

Does it involve any secured connection using user name and password or tokens etc

6

Yes there are special api token and url which I do have , no special username . I can do it in python but need to extract the data into sql server so I wanted to try powershell

7

you can do it either in python or powershell. for python try the following. i would prefer to go this route than trying to do it from SQL Server. This is just an example to get rssfeeds, you can customize it to do what you want for your api.

import feedparser
import pyodbc 

conn = pyodbc.connect("Driver={SQL Server};"               
               "Server=your.sql.server;"
               "Database=your.database;"
               "username=your.user.name;"
               "password=your.user.password!;"
               "Trusted_Connection=yes;")
cursor = conn.cursor()

--create table dbo.rssfeeds(title nvarchar(150), 
--link nvarchar(50), descriptionnvarchar(max))

import requests

resp = requests.get('https://todolist.example.com/tasks/')
if resp.status_code != 200:
    # This means something went wrong.
    raise ApiError('GET /tasks/ {}'.format(resp.status_code))
for todo_item in resp.json():
    print('{} {}'.format(todo_item['id'], todo_item['summary']))

feed = feedparser.parse("https://www.us-cert.gov/ncas/all.xml")
for entry in feed.entries:
    cursor.execute("""
    INSERT INTO dbo.rssfeeds(title,link, description)  
    VALUES (?,?,?)""",
    entry.title, entry.link, entry.description) 
    conn.commit()
8

in order to access the data the client provided two parameters (dateofservice which is the current date in format mm/dd/yyyy. and also another parameter TYPE)

I could do something like
today= date.today()
dateofservice = today.strftime("%m/%d/%Y")

PARAMETER = {'TYPE':'Employee','dateofservice':dateofservice' }.

how do i incorporated this within the example yu provide. Thanks again for yur help

9

these parameters are provided to SQL Server or the web api?

10

To the web api

11

I will let you yourself do that research. Google is your friend