There are times when you need to create databases and tables from a batch file. Whether it’s part of an automated application deployment, or you need to repeat the process on many workstations, a batch process is usually the easiest and fastest solution available. Since the emergence of Microsoft PowerShell, this has been truer than ever on Windows operating systems. In today’s article, we’ll be writing two PowerShell scripts: one that creates a database and loads a table using an .sql file, and another that loads the table data from a .csv file.
The PowerShell Integrated Scripting Environment
Although you can use any text editor to write PowerShell scripts, I would recommend using the PowerShell Integrated Scripting Environment (ISE). It’s a graphical host application for PowerShell that allows you to run commands directly, as well as write, edit, run, test, and debug your scripts, all from one place. Best of all, it’s free and comes with PowerShell!
The create_company_db.ps1 Script
Our first script will establish a connection to the database server, create a new “company” database, and then run an SQL file to create and populate the customers table.
1. Load the MySQL.Net connector into memory by entering the following command:
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
2. Create a variable to hold the connection and create the MySqlConnection object (replace the user id and password with your own):
$connStr ="server=localhost;Persist Security Info=false;user id=" + $dbusername + ";pwd=" + $dbpassword + ";" $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
This particular connection string does not specify a database to connect to. That way, we are only connecting to the MySQL server.
3. Call the Connection object’s Open() method:
$conn.Open()
4. Delete the company database if it exists already, using the ExecuteNonQuery() method:
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand $cmd.Connection = $conn $cmd.CommandText = "DROP DATABASE IF EXISTS " + $dbname $cmd.ExecuteNonQuery()
5. Create the new database:
$cmd.CommandText = 'CREATE SCHEMA `' + $dbname + '`' $cmd.ExecuteNonQuery()
6. Close the connection:
$conn.Close()
Even though we aren’t done yet, we can close the connection at this point because the rest of the processing will be performed via the MySQL command line utility.
7. Call the MySQL command line tool to execute commands contained in an .sql file:
$sqlfile = "E:Database Journal2012Marcreate_powershell_test_table.sql" mysql $dbname -u $dbusername –p $dbpassword -e "source $sqlfile"
The MySQL command line tool comes with a special command called “source” that specifies an SQL file to execute. To use it, we have to call the command line tool directly from our script and include the –e (execute) flag.
The create_powershell_test_table.sql file contains a CREATE TABLE command as well as many INSERT statements to populate the customers table with data:
CREATE TABLE customers (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`email` varchar(255) default NULL,
`phone` varchar(100) default NULL,
`street_address` varchar(255) default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
INSERT INTO `customers` (`id`,`name`,`email`,`phone`,`street_address`)
VALUES ('1','Thomas Pacheco','Duis@aliquetlobortisnisi.com','','558-2198 Facilisis, Rd.');
INSERT INTO `customers` (`id`,`name`,`email`,`phone`,`street_address`)
VALUES ('2','Nissim Hardy','in.dolor.Fusce@sollicitudinadipiscing.edu','','P.O. Box 937, 6498 Amet, St.');
INSERT INTO `customers` (`id`,`name`,`email`,`phone`,`street_address`)
VALUES ('3','Baxter Williamson','mi.Duis.risus@felisullamcorper.org','','Ap #984-7814 In St.');
...
The import_csv_file.ps1 Script
This script populates the customers table from the customer_data.csv file. CSV – short for Comma-Separated-Values – files contain one line of data for each table row. Contrary to the name, values may be delimited by other characters besides commas, such as semi-colons (;). There are many variations, such as optional headers in the first line, quotes around string values, various date formatting, and depiction of null values. Therefore, you have to be careful that the file format matches what the input process expects. Here is a sampling of the customer_data.csv contents:
id,name,email,phone,street_address 1,"Thomas Pacheco",Duis@aliquetlobortisnisi.com,,"558-2198 Facilisis, Rd." 2,"Nissim Hardy",in.dolor.Fusce@sollicitudinadipiscing.edu,,"P.O. Box 937, 6498 Amet, St." 3,"Baxter Williamson",mi.Duis.risus@felisullamcorper.org,,"Ap #984-7814 In St." 4,"Dorian Flynn",lectus.pede@pedeetrisus.edu,,"4015 Eleifend. Street" 5,"Uriel French",Aliquam.erat@Praesentinterdum.com,,"187-6569 Est, Ave" 6,"Ray Hull",urna@pedeCras.edu,,"Ap #706-127 Enim, St."
Steps one through 3 are the same as before, except that the connection string in step 2 now contains the database name (“company”) to connect to.
1:
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
2:
$connStr ="server=localhost;database=" + $dbname + ";Persist Security Info=false;user id=" + $dbusername + ";pwd=" + $dbpassword + ";"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
3:
$conn.Open()
4. This optional step deletes existing rows from the customers table:
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand $cmd.Connection = $conn $cmd.CommandText = "truncate table " + $dbname + ".customers;" $cmd.ExecuteNonQuery()
5. Now the real magic occurs. PowerShell has a Cmdlet called Import-Csv that loads a CSV file into memory. We can plug it directly into a foreach loop to process one line of the file at a time. Each field is accessed using dot notation so that $i.id is the ID column, $il.name is the name column, etc…
foreach ($i in Import-Csv $csvfile) {
$cmd.CommandText =
"INSERT INTO customers (id,name,email,phone,street_address) VALUES ("
+$i.id+",'"+$i.name+"','"+$i.email+"','"+$i.phone+"','"+$i.street_address+"');"
$cmd.ExecuteNonQuery()
}
6. Close the connection:
$conn.Close()
Here are the complete create_company_db.ps1 and import_csv_file.ps1 scripts that we worked on today.
Conclusion
Pretty much anything you can do directly via the MySQL command line interface or Workbench GUI application can be achieved via a PowerShell script, and with surprisingly little code too! So if you’re the type of person who likes to store code in SQL files, then you might want to consider grouping those into scripts that you can run as a batch job. In the next article I’ll be moving on to automating MySQL database backups using PowerShell.
