Execute SQL queries in Oracle DB in Sublime Text 2

I hope everyone is familiar with the popular Sublime Text 2 editor. I want to share my experience, how I was able to make my life easier by writing a plugin to quickly call inquiries to BD Oracle right from the editor, simply selecting the request and pressing the combination.

Beloved by us for the speed, ease and cross-platform Sublime Text is rich in a huge number of extensions, so-called. packages and plug-ins for every taste and color. But I didn’t find a plug-in for invoking SQL queries (if I’m wrong, I’ll be glad if you share it). Well, no, no - it does not matter, we will write our own.

After reading the documentation and looking at a couple of examples, let's get started.

The plugin will work very simply: highlighted the request, clicked the combination and the result will appear in a new window.
')
First of all we create a new plugin Tools -> New plugin

import sublime, sublime_plugin class ExampleCommand(sublime_plugin.TextCommand): def run(self, edit): self.view.insert(edit, 0, "Hello, World!") 

We immediately save it under the name Oquery.py, but Sublime offers us to save it in the User folder, but we will save it one level up in the Packages folder in its own Oquery folder.

To communicate with the base, we need the cx_Oracle class. Download and return to our OQuery folder and create the lib folder in it, in which we will store the necessary libraries. In it we will save the downloaded archive in the cx_Oracle folder.

I want the request response to be provided in the form of nice tables, and for this we already have a suitable plugin for us. We do the same thing: download, unpack and rename the folder to prettytable.

To store the base settings, we need the configuration file Oquery.sublime-settigs. Create it and save it in the root folder of our plugin with content (example):

 { "host" : "localhost", "port" : 1521, "dbname" : "demoDB", "username" : "user1", "password" : "pass" } 

Now we all have to start writing the plugin itself.

At the top of the file, we declare the encoding and import the modules we need, including the connector to the database and the output label library.

 # coding=utf-8 import sys import os sys.path.append(os.path.dirname(sys.executable)) sys.path.append(os.path.join(os.path.dirname(__file__), "lib","cx_Oracle")) sys.path.append(os.path.join(os.path.dirname(__file__), "lib","prettytable")) import sublime import sublime_plugin import cx_Oracle import unicodedata import prettytable 

Strings

 sys.path.append(os.path.join(os.path.dirname(__file__), "lib","cx_Oracle")) sys.path.append(os.path.join(os.path.dirname(__file__), "lib","prettytable")) 

Tells subline where to look for our libraries. When connecting a prettytable Sublime, I cursed the absence of the unicodedata module, and simply importing it did not eliminate the error, since there is a bug in Sublime, due to which the subline does not know where that unicodedata module is located. Not much googling I found a solution by adding a line

 sys.path.append(os.path.dirname(sys.executable)) 

And the problem is solved.

Getting to the team itself

 class OqueryCommand(sublime_plugin.TextCommand): def run(self, edit): #   view view = self.view #      region = view.sel()[0] if not region.empty(): #   ,     selection = view.substr(region) try: #          Oquery.sublime-settings settings = sublime.load_settings('Oquery.sublime-settings') #    db = cx_Oracle.connect(str(settings.get('username')), str(settings.get('password')), settings.get('host')+':'+str(settings.get('port'))+'/'+settings.get('dbname')) except cx_Oracle.DatabaseError, e: sublime.message_dialog(str(e)) else: #   cursor = db.cursor() try: #    cursor.execute(selection) except cx_Oracle.DatabaseError, e: sublime.message_dialog(str(e)) else: #          pt = prettytable.from_db_cursor(cursor) #     tableStr = pt.get_string() #    output = view.window().new_file(); #      output.insert(edit, 0, tableStr) else: sublime.message_dialog('Select a proper SQL query') 

That's all. but we also need to declare a combination to call our team. This is done very simply Preferences -> Key Bindings User. And add one line to it.

 [ { "keys": ["ctrl+o", "ctrl+r"], "command": "oquery" } ] 

Our plugin will work on a combination of (ctrl + o) + r. You can choose another.

That's all, now we select any request and with a slight keystroke, we get an answer in a new window. Isn't it great?

If you wish, you can convert everything to any other database.

To laziness, I attach the archive