Python routine practice (1) -- batch sorting of Snippets of SQL Prompt, pythonsnippets

Python routine practice (1) -- batch sorting of Snippets of SQL Prompt, pythonsnippets

Introduction
I usually use the SQL Prompt plug-in when writing SQL scripts. In addition to the powerful smart Prompt and formatting SQL statement functions, I also like to use the Snippets code segment function. For example, we can check whether the analyzer inputs ssf and press the Tab key. SQL Prompt can help us quickly input SELECT * FROM.
However, I am not used to reading SQL code in upper case, so I want to change the code output from the code segment to lower case. Open the code segment management interface and find that the management tool provides the function of editing code segments. However, if you want to edit code segments one by one, convert them to lowercase letters and save them, it is obviously not your style. You can see the path where SQL Prompt stores the code segment:
Find the file in the path and open it. The code snippet file is an xml file with the extension sqlpromptsnippet.
So I want to use python to bulk convert the code in the code snippet file to lower case.
 
1. xml operation-find the Code node and obtain the SQL statement of the Code segment
1. For more information about node types in xml format, seeW3School tutorial
2. You can use the mxl. dom. minidom module to read and write xml files in python. The Code Node Code is as follows:
import xml.dom.minidomsnippet = xml.dom.minidom.parse('ssf.sqlpromptsnippet')root = snippet.documentElementprint(root.nodeType,root.nodeName,root.nodeValue)code = snippet.getElementsByTagName('Code')[0]print(code.nodeType,code.nodeName,code.nodeValue)
Snippet = xml. dom. minidom. parse ('ssf. sqlpromptsnippet '): indicates to open the path named 'ssf. sqlpromptsnippet, and assign the xml file object to the snippet object.
Root = snippet.doc umentElement: Get the document element (root node) of the snippet object and give the obtained object to the root.
Code = snippet. getElementsByTagName ('code') [0]: searches for all the child elements named Code under the root node, and assigns the first child element to the Code object.
Execution result:
1 CodeSnippets None1 Code None 
Because neither CodeSnippets nor Code node is a text node, all its nodeValue attributes are None. One Code NodeCDATASection Node, Which has the following attributes:
Therefore, the correct statement for finding the Code node and obtaining the SQL statement of the Code segment is as follows:
Import xml. dom. minidomsnippet = xml. dom. minidom. parse ('ssf. sqlpromptsnippet ') root = snippet.doc umentElement # print (root. nodeType, root. nodeName, root. nodeValue) code = snippet. getElementsByTagName ('code') [0] # print (Code. nodeType, code. nodeName, code. nodeValue) statement = code. firstChild. data # The 1st (and only) sub-elements of the code are the CDATASection nodes.
print (statement) 
Execution result:
SELECT * FROM
Ii. SQL code conversion operations-UPPERCASE to lowercase
1. SQL statements can be written in uppercase to lowercase. You can directly use the lower function of the str class:
statementlower = statement.lower()print (statementlower)
Execution result
select * from
2. Some code segments in SQL Prompt contain placeholders in the format of "$ CURSOR $" and are case-sensitive. Therefore, placeholders cannot be converted to lowercase letters. Therefore, you need to first find all the placeholders in an SQL statement in the code segment, store them, and replace them after the SQL statement is converted to lowercase.
Because placeholders start with "$" and end with "$", we can easily use regular expressions to find all placeholders in SQL statements. After finding the placeholder, store it in lowercase using dict.
Import xml. dom. minidomimport resnippet = xml. dom. minidom. parse ('ct. sqlpromptsnippet ') root = snippet.doc umentElement # print (root. nodeType, root. nodeName, root. nodeValue) code = snippet. getElementsByTagName ('code') [0] # print (Code. nodeType, code. nodeName, code. nodeValue) statement = code. firstChild. data # The first (and only) sub-element of the code is the print (statement) of the CDATASection node # output the original statement # RegEx searches for all placeholders keylist = re. findall ("\ $ \ w + \ $", statement) # store placeholders and their lower-case forms as dictionaries placeholds = dict () for key in keylist: placeholds [key] = key. lower () print (placeholds) # first convert the statement to lower case statementlower = statement. lower () # The cyclic placeholder dictionary, which is replaced by the placeholder for k, v in placeholds. items (): statementlower = statementlower. replace (v, k) print (statementlower)
Execution result:
CREATE TABLE $table_name$(    $CURSOR$){'$table_name$': '$table_name$', '$CURSOR$': '$cursor$'}create table $table_name$(    $CURSOR$)
 
Iii. xml operation-Write the conversion code back to the xml file
The writexml file is used for xml write operations. The Code is as follows:
Import xml. dom. minidomimport resnippet = xml. dom. minidom. parse ('ct. sqlpromptsnippet ') root = snippet.doc umentElement # print (root. nodeType, root. nodeName, root. nodeValue) code = snippet. getElementsByTagName ('code') [0] # print (Code. nodeType, code. nodeName, code. nodeValue) statement = code. firstChild. data # The first (and only) sub-element of the code is the print (statement) keylist = re of the CDATASection node. findall ("\ $ \ w + \ $", statement) placeholds = dict () for key in keylist: placeholds [key] = key. lower () statementlower = statement. lower () for k, v in placeholds. items (): statementlower = statementlower. replace (v, k) # update the XML object code. firstChild. data = statementlower # open the file object and write f = open ('result \ ct. sqlpromptsnippet ', 'w', encoding = 'utf-8') snippet. writexml (f, addindent = '', newl ='', encoding = 'utf-8') f. close ()
Comparison of execution result generation files:
 
4. Batch operations-Batch Processing of cyclic code segments
1. The file in the loop directory uses the listdir method of the OS module.
>>> import os>>> os.listdir()['DLLs', 'Doc', 'include', 'Lib', 'libs', 'LICENSE.txt', 'NEWS.txt', 'python.exe', 'python3.dll', 'python35.dll', 'pythonw.exe', 'README.txt', 'Scripts', 'tcl', 'Tools', 'vcruntime140.dll']
2. encapsulate a single conversion method sqllower, and read the code segment files in the directory again to complete batch processing. The complete code is as follows:
Import xml. dom. minidomimport reimport osdef sqllower (name): snippet = xml. dom. minidom. parse (name) root = snippet.doc umentElement # print (root. nodeType, root. nodeName, root. nodeValue) code = snippet. getElementsByTagName ('code') [0] # print (Code. nodeType, code. nodeName, code. nodeValue) statement = code. firstChild. data # The first (and only) sub-element of code is the print (statement) # print (statement) keylist = re. find All ("\ $ \ w + \ $", statement) placeholds = dict () for key in keylist: placeholds [key] = key. lower () # print (placeholds) statementlower = statement. lower () for k, v in placeholds. items (): statementlower = statementlower. replace (v, k) # print (statementlower) # update the XML object code. firstChild. data = statementlower f = open ('result \ '+ name, 'w', encoding = 'utf-8') snippet. writexml (f, addindent = '', newl ='', encoding =' UTF-8 ') f. close () # cyclically convert for f in OS. listdir (): if f. endswith ('. sqlpromptsnippet '): print ('converting' + f) sqllower (f) print ('all conversions completed. ')
 
V. Summary
This article extracts the requirements for case-sensitivity conversion of SQL code segments from daily use and uses Python for implementation. The following modules are used:
1. the xml. dom. minidom module is used to read and write xml files.
2. The re module uses a regular expression to query all placeholders.
3. the OS module uses the listdir method to loop through files in the directory.