No code is required, and the database is operated using an API

preface

For programmers, databases are accessive, but they are not simple to operate, using several management tools, several ways to connect. H ow nice it would be if we had a tool that works with a variety of databases and allowed us to save a lot of work. After a lot of hard work, I finally found such a tool, not only support a variety of databases, but also, more powerfully, do not have to write a line of code for adaptation, to understand it.

The artifact came out

Today's main character is sandman2

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

What's more, from a simple SQLite database to a large commercial data PostgreSQL it's perfectly supported without writing a line of code

Databases currently supported:

• MySQL
• PostgreSQL
• Oracle
• Microsoft SQL Server
• SQLite
• Sybase
• Drizzle
• Firebird

It reminds me of the days when I couldn't find the right database framework, and it would have been nice to know sandman2 earlier

It's called sandman2 because its predecessor was sandman, and Sandman already has strong database support capabilities, but in SQLAlchemy 0.9, automap was added to further simplify sandman, so it was rewritten to have sandman2, and sandman2 was much more powerful than sandman

Use pip to install pip install sandman2

Once installed successfully, you get a sandman2ctl command-line tool that you can use to start a RESTful API server

Instead of writing a line of code, start directly:

sandman2ctl sqlite+pysqlite:///data.db

Note: If you are using a python version that is 3.8 and above, and on Windows, you may experience AttributeError: module 'time' has no attribute 'clock' error This is because after 3.8 the clock property of the time module is replaced with perf_counter() method, so you need to modify the 331 lines of the next lib\site-packages\sqlalchemy\util\compat.py to replace the time_func = time.clock with time_func = time.perf_counter() Just save it

Once started, the default port is 5000, and the access address is http://localhost:5000/admin you can see the server console

The database connection

You've seen ways to connect SQLite data earlier

Sandman2 is SQLAlchemy so use a 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, and so on
• Driver is a database-driven module name, such as pymysql, psycopg2, mysqldb, and so on, which, if ignored, means that the default driver is used

Take the mysql database for 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

Other databases can be connected by referring to SQLAlchemy's 引擎配置 section, where you can view the docs.sqlalchemy.org/en/13/core/engines.html

Console

Consoles are useful if you need to preview your data quickly and make simple adjustments to it

The menu on the left is a library table name except Home

Clicking on the name of the library table displays the data in the table on the right, and you can make additions and deletions

Click New to open the new page:

Students who have used Django will feel familiar, but the fields are not type-supported and can only be entered as strings to ensure that the data type is correct, otherwise they will receive an error message when saving

Clicking on the pen icon in front of the record takes you to the edit page

Click the delete icon before the record to delete the record

Once the data is With selected it can be deleted in bulk using the Delete button under the Selected menu

The console is easy to use and suitable for simple, low-volume operations

Note: Because the console cannot be accessed by logging on, it is recommended that you create the server in a local or intranet environment

API

From a RESTful perspective, a library table is collection equivalent to 资源 resource a set of 集合 is equivalent to a collection )

The following tests are conducted using curl tool, and can be used in detail by referring to The Curl's Guide to the Use of curl (www.ruanyifeng.com/blog/2019/09/curl-reference.html)

Inquire

The Http GET method returns data in JSON format, such as all records of 学生表 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 /

Pagination by parameter page such as returning the first page of the 学生表 student data

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

The number of rows returned is displayed through the parameter limit

If you want to get a specific record, you can use the primary key value as a segment, 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"}

Using the field name as a parameter is equivalent to a query condition, for example, name as Tom's student record:

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

Query criteria can be combined, for example, to query students with a class of 1 age 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"}]}

revise

POST method is used for new, new content, provided by the requested data section, 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 library table primary key is self-growing and can ignore the primary key field, otherwise it must be provided

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

Note: When updated, primary key information is provided through the primary key value section of the 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"}

DELETE method due to deletion, such as deletion of student records with id 8:

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

Other interfaces

Get the field definition information for the table, obtained through the meta section segment, for example, get the field definition for the 学生表 student

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

Export the data, obtained by querying the field export in a csv format, such as exporting student data and storing it in a student .csv file:

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

There are many more interfaces to explore

Deploy the service

Sandman2's servers are Flask-based and can be referred to on the official website, not to be repeated here.

summary

sandman2 is easy to use because it combines a lot of applications and technologies, SQLAlchemy does ORM layer, Flask does RESTful server, Bootstrap does the foretop framework, and so on

While facilitating us, we demonstrate the power of the technology portfolio so that we no longer feel bored learning about small knowledge points

Source: Public Number -- Python Technology Author: Pieson Sauce

The above is W3Cschool编程狮 about no code, the use of API operation database related to the introduction, I hope to help you.