| Previous changeset 11:83069b38aa85 (2021-02-12) Next changeset 13:cf34c344508d (2021-08-19) |
|
Commit message:
"planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 4fd70e184fca17ad430c30eb286127c4a198ef11" |
|
modified:
macros.xml query_db.py query_tabular.xml |
|
added:
test-data/math_input.tsv test-data/math_output.tsv |
| b |
| diff -r 83069b38aa85 -r 623f3eb7aa48 macros.xml --- a/macros.xml Fri Feb 12 21:20:32 2021 +0000 +++ b/macros.xml Sat Jun 19 14:15:41 2021 +0000 |
| b |
| @@ -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 |
| b |
| diff -r 83069b38aa85 -r 623f3eb7aa48 query_db.py --- a/query_db.py Fri Feb 12 21:20:32 2021 +0000 +++ b/query_db.py Sat Jun 19 14:15:41 2021 +0000 |
| b |
| @@ -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 |
| b |
| diff -r 83069b38aa85 -r 623f3eb7aa48 query_tabular.xml --- a/query_tabular.xml Fri Feb 12 21:20:32 2021 +0000 +++ b/query_tabular.xml Sat Jun 19 14:15:41 2021 +0000 |
| [ |
| @@ -1,4 +1,4 @@ -<tool id="query_tabular" name="Query Tabular" version="3.1.2"> +<tool id="query_tabular" name="Query Tabular" version="3.2.0"> <description>using sqlite sql</description> <macros> @@ -579,7 +579,27 @@ <output name="output" file="netMHC_summary_out2.tsv" ftype="tabular"/> </test> + <test> <!-- math functions --> + <repeat name="tables"> + <param name="table" ftype="tabular" value="math_input.tsv"/> + </repeat> + <param name="sqlquery" value="SELECT 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) FROM t1"/> + <output name="output"> + <assert_contents> + <has_text_matching expression="-2\t-2.222\t-2\t-3\t-2\t2\t2.222\t0\t-0.722\t\t\t-114.59155\d*\t-0.03490\d*\t\t\t4.0\t0.10839\d*\t0.13533\d*\t-0.41614\d*\t-0.90929\d*\t2.18503\d*\t3.76219\d*\t-3.62686\d*\t-0.96402\d*\t\t\t\t-1.44363\d*" /> + <has_text_matching expression="-1\t-1.999\t-1\t-2\t-1\t1\t1.999\t1\t-0.49900\d*\t\t\t-57.29577\d*\t-0.01745\d*\t\t\t1.0\t0.13547\d*\t0.36787\d*\t0.54030\d*\t-0.84147\d*\t-1.55740\d*\t1.54308\d*\t-1.17520\d*\t-0.76159\d*\t3.14159\d*\t-1.57079\d*\t\t-0.88137\d*" /> + <has_text_matching expression="0\t0.0\t0\t0\t0\t0\t0.0\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t\t\t0.0\t1.0\t1.0\t1.0\t0.0\t0.0\t1.0\t0.0\t0.0\t1.57079\d*\t0.0\t\t0.0" /> + <has_text_matching expression="1\t1.0\t1\t1\t1\t1\t1.0\t1\t1.0\t1.0\t1.0\t57.29577\d*\t0.01745\d*\t0.0\t0.0\t1.0\t2.71828\d*\t2.71828\d*\t0.54030\d*\t0.84147\d*\t1.55740\d*\t1.54308\d*\t1.17520\d*\t0.76159\d*\t0.0\t1.57079\d*\t0.0\t0.88137\d*" /> + <has_text_matching expression="2\t2.22\t2\t2\t3\t2\t2.22\t0\t0.72000\d*\t1.41421\d*\t1.48996\d*\t114.59155\d*\t0.03490\d*\t0.69314\d*\t0.30102\d*\t4.0\t9.20733\d*\t7.38905\d*\t-0.41614\d*\t0.90929\d*\t-2.18503\d*\t3.76219\d*\t3.62686\d*\t0.96402\d*\t\t\t1.31695\d*\t1.44363\d*" /> + <has_text_matching expression="3\t3.33333\d*\t3\t3\t4\t3\t3.33333\d*\t1\t0.33333\d*\t1.73205\d*\t1.82574\d*\t171.88733\d*\t0.05235\d*\t1.09861\d*\t0.47712\d*\t9.0\t28.03153\d*\t20.08553\d*\t-0.98999\d*\t0.14112\d*\t-0.14254\d*\t10.06766\d*\t10.01787\d*\t0.99505\d*\t\t\t1.76274\d*\t1.81844\d*" /> + <has_text_matching expression="4\t4.00444\d*\t4\t4\t5\t4\t4.00444\d*\t0\t1.00443\d*\t2.0\t2.00110\d*\t229.18311\d*\t0.06981\d*\t1.38629\d*\t0.60205\d*\t16.0\t54.84110\d*\t54.59815\d*\t-0.65364\d*\t-0.75680\d*\t1.15782\d*\t27.30823\d*\t27.28991\d*\t0.99932\d*\t\t\t2.06343\d*\t2.09471\d*" /> + <has_text_matching expression="5\t5.555\t5\t5\t6\t5\t5.555\t1\t1.05499\d*\t2.23606\d*\t2.35690\d*\t286.47889\d*\t0.08726\d*\t1.60943\d*\t0.69897\d*\t25.0\t258.52696\d*\t148.41315\d*\t0.28366\d*\t-0.95892\d*\t-3.38051\d*\t74.20994\d*\t74.20321\d*\t0.99990\d*\t\t\t2.29243\d*\t2.31243\d*" /> + </assert_contents> + </output> + </test> + </tests> + <help><![CDATA[ ============= Query Tabular |
| b |
| diff -r 83069b38aa85 -r 623f3eb7aa48 test-data/math_input.tsv --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/test-data/math_input.tsv Sat Jun 19 14:15:41 2021 +0000 |
| b |
| @@ -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 |
| b |
| diff -r 83069b38aa85 -r 623f3eb7aa48 test-data/math_output.tsv --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/test-data/math_output.tsv Sat Jun 19 14:15:41 2021 +0000 |
| b |
| @@ -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 |