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.
