Matias Carrasco Kind
Easyaccess tutorial, DES collaboration meeting, May 11-15, 2015
~ 300 DB users
Only few use quota
jython and other java tools
Installation and learning curve
No longer supported
( e.g., 'pip install easyaccess')
eups distrib install easyaccess 1.1.0+1
Thanks to: Alex, Nacho, Felipe, Don, Eli, Liz, Keith, Chris, Lorne and others...
Automatically created (if not found) by easyaccess (read/write by user only)
Check example content below (scroll down)
;
; DES services configuration
; Please modify the passwords accordingly
;
[db-dessci]
user = mcarras2
passwd = ****
name = dessci
server = leovip148.ncsa.uiuc.edu
port = 1521
[db-destest]
user = mcarras2
passwd = ****
name = destest
server = leovip148.ncsa.uiuc.edu
port = 1521
[db-desoper]
user = mcarras2
passwd = ****
name = desoper
server = leovip148.ncsa.uiuc.edu
port = 1521
Automatically created (if not found) by easyaccess
Configuration file for easyaccess (can be modified manually or from inside easyaccess)
#config.ini
#
# Easyaccess default parameters
#
# database : Default is dessci, change to desoper, destest and others
# Make sure the db-"database" section is in the .desservices.ini
# editor : Default editor to open from inside easyaccess if $EDITOR is not set
# prefetch : Prefetch number of rows to get from oracle (not the number of total rows)
# : This determine the number of trips to the DB to get all results from query
# histcache : The number of line in the history cache (when possible)
# timeout : The time in seconds before closing a connection for a query to print on screen
# If the results are redirected to a file there is not a timeout
# nullvalue : The value used to replace null or empty entries when printing into a file
# outfile_max_mb : Max size of each fits file in MB (default 1GB)
# autocommit : Auto commit changes in DB (default yes)
[easyaccess]
database = dessci
editor = nano
prefetch = 10000
histcache = 5000
timeout = 900
nullvalue = -9999
outfile_max_mb = 1000
autocommit = yes
[display]
color_terminal = yes
loading_bar = yes
max_rows = 1500
width = 1000
max_columns = 50
DESDM ~> SELECT ... FROM ... WHERE ... ;
bash$ easyaccess -c "SELECT ... FROM ... WHERE ... ;"
(Don't forget the ";" after the query)
_________
DESDB ~> select COADD_OBJECTS_ID, RA, DEC, MAG_AUTO_G, TILENAME from MINI_Y1A1 where rownum < 11; < check
Checking statement...
Ok!
_________
DESDB ~> select COADD_OBJECTS_ID, RA, DEC, MAG_AUTO_G, TILENAME from MINI_Y1A1 where rownum < 11;
|☆ | Press Ctrl-C to abort Rows : 10, Avg time (rows/sec): 73.7
10 rows in 0.14 seconds
COADD_OBJECTS_ID RA DEC MAG_AUTO_G TILENAME
1 3120938977 9.553670 -52.858189 99.0000 DES0037-5248
2 3120944503 9.999752 -52.754332 22.4878 DES0037-5248
3 3120954907 9.114992 -52.555158 25.3244 DES0037-5248
4 3120936831 8.994403 -52.899365 24.9927 DES0037-5248
5 3120952757 9.427335 -52.595066 23.6036 DES0037-5248
6 3120045269 10.008397 -42.256504 24.9008 DES0041-4206
7 3120047663 10.084474 -47.183702 24.6363 DES0041-4706
8 3120047896 10.556631 -47.180639 23.4764 DES0041-4706
9 3120055956 10.197952 -47.100950 26.3588 DES0041-4706
10 3120066754 10.863236 -46.987299 21.7274 DES0041-4706
(Press down arrow for more and get insight from commands)
type help {command} for more info. e.g. DESDM ~> help prefetch
_________
DESDB ~> help
\‾‾‾‾‾‾\
// / . .\
// / . _\
// / . / //
\\ \ . / //
\\ \_____/ //
\\_______// DARK ENERGY SURVEY
`‾‾‾‾‾‾‾´ DATA MANAGEMENT
*General Commands* (type help ):
===================================================
clear config edit exit help history prefetch shell
*DB Commands* (type help ):
====================================================
add_comment find_user mytables show_index
describe_table load_table refresh_metadata_cache user_tables
find_tables loadsql set_password whoami
find_tables_with_column myquota show_db
*Default Input*
===================================================
* To run SQL queries just add ; at the end of query
* To write to a file : select ... from ... where ... ; > filename
* Supported file formats (.csv, .tab., .fits, .h5)
* To check SQL syntax : select ... from ... where ... ; < check
* To access an online tutorial type: online_tutorial
_________
DESDB ~>
e.g. to disable loading bar : DESDM ~> config loading_bar set no
_________
DESDB ~> config
Change parameters from config file (config.ini). Smart autocompletion enabled
Usage:
- config show : Shows current value for parameter in config file
e.g.
- config set : Sets parameter to given value
- config all show: Shows all parameters and their values
- config filepath: Prints the path to the config file
Parameters:
database : Default DB to connect to
editor : Editor for editing sql queries, see --> help edit
prefetch : Number of rows prefetched by Oracle, see --> help prefetch
histcache : Length of the history of commands
timeout : Timeout for a query to be printed on the screen. Doesn't apply to output files
nullvalue : value to replace Null entries when writing a file (default = -9999)
outfile_max_mb : Max size of each fits file in MB
max_rows : Max number of rows to display on the screen. Doesn't apply to output files
width : Width of the output format on the screen
max_columns : Max number of columns to display on the screen. Doesn't apply to output files
color_terminal : yes/no toggles the color for terminal std output. Need to restart easyaccess
loading_bar : yes/no toggles the loading bar. Useful for background jobs
autocommit : yes/no toggles the autocommit for DB changes (default is yes)
Clears the screen
Opens up an editor window to write query
Exits easyaccess and save changes and history
Prints the history of the commands prompted
Access the shell, e.g. DESDM ~> shell pwd, or DESDM ~> !pwd
Shows, sets or sets to default the number of prefetch rows from Oracle
_________
DESDB ~> describe_table MINI_Y1A1
Rows : 10, Avg time (rows/sec): 159.5
Description of MINI_Y1A1 commented as: '"small random subsample from Y1A1"'
COLUMN_NAME DATA_TYPE DATA_FORMAT COMMENTS
1 COADD_OBJECTS_ID NUMBER 22,11,0 Null
2 RA NUMBER 22,9,6 Null
3 DEC NUMBER 22,9,6 Null
4 MAG_AUTO_G NUMBER 22,6,4 Null
5 MAG_AUTO_R NUMBER 22,6,4 Null
6 MAG_AUTO_I NUMBER 22,6,4 Null
7 TILENAME VARCHAR2 12,, Null
8 MAGERR_DETMODEL_I VARCHAR2 30,, Null
9 FLAGS_I NUMBER 22,3,0 Null
10 XCORR_SFD98_G NUMBER 22,10,6 Null
_________
DESDB ~>
_________
DESDB ~> find_tables %Y1A1%
Rows : 23, Avg time (rows/sec): 334.8
23 rows in 0.07 seconds
TABLE_NAME
1 Y1A1_RUNTAG
2 Y1A1_MOLYGON
3 Y1A1_MANGLE_RUN
4 Y1A1_FINALCUT
5 Y1A1_COADD_PHOTOZ
6 Y1A1_CCDGON
7 Y1A1_COADD
8 Y1A1_PSF_QA
9 Y1A1_OBJECTS
10 Y1A1_COADD_SPT
11 Y1A1_MOLYGON_CCDGON
12 Y1A1_COADD_OBJECTS_D04
13 Y1A1_CATALOG
14 Y1A1_COADD_SRC
15 Y1A1_COADD_OBJECTS
16 Y1A1_COADD_OBJECTS_DFULL
17 MINI_Y1A1
18 Y1A1_EXPOSURETAG
19 Y1A1_EXPOSURE
20 Y1A1_COADD_STRIPE82
21 Y1A1_COADD_OBJECTS_D10
22 Y1A1_FIRSTCUT_EVAL
23 Y1A1_IMAGE
_________
DESDB ~> find_tables_with_column %TPZ%
| ☆ | Press Ctrl-C to abort Rows : 26, Avg time (rows/sec): 18.7
26 rows in 1.40 seconds
TABLE_NAME COLUMN_NAME
1 SVA1_GOLD_PHOTOZ TPZ_ZCONF
2 SVA1_GOLD_PHOTOZ TPZ_ZP
3 SVA1_GOLD_PHOTOZ TPZ_ZPERR
4 SVA1_GOLD_PHOTOZ_PDF TPZ
5 SVA1_GOLD_PHOTOZ_SPARSE TPZ
6 TPZ_SVA1_GOLD TPZ_SG_CLASS
7 TPZ_SVA1_GOLD TPZ_ZCONF
8 TPZ_SVA1_GOLD TPZ_ZERR
9 TPZ_SVA1_GOLD TPZ_ZPHOT
10 Y1A1_COADD_PHOTOZ TPZ_ZCONF
11 Y1A1_COADD_PHOTOZ TPZ_ZP
12 Y1A1_COADD_PHOTOZ TPZ_ZPERR
13 ZBINS TPZ_ZVALS
14 mcarras2.SVA1_GOLD_PHOTOZ TPZ_ZCONF
15 mcarras2.SVA1_GOLD_PHOTOZ TPZ_ZP
16 mcarras2.SVA1_GOLD_PHOTOZ TPZ_ZPERR
17 mcarras2.SVA1_GOLD_PHOTOZ_PDF TPZ
18 mcarras2.SVA1_GOLD_PHOTOZ_SPARSE TPZ
19 mcarras2.TPZ_SVA1_GOLD TPZ_SG_CLASS
20 mcarras2.TPZ_SVA1_GOLD TPZ_ZCONF
21 mcarras2.TPZ_SVA1_GOLD TPZ_ZERR
22 mcarras2.TPZ_SVA1_GOLD TPZ_ZPHOT
23 mcarras2.Y1A1_COADD_PHOTOZ TPZ_ZCONF
24 mcarras2.Y1A1_COADD_PHOTOZ TPZ_ZP
25 mcarras2.Y1A1_COADD_PHOTOZ TPZ_ZPERR
26 mcarras2.ZBINS TPZ_ZVALS
_________
DESDB ~> find_user sevilla
| ☆ | Press Ctrl-C to abort Rows : 1, Avg time (rows/sec): 3.6
1 rows in 0.28 seconds
USERNAME EMAIL FIRSTNAME LASTNAME
1 nsevilla nsevilla@gmail.com Ignacio Sevilla
_________
DESDB ~> user_tables nsevilla
Public tables from NSEVILLA
0
0 SVA1_GOLD_REP
1 Y1A1_STRIPE82_MATCHES_V1
2 Y1A1_STRIPE82_MATCHES_V2
_________
DESDB ~>
_________
DESDB ~> whoami
Rows : 1, Avg time (rows/sec): 16.0
USERNAME EMAIL FIRSTNAME LASTNAME
1 mcarras2 mcarras2@ncsa.illinois.edu Matias Kind
_________
DESDB ~> myquota
| ☆ | Press Ctrl-C to abort Rows : 1, Avg time (rows/sec): 1.6
TABLESPACE_NAME GBYTES_USED GBYTES_LEFT
1 USERS 34.363403 215.636597
_________
DESDB ~> mytables
| ☆ | Press Ctrl-C to abort Rows : 11, Avg time (rows/sec): 22.1
List of my tables
TABLE_NAME
1 ZBINS
2 SVA1_GOLD_PHOTOZ_SPARSE
3 SVA1_GOLD_PHOTOZ
4 MINI_Y1A1
5 FGOTTENMETADATA
6 Y1A1_COADD_PHOTOZ
7 SPARSE_DICT
8 MY_TABLE
9 SPEC_MATCHED
10 SVA1_GOLD_PHOTOZ_PDF
11 TPZ_SVA1_GOLD
_________
DESDB ~>
_________
DESDB ~> show_db
user: MCARRAS2
db : DESSCI
host: LEOVIP148.NCSA.UIUC.EDU
Personal links:
OWNER DB_LINK USERNAME HOST CREATED
No rows selected
_________
DESDB ~> set_password
Enter new password:
Re-Enter new password:
Password changed in DESSCI
Password changed in DESOPER
_________
DESDB ~>
This refresh the cache with tables, columns and users for autocompletion
_________
DESDB ~> refresh_metadata_cache
Updated 11.33 hours ago
Re-creating metadata table ...
| ☆ | Press Ctrl-C to abort
FGOTTENMETADATA table Created!
Loading metadata into cache...
_________
DESDB ~>
Describes the indices in a TABLE as column_name, oracle_Type, date_length, comments
_________
DESDB ~> show_index Y1A1_COADD_PHOTOZ
| ☆ | Press Ctrl-C to abort Rows : 1, Avg time (rows/sec): 2.3
1 rows in 0.44 seconds
TABLE_NAME COLUMN_NAME INDEX_TYPE INDEX_NAME ITYP_NAME
1 Y1A1_COADD_PHOTOZ COADD_OBJECTS_ID NORMAL SYS_C001624313 Null
To download a table from DB, to upload tables from files, to add comments
(Press down arrow)
DESDM ~> SELECT ... FROM ... WHERE ... ; > file.csv
Supported output files (.csv, .fits, .tab, .h5)
_________
DESDB ~> select COADD_OBJECTS_ID, RA, DEC, MAG_AUTO_G, TILENAME from MINI_Y1A1 where rownum < 11; > test.tab
Fetching data and saving it to test.tab ...
| ☆ | Press Ctrl-C to abort Rows : 10, Avg time (rows/sec): 9.3
Written 10 rows to test.tab in 1.08 seconds and 1 trips
_________
DESDB ~> shell more test.tab
COADD_OBJECTS_ID RA DEC MAG_AUTO_G TILENAME
3120938977 9.55367000 -52.85818900 99.00000000 DES0037-5248
3120944503 9.99975200 -52.75433200 22.48780060 DES0037-5248
3120954907 9.11499200 -52.55515800 25.32439995 DES0037-5248
3120936831 8.99440300 -52.89936500 24.99270058 DES0037-5248
3120952757 9.42733500 -52.59506600 23.60359955 DES0037-5248
3120045269 10.00839700 -42.25650400 24.90080070 DES0041-4206
3120047663 10.08447400 -47.18370200 24.63629913 DES0041-4706
3120047896 10.55663100 -47.18063900 23.47640038 DES0041-4706
3120055956 10.19795200 -47.10095000 26.35880089 DES0041-4706
3120066754 10.86323600 -46.98729900 21.72739983 DES0041-4706
_________
DESDB ~> !head -n 2 test.tab
COADD_OBJECTS_ID RA DEC MAG_AUTO_G TILENAME
3120938977 9.55367000 -52.85818900 99.00000000 DES0037-5248
_________
DESDB ~> !more michigan.csv
COADD_OBJECTS_ID,RA,DEC,MAG_AUTO_G,TILENAME
3120938977,9.55367000,-52.85818900,99.00000000,DES0037-5248
3120944503,9.99975200,-52.75433200,22.48780060,DES0037-5248
3120954907,9.11499200,-52.55515800,25.32439995,DES0037-5248
3120936831,8.99440300,-52.89936500,24.99270058,DES0037-5248
3120952757,9.42733500,-52.59506600,23.60359955,DES0037-5248
3120045269,10.00839700,-42.25650400,24.90080070,DES0041-4206
3120047663,10.08447400,-47.18370200,24.63629913,DES0041-4706
3120047896,10.55663100,-47.18063900,23.47640038,DES0041-4706
3120055956,10.19795200,-47.10095000,26.35880089,DES0041-4706
3120066754,10.86323600,-46.98729900,21.72739983,DES0041-4706
_________
DESDB ~> load_table michigan.csv
Table MICHIGAN created successfully with 10 rows and 5 columns in 0.35 seconds
You might want to refresh the metadata (refresh_metadata_cache)
so your new table appears during autocompletion
To make this table public run:
grant select on MICHIGAN to DES_READER;
_________
DESDB ~> describe_table michigan
| ☆ | Press Ctrl-C to abort Rows : 5, Avg time (rows/sec): 9.0
Description of MICHIGAN commented as: 'None'
COLUMN_NAME DATA_TYPE DATA_FORMAT COMMENTS
1 COADD_OBJECTS_ID NUMBER 22,,0 Null
2 RA BINARY_DOUBLE 8,, Null
3 DEC BINARY_DOUBLE 8,, Null
4 MAG_AUTO_G BINARY_DOUBLE 8,, Null
5 TILENAME VARCHAR2 12,, Null
_________
DESDB ~> select * from michigan
Invalid command or missing ; at the end of query.
Type help or ? to list commands
_________
DESDB ~> select * from michigan;
| ☆ | Press Ctrl-C to abort Rows : 10, Avg time (rows/sec): 38.6
10 rows in 0.26 seconds
COADD_OBJECTS_ID RA DEC MAG_AUTO_G TILENAME
1 3120938977 9.553670 -52.858189 99.000000 DES0037-5248
2 3120944503 9.999752 -52.754332 22.487801 DES0037-5248
3 3120954907 9.114992 -52.555158 25.324400 DES0037-5248
4 3120936831 8.994403 -52.899365 24.992701 DES0037-5248
5 3120952757 9.427335 -52.595066 23.603600 DES0037-5248
6 3120045269 10.008397 -42.256504 24.900801 DES0041-4206
7 3120047663 10.084474 -47.183702 24.636299 DES0041-4706
8 3120047896 10.556631 -47.180639 23.476400 DES0041-4706
9 3120055956 10.197952 -47.100950 26.358801 DES0041-4706
10 3120066754 10.863236 -46.987299 21.727400 DES0041-4706
Add comments to columns in table created
Add comments to table itself
_________
DESDB ~> describe_table michigan
| ☆| Press Ctrl-C to abort Rows : 5, Avg time (rows/sec): 3.3
Description of MICHIGAN commented as: 'None'
COLUMN_NAME DATA_TYPE DATA_FORMAT COMMENTS
1 COADD_OBJECTS_ID NUMBER 22,,0 Null
2 RA BINARY_DOUBLE 8,, Null
3 DEC BINARY_DOUBLE 8,, Null
4 MAG_AUTO_G BINARY_DOUBLE 8,, Null
5 TILENAME VARCHAR2 12,, Null
_________
DESDB ~> add_comment column MICHIGAN.COADD_OBJECTS_ID id
| ☆ | Press Ctrl-C to abort
Comment added to column: COADD_OBJECTS_ID in table MICHIGAN
_________
DESDB ~> add_comment column MICHIGAN.ra position in the sky -- ra
| ☆ | Press Ctrl-C to abort
Comment added to column: ra in table MICHIGAN
_________
DESDB ~> add_comment column MICHIGAN.dec position in the sky -- dec
| ☆ | Press Ctrl-C to abort
Comment added to column: dec in table MICHIGAN
_________
DESDB ~> add_comment column MICHIGAN.tilename This is the number of the tile
| ☆ | Press Ctrl-C to abort
Comment added to column: tilename in table MICHIGAN
_________
DESDB ~> add_comment table MICHIGAN example table
|☆ | Press Ctrl-C to abort
Comment added to table: MICHIGAN
_________
DESDB ~> describe_table MICHIGAN
| ☆ | Press Ctrl-C to abort Rows : 5, Avg time (rows/sec): 13.1
Description of MICHIGAN commented as: 'example table'
COLUMN_NAME DATA_TYPE DATA_FORMAT COMMENTS
1 COADD_OBJECTS_ID NUMBER 22,,0 id
2 RA BINARY_DOUBLE 8,, position in the sky -- ra
3 DEC BINARY_DOUBLE 8,, position in the sky -- dec
4 MAG_AUTO_G BINARY_DOUBLE 8,, Null
5 TILENAME VARCHAR2 12,, This is the number of the tile
_________
DESDB ~>
-- sql query example
-- These are comments
SELECT
T1.COADD_OBJECTS_ID, --id
T1.RA, --ra
T1.DEC, --dec
T1.MAG_AUTO_G-T1.XCORR_SFD98_G G, --extinction corrected
T1.MAGERR_DETMODEL_I, --I no corrected
T2.DESDM_ZP,--photoz
T2.DESDM_ZPERR --photoz-err
FROM
MINI_Y1A1 T1, --sub sample from y1a1
Y1A1_COADD_PHOTOZ T2 -- photoz table
WHERE
T2.COADD_OBJECTS_ID = T1.COADD_OBJECTS_ID; --matching by ids
_________
DESDB ~> loadsql test.sql
SELECT T1.COADD_OBJECTS_ID, T1.RA, T1.DEC, T1.MAG_AUTO_G-T1.XCORR_SFD98_G G, T1.MAGERR_DETMODEL_I, T2.DESDM_ZP, T2.DESDM_ZPERR FROM MINI_Y1A1 T1, Y1A1_COADD_PHOTOZ T2 WHERE T2.COADD_OBJECTS_ID = T1.COADD_OBJECTS_ID;
submit query? (Y/N): y
| ☆ | Press Ctrl-C to abort Rows : 18, Avg time (rows/sec): 1.4
18 rows in 12.93 seconds
COADD_OBJECTS_ID RA DEC G MAGERR_DETMODEL_I DESDM_ZP DESDM_ZPERR
1 3120938977 9.553670 -52.858189 98.936168 20141118000041_DES0037-5248 0.053050 0.15650
2 3120944503 9.999752 -52.754332 22.431785 20141118000041_DES0037-5248 0.267907 0.11793
3 3120954907 9.114992 -52.555158 25.263235 20141118000041_DES0037-5248 0.682050 0.06274
4 3120936831 8.994403 -52.899365 24.932943 20141118000041_DES0037-5248 0.562024 0.13328
5 3120952757 9.427335 -52.595066 23.547770 20141118000041_DES0037-5248 1.004921 0.08233
6 3120045269 10.008397 -42.256504 24.859159 20141118000041_DES0041-4206 0.985158 0.15018
7 3120047663 10.084474 -47.183702 24.589065 20141118000041_DES0041-4706 0.894567 0.08141
8 3120047896 10.556631 -47.180639 23.428831 20141118000041_DES0041-4706 0.550385 0.05724
9 3120055956 10.197952 -47.100950 26.315677 20141118000041_DES0041-4706 0.434088 0.12292
10 3120066754 10.863236 -46.987299 21.691686 20141118000041_DES0041-4706 0.378991 0.11451
11 3120060168 10.673803 -47.056676 23.599043 20141118000041_DES0041-4706 0.481716 0.08525
12 3120064061 10.250745 -47.011011 24.605948 20141118000041_DES0041-4706 0.804396 0.09282
13 3121043108 9.145812 -41.983799 22.939673 20141118000041_DES0037-4206 0.486377 0.10291
14 3121052639 9.156004 -41.756442 20.893839 20141118000041_DES0037-4206 0.144946 0.05946
15 3121214820 9.573569 -41.374574 23.683248 20141118000041_DES0036-4123 0.630103 0.03789
16 3121235477 9.589115 -41.156993 22.892964 20141118000041_DES0036-4123 0.945554 0.06161
17 3121191613 8.979183 -41.611909 25.117038 20141118000041_DES0036-4123 0.854403 0.11231
18 3121189323 9.657756 -41.648061 24.081666 20141118000041_DES0036-4123 0.982048 0.11320
On how to run easyaccess commands from inside a python script
# # easyaccess python API
import easyaccess as ea
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
connection=ea.connect()
# ###Using cursor
cursor=connection.cursor() ##create a cursor object to handle the DB
query='select * from mini_y1a1' ##query
QQ=cursor.execute(query) #execute query
QQ.description
header = [item[0] for item in cursor.description]
rows = cursor.fetchall() ## Bring the data
cols = np.array(zip(*rows))
# ### Using easyaccess pre-defined
DF=connection.query_to_pandas(query) #one line!
pd.set_option('display.max_rows',12) #Display only
sns.set_style("whitegrid")
temp=sns.JointGrid("MAG_AUTO_G", "MAG_AUTO_R",DF,xlim=[20,27],ylim=[20,27])
temp.plot_joint(sns.kdeplot,cmap='jet',clip=[(20,27),(20,27)])
temp.plot_joint(plt.scatter, s= .3,color='black')
temp.plot_marginals(sns.distplot, color='green',kde=False)
sns.distplot(DF.MAG_AUTO_I[(DF.MAG_AUTO_I>15) & (DF.MAG_AUTO_I<28)],color='red')
# ## Save to a file
connection.query_and_save(query,'mytable.csv') #same formatting
# ## Upload table
connection.load_table('mytable.csv',name='test_api')
connection.mytables()
# ## Load SQL
query2=connection.loadsql('test.sql')
print query2
# ## ping the DB
connection.ping()
cursor.execute('drop table test_api')
connection.mytables()
# ## Running easyaccess commands
connection.onecmd('help')
connection.onecmd('whoami')
connection.close()
connection.ping()