0

I'm not sure if this is the correct area to ask this question, quite honestly, I'm not sure how to phrase the question because I don't know if what I'm thinking is possible.

I'm trying to figure out a better way to trigger third party API calls from an on Premise SQL Server database. Currently, there is a Python script that is ran, takes information from an the SQL server, depending on the Python conditions, will hit an API endpoint. This process allows the company that I work for to add, cancel, and update a person's information at a third party vendor via their API.

I manually run the Python script. I could put this script on job scheduler, like cronjobs. Is there a way to "listen" for a certain type of update in the SQL Server, trigger the Python scripts to run a certain API call? Is it possible to cut out the Python scripts all together and just hit the API depending on the type of update that is sent to the SQL Server? Is there a SSIS package that can be used to facilitate this?

I'm a one wo-man show at a small company.

hnewbie
  • 1
  • 4
  • It seems that `sp_execute_external_script` is likely the feature or SQL Server that'd most closely match this - https://learn.microsoft.com/en-us/sql/machine-learning/tutorials/quickstart-python-create-script?view=sql-server-ver16#run-a-simple-script - perhaps along with ordinary database triggers? – Ben Cottrell Jan 03 '23 at 19:33
  • Ensure that INSERT / UPDATE produces a timestamped row, with an index on that timestamp. Then have a python cron job poll with cheap "give me the most recent row" queries, and run the required job when it notices the result has changed. – J_H Feb 03 '23 at 23:35

2 Answers2

1

You can read the transaction and run actions accordingly: https://sqlfascination.com/2010/02/03/how-do-you-decode-a-simple-entry-in-the-transaction-log-part-1/

I have made similar approach with mysql by reading it's binlog stream which is pretty similar to sql server transactions log.

zfou
  • 111
  • 2
0

The best way is to:

  • find the thing that updates the SQL,
  • make sure that call goes through an API layer,
  • get the API layer to push an event to a queue,
  • have your scheduled task application subscribe to that queue
Ewan
  • 70,664
  • 5
  • 76
  • 161
  • Do I build the API layer that you're referring to? What would be the difference between what I have going on vs doing it your way? – hnewbie Jan 04 '23 at 16:33
  • its unclear because you dont say anything about the rest of the stack. You are concentrating on the database as the main thing, but a database should always be wrapped in an api layer. Because that layer is usual a bespoke program you have created, its easy and scalable to add features there. Whereas the database is restricted and hard to scale – Ewan Jan 04 '23 at 16:57
  • well, I'm trying to keep the question as precise as possible without rambling. These scripts were not written by me, I just inherited them. Learning from your answer, I would need an API around the SQL DB itself, which can be done in Python, turn the update into a call through the API, which in turns pushes an event into a queue? Then run the task scheduler every second to check if a new event has entered the queue? – hnewbie Jan 04 '23 at 17:49
  • kind of.. if you push to a queue you can have a program continually running and listening to the queue. then when a new message comes in it can pick it up instantly and run whatever – Ewan Jan 05 '23 at 10:49
  • I know you just want a "how do i trigger a python script from a db" answer, but doing this is generally considered bad practice. – Ewan Jan 05 '23 at 10:51
  • No I don't. I'm really trying to better/expand my knowledge and do things right at this company. The main reason why I reached out to to Stack Exchange is because people like you who have the knowledge and experience I can learn from. Thank you. – hnewbie Jan 05 '23 at 14:16