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

This article mainly shows you "how to use SanicDB tools", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use SanicDB tools" this article.

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): example

There are two examples in the code repository:

Test.py is an example of using asynchronous MySQL in ordinary asyncio programs

Example.py is an example of use in Sanic Web

In particular, the event_loop object has not been generated in the Sanic object app (it is generated after app.run ()), so running the sql statement will report an error before initializing SanicDB in Sanic (app.run ()). If you need to initialize and run some SQL operations after creating a connection, you can take advantage of Sanic's Listner:

@ app.listener ('before_server_start') async def setup_db (app, loop): app.db = await db_setup () these are all the contents of the article "how to use SanicDB tools". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.