I recently went through an example of making SQL Server changes with PowerShell. To continue on a PowerShell trend, let’s take a look at inserting and exporting MySQL values with PowerShell.
Inserting into MySQL with PowerShell
I created a local example database called exampledb with a table called Comic. Let’s say we want to connect to MySQL and insert a new record.
First, let’s set a couple of connection variables in our PowerShell script:
$mysql_server = "localhost"
$mysql_user = "root"
$mysql_password = "Password1234"
$dbName = "exampledb"
Next, we’ll want to reference our MySQL assembly:
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll")
Let’s put our connection together. We’ll create a $Connection variable for our MySQL connection and specify the connection string based on the variables we set above:
$Connection = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = "SERVER=$mysql_server;DATABASE=$dbName;UID=$mysql_user;PWD=$mysql_password"
Time to open our connection:
$Connection.Open()
We’ll create a variable called $sql for our INSERT statement and reference our connection:
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
We’ll then add our INSERT statement and execute that query:
$sql.CommandText = "INSERT INTO Comic (Title, Writer) VALUES (""Batman"",""Bill Finger"")"
$sql.ExecuteNonQuery()
$Connection.Close()
Combine all of that together and we have:
$mysql_server = "localhost"
$mysql_user = "root"
$mysql_password = "Password1234"
$dbName = "exampledb"
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll")
$Connection = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = "SERVER=$mysql_server;DATABASE=$dbName;UID=$mysql_user;PWD=$mysql_password"
$Connection.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$sql.CommandText = "INSERT INTO Comic (Title, Writer) VALUES (""Batman"",""Bill Finger"")"
$sql.ExecuteNonQuery()
$Connection.Close()
Once the script above is executed in PowerShell, we will have a new record in the comic table:
Export Results to CSV
We’ve successfully accessed our MySQL database using PowerShell and inserted a record. What if we want to query our table and export the results to a CSV file?
We’ll need to include an output path:
$output_path = "C:\temp\mysql_output.csv"
We’ll do the same type of $sql connection work as before but use a SELECT statement in our CommandText:
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$sql.CommandText = "SELECT Id, Comic, Writer FROM exampledb.comic;"
We’ll add new variables for $dataAdapter and $dataSet:
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($sql)
$dataSet = New-Object System.Data.DataSet
Fill $dataSet and then use Export-Csv to export our data from $dataSet:
$dataAdapter.Fill($dataSet) | Out-Null
$dataSet.Tables[0] | Export-Csv -path $output_path -NoTypeInformation
When we put all of that together, we have:
$mysql_server = "localhost"
$mysql_user = "root"
$mysql_password = "Password1234"
$dbName = "exampledb"
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll")
$Connection = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = "SERVER=$mysql_server;DATABASE=$dbName;UID=$mysql_user;PWD=$mysql_password"
$Connection.Open()
$output_path = "C:\temp\mysql_output.csv"
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$sql.CommandText = "SELECT * FROM exampledb.comic;"
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($sql)
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet) | Out-Null
$dataSet.Tables[0] | Export-Csv -path $output_path -NoTypeInformation
$Connection.Close()
We can run that script confirm the file is in our output directory:
When we open our file, we’ll see the exported data:
Thanks for reading!
Chad Callihan 
One thought on “Inserting and Exporting MySQL Values with PowerShell”