How To Save Data From Api In Database

Hands-on Tutorials

How to Generate Information Accessing API Endpoints Without Fifty-fifty Coding

An Introduction of Python library — sandman2

Every bit the most common compages of whatever Web or Mobile applications, database + backend API + frontend is very typical and classic. While dissimilar applications volition definitely accept their own front-end blueprint, the backend API is usually very similar. That is, they must be able to access the data in the database. In other words, the major responsibility of the backend API is acting as a middleware to receive the requests from the front-end and remember the database based on the requests, and and then render the results to the front-terminate so that the users can eat.

Of course, for most of the applications, the backend API also needs a certain level of customising, specially the security requirement such equally JWT and and so on. However, when in that location is not too much security concern, there is a fashion to "generate" your backend API that tin access your database without even coding.

In this article, I'll introduce a Python library can do this for you. Imagine that you are a front-stop developer, yous only desire to test some front-end features in your testing environment and don't want to waste as well much fourth dimension on writing your own backend API. In this example, the Python library sandman2 is one of the all-time choices!

Preparation of Sample Database

The sandman2 library supports many database management systems, such as:

SQLite
MySQL
PostgreSQL
Oracle
MS SQL Server
Sybase
Drizzle
Firebird

The sandman2 library supports them every bit different SQL "dialect", which means that we don't need to worry about which database nosotros're using, merely tell sandman2 which database it is and it will work out-of-box.

In this commodity, I will use SQLite as an example because information technology is the easiest one, which I don't demand to download, install and configure anything.

In 1 of my previous article, I take introduced a Python built-in library sqlite3 that tin can generate an SQLite database very easily. I will skip introducing the SQLite library in Python. You lot can check out my article below for details.

Let's create a database called "my-test".

          import sqlite3 as sl          con = sl.connect('sandman2/my-test.db')

Then, create a table called USER for demonstrating purposes.

          with con:
            con.execute("""
            CREATE Table USER (
            id INTEGER NOT NULL Main Fundamental AUTOINCREMENT,
            name TEXT,
            age INTEGER
            );
            """)

After that, let's insert some sample rows.

          sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
            (1, 'Alice', 21),
            (2, 'Bob', 22),
            (3, 'Chris', 23)
]          with con:
            con.executemany(sql, data)

OK. At present, we tin query the table.

          with con:
            data = con.execute("SELECT * FROM USER")
            for row in data:
            print(row)          # Output:
# (1, 'Alice', 21)
# (2, 'Bob', 22)
# (3, 'Chris', 23)

We have got a database with a populated tabular array now. Let's get-go to demo the sandman2 library in the adjacent department.

Spider web-based SQL Client

Before everything, we need to install the sandman2 library. Just using pip.

          pip install sandman2

Then, every bit I stated in the title, we don't need to write Whatever lawmaking, just using the command-line interface.

          $ sandman2ctl sqlite+pysqlite:///sandman2/my-test.db

The illustration below shows how the connectedness string is constructed.

Please be noticed that the DB driver is optional. If we keep it empty, SQLAlchemy (sandman2 is built on peak of SQLAlchemy) will effort to employ the default driver. Yet, it might mutter if the commuter does not exist. Don't worry, we can get the name of the default driver library then that we can using pip to install information technology.

Afterwards running the command, the spider web services started.

Let'south try the web client using any browser to access the admin panel.

          http://localhost:5000/admin/

We can see that in that location is already an object USER on the folio. That is because we have created this table. If we click USER, nosotros can see that the three rows nosotros inserted previously are displaying in a data table.

On this page, we can click the "Create" button to insert a new row, update an existing row or bulk deleting rows. You volition be able to find these features easily, so permit me skip those things and evidence yous the most important feature in the side by side section.

Data Accessing RESTful API

The coolest characteristic of sandman2 must be the automatically generated RESTful API endpoints of the database we take connected to.

In the above section, keep the web service running, then the API is already there for u.s.a. to use. You may prefer API testing tools such equally Postman or Insomnia, just in this article, I'll just utilize "ringlet" command to proceed information technology unproblematic, and so you don't take to download anything if you just want to test the API endpoints.

ane. Become a list of data entries in a table

Remember we accept a table chosen USER? Now, we can only send Become request to the following URL to get a list of entries.

          http://localhost:5000/user/

Please be noticed that

About of the DBMS such equally SQLite and MySQL are non example sensitive, then we tin can use lower case for the table names safely.
The forwards slash / at the cease of the URL must not be missed.

Then, let's test the GET API using scroll.

          curl http://localhost:5000/user/

It returned all records in the table. However, what if we accept too many entries in a table and want to do the pagination? Yes, sandman2 supports that out-of-box!

          coil "http://localhost:5000/user/?page=ane&limit=ii"
curl "http://localhost:5000/user/?page=two&limit=2"

In this case, nosotros use folio parameter to specify the page number and the limit parameter to specify the page size. In folio 1, we got the first ii users Alice and Bob. Then, on folio 2, we got the tertiary row for Chris and just one row returned because there are no more rows.

2. Get rows past key

Since we divers the master central, we can besides query the table by information technology.

          roll http://localhost:5000/user/3

three. Filter rows past any fields

We can as well filter the rows past any fields.

          ringlet "http://localhost:5000/user/?name=Chris"

Currently, there is a limitation that filter on comparison >, <, >=, <= is non supported.

four. Insert a new row

To insert a new row, we need to apply the Mail service method.

          whorl -X Mail -d "{\"age\":24, \"name\":\"David\"}" -H "Content-Type: awarding/json" http://localhost:5000/user/

Please be noticed that I'm using Windows so that the single quote won't work in the cmd and I have to use backwards slashes to escape the double-quotes. You may not need those if you're using Linux or Mac Os.

Also, we have prepare the "id" field as auto-increment when we create the USER table, so we don't need to pass in the "id" values.

Nosotros tin can non go to the Web-based SQL client to bank check the tabular array. The new user David is successfully inserted.

five. Updating a row

Nosotros can use the PATCH method to update a row.

          whorl -X PATCH -d "{\"age\":30}" -H "Content-Type: application/json" http://localhost:5000/user/3

Every bit shown in the example, it is important to pass the current main key "id" in the URL, which is "three" in our case. Sandman2 relies on the primary primal to find the record to be updated.

If we get back to the web client, it tin can be seen that the historic period of Chris has been updated.

half-dozen. Deleting a row

We can use the DELETE method to delete a row.

          curl -X DELETE -H "Content-Type: application/json" http://localhost:5000/user/3

After running the to a higher place CURL command, the user with "id=3" has been deleted.

7. Definition of a table

The sandman2 library supports not only the general data accessing API but also some other avant-garde API, such as getting the metadata of a tabular array.

          scroll http://localhost:5000/user/meta

8. Export table to a CSV file

Finally, it also supports exporting data to a CSV file.

          scroll -o user.csv "http://localhost:5000/user/?export"

Summary

In this commodity, I take introduced an astonishing Python library called sandman2. It is written in Python but you don't demand to write ANY Python code to use it. By just running it with a valid database connection string, you volition become

A Web-based SQL customer
An entire ready of data accessing RESTful API

Information technology turns out that there is non also much security implementation in sandman2, so please never use it in product. However, security concerns usually hateful customised implementation which is nearly impossible to exist generalised in an out-of-box toolset. Therefore, sandmand2 is one of the excellent libraries that can salve usa a lot of time in testing and experimenting.

If yous feel my manufactures are helpful, please consider joining Medium Membership to support me and thousands of other writers! (Click the link above)