python写vim script 之 山寨版 dbext

山寨版 dbext
需要有python支持的vim,windows版的gvim已经有python支持
需要安装python2.4(其他版本好像不行)
pymssql,cx_oracle.

"==================================================
" File:         SzDbext.vim
" Brief:        山寨版dbext
" Authors:      shrek<shrek.wang AT gmail DOT com>
" Last Change:  2009-01-20 22:33:06
" Version:      0.00000000000000000000000000000002
" Licence:      LGPL
"
"
" Usage: 1:修改SzDbext.vim中的数据库连接参数
"        2:放到 plugin 目录下
"        3:编辑任竟文件
"        4:用v命令选中文本块
"        4: ,,  查询选定的sql
"           ,dt 查询选定文本的表结构
"           ,lt 列出包含选定文本的表
"
" Todo: 1:将数据库的连接参数用vim变量独立出来 
"       2:支持ddl


let g:selection=-1
python profile={}

function! GetDbOption()
python << EOF
from vim import *

aa=dict(host="192.168.0.280",user="**",password="**",database="data1")
bb=dict(host="192.168.0.281",user="**",password="**",database="sat2")
cc=dict(host="192.168.0.282",user="**",password="**",database="data3")

array=[aa,bb,cc]
selection=eval("g:selection")
if int(selection) < 0 :
  for index,item in enumerate(array):
    print str(index) + ": "+item["host"]+":  "+item["database"]
  command("let g:selection=input('please enter a selection')")
  selection=eval("g:selection")
  profile=array[int(selection)]
  print profile


EOF
endfunction


function! GetVisualBlock() range
    let save = @"
    silent normal gvy
    let vis_cmd = @"
    let @" = save
    return vis_cmd
endfunction 

function! SwitchToSQLResult()
  let s:cur_buf = bufnr("%")
  let s:szdb_result_buf=bufnr("SzdbResult")
  if bufwinnr(s:szdb_result_buf) > 0
    exec bufwinnr(s:szdb_result_buf) . "wincmd w"
    %d
  else
    exec 'silent! botright split SzdbResult' 
    exec "e SzdbResult"
    exec "set nowrap"
  endif

endfunction

function! QueryVisualSQL() range
python << EOF
import vim 
vb=vim.eval("GetVisualBlock()")
lines=vb.split("\n")
sql=""
for line in lines:
  sql=sql+line+" "
vim.command('call QuerySQL("'+sql+'")')
EOF
endfunction

function! QuerySQL(sql) 
python << EOF
import vim 
if not profile:
  vim.command("call GetDbOption()")
  
conn=""
if profile.has_key("sid") :
  import cx_Oracle
  dns_tns=cx_Oracle.makedsn(profile["host"],1521,profile["sid"])
  conn = cx_Oracle.connect(profile["user"], profile["password"], dns_tns)
else :
  import pymssql
  conn = pymssql.connect(host=profile["host"],user=profile["user"], \
    password=profile["password"],database=profile["database"])


cur = conn.cursor()

sql=vim.eval("a:sql")
cur.execute(sql)

columns,maxlens=[],[]
vim.command("call SwitchToSQLResult()")
if cur.description:
  for column in cur.description:
    columns.append(column[0])
    maxlens.append(0)

  resultset=[columns]
  for row in cur.fetchall():
    for index,field in enumerate(row):
      if (len(str(field))>maxlens[index]):
        maxlens[index]=len(str(field))
    resultset.append(row)

  for index,field in enumerate(columns):
    if (len(str(field))>maxlens[index]):
      maxlens[index]=len(str(field))

  headline=""
  for item in maxlens:
    headline=headline+"+"+("-"*item)+"--"
  headline=headline+"+"

  for rowindex,row in enumerate(resultset):
    line=""
    for index,field in enumerate(row):
      line=line+"| "+str(field).replace("\n","").ljust(maxlens[index]+1)
    if rowindex<2: vim.current.buffer.append(headline)
    vim.current.buffer.append( line+"|")
  vim.current.buffer.append( headline)
vim.current.buffer[0]="Host:"+profile["host"]+"  Database:"+profile["database"]
vim.current.buffer.append("affected "+str(cur.rowcount)+" rows.")
conn.commit()
cur.close()
conn.close()
EOF
  redraw! 
  exec bufwinnr(s:cur_buf) . "wincmd w"
endfunction

function! QueryTables()
python << EOF
import vim
vb=vim.eval("GetVisualBlock()")
if profile.has_key("sid"):
  sql="select  table_name   from   user_tables where table_name like '%"+vb.upper()+"%'"
else:
  sql="SELECT name FROM  sysobjects Where  name like '%"+vb+"%' and type = 'U' order by name"
vim.command('call QuerySQL("'+sql+'")')
EOF
endfunction

function! DescTable() 
python << EOF
import vim
vb=vim.eval("GetVisualBlock()")

if profile.has_key("sid"):
  sql="select column_name,data_type from user_tab_columns where table_name = '"+vb.upper()+"'"
else:
  sql="""Select col.[name]  as '字段名', type.[name] as '类型' , col.[length]as '长度' \ 
      From syscolumns as col   \
      Left Join systypes as type on col.xtype = type.xtype \ 
      where col.id = (Select id From sysobjects Where name = '"""+vb+"')"

vim.command('call QuerySQL("'+sql+'")')
EOF
endfunction

map <silent>,, :call QueryVisualSQL()<cr>
map <silent>,dt :call DescTable()<cr>
map <silent>,lt :call QueryTables()<cr>