Python daily Practice (1)--sql prompt Snippets Batch Finishing

Introduction

I use the SQL prompt plugin when writing SQL scripts, and I like to use the Snippets Code snippets feature in addition to the powerful smart hints and formatting SQL statements. For example, we can press the TAB key after checking the analyzer input SSF, and SQL prompt can help us quickly enter SELECT * from.

But individuals are not used to looking at uppercase SQL code, so they want to make the code snippet output into lowercase. Open the Code snippets Management interface, found that the management tool provides the ability to edit code snippets, but if you want to edit one by one, converted to lowercase, and then save, it is obviously not our style. You can see the path to the code snippet that SQL prompt holds:

Find the file in the path open to see that the snippet file is an XML file with an extension of sqlpromptsnippet.

So you want to use Python to convert the code in the snippet file to lowercase in bulk.

I. XML operations--Find the Code node and get the SQL statement for the snippet

1. Xml format File node type detailed introduction can refer to w3school tutorial

2. In Python, read-write XML files can use the Mxl.dom.minidom module to find code node codes as follows:

 import   Xml.dom.minidomsnippet  = Xml.dom.minidom.parse ( '  ssf.sqlpromptsnippet    " ) root  = Snippet.documentelement  print   ( 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 opening the current path named ' ssf.sqlpromptsnippet' XML file and assigns the XML file object to the Snippet object.

root = snippet.documentelement: Represents the document element (root node) that gets the snippet object, and gives the obtained object to root.

Code = snippet.getelementsbytagname ('code') [0] : Indicates that all child elements named Code are found under root root, and the first child element is assigned to a code object.

Execution Result:

1 codesnippets None

Because neither the codesnippets nor the code node is a text node, all of its nodevalue properties are none. The Code node is a 1- cdatasection node with the following properties:

So the correct statement to find the code node and get the SQL statement for the snippet is as follows:

 import   Xml.dom.minidomsnippet  = Xml.dom.minidom.parse ( '  ssf.sqlpromptsnippet    " ) root  = Snippet.documentelement  #  print ( Root.nodetype,root.nodename,root.nodevalue)  code = Snippet.getelementsbytagname (  " code   " ) [0]  #   Print (code.nodetype,code.nodename,code.nodevalue)  statement = Code.firstChild.data #   The 1th (and only) child element of code is the cdatasection node  

Print

Execution Result:

SELECT * FROM

two. SQL code conversion operation--uppercase to lowercase

1. SQL statement uppercase to lowercase, you can directly use the STR class lower function:

Statementlower = statement.lower ()print (statementlower)

Execution results

From

2. There are some snippets in SQL prompt that contain placeholders, with placeholders formatted as "$CURSOR $" and are case-sensitive, so placeholders cannot be converted to lowercase. Therefore, it is necessary to find all the placeholders in the SQL statements in the code snippet and store them and replace them after the SQL statements are converted to lowercase.

Because placeholders start with "$" and End with "$", we can easily use regular expressions to find all the placeholders in an SQL statement. After finding it, the placeholder and its lowercase form are stored with Dict.

ImportXml.dom.minidomImportResnippet= Xml.dom.minidom.parse ('Ct.sqlpromptsnippet') Root=snippet.documentelement#print (Root.nodetype,root.nodename,root.nodevalue)Code = Snippet.getelementsbytagname ('Code') [0]#print (Code.nodetype,code.nodename,code.nodevalue)statement = Code.firstChild.data#the 1th (and only) child element of code is the Cdatasection node print (statement)Print(statement)#Output Original Statement#Regular lookup for all placeholdersKeylist = Re.findall ("\$\w+\$", statement)#store placeholders and their lowercase form as a dictionaryPlaceholds =dict () forKeyinchKeylist:placeholds[key]=Key.lower ()Print(Placeholds)#Convert statements to lowercase firstStatementlower =Statement.lower ()#round-robin placeholder dictionary, replacing back-up placeholders forKvinchPlaceholds.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 $)

three. XML operation--write the conversion code back to the XML file

The XML write operation uses the WriteXml file with the following code:

ImportXml.dom.minidomImportResnippet= Xml.dom.minidom.parse ('Ct.sqlpromptsnippet') Root=snippet.documentelement#print (Root.nodetype,root.nodename,root.nodevalue)Code = Snippet.getelementsbytagname ('Code') [0]#print (Code.nodetype,code.nodename,code.nodevalue)statement = Code.firstChild.data#the 1th (and only) child element of code is the Cdatasection node print (statement)keylist= Re.findall ("\$\w+\$", statement) Placeholds=dict () forKeyinchKeylist:placeholds[key]=Key.lower ()Statementlower=Statement.lower () forKvinchPlaceholds.items (): Statementlower=statementlower.replace (v,k)#Updating XML objectsCode.firstChild.data =Statementlower#Open the File object, and then writef = open ('Result\ct.sqlpromptsnippet','W', encoding ='Utf-8') Snippet.writexml (f, addindent="', newl="', encoding='Utf-8') F.close ()

Comparison of execution result generation files:

four. Batch operation--Cycle Code snippet file batch processing

1. The file under the cyclic directory, using the Listdir method of the OS module.

>>>ImportOS>>>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. Package a single conversion into a method sqllower, and then loop through the code snippet file in the directory to complete the batch processing, the complete code is as follows:

ImportXml.dom.minidomImportReImportOSdefsqllower (name): Snippet=Xml.dom.minidom.parse (name) root=snippet.documentelement#print (Root.nodetype,root.nodename,root.nodevalue)Code = Snippet.getelementsbytagname ('Code') [0]#print (Code.nodetype,code.nodename,code.nodevalue)statement = Code.firstChild.data#the 1th (and only) child element of code is the Cdatasection node print (statement)    #print (statement)keylist= Re.findall ("\$\w+\$", statement) Placeholds=dict () forKeyinchKeylist:placeholds[key]=Key.lower ()#print (placeholds)Statementlower =Statement.lower () forKvinchPlaceholds.items (): Statementlower=statementlower.replace (v,k)#print (statementlower)    #Updating XML objectsCode.firstChild.data =Statementlower F= Open ('result\\'+ Name,'W', encoding ='Utf-8') Snippet.writexml (f, addindent="', newl="', encoding='Utf-8') F.close ()#Loop to convert forFinchOs.listdir ():ifF.endswith ('. Sqlpromptsnippet'):        Print('is transitioning'+f) sqllower (f)Print('all conversions are complete. ')

Five. Summary

This article extracts the need for SQL Snippet case translation from everyday use and uses Python to implement it. The following modules are used:

1. Xml.dom.minidom module, used to read and write XML files.

2. Re module, using regular expressions, query all placeholders.

3. OS module, use the Listdir method to cycle files in the directory.

Python daily Practice (1)--sql prompt Snippets Batch Finishing