Inserting and Exporting MySQL Values with PowerShell

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!

One thought on “Inserting and Exporting MySQL Values with PowerShell”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s