urls.py加入:
url(r'^exp/$',views.exp,name='exp'),
views.py加入该方法:
def exp(request):
if request.method=="POST":
host=request.POST.get("dbip")
ports=request.POST.get("dbport")
dbuname=request.POST.get("dbuname")
dbpwd=request.POST.get("dbpwd")
dbname=request.POST.get("databases")
sqlstr=request.POST.get("sqlstr")
expsrts=""
try:
expsrts=dbs2.exportdbtitle(host,ports,dbuname,dbpwd,dbname,sqlstr)
except Exception:
return render(request,"expdatabase.html",{"login_err":"FAIL"})
return render(request,"expdatabase.html",{"login_err":"downloadlink:ftp://FTP服务器IP地址/excel/"+expsrts})
else:
return render(request,"expdatabase.html",{"login_err":"no set"})
dbs2.py写好exportdbtitle的方法:
import pymssql #导入这个模块,当然,也要导入其它代码当中有用到的模块,这里就不一一列举了
def exportdbtitle(hosts,ports,users,pwds,dbs,sql_str):
conn=pymssql.connect(host=hosts,port=ports,user=users,password=pwds,database=dbs,charset="UTF-8")
cursor = conn.cursor()
cursor.execute(sql_str)
data=cursor.fetchall()
index = cursor.description
titles=[]
nowtime=time.strftime('%Y-%m-%d-%H-%M-%S',time.localtime(time.time()))+""
for i in range(len(index)):
titles.append(index[i][0])
# workbook2007 = xlsxwriter.Workbook('c:\\exports.xlsx') #创建目标excel表格
workbook2007 = xlsxwriter.Workbook('.\\excel\\exports'+nowtime+'.xlsx') #创建目标excel表格
#此处将上一行的目录发布为FTP服务器的EXCEL目录,方便将来用户下载导出的EXCEL文件,并设置好访问权限
worksheet2007 = workbook2007.add_worksheet()
fw=open('DSE_WR_W_DAY.txt','w')
format = workbook2007.add_format()
formattitle =workbook2007.add_format({'bold': True, 'font_color': 'black'})
col=0
for t1 in titles:
print t1
worksheet2007.write(0,col,str(t1),formattitle)
col=col+1
#同时输出到excel和txt
[[[[worksheet2007.write(i+1,j,str(value),format),fw.write(str(value)),fw.write('\t')] for j,value in enumerate(value_r)] ,fw.write('\n')] for i,value_r in enumerate(data)]
workbook2007.close()
fw.close()
expstr='exports'+nowtime+'.xlsx'
return expstr
#print ('excel创建完成')
前端的expdatabase.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form id="loginForm" action="{% url 'exp' %}" method="POST"> {% csrf_token %}
IP\实例<input type="text" class="form-control" name="dbip" placeholder="IP\实例" value="默认IP\实例名"></br>
实例端口<input type="text" class="form-control" name="dbport" placeholder="实例端口" value="1444"></br>
连接帐号<input type="text" class="form-control" name="dbuname" placeholder="连接帐号"></br>
连接密码<input type="password" class="form-control" name="dbpwd" placeholder="连接密码"></br>
数据库名<input type="text" class="form-control" name="databases" placeholder="数据库名"></br>
SQL语句<input type="text" class="form-control" name="sqlstr" placeholder="SQL语句"></br>
<button class="btn btn-success btn-block" type="submit">
<b>确定导出</b>
</button>
<h4 style="color: red"><b>{{ login_err }}</b></h4>
</form>
</body>
</html>
演示效果:
输入对应的信息,确定导出数据,系统返回下载链接,为确保数据的安全,设置好FTP的访问权限