flask+easyui-datagrid

datagrid显示数据库中数据数据

flask：

后端

pySqlite.createSqliteDb 链接数据库

#原计划使用sqlite数据库，考虑到数据库稳定性，后改用mysql数据库。

import sqlite3 as sqlit

import sqlite3

import os

def init():

global conn

global cur

#conn=sqlite3.connect('baseDb')

conn=sqlit.connect("D:/pycharm/dbFlower/pySqlite/baseDb")

cur= conn.cursor()

#cur.execute("select * from sqlite_master")

def createConDb(dbName):

db_path=dbName+'.db'

if dbName == '':

print('dbName can not null')

# elif os.path.exists(db_path):

# print('dataBase is exists')

#else:

#conn=sqlite3.connect('baseDb.db')

def Sqlite_getTabAll(tabName):

sql = "select * from %s"%(tabName)

print(f'{sql}')

cur.execute(sql)

#content = cur.fetchall()

#return content

return cur

def Sqlite_getTabOne(tabName):

sql = "select type,name from %s"%(tabName)

print(f'{sql}')

cur.execute(sql)

content = cur.fetchall()

return content

def Sqlite_getTabDes(tabName):

sql="PRAGMA table_info(%s"%(tabName)

sql=sql+')'

print(f'sql is :{sql}')

cur.execute(sql)

labels=cur.fetchall()

labels=[l[1] for l in labels]

return labels

if __name__=='__main__':

print('begin')

init()

print('1')

myRows=Sqlite_getTabAll("cpt1")

myRows1=myRows[0]

myRows2 = myRows[0][1]

print(f'myrow is :,{myRows1},{myRows2}')

labels=Sqlite_getTabDes('cpt1')

print(f'labels is :{labels[0]}')

pySqlite.sqliteHelper 数据库操作

#原计划使用sqlite数据库，考虑到数据库稳定性，后改用mysql数据库。

import sqlite3 as sqlit

import sqlite3

import os

class SqliteHelper:

def __init__(self,dbPath):

self.dbPath = dbPath

def connect(self):

self.conn=sqlit.connect(self.dbPath)

self.cursor=self.conn.cursor()

def close(self):

self.cursor.close()

self.conn.close()

#获取字段名

def get_des(self,sql,params=()):

result=None

try:

self.connect()

self.cursor.execute(sql,params)

result=self.cursor.description

except Exception as e:

print(e.args)

return result

def get_one(self,sql,params=()):

result=None

try:

self.connect()

self.cursor.execute(sql, params)

result = self.cursor.fetchone()

self.close()

except Exception as e:

print(e.args)

return result

def get_all(self,sql,params=()):

list=()

try:

self.connect()

self.cursor.execute(sql,params)

list=self.cursor.fetchall()

self.close()

except Exception as e:

print(e.args)

return list

def insert(self,sql,params=()):

return self.__edit(sql,params)

def update(self, sql, params=()):

return self.__edit(sql, params)

def delete(self, sql, params=()):

return self.__edit(sql, params)

def __edit(self,sql,params):

count=0

try:

self.connect()

count=self.cursor.execute(sql,params)

self.conn.commit()

self.close()

except Exception as e:

print(e.args)

return count

if __name__=='__main__':

dbpath="D:/pycharm/dbFlower/pySqlite/baseDb"

tName='workcontent'

sname=2

sql = 'select * from %s where id=?;' % tName

#sql = 'insert into %s(name,age)' % TabName + ' value (%s)'

#params = [tName,sname]

params=[sname]

my=SqliteHelper(dbpath)

mydata=my.get_one(sql,params)

print(mydata)

util.util 数据库结果集转成json

from pySqlite.sqliteHelper import SqliteHelper

import json

#获得普通json

def getSqliteJson(tabName):

tName=tabName

sname=1

dbPath="D:/pycharm/dbFlower/pySqlite/baseDb"

#sql = 'select * from %s where id=?' %tName

sql = 'select distinct addrType ,addrType from %s' %tName

#params = [sname]

params=[]

sqlhelp=SqliteHelper(dbPath)

workConList = sqlhelp.get_all(sql, params)

workConDes=sqlhelp.get_des(sql,params)

colCount=len(workConDes)

myData="[{"

for jj in range(len(workConList)):

for ii in range(colCount):

myData=myData+"\""+workConDes[ii][0]+"\":"+"\""+str(workConList[jj][ii])+"\","

#去掉每行尾部 ，

myData=myData.strip(',')

myData=myData+'},{'

#去掉最后尾部，

myData=myData.rstrip(',{')

myData=myData+"]"

#myDatas=bytes(myData, encoding="utf-8").decode()

#myDatas =myData.encode("unicode-escape")

myDatas=myData

#myDatas=json.dump(myDatas)

print(myDatas)

return myDatas

#数据库表转为json

def getSqliteGridJson(tabName):

tName=tabName

sname=1

dbPath="D:/pycharm/dbFlower/pySqlite/baseDb"

#sql = 'select * from %s where id=?' %tName

sql = 'select * from %s ' %tName

#params = [sname]

params=[]

sqlhelp=SqliteHelper(dbPath)

workConList = sqlhelp.get_all(sql, params)

workConDes=sqlhelp.get_des(sql,params)

colCount=len(workConDes)

myData="{\"total\":1,\"rows\":[{"

for jj in range(len(workConList)):

for ii in range(colCount):

myData=myData+"\""+workConDes[ii][0]+"\":"+"\""+str(workConList[jj][ii])+"\","

#去掉每行尾部 ，

myData=myData.strip(',')

myData=myData+'},{'

#去掉最后尾部，

myData=myData.rstrip(',{')

myData=myData+"]}"

#myDatas=bytes(myData, encoding="utf-8").decode()

myDatas =myData.encode("unicode-escape")

#myDatas=myData

#myDatas=json.dump(myDatas)

print(myDatas)

return myDatas

if __name__=='__main__':

getSqliteGridJson('workContent')

py_flask_easyui 主文件

from flask import Flask, render_template

from pySqlite import createSqliteDb

from util.util import *

import os

import json

app =Flask(__name__)

@app.route("/workCon")

def workConTent():

return render_template('workCon.html')

@app.route("/test")

def test():

return render_template('test.html')

@app.route("/myRows")

def stuinforlist():

createSqliteDb.init()

myrows=createSqliteDb.Sqlite_getTabAll('cpt1')

#return render_template('show.html',res = json.dumps(myrows))

return json.dumps(myrows)

@app.route("/myData")

def myData():

myDatas=getSqliteGridJson('workContent')

return myDatas

@app.route("/comBoxData")

def comBoxData():

myDatas=getSqliteJson('workContent')

return myDatas

if __name__ == '__main__':

app.run(host='0.0.0.0',port=5002)

前端

workCon.html

<!DOCTYPEhtml PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "" target="_blank">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"" target="_blank">>

<html >

<head>

<meta charset="UTF-8"/>

<link rel="stylesheet" type="text/css" href="../static/easyui/themes/default/easyui.css"/>

<link rel="stylesheet" type="text/css" href="../static/easyui/themes/icon.css"/>

<link rel="stylesheet" type="text/css" href="../static/easyui/demo/demo.css"/>

<script type="text/javascript" src="../static/easyui/jquery.min.js"></script>

<script type="text/javascript" src="../static/easyui/jquery.easyui.min.js"></script>

<!--script type="text/javascript" src="../static/easyui/easyui-lang-zh_CN.js"></script>

<script type="text/javascript" src="../static/js1/manageruser.js"></script-->

</head>

<body>

<div class="easyui-layout" data-options="fit:true">

<div data-options="region:'center',title:'用户列表'">

<table id="dg" class="easyui-datagrid" style="width:100%;height:100%"

data-options="

method:'get',

url:'myData',

iconCls: 'icon-edit',

singleSelect:'true',

toolbar:'#tb',

onClickCell:onClickCell ">

<thead>

<tr>

<th data-options="field:'id',align:'center',width:'9%'">ID</th>

<th data-options="field:'addrDes',align:'center',width:'9%'">地址描述</th>

<th data-options="field:'addr',align:'center',width:'9%'">地址</th>

<th data-options="field:'addrType',align:'center',width:'9%'">地址类型</th>

<th data-options="field:'dbName',align:'center',width:'9%'">数据库名</th>

<th data-options="field:'serverPort',align:'center',width:'9%'">端口</th>

<th data-options="field:'uName',align:'center',width:'9%'">用户</th>

<th data-options="field:'pWord',align:'center',width:'9%'">密码</th>

<th data-options="field:'meno',align:'center',width:'9%',editor:'text'">备注</th>

</tr>

</thead>

</table>

<!--过滤条件-->

<div id="tb" style="padding:5px;height:auto">

<div>

ip: <input id="Addr" class="easyui-textbox" style="width:120px">

描述: <input id="AddDes" class="easyui-textbox" style="width:120px">

地址类型:

<input id="addrType" class="easyui-combobox"

name="language"

data-options="

url:'comBoxData',

method:'get',

valueField:'addrType',

textField:'addrType',

panelHeight:'auto'

">

<a href="#" class="easyui-linkbutton" iconCls="icon-search" οnclick="doSearch()">Search</a>

</div>

</div>

</div>

</div>

<script type="text/javascript">

//单个文本编辑begin

$.extend($.fn.datagrid.methods, {

editCell: function(jq,param){

return jq.each(function(){

var opts = $(this).datagrid('options');

var fields = $(this).datagrid('getColumnFields',true).concat($(this).datagrid('getColumnFields'));

for(var i=0; fields.length>i; i++){

var col = $(this).datagrid('getColumnOption', fields[i]);

col.editor1 = col.editor;

if (fields[i] != param.field){

col.editor = null;

}

}

$(this).datagrid('beginEdit', param.index);

for(var i=0; fields.length>i; i++){

var col = $(this).datagrid('getColumnOption', fields[i]);

col.editor = col.editor1;

}

});

}

});

var editIndex = undefined;

function endEditing(){

if (editIndex == undefined){return true}

if ($('#dg').datagrid('validateRow', editIndex)){

$('#dg').datagrid('endEdit', editIndex);

editIndex = undefined;

return true;

} else {

return false;

}

}

function onClickCell(index, field){

if (endEditing()){

$('#dg').datagrid('selectRow', index)

.datagrid('editCell', {index:index,field:field});

editIndex = index;

}

}

//单个文本编辑end;

function doSearch(){

$('#dg').datagrid('load',{

itemid: $('#itemid').val(),

productid: $('#productid').val()

});

}

</script>

</body>

</html>