Today the project is capable of creating a simple back-end service that automatically provides restful API CURD services by creating a new table in mysql.
The key point
- Dynamically invoke the corresponding CURD method based on the four REST verb forms;
- Write the intermediate layer (baseDao) between REST and the basic database access class to realize the business logic processing between REST and data access interface;
- Write the basic database access class (DeHelper) to achieve the conversion from the dictionary form of parameters to SQL statements;
Implementation of the rest API
The following form of REST-API is implemented
[GET]/rs/users/{id} [GET]/rs/users/key1/value1/key2/value2/... /keyn/valuen [POST]/rs/users [PUT]/rs/users/{id} [DELETE]/rs/users/{id}Copy the codeBasic database access classes
This class interconnects with pymysql library and provides standard CURD interface.
Preparing database tables
Create the Users table in the database. The script is as follows:
CREATE TABLE `users` ( `_id` int(11) NOT NULL AUTO_INCREMENT, 'name' varchar(32) CHARACTER SET UTf8MB4 DEFAULT COMMENT ', 'phone' varchar(1024) DEFAULT ', 'address' varchar(1024) DEFAULT NULL, 'status' tinyint(4) DEFAULT '1' COMMENT' 1 - effectively; 9 delete ', 'create_time' datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time ', PRIMARY KEY (' _id'), Uuid (' id ') USING BTREE ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='表';Copy the codeCreating a database configuration file (configs.json)
Data connection configuration, not in the version library.
{
"db_config": {
"db_host": "ip",
"db_port": 1234,
"db_username": "root",
"db_password": "******",
"db_database": "name",
"db_charset": "utf8mb4"
}
}Copy the codeConnect to the Pymysql interface
Pymysql is encapsulated with function exec_SQL to provide a unified interface to access mysql. The is_Query function is used to distinguish between querying (R) and performing (CUD) operations. The error handling took a long time, and the error form returned by the insert exception was not the same as the others! The return argument is a triplet (successful execution, query result or error object, number of query result or affected rows)
with open("./configs.json", 'r', encoding='utf-8') as json_file: dbconf = json.load(json_file)['db_config'] def exec_sql(sql, values, is_query=False): try: Error = {} # Conn = pymysql.connect(host=dbconf['db_host'], port=dbconf['db_port'], user=dbconf['db_username'], passwd=dbconf['db_password'], db=dbconf['db_database'], charset=dbconf['db_charset']) with conn.cursor(pymysql.cursors.DictCursor) as cursor: Num = cursor.execute(SQL, values) $is_query: $cursor.fetchall() else: MIT () print('Sql: ', Sql, 'Values: ', Values) except Exception as err: flag = True error = err print('Error: ', err) finally: conn.close() if flag: return False, error, num if 'num' in dir() else 0 return True, result if 'result' in dir() else '', numCopy the codeQuery interface
Pymysql’s query interface can accept arrays, tuples and dictionaries. This query interface uses array form to call. This interface now supports only combining parameters with conditions.
def select(tablename, params={}, fields=[]): sql = "select %s from %s " % ('*' if len(fields) == 0 else ','.join(fields), Tablename) ks = params.keys() where = "ps = [] PVS = [] if len(ks) > 0: ps.append(al + " =%s ") pvs.append(params[al]) where += ' where ' + ' and '.join(ps) rs = exec_sql(sql+where, pvs, True) print('Result: ', rs) if rs[0]: return {"code": 200, "rows": rs[1], "total": rs[2]} else: return {"code": rs[1].args[0], "error": rs[1].args[1], "total": rs[2]}Copy the codeInsert the interface
Arguments are provided as arrays, and error message parsing differs from other interfaces.
def insert(tablename, params={}): SQL = "insert into % s" % tablename ks = params. The keys () SQL + = "(`" + "`, `" join (ks) + "`)" v = # field combination List (params.values()) # SQL += "values (%s)" % ','. Join (['%s']*len(vs)) # configure the appropriate placeholder rs = exec_SQL (SQL, vs) if rs[0]: return {"code": 200, "info": "create success.", "total": rs[2]} else: return {"code": 204, "error": rs[1].args[0], "total": rs[2]}Copy the codeModify the interface
The parameter is provided in the form of a dictionary, in the form of a placeholder: % (keyname) s. You can only change the parameter by pressing the primary key.
def update(tablename, params={}): sql = "update %s set " % tablename ks = params.keys() for al in ks: # field with a placeholder splicing SQL + = "`" + al + "` = % (" + al +)" s," SQL = exec_sql [: 1] # exec_sql [: 1] # exec_sql [: 1] # exec_sql [: 1] # exec_sql [: 1] # exec_sql [: 1] # exec_sql [: 1] # exec_sql [: 1] return {"code": 200, "info": "update success.", "total": rs[2]} else: return {"code": rs[1].args[0], "error": rs[1].args[1], "total": rs[2]}Copy the codeDelete the interface
The parameter is provided in the form of a dictionary and is in the form of a placeholder: % (keyname) s. You can only delete the parameter by pressing the primary key.
def delete(tablename, params={}):
sql = "delete from %s " % tablename
sql += " where _id = %(_id)s "
rs = exec_sql(sql, params)
if rs[0]:
return {"code": 200, "info": "delete success.", "total": rs[2]}
else:
return {"code": rs[1].args[0], "error": rs[1].args[1], "total": rs[2]}Copy the codeMiddle layer
A default operation database interface is provided to implement the underlying business logic, which is sufficient for a single-table CURD. When you have complex business logic, just inherit it and extend it.
import dbhelper class BaseDao(object): def __init__(self, table): self.table = table def retrieve(self, params={}, fields=[], session={}): return dbhelper.select(self.table, params) def create(self, params={}, fields=[], session={}): If '_id' in params and len(params) < 2 or '_id' not in params and len(params) < 1: 301, "err": "The params is error."} return dbhelper.insert(self.table, params) def update(self, params={}, fields=[], session={}): If '_id' not in params or len(params) < 2: #_id must be provided with at least one change return {"code": 301, "err": "The params is error."} return dbhelper.update(self.table, params) def delete(self, params={}, fields=[], session={}): If '_id' not in params: #_id must provide return {"code": 301, "err": "The params is error."} return dbhelper.delete(self.table, params)Copy the codeCall CURD dynamically
Depending on the rest method invoked by the client, it is critical to dynamically call the corresponding method of baseDao to automatically allocate method calls and to automatically provide basic access to CURD simply by creating a data table. Fortunately, dynamic languages can do this very easily, and node.js is much more convenient and customary
method = {
"GET": "retrieve",
"POST": "create",
"PUT": "update",
"DELETE": "delete"
}
getattr(BaseDao(table), method[request.method])(params, [], {})Copy the codeDescription:
- Table is the name of the table parsed in the previous chapter.
- Method is a constant object, which corresponds to the rest verb. Ypthon is not familiar with it, so I define a variable to use first.
- Request. method The actual REST verb of the client’s request;
- Params are the parameter objects parsed out in the previous chapter;
The complete code
git clone https://github.com/zhoutk/pyrest.git
cd pyrest
export FLASK_APP=index.py
flask runCopy the codesummary
At this point, we have implemented the basic framework functionality, and now it is time to enrich its wings. For example: session, file upload, cross-domain, routing improvement (support seamless switching operation database base class and subclass), parameter verification, basic query function enhancement (paging, sorting, fuzzy matching, etc.). I miss the way json objects are written in Node.js without quotes around keys.
The patch
Just changed the parameter form of the insert method in the basic database access class to a dictionary, the result exception information is also normal, the article is not changed, interested please consult the source code.