Use python to solve the import and export dependency of mysql view

view

A view is a virtual (non-real) table that essentially gets a dynamic data set according to the SQL statement and gives it a name. Users can use the name to get the result set and use it as a table.

Create a view

Create a view named v1 that queries all the data in the color table

CREATE VIEW v1 AS SELECT * FROM color;

Look at the view

When using a view, it can be manipulated as a table. Since a view is a virtual table, it cannot be used to create, update, or delete real tables. It can only be used for querying.

select * from v1; --  Equal to perform SELECT * FROM color

The output

+-----+--------+
| nid | title |
+-----+--------+
| 1 | red |
| 2 | yellow |
+-----+--------+
2 rows in set (0.00 sec)

Modify the view

ALTER VIEW v1 AS SELECT * FROM color WHERE nid = 1;

Delete the view

DROP VIEW v1;

reference

navicat is the best of the mysql visualizations, but when it comes to handling the import and export of views, it handles them alphabetically by view name, and if the view has dependencies, an error is reported during the import process. This problem 1 stuck with me, 1 degree bypassed because I used docker to deploy mysql. Recently I had to face this problem head-on, so I wrote a gadget to solve it.

The overall train of thought

It is easy to find all views and their definitions on mysql, so you can write a view export tool, adjust the location of each view when storing, handle the dependencies between them, and put the dependencies first, thus solving the dependency problem when importing.

Get view information

Run the following query to get information about all the views in the database.

select * from information_schema.VIEWS where TABLE_SCHEMA = DatabaseName

Query result field description:

TABLE_NAME: View name in several libraries VIEW_DEFINITION: The definition code for the view, with only the query part DEFINER: Name of the view definition (creator) SECURITY: Security level

In summary, all the view information is stored in this table, and I only need TABLE_NAME and VIEW_DEFINITION to complete the task.

Algorithm description

Place the query results in dict, with the view name key; The view is defined as value; Write the function process_rely to handle the dependency. The input parameter rely_old is an array to hold all the view names. Returns an array of view names after the arguments are adjusted by dependency. And the reason why I did this is because I started thinking about the fact that if you have complex dependencies, you might not get through one iteration, you might have to make a recursive call or multiple calls.

Description of process_rely function algorithm:

Layer 1 loops, taking a view name from rely_old

Layer 2 loops, extracting 1 key from dict

If the key value is dependent on the definition of a layer 1 element

If the key value is not already in the result array

If the level 1 element is not in the result array

Appends the key value to the result array

Layer 1 elements are in the resulting array

Inserts the key value in front of a tier 1 element

The key values are in the result array

Layer 1 elements are in the resulting array

Find each position in the result array

If the level 1 element is after the key value

Moves the key value in front of the tier 1 element

When the second layer loop ends, if the first layer element is not in the result set

Appends layer 1 elements to the result set

Return result set

The above instructions are given in python code mode. Fortunately, the algorithm can handle complex dependencies once. In the writing process, I was able to deal with complex dependencies through multiple iterations when the dependency algorithm was not perfect at the beginning. Therefore, the firm confidence to win, to complete the task.

The complete code

import pymysql
conn = pymysql.connect(host='172.17.0.1', port=3306, user='root',
      passwd='123456', db='database', charset='utf8mb4')
def process_rely(parmas={}, rely_old=[]):
 _rely = []
 _keys = list(parmas.keys())
 for k in rely_old:
  for bl in _keys:
   if str(parmas[k]).find(bl) > -1:
    if bl not in _rely:
     if k not in _rely:
      _rely.append(bl)
     else:
      i = _rely.index(k)
      _rely.insert(i, bl)
    else:
     if k in _rely:
      i = _rely.index(k)
      j = _rely.index(bl)
      if i < j:
       del _rely[j]
       _rely.insert(i, bl)
  if k not in _rely:
   _rely.append(k)
 return _rely
cur = conn.cursor()
cur.execute('select TABLE_NAME, VIEW_DEFINITION from information_schema.VIEWS where TABLE_SCHEMA = %s ', 'database')
rs = cur.fetchall()
cur.close()
conn.close()
ps = {}
for al in rs:
 ps['`' + al[0] + '`'] = al[1]
rely = process_rely(ps, list(ps.keys()))
# rely = process_rely(ps, rely1)
file_object = open('view.sql', 'w')
for al in rely:
 file_object.write('DROP VIEW IF EXISTS ' + al + ';\n')
 file_object.write('CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ' + al +
      ' AS ' + ps[al] + ';\n\n')
file_object.close()

summary

Think clearly, code to 1 step closer to the final goal, a small step to a thousand miles. When making this tool, 1 felt very troublesome at the beginning. If the dependency relationship was deep, it might not be handled well once. Because of the idea of iteration, the perfect ending of solving the problem in the first iteration was finally completed.