detest.py
from flask import Flask, render_template
from sqlalchemy import create_engine, Column, Integer, String, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import case
from flask_wtf import FlaskForm
from wtforms import StringField, SelectField, IntegerField, SubmitField
from flask_bootstrap import Bootstrap
app = Flask(__name__)
app.config['SECRET_KEY'] = 'hard to guess string'
app.config['DEBUG']= True
bootstrap = Bootstrap(app)
engine = create_engine('sqlite:///test.db', echo=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()
class Test(Base):
__tablename__ = 'test1'
id = Column(Integer, primary_key=True)
samp = Column(String(64))
gene = Column(String(64))
zygo = Column(String(64))
count = Column(Integer)
z_id = Column(Integer)
Base.metadata.create_all(engine)
class TestForm(FlaskForm):
name = StringField('Sample name:')
gene = StringField('Gene name:')
count = IntegerField('count cut-off:')
inhe = SelectField('inherit model', choices=[('', 'any'), ('recessive', 'recessive'), ('dominant', 'dominant'), ('pathogenic', 'pathogenic')])
zygo = SelectField('zygote', choices=[('', 'both'), ('%het%', 'hetero'), ('%hom%', 'homo')])
submit = SubmitField('Search')
@app.route("/", methods=['GET', 'POST'])
def index():
form = TestForm()
sample = session.query(Test)
if form.name.data:
sample = sample.filter(Test.samp.like(form.name.data))
if form.gene.data:
sample = sample.filter(Test.gene.like(form.gene.data))
if form.count.data:
sample = sample.filter(Test.count<=form.count.data)
if form.zygo.data:
sample = sample.filter(Test.zygo.like(form.zygo.data))
return render_template('index.html', contents=sample, form=form)
if __name__ == "__main__":
app.run()index.html
<!-- templates/index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
{% import "bootstrap/wtf.html" as wtf %}
</head>
<body>
{{ wtf.quick_form(form) }}
<div class="table-responsive" style="height:300px; overflow:scroll">
<table class="table table-striped">
<thread>
<tr>
<th>ID</th>
<th>Sample</th>
<th>Gene</th>
<th>Zygote</th>
<th>count</th>
<th>Z_ID</th>
</tr>
</thread>
<tbody>
{% for samp in contents %}
<tr>
<td>{{ samp.id }}</td>
<td>{{ samp.samp }}</td>
<td>{{ samp.gene }}</td>
<td>{{ samp.zygo }}</td>
<td>{{ samp.count }}</td>
<td>{{ samp.z_id }}</td>
</tr>
{% else %}
<td>no entry exist!</td>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>これに上のtest.dbを読み込んでテスト環境にする。
検索式を書き込んで実行テストし放題。
