自动生成授权sql脚本

目录有一个EXECL表，里面有几个表，其中一个表是数据库名，其它的表中数据则是表明和可以赋予的权限，然后要根据EXECL中的要求生成类似下面SQL脚本

Use AIS20090819174043;
go
grant select on t_Department to K3E7swap;
grant select on t_ICItem to K3E7swap;
grant select on t_Account to K3E7swap;
grant select on t_AcctGroup to K3E7swap;
grant select on cn_Account to K3E7swap;
grant select on t_AcctGroup to K3E7swap;

其实对EXECL熟的话，好像也能做出来，但EXECL高级功能不熟，只好折腾了下，把EXECL导入到sqlite数据库中然后又写了个python脚本自动生成SQL脚本。（正好复习下python和sql）

#!/usr/bin/env python
#coding:utf-8
# Author:  Edward --
# Purpose: 自动生成授权SQL语句
# Created: 2013-11-26

import sys
import sqlite3

conn = sqlite3.connect("e:\\test.db")
curs = conn.cursor()

sql1 = "select 'Use ' || Database || ';' from Database;"
sql2 = "select 'grant ' || Permission || ' on '|| Object ||' to K3E7swap;' from Base;"
sql3 = "select 'grant ' || Permission || ' on '|| Object ||' to K3E7swap;' from Business;"

f = open('e:\\test.sql', 'w')

curs.execute(sql1)
rows = curs.fetchall()
for i in rows:
    f.writelines(i[0]+'\n')
    #f.write('\n')
    f.writelines('go\n')
    
    curs.execute(sql2)
    rows1 = curs.fetchall()
    for j in rows1:
        f.writelines(j[0]+'\n')
        #f.write('\n')
    curs.execute(sql3)
    rows2 = curs.fetchall()
    for j in rows2:
        f.writelines(j[0]+'\n')
    
    f.write('\n')
        
f.close()
conn.close()

这个是给个别表授权的SQL脚本生成脚本

#!/usr/bin/env python
#coding:utf-8
# Author:  Edward --
# Purpose: 自动生成授权SQL语句1
# Created: 2013-11-26

import sys
import sqlite3

conn = sqlite3.connect("e:\\test.db")
curs = conn.cursor()

tablelist = ['t_Stock']

sql1 = "select 'Use ' || Database || ';' from Database;"
sql2 = "grant select on "+tablelist[0] + " to K3E7swap;"

f = open('e:\\test1.sql', 'w')

curs.execute(sql1)
rows = curs.fetchall()
for i in rows:
    f.writelines(i[0]+'\n')
    #f.write('\n')
    f.writelines('go\n')
    
    for table in tablelist:
        f.writelines("grant select on "+ table + " to K3E7swap;\n")
    
    f.write('\n')
        
f.close()
conn.close()