def raw_req(request): if request.method == "POST": form = RawForm(request.POST) db = form.data["db_to_select_from"] session = make_input_db_session(db) statement = form.data["select_body"] ret = session.execute(statement).fetchall() return render_to_response("raw_req_form.html", {"form":form, "ret" : ret}, context_instance=RequestContext(request)) else: form = RawForm() return render_to_response("raw_req_form.html", {"form":form}, context_instance=RequestContext(request))
def make_naive_graphs(request, db_name): s = make_input_db_session(db_name, echo=True) mrs_ret = s.query(table_mapping_no_eager_loading.master_record).all() start = date(1996,10,1) end = date(2012,12,12) x, y = segment_mr_month(mrs_ret, start, end) plt.bar(x, y, width=10, color="black") ax = plt.subplot(111) years = mdates.YearLocator() # every year months = mdates.MonthLocator() # every month yearsFmt = mdates.DateFormatter('%Y') ax.xaxis.set_major_locator(years) ax.xaxis.set_major_formatter(yearsFmt) ax.xaxis.set_minor_locator(months) ax.set_xlim(start, end) ax.grid(True) labels = ax.get_xticklabels() for label in labels: label.set_rotation(30) fig = plt.gcf() html = make_img_tag(fig) fig.autofmt_xdate() plt.close() ret = s.query(problem_code).all() ret.sort(key=lambda x: len(x.device_problems)) top_25 = ret[::-1][:25] counts = [len(i.device_problems) for i in top_25] labels = [i.problem_description for i in top_25] pos = range(len(top_25)) plt.barh(pos, counts, color="green") ax = plt.subplot(111) ax.grid(True) plt.yticks(pos, labels) fig = plt.gcf() fig.set_figwidth(20) fig.set_figheight(12) html = html + "<br> <br>" + make_img_tag(fig) plt.close() event_type_dict = {} for m_r in mrs_ret: if event_type_dict.get(m_r.event_type): event_type_dict[m_r.event_type] += 1 else: event_type_dict[m_r.event_type] = 1 labels = event_type_dict.keys() counts = [event_type_dict[i] for i in labels] pos = range(len(labels)) plt.bar(pos, counts, color="teal") plt.xticks(pos, labels) fig = plt.gcf() html = html + "<br>" + make_img_tag(fig) plt.close() s.close() html = html + "<br>" + str(y) return render_to_response("db_graph.html", {"html" : html}, context_instance=RequestContext(request), )
def out_form(request): if request.method== "POST": OutFormSet = formset_factory(OutForm) outformset = OutFormSet(request.POST) if not outformset.is_valid(): HttpResponse("formset is not valid") db_form = DBForm(request.POST) s = make_dbdb_session() ret_prox = s.query(Db).all() dbs = [(i.name, i.name) for i in ret_prox] s.close() del ret_prox db_form.fields['input_db'].choices = dbs db_form.full_clean() input_db_name = db_form.cleaned_data["input_db"] if not verify_db_name(input_db_name): return HttpResponse("need a real dbname") session = make_input_db_session(input_db_name, echo=ECHO) start = time.time() base_q = session.query(device).join("master_record") filter_dict_li = [i.cleaned_data for i in outformset.forms] filter_to_commit = make_filter_with_forms(filter_dict_li, {"name":db_form.cleaned_data["filter_name"]}) base_q = base_q.filter(filter_to_commit.expression) base_q = base_q.\ options(joinedload(device.problems), joinedload(device.master_record)) ret_o = base_q.all() li_to_commit = ret_o for i in ret_o: def span_left_from_dev(j): if j.text_records: [make_transient(i) for i in j.text_records] if j.patients: [make_transient(i) for i in j.patients] make_transient(j) def span_right_from_dev(j): if j.problem_code: make_transient(j.problem_code) make_transient(j) [span_right_from_dev(j) for j in i.problems] span_left_from_dev(i.master_record) make_transient(i) session.close() db_name = db_form.cleaned_data["output_db"] engine1 = create_engine("mysql://root@localhost:3306/mysql") if db_name in db_deny_set: return HttpResponse("You can't play with that db") if " " in db_name: return HttpResponse("no spaces here") dbdb_session = make_dbdb_session() ret_prox = dbdb_session.query(Db).all() dbli = [i.name for i in ret_prox] if db_name in dbli: if db_form.cleaned_data["drop_output_selector"]: engine1.execute("drop database " + db_name) engine1.execute("create database " + db_name) i = dbli.index(db_name) db_entry = ret_prox[i] db_entry.date_created = datetime.now() relation = Db_relation(child_db_name=db_name,parent_db_name=input_db_name) else: db_entry = Db(name=db_name, date_created=datetime.now()) relation = Db_relation(parent_db_name=input_db_name, child_db_name=db_name) engine1.execute("create database " + db_name) dbdb_session.add(filter_to_commit) dbdb_session.commit() relation._filter = filter_to_commit db_entry.child_relations.append(relation) dbdb_session.add(db_entry) dbdb_session.commit() dbdb_session.close() del engine1 engine1b = create_engine('mysql://root@localhost:3306/' + db_name) Base.metadata.create_all(bind=engine1b) del engine1b engine2 = create_engine('mysql://root@localhost:3306/' + db_name, echo=ECHO) nDBSesh = sessionmaker(bind=engine2)() #adds on duplicate key... to the mass inserts for the newly created db @compiles(Insert) def add_string(insert, compiler, **kw): s = compiler.visit_insert(insert, **kw) MDR_tables = [ "master_table", "text_table", "patient_table", "device_problem_table", "device_table", ] rems = "" if insert.table.name == "problem_code_table": rems = "device_problem_code = device_problem_code" elif insert.table.name in MDR_tables: rems = "MDR_report_key = MDR_report_key" return s + " ON DUPLICATE KEY UPDATE " + rems nDBSesh.add_all(li_to_commit) nDBSesh.commit() nDBSesh.execute("CREATE INDEX date_index ON master_table (date_report)") nDBSesh.execute("CREATE INDEX mdr_key_index ON text_table (MDR_report_key)") nDBSesh.close() del engine2 #redefining add_string prevents insert dying on other things @compiles(Insert) def add_string(insert, compiler, **kw): s = compiler.visit_insert(insert, **kw) return s stop = time.time() dif = stop - start return HttpResponse("inserted this many records into new db: " + str(len(ret_o)) + "<br>" + str(dif)+ "<br><br>" + "====The expression made looked like this==== <br>" + str(filter_to_commit.expression) + "") else: return HttpResponse("You gotta post")