Monday, March 02, 2020

Querying Database Using Command Line Client and Powershell

I have found Powershell is a powerful tool. When it combines with a command line tool, such as mysql, we can perform sophisticated tasks easily. The output of Powershell is an object instead of a text string. We can perform SQL-like operations such as selecting columns and filtering rows using where clause. Since I found out the power of Powershell last year, I have been using Powershell as my major tool to query databases including SQL Server, MySQl and Postgre. In this post, I demonstrate how to query MySQL using mysql client and Powershell.

First, we run the following command to set up our host, user name, password and port.
mysql_config_editor set --login-path=local --host=localhost 
                         --user=root --port=3306 --password
When we run the above command at a shell, it will prompt us to input password. It will create a file .mylogin.cnf that is not humanly readable under home directory (I am using Mac. For Windows, the file is under %APPDATA%\MySQL). The reason of doing this is to avoid an annoying security warning message if we supply the password in mysql command. If you don't mind the warning message, you can skip the step. Then I write a Powershell function to query MySQL database. I save the scripts as mysql_f.ps1 on my Mac.
function runsql {
param($s)
mysql --login-path=local -D dataexp -B -e "$s" | convertfrom-csv -delimiter `t
}
I issue the following command from the shell
prompt>. "./mysql_f.ps1"
Now the function is defined. I test the function.
prompt>runsql "select 1 as val"

val
---
1
It worked. The following query displays the table in the current database.
prompt>runsql "select TABLE_SCHEMA, table_name, create_time 
       from information_schema.tables where table_schema=database()"

TABLE_SCHEMA TABLE_NAME             CREATE_TIME
------------ ----------             -----------
dataexp      flyway_schema_history  2019-11-21 11:56:31
dataexp      mdl_sampled            2020-01-14 10:40:06
dataexp      mdl_set                2020-01-14 09:46:59
dataexp      sample_user_id         2020-01-14 09:58:34
dataexp      t_all_pk1              2019-12-05 11:53:43
dataexp      t_all_pk1_int          2019-12-05 22:14:54
dataexp      t_all_tab_cols         2019-11-21 15:39:00
From the Powershell output, we select only table_name an create_time by piping the output to "select" command of Powershell.
prompt>runsql "select TABLE_SCHEMA, table_name, create_time 
       from information_schema.tables 
  where table_schema=database()" | select table_name, create_time
TABLE_NAME             CREATE_TIME
----------             -----------
flyway_schema_history  2019-11-21 11:56:31
mdl_sampled            2020-01-14 10:40:06
mdl_set                2020-01-14 09:46:59
sample_user_id         2020-01-14 09:58:34
t_all_pk1              2019-12-05 11:53:43
t_all_pk1_int          2019-12-05 22:14:54
t_all_tab_cols         2019-11-21 15:39:00
The above operation is possible because in runsql function, we use convertfrom-csv to convert text output of mysql to Powershell object. Convertfrom-csv is really a magic function! Once we have an Powershell object, we can do all sorts of things.