make jdbc calls in command line
Project description
doc on github.io
dbx
dbx is the command line tool included in py-dbx package. It was named sretools-dbx as you can see in below examples until fully updated.
you run dbx or python -mdbx other than sretools-dbx in the past.
Installation
pip install py-dbx
run with python -mdbx or just dbx
dbx (sretools-dbx)
sretools-dbx is a generic database client using JDBC. It's not designed to replace any current factory client but to provide people who work under terminals a new way to access their data with better console output, and ablility to export as JSON, YAML or HTML tables.
It's at command level which means it's open to full bash access and one can always utilize his or her expertis on unix tools.
$ sretools-dbx -h
usage: sretools-dbx [-h] [-J JAR] [-u USER] [-p PASSWORD] [-D DRIVER] [-U URL] [-C] [-Q SQL] [-w MAXWIDTH] [-F FORMAT] [-v] [-X]
optional arguments:
-h, --help show this help message and exit
-J JAR, --jar JAR jdbc driver jar file
-u USER, --user USER user. default as current unix user.
-p PASSWORD, --password PASSWORD
password. ENV["dbx_mypwd"]
-D DRIVER, --driver DRIVER
jdbc driver name
-U URL, --url URL jdbc url or connection string
-C, --connect force connect as server
-Q SQL, --sql SQL sql statement to run
-w MAXWIDTH, --maxwidth MAXWIDTH
maxwidth of column
-F FORMAT, --outformat FORMAT
json,yaml,csv,html
-v, --pivot pivot the view
-X, --debug debug mode
Tests with DB2, Postgresql and MySQL
connect to database (-C)
user/passwod can be specifed in URL/connection string. or explicitly specified. if not specified, dbx will try to use current unix user; as to password, it will check environment variable dbx_password then will prompt. in case ssl being used and not password needed, use 'dummy' for password.
$ # all in connection string
$ sretools-dbx -J ~/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U "jdbc:mysql://localhost:3306/mysql?user=yonghang&password=password&allowPublicKeyRetrieval=true&useSSL=false" -C
# DBXServer 972045@/home/yonghang/.cache/sretools/.dbx.971825
$ ps -ef | grep dbx | grep -v grep
yonghang 972045 1 21 10:24 pts/1 00:00:01 /usr/bin/python3 /usr/local/bin/sretools-dbx -J /home/yonghang/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U jdbc:mysql://localhost:3306/mysql?user=yonghang&password=password&allowPublicKeyRetrieval=true&useSSL=false -C
$ # or
$ sretools-dbx -J ~/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U "jdbc:mysql://localhost:3306/mysql?allowPublicKeyRetrieval=true&useSSL=false" -u yonghang -p password -C
# DBXServer 972146@/home/yonghang/.cache/sretools/.dbx.971825
$ ps -ef | grep dbx | grep -v grep
yonghang 972146 1 88 10:24 pts/1 00:00:01 /usr/bin/python3 /usr/local/bin/sretools-dbx -J /home/yonghang/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U jdbc:mysql://localhost:3306/mysql?allowPublicKeyRetrieval=true&useSSL=false -u yonghang -p password -C
$ # or
$ export dbx_password=password
$ sretools-dbx -J ~/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U "jdbc:mysql://localhost:3306/mysql?allowPublicKeyRetrieval=true&useSSL=false" -u yonghang -C
# DBXServer 972374@/home/yonghang/.cache/sretools/.dbx.971825
$ ps -ef | grep dbx | grep -v grep
yonghang 972374 1 32 10:25 pts/1 00:00:01 /usr/bin/python3 /usr/local/bin/sretools-dbx -J /home/yonghang/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U jdbc:mysql://localhost:3306/mysql?allowPublicKeyRetrieval=true&useSSL=false -u yonghang -C
disconnect against database
send "\q" will cause disconnection from the database. note, dbx can get the SQL to run with -Q or from unix pipe.
$ ps -ef | grep dbx | grep -v grep
yonghang 972374 1 0 10:25 pts/1 00:00:01 /usr/bin/python3 /usr/local/bin/sretools-dbx -J /home/yonghang/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U jdbc:mysql://localhost:3306/mysql?allowPublicKeyRetrieval=true&useSSL=false -u yonghang -C
$ sretools-dbx -Q "\q"
$ ps -ef | grep dbx | grep -v grep
$ # or
$ ps -ef | grep dbx | grep -v grep
yonghang 973586 1 89 10:31 pts/1 00:00:01 /usr/bin/python3 /usr/local/bin/sretools-dbx -J /home/yonghang/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U jdbc:mysql://localhost:3306/mysql?allowPublicKeyRetrieval=true&useSSL=false -u yonghang -C
$ echo "\q" | sretools-dbx
$ ps -ef | grep dbx | grep -v grep
$
Query database
use mysql as example
$ sretools-dbx -J /home/yonghang/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U 'jdbc:mysql://localhost:3306/mysql?user=yonghang&password=password&allowPublicKeyRetrieval=true&useSSL=false' -C
# DBXServer 974814@/home/yonghang/.cache/sretools/.dbx.971825
$ echo "select user,host,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Create_priv,Drop_priv from mysql.user limit 2" | sretools-dbx
user host Select_priv Insert_priv Update_priv Delete_priv Create_priv Create_priv Drop_priv
-----------------------------------------------------------------------------------------------
root % Y Y Y Y Y Y Y
yonghang % Y Y Y Y Y Y Y
$ # for very wide table, it's easier to pivot the result with -v
$ echo "select user,host,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Create_priv,Drop_priv from mysql.user limit 2" | sretools-dbx -v
user : root
host : %
Select_priv : Y
Insert_priv : Y
Update_priv : Y
Delete_priv : Y
Create_priv : Y
Create_priv : Y
Drop_priv : Y
--
user : yonghang
host : %
Select_priv : Y
Insert_priv : Y
Update_priv : Y
Delete_priv : Y
Create_priv : Y
Create_priv : Y
Drop_priv : Y
$
$ echo "select table_schema,table_name,view_definition from information_schema.views limit 2" | sretools-dbx
TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sys version select '2.1.1' AS `sys_version`,version() AS `mysql_version`
sys innodb_buffer_stats_by_schema select if((locate('.',`ibp`.`TABLE_NAME`) = 0),'InnoDB System',replace(substring_index(`ibp`.`TABLE_NAME`,'.',1),'`','')) AS `object_schema`,format_bytes(sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`))) AS `allocated`,format_bytes(sum(`ibp`.`DATA_SIZE`)) AS `data`,count(`ibp`.`PAGE_NUMBER`) AS `pages`,count(if((`ibp`.`IS_HASHED` = 'YES'),1,NULL)) AS `pages_hashed`,count(if((`ibp`.`IS_OLD` = 'YES'),1,NULL)) AS `pages_old`,round((sum(`ibp`.`NUMBER_RECORDS`) / count(distinct `ibp`.`INDEX_NAME`)),0) AS `rows_cached` from `information_schema`.`INNODB_BUFFER_PAGE` `ibp` where (`ibp`.`TABLE_NAME` is not null) group by `object_schema` order by sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`)) desc
$
$ # limit max col width to 80 for better look
$ echo "select table_schema,table_name,view_definition from information_schema.views limit 2" | sretools-dbx -w80
TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION
---------------------------------------------------------------------------------------------------------------------------
sys version select '2.1.1' AS `sys_version`,version() AS `mysql_version`
sys innodb_buffer_stats_by_schema select if((locate('.',`ibp`.`TABLE_NAME`) = 0),'InnoDB System',replace(substring
_index(`ibp`.`TABLE_NAME`,'.',1),'`','')) AS `object_schema`,format_bytes(sum(if
((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`))) AS `allocated`,f
ormat_bytes(sum(`ibp`.`DATA_SIZE`)) AS `data`,count(`ibp`.`PAGE_NUMBER`) AS `pag
es`,count(if((`ibp`.`IS_HASHED` = 'YES'),1,NULL)) AS `pages_hashed`,count(if((`i
bp`.`IS_OLD` = 'YES'),1,NULL)) AS `pages_old`,round((sum(`ibp`.`NUMBER_RECORDS`)
/ count(distinct `ibp`.`INDEX_NAME`)),0) AS `rows_cached` from `information_sch
ema`.`INNODB_BUFFER_PAGE` `ibp` where (`ibp`.`TABLE_NAME` is not null) group by
`object_schema` order by sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPR
ESSED_SIZE`)) desc
in mysql, the view definition text is kind of '1 line' text which is not good for reading. most likely we may have line breaks there. below let's look at another example for the formatting part. the new example is with ibm-db2.
$ sretools-dbx -J /home/yonghang/jdbc/db2jcc4.jar -D com.ibm.db2.jcc.DB2Driver -U "jdbc:db2://localhost:50000/sample:user=db2inst1;password=db2inst1;" -C
# DBXServer 978653@/home/yonghang/.cache/sretools/.dbx.971825
$
$ echo "select viewschema,viewname,varchar(text) as text from syscat.views fetch first 1 rows only with ur" | sretools-dbx
VIEWSCHEMA VIEWNAME TEXT
-------------------------------------------------------------------------------------------------------
SYSIBM CHECK_CONSTRAINTS CREATE OR REPLACE VIEW SYSIBM.CHECK_CONSTRAINTS
(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE)
AS SELECT
CAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR,
CAST(NAME AS VARCHAR(128)), TEXT
FROM SYSIBM.SYSCHECKS
WHERE TYPE='C'
UNION ALL
SELECT CAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR,
CAST(CONCAT(RTRIM(CONCAT(CHAR(CTIME), CHAR(FID) ) ),
RTRIM(CHAR(COLNO)) ) AS VARCHAR(128) ),
CAST(CONCAT(CONCAT('CHECK (', C.NAME), ' IS NOT NULL)') AS CLOB(64K) )
FROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T
WHERE C.TBCREATOR = T.CREATOR AND C.TBNAME = T.NAME AND TYPE IN('U', 'T')
AND NULLS ='N'
export to JSON,YAML or HTML
$ echo "select viewschema,viewname,varchar(text) as text from syscat.views fetch first 1 rows only with ur" | sretools-dbx -Fjson
[
{
"VIEWSCHEMA": "SYSIBM ",
"VIEWNAME": "CHECK_CONSTRAINTS",
"TEXT": "CREATE OR REPLACE VIEW SYSIBM.CHECK_CONSTRAINTS \n(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE) \nAS SELECT \nCAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR, \nCAST(NAME AS VARCHAR(128)), TEXT \nFROM SYSIBM.SYSCHECKS \nWHERE TYPE='C' \nUNION ALL \nSELECT CAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR, \nCAST(CONCAT(RTRIM(CONCAT(CHAR(CTIME), CHAR(FID) ) ), \nRTRIM(CHAR(COLNO)) ) AS VARCHAR(128) ), \nCAST(CONCAT(CONCAT('CHECK (', C.NAME), ' IS NOT NULL)') AS CLOB(64K) ) \nFROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T \nWHERE C.TBCREATOR = T.CREATOR AND C.TBNAME = T.NAME AND TYPE IN('U', 'T') \nAND NULLS ='N'\n"
}
]
$ echo "select viewschema,viewname,varchar(text) as text from syscat.views fetch first 1 rows only with ur" | sretools-dbx -Fyaml
- TEXT: "CREATE OR REPLACE VIEW SYSIBM.CHECK_CONSTRAINTS \n(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,\
\ CONSTRAINT_NAME, CHECK_CLAUSE) \nAS SELECT \nCAST(CURRENT SERVER AS VARCHAR(128)),\
\ TBCREATOR, \nCAST(NAME AS VARCHAR(128)), TEXT \nFROM SYSIBM.SYSCHECKS \nWHERE\
\ TYPE='C' \nUNION ALL \nSELECT CAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR,\
\ \nCAST(CONCAT(RTRIM(CONCAT(CHAR(CTIME), CHAR(FID) ) ), \nRTRIM(CHAR(COLNO))\
\ ) AS VARCHAR(128) ), \nCAST(CONCAT(CONCAT('CHECK (', C.NAME), ' IS NOT NULL)')\
\ AS CLOB(64K) ) \nFROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T \nWHERE C.TBCREATOR\
\ = T.CREATOR AND C.TBNAME = T.NAME AND TYPE IN('U', 'T') \nAND NULLS ='N'\n"
VIEWNAME: CHECK_CONSTRAINTS
VIEWSCHEMA: 'SYSIBM '
$
$
$ echo "select viewschema,viewname,varchar(text) as text from syscat.views fetch first 1 rows only with ur" | sretools-dbx -Fhtml
<table border=1 style="border-collapse:collapse;">
<tr>
<td><b>VIEWSCHEMA</b></td>
<td><b>VIEWNAME</b></td>
<td><b>TEXT</b></td>
</tr>
<tr>
<td>SYSIBM </td>
<td>CHECK_CONSTRAINTS</td>
<td>CREATE OR REPLACE VIEW SYSIBM.CHECK_CONSTRAINTS <br>(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE) <br>AS SELECT <br>CAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR, <br>CAST(NAME AS VARCHAR(128)), TEXT <br>FROM SYSIBM.SYSCHECKS <br>WHERE TYPE='C' <br>UNION ALL <br>SELECT CAST(CURRENT SERVER AS VARCHAR(128)), TBCREATOR, <br>CAST(CONCAT(RTRIM(CONCAT(CHAR(CTIME), CHAR(FID) ) ), <br>RTRIM(CHAR(COLNO)) ) AS VARCHAR(128) ), <br>CAST(CONCAT(CONCAT('CHECK (', C.NAME), ' IS NOT NULL)') AS CLOB(64K) ) <br>FROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T <br>WHERE C.TBCREATOR = T.CREATOR AND C.TBNAME = T.NAME AND TYPE IN('U', 'T') <br>AND NULLS ='N'<br></td>
</tr>
use plugin for sql shortcut
plugin is YAML file including queries. I made a random example from some SQL from internet as below.
generated YAML file,
mysql:
db:
usage: '
SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb")
total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")
data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0)
pct_used
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC
'
perf: {}
tablespace:
usage: '
SELECT s.schema_name, table_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb")
total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")
data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0)
pct_used
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
ORDER BY total_size DESC
'
user: select user,host from mysql.user
then load the YAML plugin with -P. This can be loaded when starting the connection then all following connections can use the commands defined. Or you can specify the plugin for each run -- this also means you can use differnt plugins.
$ sretools-dbx -J ~/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U "jdbc:mysql://localhost:3306/mysql?user=yonghang&password=password&allowPublicK
eyRetrieval=true&useSSL=false" -C
# DBXServer 1071685@/home/yonghang/.cache/sretools/.dbx.1065306
$ echo "\mysql.user" | ./dbx.py -P test/test.plugin.yaml
user host
--------------------------
root %
yonghang %
mysql.infoschema localhost
mysql.session localhost
mysql.sys localhost
root localhost
$ sretools-dbx -P test/test.plugin.yaml -J ~/jdbc/mariadb-java-client-2.7.1.jar -D org.mariadb.jdbc.Driver -U "jdbc:mysql://localhost:3306/mysql?user=yonghang&pass
word=password&allowPublicKeyRetrieval=true&useSSL=false" -C
# DBXServer 1071773@/home/yonghang/.cache/sretools/.dbx.1065306
$
$ echo "\mysql.user" | ./dbx.py
user host
--------------------------
root %
yonghang %
mysql.infoschema localhost
mysql.session localhost
mysql.sys localhost
root localhost
$
$ echo "\mysql.db.usage" | ./dbx.py
SCHEMA_NAME total_size data_used data_free pct_used
----------------------------------------------------------
mysql 7.70Mb -120.30Mb 128.00Mb -1561.66
sys 0.02Mb 0.02Mb 0.00Mb 100.0
sample 0.02Mb 0.02Mb 0.00Mb 100.0
information_schema 0.00Mb 0.00Mb 0.00Mb 0.0
performance_schema 0.00Mb 0.00Mb 0.00Mb 0.0
$
$ echo "\mysql.tablespace.usage" | ./dbx.py
SCHEMA_NAME TABLE_NAME total_size data_used data_free pct_used
---------------------------------------------------------------------------------------------------------------
mysql time_zone_transition 4.52Mb 0.52Mb 4.00Mb 11.42
mysql help_topic 1.61Mb -2.39Mb 4.00Mb -148.54
mysql time_zone_transition_type 0.44Mb -3.56Mb 4.00Mb -814.29
mysql time_zone_name 0.25Mb -3.75Mb 4.00Mb -1500.0
mysql help_keyword 0.23Mb -3.77Mb 4.00Mb -1606.67
mysql help_relation 0.08Mb -3.92Mb 4.00Mb -5020.0
mysql time_zone 0.08Mb -3.92Mb 4.00Mb -5020.0
mysql global_grants 0.05Mb -3.95Mb 4.00Mb -8433.33
mysql db 0.03Mb -3.97Mb 4.00Mb -12700.0
mysql help_category 0.03Mb -3.97Mb 4.00Mb -12700.0
mysql procs_priv 0.03Mb -3.97Mb 4.00Mb -12700.0
mysql proxies_priv 0.03Mb -3.97Mb 4.00Mb -12700.0
mysql tables_priv 0.03Mb -3.97Mb 4.00Mb -12700.0
mysql columns_priv 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql component 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql default_roles 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql engine_cost 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql func 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql gtid_executed 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql innodb_index_stats 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql innodb_table_stats 0.02Mb -3.98Mb 4.00Mb -25500.0
mysql password_history 0.02Mb -3.98Mb 4.00Mb -25500.0
......
performance_schema session_connect_attrs 0.00Mb 0.00Mb 0.00Mb 0.0
performance_schema session_status 0.00Mb 0.00Mb 0.00Mb 0.0
performance_schema session_variables 0.00Mb 0.00Mb 0.00Mb 0.0
performance_schema setup_actors 0.00Mb 0.00Mb 0.00Mb 0.0
performance_schema setup_consumers 0.00Mb 0.00Mb 0.00Mb 0.0
$
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
py-dbx-1.0.5.tar.gz
(14.0 kB
view hashes)
