def query(request, db, q): global DBS if not DBS.has_key(db): abort(404) filters = request.url.replace(request.base_url, '', 1) htsql = HTSQL(DBS[db]['connection']) if filters and ('?' in q): filters = '&%s' % filters.strip()[1:] q = '/%s%s' % (q, filters or '') data = htsql.produce(str(q)) return (data, data.records[0].__fields__, q)
def get_nested(): test = HTSQL("mysql://*****:*****@localhost/nestedsetspoc") #rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}?" + criterion) rows = test.produce("/clinical_data{id, patient_sid :as sid,string_value :as value,attribute{attribute_value}}") # rows = test.produce("/attribute{attribute_value, clinical_data{patient_sid :as sid,string_value :as value}}") with test: text = ''.join(emit('x-htsql/json', rows)) #print text, rows return text
def query(request, db, q): global DBS if not DBS.has_key(db): abort(404) filters = request.url.replace(request.base_url, '', 1) htsql = HTSQL(DBS[db]['connection']) if filters and ('?' in q): filters = '&%s' % filters.strip()[1:] q = '/%s%s' % (q, filters or '') data = htsql.produce(str(q)) return (data, data.records[0].__fields__, q)
def query(request, db, q): global DB_DIR filters = request.url.replace(request.base_url, '', 1) dbs = loadsqlites(DB_DIR) if not dbs.has_key(db): return '[!] missing db' htsql = HTSQL(dbs[db]) if filters and ('?' in q): filters = '&%s' % filters.strip()[1:] q = '/%s%s' % (q, filters or '') data = htsql.produce(str(q)) return (data, data.records[0].__fields__, q)
def get_factor(): #test = HTSQL("mysql://*****:*****@localhost/test") test = HTSQL("pgsql://*****:*****@localhost/test") rows = test.produce("/factor") with test: text = ''.join(emit('x-htsql/json', rows)) if mode == 'test': print text, rows return text
def action_htsql_query(self, query): """Executes a HTSQL Query""" try: from htsql.core.fmt.emit import emit from htsql.core.error import Error as HTSQL_Error from htsql import HTSQL except ImportError: return False, "HTSQL installation not found" # Resolve RDBMSs to their respective HTSQL engines engines = { 'postgres': 'pgsql', 'sqlite': 'sqlite', 'mysql': 'mysql', 'oracle': 'oracle', 'mssql': 'mssql', } if db_settings.password: password = "******" + urllib.quote_plus(db_settings.password) else: password = "" authority = '%s%s@%s:%s' % ( db_settings.username, password, db_settings.address, db_settings.port) uri = '%s://%s/%s' % ( engines[db_settings.rdbms], authority, db_settings.dbname) exts = [{ 'tweak.override': { 'unique_keys': 'product(sellable_id)', 'globals': { 'between($date, $start, $end)': '($date >= $start & $date <= $end)', 'trunc_hour($d)': 'datetime(year($d), month($d), day($d), hour($d))', 'trunc_day($d)': 'datetime(year($d), month($d), day($d))', 'trunc_month($d)': 'datetime(year($d), month($d), 01)', }}, }] store = HTSQL(uri, *exts) try: rows = store.produce(query) except HTSQL_Error as e: return False, str(e) with store: json = ''.join(emit('x-htsql/json', rows)) return True, json
def index(request): htsql = HTSQL('pgsql://%s:%s@%s/%s' % (settings.DATABASES['default']['USER'], settings.DATABASES['default']['PASSWORD'], settings.DATABASES['default']['HOST'], settings.DATABASES['default']['NAME'])) results = [] columns = [] query = '' if request.method == 'POST': query = request.POST['query'] results = htsql.produce(query) for prop in dir( results[0] ): if prop.startswith( '__' ) : continue if prop in ['count', 'index', 'make', 'bit_length', 'conjugate', 'denominator', 'imag', 'numerator'] : continue columns.append(prop) return render_to_response('index.html',{'results': results, 'query': query, 'columns': columns}, context_instance = RequestContext(request))
#!/usr/bin/env python from htsql import HTSQL import time db = HTSQL('sqlite:db.sqlite3') queries = [ """/snp.filter(daf_afr < 0.1 & daf_sea > 0.9).sort(daf_sea-).limit(100)""", """/snp.filter(chromosome = 'MAL1' & position > 100000 & position < 200000).sort(position).limit(100)""", """/snp.filter(genetext ~ 'pfmdr').sort(chromosome, position).limit(100)""" ] for query in queries: print query for _ in range(5): # repeat each query 5 times time.sleep(1) # sleep for 1 second between queries before = time.clock() result = db.produce(query) count = sum(1 for _ in result) after = time.clock() print '%s rows in %ss' % (count, after - before)
class AutoVivification(dict): """Implementation of perl's autovivification feature.""" def __getitem__(self, item): try: return dict.__getitem__(self, item) except KeyError: value = self[item] = type(self)() return value from htsql import HTSQL import pprint htsql = HTSQL("mysql://*****:*****@localhost/hesk") rows = htsql.produce("/hesk_tickets{custom4,custom1, name, custom3, subject, priority}") # +-----------------+---------------------+---------------------------+-------------+--------------------------------+----------+ # | custom4 | custom1 | name | custom3 | subject | priority | # +-----------------+---------------------+---------------------------+-------------+--------------------------------+----------+ partitions = AutoVivification() for row in rows: partitions[row.custom4][row.custom1][row.name][row.custom3][row.subject] = row.priority tipos = [] for tipo in partitions:
from htsql import HTSQL import sys, decimal, datetime db = __pbbt__['demo'].db htsql = HTSQL(db) uri = "/school{code, count(program), count(department)}" print("URI:", uri) for row in htsql.produce(uri): print(row) print() uri = "/school{name, count(program)}?code=$school_code" school_code = "bus" print("URI:", uri) print("$school_code: %r" % school_code) for row in htsql.produce(uri, school_code=school_code): print("%s: %s" % row) print() uri = "/school{name, num_prog:=count(program)}" \ "?num_prog>=$min_prog&num_prog<=$max_prog" min_prog = 6 max_prog = 8 print("URI:", uri) print("$min_prog: %r" % min_prog) print("$max_prog: %r" % max_prog) for row in htsql.produce(uri, min_prog=min_prog, max_prog=max_prog): print("%s: %s" % (row.name, row.num_prog)) print()
class AutoVivification(dict): """Implementation of perl's autovivification feature.""" def __getitem__(self, item): try: return dict.__getitem__(self, item) except KeyError: value = self[item] = type(self)() return value htsql = HTSQL(DB_URL) rows = htsql.produce("/protocolos_gasto{proyecto{status, linea_de_investigacion{linea_investigacion_desc :as desc} :as linea, titulo}, descripcion, monto}") # | proyecto | | | # +---------------+----------------------------------+----------------------------------+ | | # | | linea | | | | # | +----------------------------------+ | | | # | status | linea_investigacion_desc | titulo | descripcion | monto | partitions = AutoVivification() status = linea = titulo = concepto = '' for row in rows: if row.proyecto.linea: linea = row.proyecto.linea.desc else:
import json from htsql import HTSQL from db_conf import DB_URL htsql = HTSQL(DB_URL) rows = htsql.produce("/helpdesk_ticket{ tipo.tipo, categoria.nombre, estado.estado, fecha_creacion}") class AutoVivification(dict): """Implementation of perl's autovivification feature.""" def __getitem__(self, item): try: return dict.__getitem__(self, item) except KeyError: value = self[item] = type(self)() return value partitions = AutoVivification() aguas = {} for row in rows: if not row.nombre: nombre = 'sin categorizar' else: nombre = row.nombre if not row.tipo: tipo = 'default' else: tipo = row.tipo
def action_htsql_query(self, query): """Executes a HTSQL Query""" try: from htsql.core.fmt.emit import emit from htsql.core.error import Error as HTSQL_Error from htsql import HTSQL except ImportError: return False, "HTSQL installation not found" # Resolve RDBMSs to their respective HTSQL engines engines = {"postgres": "pgsql", "sqlite": "sqlite", "mysql": "mysql", "oracle": "oracle", "mssql": "mssql"} if db_settings.password: password = "******" + urllib.quote_plus(db_settings.password) else: password = "" authority = "%s%s@%s:%s" % (db_settings.username, password, db_settings.address, db_settings.port) uri = "%s://%s/%s" % (engines[db_settings.rdbms], authority, db_settings.dbname) # FIXME We should find a way to share this piece of code between Stoq # Portal and Stoq Server, without having to duplicate it every time exts = [ { "tweak.override": { "field-labels": { # Sellable fixes "sellable.price": ( "(if (on_sale_start_date <= now() &" "now() <= on_sale_end_date, on_sale_price," "base_price))" ), # Sale fixes # When the sale is not confirmed yet, the total_amount will be 0 # (just like the discount and surcharge). "sale.subtotal": "(total_amount + discount_value - surcharge_value)", "sale.discount_percentage": "(if(subtotal > 0," " (discount_value / subtotal), 0))", "sale.surcharge_percentage": "(if(subtotal > 0," " (surcharge_value / subtotal), 0))", "sale.cmv": "(sum(sale_item.total_cost))", "sale.returned_items_cost": "(sum(returned_sale_via_new_sale." " returned_sale_item.sale_item.total_cost))", "sale.profit_margin": "(if((cmv - returned_items_cost) > 0," " ((total_amount /" " (cmv - returned_items_cost)) - 1) * 100, 0))", # SaleItem Fixes "sale_item.total_price": "(price + ipi_info.v_ipi)", "sale_item.sale_discount": "(total_price * sale.discount_percentage)", "sale_item.sale_surcharge": "(total_price * sale.surcharge_percentage)", "sale_item.price_with_discount": ("(total_price - sale_discount + sale_surcharge)"), "sale_item.subtotal": "(total_price * quantity)", "sale_item.total_with_discount": "(price_with_discount * quantity)", "sale_item.total_cost": "(quantity * if(average_cost > 0," " average_cost," " sellable.cost))", # Other fixes "branch.main_address": "(person.address.filter(is_main_address))", "transfer_order.branch": "(source_branch)", "branch.description": ( "(if(is_null(person.company.fancy_name)," " person.name," " person.company.fancy_name))" ), }, "globals": { "identifier_str": ( "branch.acronym + head('0000', 5 - " "length(string(identifier)))" "+ string(identifier)" ), "trunc_month($d)": "datetime(year($d), month($d), 01)", "trunc_day($d)": "datetime(year($d), month($d), day($d))", "trunc_hour($d)": "datetime(year($d), month($d), day($d), hour($d))", "between($date, $start, $end)": "($date >= $start & $date <= $end)", # FIXME supplier name cannot be on field labels because # databases without nfe schema will cause an error when # this method is called "supplier_name": ( "(if(is_null(nfe_supplier.name)," " nfe_supplier.fancy_name," " nfe_supplier.name))" ), }, } } ] # FIXME: This is to support old stoq versions, which didn't have # a UNIQUE constraint on product.sellable_id column if stoq.stoq_version < (1, 10, 90): exts[0]["tweak.override"]["unique_keys"] = "product(sellable_id)" store = HTSQL(uri, *exts) try: rows = store.produce(query) except HTSQL_Error as e: return False, str(e) with store: json = "".join(emit("x-htsql/json", rows)) return True, json
def action_htsql_query(self, query): """Executes a HTSQL Query""" try: from htsql.core.fmt.emit import emit from htsql.core.error import Error as HTSQL_Error from htsql import HTSQL except ImportError: return False, "HTSQL installation not found" # Resolve RDBMSs to their respective HTSQL engines engines = { 'postgres': 'pgsql', 'sqlite': 'sqlite', 'mysql': 'mysql', 'oracle': 'oracle', 'mssql': 'mssql', } if db_settings.password: password = "******" + urllib.quote_plus(db_settings.password) else: password = "" authority = '%s%s@%s:%s' % (db_settings.username, password, db_settings.address, db_settings.port) uri = '%s://%s/%s' % (engines[db_settings.rdbms], authority, db_settings.dbname) # FIXME We should find a way to share this piece of code between Stoq # Portal and Stoq Server, without having to duplicate it every time exts = [{ 'tweak.override': { 'field-labels': { # Sellable fixes 'sellable.price': ('(if (on_sale_start_date <= now() &' 'now() <= on_sale_end_date, on_sale_price,' 'base_price))'), # Sale fixes # When the sale is not confirmed yet, the total_amount will be 0 # (just like the discount and surcharge). 'sale.subtotal': '(total_amount + discount_value - surcharge_value)', 'sale.discount_percentage': '(if(subtotal > 0,' ' (discount_value / subtotal), 0))', 'sale.surcharge_percentage': '(if(subtotal > 0,' ' (surcharge_value / subtotal), 0))', 'sale.cmv': '(sum(sale_item.total_cost))', 'sale.returned_items_cost': '(sum(returned_sale_via_new_sale.' ' returned_sale_item.sale_item.total_cost))', 'sale.profit_margin': '(if((cmv - returned_items_cost) > 0,' ' ((total_amount /' ' (cmv - returned_items_cost)) - 1) * 100, 0))', # SaleItem Fixes 'sale_item.total_price': '(price + ipi_info.v_ipi)', 'sale_item.sale_discount': '(total_price * sale.discount_percentage)', 'sale_item.sale_surcharge': '(total_price * sale.surcharge_percentage)', 'sale_item.price_with_discount': ('(total_price - sale_discount + sale_surcharge)'), 'sale_item.subtotal': '(total_price * quantity)', 'sale_item.total_with_discount': '(price_with_discount * quantity)', 'sale_item.total_cost': '(quantity * if(average_cost > 0,' ' average_cost,' ' sellable.cost))', # Other fixes 'branch.main_address': '(person.address.filter(is_main_address))', 'transfer_order.branch': '(source_branch)', 'branch.description': ('(if(is_null(person.company.fancy_name),' ' person.name,' ' person.company.fancy_name))'), }, 'globals': { 'identifier_str': ("branch.acronym + head('0000', 5 - " "length(string(identifier)))" "+ string(identifier)"), 'trunc_month($d)': 'datetime(year($d), month($d), 01)', 'trunc_day($d)': 'datetime(year($d), month($d), day($d))', 'trunc_hour($d)': 'datetime(year($d), month($d), day($d), hour($d))', 'between($date, $start, $end)': '($date >= $start & $date <= $end)', # FIXME supplier name cannot be on field labels because # databases without nfe schema will cause an error when # this method is called 'supplier_name': ('(if(is_null(nfe_supplier.name),' ' nfe_supplier.fancy_name,' ' nfe_supplier.name))'), } } }] # FIXME: This is to support old stoq versions, which didn't have # a UNIQUE constraint on product.sellable_id column if stoq.stoq_version < (1, 10, 90): exts[0]['tweak.override']['unique_keys'] = 'product(sellable_id)' store = HTSQL(uri, *exts) try: rows = store.produce(query) except HTSQL_Error as e: return False, str(e) with store: json = ''.join(emit('x-htsql/json', rows)) return True, json
# http://htsql.org/doc/embed.html # run: pip install HTSQL from htsql import HTSQL htsql = HTSQL("sqlite:vsee/db.sqlite3") # get info for a specific company company = "iWantTutor" rows = htsql.produce("/companies_company{*}?name='" + company + "'") for row in rows: print row.name print row.desc_cb print row.desc_al print row.cb_url print row.al_url print row.logo_url print row.company_url print row.market print "------------------------------------------------------" # get list of related companies sorted by similarity company = "iWantTutor" rows = htsql.produce("/companies_distance{score-, c1_id.name, c2_id.name}.filter(c1_id.name='" + company + "'|c2_id.name='" + company + "').limit(10)") # list of related companies similar = [] # scores scores = [] for row in rows: if not row[1] == company:
def get_htsql(): #if mode == 'test': # print criterion #test = HTSQL("mysql://*****:*****@localhost/test") test = HTSQL("pgsql://*****:*****@localhost/test") #rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}?" + criterion) #rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}") #test = HTSQL("mysql://*****:*****@localhost/test") rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}") # rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}") # http://127.0.0.1:8080/evaluation{comments,site{geoposition{id}?latitude=46&longitude%3E47},site{geoposition{accuracy}?latitude=46&longitude%3E47},evaluator{first_name}}?evaluator.first_name='you'/:sql # http://127.0.0.1:8080/site{site_name :as location, count(evaluation) :as 'N visits'} # http://127.0.0.1:8080/site{site_name :as location, count(evaluation) :as 'N visits'}?site_name!='Home' # http://127.0.0.1:8080/person{first_name + ' ' + last_name :as name, count(evaluation) :as 20'N visits'}?count(evaluation)>0 # /evaluation{comments,site{geoposition?accuracy=46.0&latitude%3C42},evaluator{first_name}}?evaluator.first_name='you' # # SELECT "evaluation"."comments", # "site"."?_1", # "site"."?_2", # "site"."id_1", # "site"."latitude", # "site"."longitude", # "site"."accuracy", # "site"."timestamp", # "person_2"."?", # "person_2"."first_name" # FROM "evaluation" # LEFT OUTER JOIN "person" AS "person_1" # ON ("evaluation"."evaluator_id" = "person_1"."id") # LEFT OUTER JOIN (SELECT TRUE AS "?_1", # "geoposition"."?" AS "?_2", # "geoposition"."id" AS "id_1", # "geoposition"."latitude", # "geoposition"."longitude", # "geoposition"."accuracy", # "geoposition"."timestamp", # "site"."id" AS "id_2" # FROM "site" # LEFT OUTER JOIN (SELECT TRUE AS "?", # "geoposition"."id", # "geoposition"."latitude", # "geoposition"."longitude", # "geoposition"."accuracy", # "geoposition"."timestamp" # FROM "geoposition" # WHERE ("geoposition"."accuracy" = 46.0::FLOAT8) # AND ("geoposition"."latitude" < 42.0::FLOAT8)) AS "geoposition" # ON ("site"."geoposition_id" = "geoposition"."id")) AS "site" # ON ("evaluation"."site_id" = "site"."id_2") # LEFT OUTER JOIN (SELECT TRUE AS "?", # "person"."first_name", # "person"."id" # FROM "person") AS "person_2" # ON ("evaluation"."evaluator_id" = "person_2"."id") # WHERE ("person_1"."first_name" = 'you') # ORDER BY "evaluation"."id" ASC with test: text = ''.join(emit('x-htsql/json', rows)) if mode == 'test': print text, rows return text
def populate(self): # We can't store the session as there is no way to pickle it session = HTSQL(self._connexion) self._data = session.produce(self.query, **self.parameters)