Mercurial > repos > iuc > sqlite_to_tabular

changeset 12:575cd6cd537c draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
"planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 4fd70e184fca17ad430c30eb286127c4a198ef11"
author iuc
date Sat, 19 Jun 2021 14:16:32 +0000
parents bce29ec10b78
children c29d2f80a066
files macros.xml query_db.py sqlite_to_tabular.xml test-data/math_input.tsv test-data/math_output.tsv
diffstat 5 files changed, 322 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/macros.xml	Fri Feb 12 21:20:55 2021 +0000
+++ b/macros.xml	Sat Jun 19 14:16:32 2021 +0000
@@ -383,6 +383,7 @@
     =========== ========== ========== ===================== ========== ============
 
 
+   
   Regular_expression_ functions are included for: 
 
   ::
@@ -423,7 +424,33 @@
     James       Smith      20/10/80
     =========== ========== ==========
 
-.. _Regular_expression: https://docs.python.org/release/2.7/library/re.html
+  Math_ functions *( python math library: https://docs.python.org/3.6/library/math.html )*:
+
+  acos(x), acosh(x), asin(x), asinh(x), atan(x), atanh(x), atan2(x, y), ceil(x), cos(x), cosh(x), degrees(x), exp(x), expm1(x), fabs(x), floor(x), fmod(x, y), log(b,x), log(x), log10(x), log1p(x), log2(x), mod(x, y), pow(x, y), radians(x), sin(x), sinh(x), sqrt(x), tan(x), tanh(x), trunc(x)
+
+
+  ::
+
+    Query:
+
+    SELECT SaleAmount, floor(SaleAmount) as "dollars"
+    FROM sales
+
+  Results:
+    
+    ============  ========
+      SaleAmount   dollars
+    ============  ========
+          100.22       100
+           99.95        99
+          122.95       122
+          100.00       100
+          555.55       555
+    ============  ========
+  
+
+.. _Regular_expression: https://docs.python.org/3.9/library/re.html
+.. _Math: https://docs.python.org/3.9/library/math.html
 .. _SQLite: http://www.sqlite.org/index.html
 .. _SQLite_functions: http://www.sqlite.org/docs.html
 
--- a/query_db.py	Fri Feb 12 21:20:55 2021 +0000
+++ b/query_db.py	Sat Jun 19 14:16:32 2021 +0000
@@ -2,6 +2,7 @@
 
 from __future__ import print_function
 
+import math
 import re
 import sqlite3 as sqlite
 import sys
@@ -16,6 +17,10 @@
     """
 
 
+def msg(e):
+    print(e, file=sys.stderr)
+
+
 def regex_match(expr, item):
     return re.match(expr, item) is not None
 
@@ -28,12 +33,283 @@
     return re.sub(expr, replace, item)
 
 
+def math_acos(x):
+    try:
+        return math.acos(x)
+    except ValueError as ve:
+        msg('acos(%s): %s' % (x, ve))
+        return None
+
+
+def math_acosh(x):
+    try:
+        return math.acosh(x)
+    except ValueError as ve:
+        msg(f'acosh({x}): {ve}')
+        return None
+
+
+def math_asin(x):
+    try:
+        return math.asin(x)
+    except ValueError as ve:
+        msg(f'asin({x}): {ve}')
+        return None
+
+
+def math_asinh(x):
+    try:
+        return math.asinh(x)
+    except ValueError as ve:
+        msg(f'asinh({x}): {ve}')
+        return None
+
+
+def math_atan(x):
+    try:
+        return math.atan(x)
+    except ValueError as ve:
+        msg(f'atan({x}): {ve}')
+        return None
+
+
+def math_atanh(x):
+    try:
+        return math.atanh(x)
+    except ValueError as ve:
+        msg(f'atanh({x}): {ve}')
+        return None
+
+
+def math_atan2(x, y):
+    try:
+        return math.atan2(x, y)
+    except ValueError as ve:
+        msg(f'atan2({x}, {y}): {ve}')
+        return None
+
+
+def math_ceil(x):
+    try:
+        return math.ceil(x)
+    except ValueError as ve:
+        msg(f'ceil({x}): {ve}')
+        return None
+
+
+def math_cos(x):
+    try:
+        return math.cos(x)
+    except ValueError as ve:
+        msg(f'cos({x}): {ve}')
+        return None
+
+
+def math_cosh(x):
+    try:
+        return math.cosh(x)
+    except ValueError as ve:
+        msg(f'cosh({x}): {ve}')
+        return None
+
+
+def math_degrees(x):
+    try:
+        return math.degrees(x)
+    except ValueError as ve:
+        msg(f'degrees({x}): {ve}')
+        return None
+
+
+def math_exp(x):
+    try:
+        return math.exp(x)
+    except ValueError as ve:
+        msg(f'exp({x}): {ve}')
+        return None
+
+
+def math_expm1(x):
+    try:
+        return math.expm1(x)
+    except ValueError as ve:
+        msg(f'expm1({x}): {ve}')
+        return None
+
+
+def math_fabs(x):
+    try:
+        return math.fabs(x)
+    except ValueError as ve:
+        msg(f'fabs({x}): {ve}')
+        return None
+
+
+def math_floor(x):
+    try:
+        return math.floor(x)
+    except ValueError as ve:
+        msg(f'floor({x}): {ve}')
+        return None
+
+
+def math_fmod(x, y):
+    try:
+        return math.fmod(x, y)
+    except ValueError as ve:
+        msg(f'fmod({x}, {y}): {ve}')
+        return None
+
+
+def math_blog(b, x):
+    try:
+        return math.log(b, x)
+    except ValueError as ve:
+        msg(f'log({b}, {x}): {ve}')
+        return None
+
+
+def math_log(x):
+    try:
+        return math.log(x)
+    except ValueError as ve:
+        msg(f'log({x}): {ve}')
+        return None
+
+
+def math_log10(x):
+    try:
+        return math.log10(x)
+    except ValueError as ve:
+        msg(f'log10({x}): {ve}')
+        return None
+
+
+def math_log1p(x):
+    try:
+        return math.log1p(x)
+    except ValueError as ve:
+        msg(f'log1p({x}): {ve}')
+        return None
+
+
+def math_log2(x):
+    try:
+        return math.log2(x)
+    except ValueError as ve:
+        msg(f'log2({x}): {ve}')
+        return None
+
+
+def math_mod(x, y):
+    try:
+        return x % y
+    except ValueError as ve:
+        msg(f'mod({x}, {y}): {ve}')
+        return None
+
+
+def math_pow(x, y):
+    try:
+        return math.pow(x, y)
+    except ValueError as ve:
+        msg(f'pow({x}, {y}): {ve}')
+        return None
+
+
+def math_radians(x):
+    try:
+        return math.radians(x)
+    except ValueError as ve:
+        msg(f'radians({x}): {ve}')
+        return None
+
+
+def math_sin(x):
+    try:
+        return math.sin(x)
+    except ValueError as ve:
+        msg(f'sin({x}): {ve}')
+        return None
+
+
+def math_sinh(x):
+    try:
+        return math.sinh(x)
+    except ValueError as ve:
+        msg(f'sinh({x}): {ve}')
+        return None
+
+
+def math_sqrt(x):
+    try:
+        return math.sqrt(x)
+    except ValueError as ve:
+        msg(f'sqrt({x}): {ve}')
+        return None
+
+
+def math_tan(x):
+    try:
+        return math.tan(x)
+    except ValueError as ve:
+        msg(f'tan({x}): {ve}')
+        return None
+
+
+def math_tanh(x):
+    try:
+        return math.tanh(x)
+    except ValueError as ve:
+        msg(f'tanh({x}): {ve}')
+        return None
+
+
+def math_trunc(x):
+    try:
+        return math.trunc(x)
+    except ValueError as ve:
+        msg(f'trunc({x}): {ve}')
+        return None
+
+
 def get_connection(sqlitedb_path, addfunctions=True):
+    sqlite.enable_callback_tracebacks(addfunctions)
     conn = sqlite.connect(sqlitedb_path)
     if addfunctions:
         conn.create_function("re_match", 2, regex_match)
         conn.create_function("re_search", 2, regex_search)
         conn.create_function("re_sub", 3, regex_sub)
+        conn.create_function("acos", 1, math_acos)
+        conn.create_function("acosh", 1, math_acosh)
+        conn.create_function("asin", 1, math_asin)
+        conn.create_function("asinh", 1, math_asinh)
+        conn.create_function("atan", 1, math_atan)
+        conn.create_function("atanh", 1, math_atanh)
+        conn.create_function("atan2", 2, math_atan2)
+        conn.create_function("ceil", 1, math_ceil)
+        conn.create_function("cos", 1, math_cos)
+        conn.create_function("cosh", 1, math_cosh)
+        conn.create_function("degrees", 1, math_degrees)
+        conn.create_function("exp", 1, math_exp)
+        conn.create_function("expm1", 1, math_expm1)
+        conn.create_function("fabs", 1, math_fabs)
+        conn.create_function("floor", 1, math_floor)
+        conn.create_function("fmod", 2, math_fmod)
+        conn.create_function("log", 1, math_log)
+        conn.create_function("log", 2, math_blog)
+        conn.create_function("log10", 1, math_log10)
+        conn.create_function("log2", 1, math_log2)
+        conn.create_function("log1p", 1, math_log1p)
+        conn.create_function("mod", 2, math_mod)
+        conn.create_function("pow", 2, math_pow)
+        conn.create_function("radians", 1, math_radians)
+        conn.create_function("sin", 1, math_sin)
+        conn.create_function("sinh", 1, math_sinh)
+        conn.create_function("sqrt", 1, math_sqrt)
+        conn.create_function("tan", 1, math_tan)
+        conn.create_function("tanh", 1, math_tanh)
+        conn.create_function("trunc", 1, math_trunc)
     return conn
 
 
--- a/sqlite_to_tabular.xml	Fri Feb 12 21:20:55 2021 +0000
+++ b/sqlite_to_tabular.xml	Sat Jun 19 14:16:32 2021 +0000
@@ -1,5 +1,5 @@
 <?xml version="1.0"?>
-<tool id="sqlite_to_tabular" name="SQLite to tabular" version="2.0.1">
+<tool id="sqlite_to_tabular" name="SQLite to tabular" version="3.2.0">
     <description>for SQL query</description>
 
     <macros>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/test-data/math_input.tsv	Sat Jun 19 14:16:32 2021 +0000
@@ -0,0 +1,8 @@
+-2	-2.222
+-1	-1.999
+0	0
+1	1.0
+2	2.22
+3	3.33333
+4	4.00444
+5	5.555
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/test-data/math_output.tsv	Sat Jun 19 14:16:32 2021 +0000
@@ -0,0 +1,9 @@
+#c1	c2	trunc(c2)	floor(c2)	ceil(c2)	abs(c1)	fabs(c2)	mod(c1,2)	fmod(c2, 1.5)	sqrt(c1)	sqrt(c2)	degrees(c1)	radians(c1)	log(c1)	log10(c1)	pow(c1,2)	exp(c2)	exp(c1)	cos(c1)	sin(c1)	tan(c1)	cosh(c1)	sinh(c1)	tanh(c1)	acos(c1)	asin(c1)	acosh(c1)	asinh(c1)
+-2	-2.222	-2	-3	-2	2	2.222	0	-0.722			-114.59155902616465	-0.03490658503988659			4.0	0.10839210768078596	0.1353352832366127	-0.4161468365471424	-0.9092974268256817	2.185039863261519	3.7621956910836314	-3.6268604078470186	-0.9640275800758169				-1.4436354751788103
+-1	-1.999	-1	-2	-1	1	1.999	1	-0.4990000000000001			-57.29577951308232	-0.017453292519943295			1.0	0.13547068621005243	0.36787944117144233	0.5403023058681398	-0.8414709848078965	-1.557407724654902	1.5430806348152437	-1.1752011936438014	-0.7615941559557649	3.141592653589793	-1.5707963267948966		-0.881373587019543
+0	0.0	0	0	0	0	0.0	0	0.0	0.0	0.0	0.0	0.0			0.0	1.0	1.0	1.0	0.0	0.0	1.0	0.0	0.0	1.5707963267948966	0.0		0.0
+1	1.0	1	1	1	1	1.0	1	1.0	1.0	1.0	57.29577951308232	0.017453292519943295	0.0	0.0	1.0	2.718281828459045	2.718281828459045	0.5403023058681398	0.8414709848078965	1.557407724654902	1.5430806348152437	1.1752011936438014	0.7615941559557649	0.0	1.5707963267948966	0.0	0.881373587019543
+2	2.22	2	2	3	2	2.22	0	0.7200000000000002	1.4142135623730951	1.489966442575134	114.59155902616465	0.03490658503988659	0.6931471805599453	0.3010299956639812	4.0	9.207330865882252	7.38905609893065	-0.4161468365471424	0.9092974268256817	-2.185039863261519	3.7621956910836314	3.6268604078470186	0.9640275800758169			1.3169578969248166	1.4436354751788103
+3	3.33333	3	3	4	3	3.33333	1	0.3333300000000001	1.7320508075688772	1.8257409454793962	171.88733853924697	0.05235987755982989	1.0986122886681098	0.47712125471966244	9.0	28.03153145593222	20.085536923187668	-0.9899924966004454	0.1411200080598672	-0.1425465430742778	10.067661995777765	10.017874927409903	0.9950547536867305			1.762747174039086	1.8184464592320668
+4	4.00444	4	4	5	4	4.00444	0	1.0044399999999998	2.0	2.0011096921458353	229.1831180523293	0.06981317007977318	1.3862943611198906	0.6020599913279624	16.0	54.84110477970282	54.598150033144236	-0.6536436208636119	-0.7568024953079282	1.1578212823495775	27.308232836016487	27.289917197127753	0.999329299739067			2.0634370688955608	2.0947125472611012
+5	5.555	5	5	6	5	5.555	1	1.0549999999999997	2.23606797749979	2.356904749878535	286.4788975654116	0.08726646259971647	1.6094379124341003	0.6989700043360189	25.0	258.52696452057376	148.4131591025766	0.2836621854632263	-0.9589242746631385	-3.380515006246585	74.20994852478785	74.20321057778875	0.9999092042625951			2.2924316695611777	2.3124383412727525