Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge of this article "how to use API to operate the database", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to use API to operate the database" article.

The artifact appears.

Today's protagonist is sandman2.

You can automatically generate a RESTful API server based on an existing database without writing any code, which, in the author's words, is simply like adding salt to food.

What's more, from simple SQLite databases to large business data PostgreSQL, it can be perfectly supported without having to write a single line of code.

Currently supported databases:

MySQL

PostgreSQL

Oracle

Microsoft SQL Server

SQLite

Sybase

Drizzle

Firebird

This reminds me of the days when I was in a hurry because I couldn't find a suitable database framework. I wish I had known sandman2 earlier.

It is called sandman2 because its predecessor, sandman,sandman, already has a strong ability to support databases. However, in version 0.9 of SQLAlchemy, automap function is added, which can further simplify sandman, so sandman is rewritten and sandman2 is available, and the function of sandman2 is much better than that of sandman.

Install pip install sandman2 using pip

After the installation is successful, you will get a sandman2ctl command line tool that will be used to start a RESTful API server

Instead of writing a single line of code, start:

Sandman2ctl sqlite+pysqlite:///data.db

Note: if you use python version 3.8or above, and on Windows, you may encounter the error of AttributeError: module 'time' has no attribute' clock'. This is because the clock property of the time module has been replaced with the perf_counter () method after 3.8. so you need to modify line 331 of lib\ site-packages\ sqlalchemy\ util\ compat.py and change time_func = time.clock to time_func = time.perf_counter () to save it.

After startup, the default port is 5000 and the access address is http://localhost:5000/admin to see the server console.

Database connection

You've seen a way to connect SQLite data earlier.

Sandman2 is based on SQLAlchemy, so use connection Url to connect to the database

The format is

Dialect+driver://username:password@host:port/database

Dialect is a database type, such as mysql, SQLite, etc.

Driver is the name of the database driver module, such as pymysql, psycopg2, mysqldb, etc. If omitted, the default driver is used

Take the mysql database as an example:

Sandman2ctl 'mysql+pymysql://bob:bobpasswd@localhost:3306/testdb'

If the pymysql module is not installed in the environment, it must be installed before it can start properly

For more information on how to connect to other databases, please refer to the engine configuration section of SQLAlchemy. Check docs.sqlalchemy.org/en/13/core/engines.html here.

Console

The console is very useful if you need to preview the data quickly and make simple adjustments to the data.

The menu on the left is the name of the library table except Home.

Click the corresponding database table name, the data in the table will be displayed on the right, and you can add, delete and modify it.

Click add to open the new page:

Students who have used Django will feel very familiar, but the field has no type support, so it can only be entered as a string to ensure that the data type is correct, otherwise you will receive an error message when saving.

Click the pen icon in front of the record and you will go to the editing page.

Click the delete icon before the record to delete the record

In addition, after you select more data, you can delete it in batches through the Delete button under the With selected menu.

The console is easy to use and suitable for some simple operations with small amount of data.

Note: since the console cannot be accessed by login, it is recommended that the server be created in a local or intranet environment

API

From RESTful's point of view, a library table is equivalent to a resource (resource), and a set of resources is equivalent to a collection (collection).

Query

Through the Http GET method, the data is returned in JSON format, such as all records of the student table student:

$curl http://localhost:5000/student/{"resources":[{"age":18,"class":"1","id":1,"name":"\u5f20\u4e09","profile":"\u64c5\u957f\u5b66\u4e60"},...

Note: resources should end with /

Paging by the parameter page, such as returning the first page of the student table student

$curl http://localhost:5000/student/?page=1{"resources":[{"age":18,"class":"1"...

Display the number of rows returned through the parameter limit

If you want to get a specific record, you can use the primary key value as a section, such as a student record with an id of 3.

$curl http://localhost:5000/student/3{"age":18,"class":"2","id":3,"name":"\u738b\u4e94","profile":"\u7231\u7f16\u7a0b"}

Take the field name as a parameter, which is equivalent to the query condition, for example, query the student record whose name is Tom:

$curl http://localhost:5000/student/?name=Tom{"resources":[{"age":19,"class":"1","id":7,"name":"Tom","profile":"Handsome"}]}

Query conditions can be combined, for example, query class 1 students aged 18:

$curl http://localhost:5000/student/?class=1&age=19{"resources":[{"age":19,"class":"1","id":2,"name":"\u674e\u56db","profile":"\u559c\u6b22\u7bee\u7403"},{"age":19,"class":"1","id":7,"name":"Tom","profile":"Handsome"}]} modification

The POST method is used to add new content, which is provided by the data part of the request, such as adding a student information:

$curl-X POST-d'{"name": "Lily", "age": 17, "class": 1, "profile": "Likely"}'- H "Content-Type:application/json" http://127.0.0.1:5000/student/{"age":17,"class":"1","id":8,"name":"Lily","profile":"Likely"}

Note: the primary key of the database table is self-growing, you can ignore the primary key field, otherwise you must provide

The PATCH method is used to update, update content, provided by the requested data part, such as changing the class of students whose id is 1 to 3

Note: when updating, primary key information is provided through the primary key value section of url, not in the data section

$curl-X PATCH-d'{"class": 3}'- H "Content-Type:application/json" http://127.0.0.1:5000/student/1{"age":18,"class":"3","id":1,"name":"\u5f20\u4e09","profile":"\u64c5\u957f\u5b66\u4e60"}

The DELETE method due to deletion, such as deleting a student record with an id of 8:

$curl-X DELETE-H "Content-Type: application/json" http://127.0.0.1:5000/student/8 other interfaces

Get the field definition information of the table through the meta section, for example, get the field definition of the student table student:

$curl http://127.0.0.1:5000/student/meta{"age":"INTEGER(11)","class":"VARCHAR(255)","id":"INTEGER(11) (required) "," name ":" VARCHAR "," profile ":" VARCHAR (500) "}

Export data and obtain it through the query field export. The data format is csv. For example, export student data and store it in a student.csv file:

$curl-o student.csv http://127.0.0.1:5000/student/?export% Total% Received% Xferd Average SpeedTime Time Time Current Dload Upload Total Spent Left Speed100 202 100 202 00 2525 0 -: 2525

There are more interfaces for you to explore.

Deployment servic

Sandman2's server is based on Flask, which can be referenced on the official website. I won't repeat it here.

The above is about the content of this article on "how to use API to operate the database". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.