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.

Improve this question
2
  • It seems that sp_execute_external_script is likely the feature or SQL Server that'd most closely match this - learn.microsoft.com/en-us/sql/machine-learning/tutorials/… - perhaps along with ordinary database triggers?
    – Ben Cottrell
    Commented Jan 3, 2023 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
    Commented Feb 3, 2023 at 23:35

2 Answers 2

Reset to default
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.

Improve this answer
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
Improve this answer
6
  • 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
    Commented Jan 4, 2023 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
    Commented Jan 4, 2023 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
    Commented Jan 4, 2023 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
    Commented Jan 5, 2023 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
    Commented Jan 5, 2023 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.