Access to data in SQL Server from the web! a little bit of ...Javascript, Python and SQL Server

Hi guys,

Welcome back mates to another atypical post!

 

In the latest post we talk about how to access data in Microsoft SQL Server from the Python language: Python from 0 to Microsoft SQL Server! A tutorial to try step to step

 

Today we do a little step forward.

We will see how to access our data in SQL Server directly from a web pages.

 

Why?

 

Well, for many reason.

Mainly because I wanted to show a quick way to read and write the data that is inside a database.
I also wanted to show that beautiful interfaces makes the web available.
Finally I wanted to show you the basics of the Javascript language and also how to create an API from python.

I mean, a lot of stuff, so let’s get started!

Intro.

Yes, this post need an introduction to descrive what we will do.

In the latest post the have installed Python and with this language we have accessed data from a database in SQL Server.

Today we slightly modify this project: the already written function will become an API accessible from the web.

Finally, a project in Javascript will call this API and show the data read.

Today we limit ourselves to read a data from the database, obviously in a similar way it will be possible to create all the functions to insert, modify or delete the data in the database.

 

 

Python: Creating an API

 

To create an API in Python we need FastAPI, a Web framework for developing RESTful APIs in Python.

 

To Install FastAPI simply execute in the command line the following command:

pip install fastapi

Then import in you script:

 

FastAPI can manage the CORS (Cross-origin resource share) mechanism thanks to CORS middleware you can find at fastapi.middleware.cors.

 

Simply import it:

 
 Then configure valid origins and methods.

I’m not interested in using CORS for this I put in the origins array the values "*".

Same goes for the allow_method and allow_header.

Now we start to see how to create our own API!

Use the @app.get method.

As parameter put the function name: I put my-first-api.

Then define a function that will be called.

The function will connect to the database using the pyodbc library by setting a connection string.

In my case the connection string point to the SQL Server database EMPTY.

The SQL command executed will retrieve the number of the rows of the table ORDTES.

 

The API is done.

 

 

Running an API with Uvicorn

 

Uvicorn is an ASGI web server implementation for Python.

 

To install execute the following command:

pip install uvicorn

Once installed simply execute the following command:

uvicorn script:app

 

Now your API is running but let's do the first test by calling the API from a browser!

 

Try the API in a browser

For the first try, open a web browser and type the http address:

127.0.0.1:8000/my-first-api

....and press return

Wow! the browser display a value!

The number "1" is the number of rows in the table ORDTES.

Javascript! Call the APIs from the Web!

 

What we will do is to create a web page and from this web page we will call our API.

I will use Javascript and i will display the same information we already get from the browser:

 

On of the unwritten rules of computer science says that all modern software must have a pleasant and responsive interface.

Showing data from a web page allows us to get all this for free.

Our interface (front end) displayed on a mobile phone screen:

But now let’s get to work!

Open an index.js file with visual studio code then define a function UserAction()

This function will call our API "my-first-api"

We will use an object called XMLHttpRequest that transfer data between a web browser and a web server

Basically you have to

Call the OPEN method to execute a GET on your address (http://127.0.0.1.8000/my-first-api)

Set some properties on the requestheader through the SETREQUESTHEADER method:

(mainly to avoid problems with the CORS)

Call the SEND method to start the call to your API

In this very simple case we are not sending any data to the API.

We only read the response of the API.

To read the response we use a callback technique: We define a function that will be called on the ONREADYSTATECHANGE event.

Basically when the API return the result invoke the event ONREADYSTATECHANGE and so call our function.

The function read the result from the this.responsetext variable...

..end put the value on the web page!

That'all for today mates!

>>If you find an advertisement that genuinely interests you simply click it from on my blog, please. You will help me to contine writing more and more interesting content. ~Luke

Previous post: Python from 0 to Microsoft SQL Server! A tutorial to try step to step

 

 

I recently bought the hardware to read directly the signal from the floppy disk drive head.I recovered all the floppies I had that were no longer readable. 

If you have one or more disks that you care about and want to try to recover send me a message or an email (lucadrbiondi@gmail.com).

... It’s not a job but a hobby