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

This article mainly explains "what is the function of Python's SanicDB". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the role of Python's SanicDB?"

SanicDB is a tool developed for Python's asynchronous Web framework Sanic to facilitate the operation of MySQL, and it is a lightweight encapsulation of aiomysql.Pool. Sanic is the Web framework of asynchronous IO. It is more efficient to read and write MySQL with asynchronous IO at the same time. Although this module is called SanicDB, it can be used anywhere asynchronous IO manipulates MySQL.

SanicDB was inspired by the operation of MySQLdb (Python encapsulation of MySQL's C language interface) in tornado. Later, when tornado removed it, someone wrote this part of the code as a separate module called torndb,torndb, which encapsulates python-mysql and cannot operate asynchronously. But it is very simple and very used to it.

When using Sanic, I found that aiomysql for asynchronous IO is available, but it is still a bit troublesome to use, so I started to encapsulate aiomysql. Since aiomysql supports connection pooling, encapsulate aiomysql.Pool directly.

First, take a look at its initialization.

Class SanicDB: "A lightweight wrapper around aiomysql.Pool for easy to use"def _ _ init__ (self, host, database, user, password, loop=None, sanic=None, minsize=3, maxsize=5, return_dict=True, pool_recycle=7*3600, autocommit=True, charset =" utf8mb4 ", * * kwargs):''

The first four parameters correspond to the database

Loop is the event loop (event_loop) in your application.

Sanic is the sanic object in your Sanic application

Loop and sanic only need to provide one. When they exist at the same time, the utility of connecting to the database is the loop in sanic, as can be seen in the _ _ init__ () code.

Minisize and maxsize are the limit on the number of connection pools

Return_dict is the returned data. A record as a dict,key is the field name of the MySQL table, and value is the value of the field.

Pool_recycle is the time interval for connection pool reconnection. The default connection idle time for MySQL is 8 hours.

How to read and write to MySQL:

Async def query (self, query, * parameters, * * kwparameters):

Async def execute (self, query, * parameters, * * kwparameters):

Async def get (self, query, * parameters, * * kwparameters):

You can see from the code that the above three functions are all called by execute () of cursor to execute the SQL command, but query (), get () is used for reading operations to return data, query returns all data (list), and get returns only one piece of data.

The execute () method is used to perform the write operation and returns the affected line ID.

The above three functions are all the same:

Query is the SQL statement to be executed, and the last two parameters are used to parameterize the execution of the sql statement. Parameterized sql is performed to prevent SQL injection, so what does parameterized SQL mean? for example, it is the goal.

For example, when a user logs in, we need to execute a SQL,Python stitching SQL as follows:

Sql = "select * from user where name='%s' and password='%s'"% (input_name, input_password)

Both input_name and input_password are entered by the user. If the user wants to do something wrong, he enters the input_name as "'or 1 = 1 -", and the spliced sql becomes:

Select * from user where name='' or 1 = 1-'and password='anything'

After or 1 is True, and-is followed by a comment. This statement can be executed no matter what input_name or input_password is, and has been verified by the user to achieve the purpose of injection.

Parameterized execution of SQL means handing over the task of stitching SQL to SanicDB (actually the pymysql after the aiomysql after it). We can only pass the parameters to be stitched as * parameters to the read / write method:

Mydb.query ("select * from user where user=%s and password=%s", input_name, input_password)

More advanced encapsulation:

Query (), get (), execute () are the basic operations. On this basis, further encapsulation is made to make it more convenient to write applications:

# check whether a table contains a record whose field is a certain value async def table_has (self, table_name, field, value): # insert an item into a table, item is the field name, and # ignore_duplicated is True, do not report error async def table_insert (self, table_name, item, ignore_duplicated=True) when the unique index is repeated: # Update a table record, updates is dict Key is the field name async def table_update (self, table_name, updates, field_where, value_where): at this point, I believe you have a deeper understanding of "what the SanicDB of Python does". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.