Freelance Projects

Upeksha Wisidagama

Vim MySQL Query Plugin

Install Python MySQL Module

Note: Vim MySQL Query Plugin needs Python MySQLDB module. Install the MySQL module using the below command.

sudo apt-get install python-mysqldb

Source UW MySQL Query Plugin

Paste the following script in a new buffer :vnew and source it using :source %.

You might save this for later use.

‘UW Query Database (~/.vim/scripts/database/uw-query-mysql.vim)’ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

" -----------------------------------------------------------------------------
"
"   UW Query Database
"   ~~~~~~~~~~~~~~~~~~~~
"
"   Execute any query on the database and get the result onto the current
"   buffer.
"
"   0. Source this file :source %.
"   1. Open a new buffer.
"   2. Enter your query in the first line.
"   3. Press ';s'.
"   4. Buffer will be filled with the results.
"   5. Press 'u' and edit again.
"   6. Press ';s'.
"   7. Continue until you get what you want.
"   8. Process with the Vim, Python or Unix tools.
"
"   Maintainer: Upeksha Wisidagama
"   License: GNU GPL v3 or later
"
" -----------------------------------------------------------------------------
"   Sample Queries
"
"   You can keep your frequently used queries in this area.
"
" -----------------------------------------------------------------------------
:fun! UW_Query_Database()
python << endpython
import vim
import MySQLdb as mdb

def uw_query_database():
    current_buffer = vim.current.buffer
    uw_query = current_buffer[0]
    data = uw_query_db( uw_query )
    uw_write_buffer( data )

def uw_query_db( uw_query ):
    try:
        con = mdb.connect('HOSTNAME', 'USER', 'PASSWORD', 'DB_NAME');
        cur = con.cursor()
        cur.execute(uw_query)
        data = cur.fetchall()
        return data
    except mdb.Error, e:
        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)
    finally:
        if con:
            con.close()

def uw_write_buffer( data ):
    current_buffer = vim.current.buffer
    i = 0
    current_buff = []
    for datum in data :
        record = ''
        for field in datum :
            record = record + str(field) + '\t'

        current_buff.append(record)
        i = i + 1

    current_buffer[:] = current_buff
    vim.command( '%!column -t' )

uw_query_database()
endpython
:endfun
" -----------------------------------------------------------------------------
:noremap <silent> ;s :call UW_Query_Database()<CR>
" -----------------------------------------------------------------------------

Map to Anything

Currently executing the query is mapped to ;s. But you are free to change it to anything you want.

:noremap <silent> ANYTHING :call UW_Query_Database()<CR>

In Buffer Freaking Fast Data Processing

Enter your query in the first line in a new buffer. Press ‘;s’. Buffer will be filled with the results. Press ‘u’ and edit again. Press ‘;s’. Continue until you get what you want.

You can do all your processing just after retrieving the data from the database, in the buffer itself!

Process data at home in Vim. You can even outsource some tasks to some GNU Tools external to Vim such as sort, column.

Open a new buffer :vnew and enter the following query.

select id, lockcode, model, paid, mask_no from gtk_sections where model = 111 and paid = 'paid' order by mask_no desc

Press ;s. The buffer will be filled with the results.

Suppose you have the following data in your buffer

‘After Query’ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

19495  marcon4396  111   paid  3062
18959  miglon9862  111   paid  3061
18248  julgom8162  111   paid  3060
19213  aleber8385  111   paid  3059
19010  danmic8781  111   paid  3058
14225  marbro1603  111   paid  3057
19190  joresp5333  111   paid  3056
19317  dancas7317  111   paid  3055
19052  margau1590  111   paid  3054
19275  herrio2625  111   paid  3053
18816  leahid6833  111   paid  3052
18839  gasote1450  111   paid  3051
20149  carsar2406  111   paid  3050
20133  juaret6692  111   paid  3049
20130  anafin1664  111   paid  3048

E.g. Run :%!sort -n -k 1

‘After Processing with sort’ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

14225  marbro1603  111   paid  3057
18248  julgom8162  111   paid  3060
18816  leahid6833  111   paid  3052
18839  gasote1450  111   paid  3051
18959  miglon9862  111   paid  3061
19010  danmic8781  111   paid  3058
19052  margau1590  111   paid  3054
19190  joresp5333  111   paid  3056
19213  aleber8385  111   paid  3059
19275  herrio2625  111   paid  3053
19317  dancas7317  111   paid  3055
19495  marcon4396  111   paid  3062
20130  anafin1664  111   paid  3048
20133  juaret6692  111   paid  3049
20149  carsar2406  111   paid  3050

You no longer have to copy paste data from other programs to prepare your reports. Vim can handle it for you with UW MySQL Database Query Plugin.