Perl Tutorial: How to create a table in MySQL using Perl

In this tutorial, we’ll learn to create a table on MySQL database using Perl programming language. We’ll see an example as well for a better understanding of the concept.

Perl is a programming language initially developed for text manipulation and is currently widely used for several purposes from text manipulation to cloud maintenance. In this tutorial, we’ll be connected to a MySQL database and afterward create a new table within that database.

MySQL is a relational database management system (RDBMS) and is widely used. MySQL can be used for small database maintenance as well as the maintenance and manipulation of data for a large number of databases. We’ll be using MySQL in this tutorial.

Also read: Perl Tutorial: How to connect to MySQL with Perl

Pre-requisites

For this tutorial there are three pre-requisites for your system, which are mentioned below:

• MySQL: MySQL must be installed on your system, if you do not have it installed yet, use the below link-
  https://dev.mysql.com/downloads/
• Perl: As Perl is the programming language for this tutorial, it must already be installed on your system, although if still not installed download it from the below link-
  https://www.perl.org/get.html

Now, it’s assumed that you have done all the required installations. We’ll now have to install Perl DBI(Database Access Module) as it allows us to integrate with the database using Perl programming language. If you have already done the installation you can skip this part, otherwise, you can follow along.

Open Command Prompt, firstly, we’ll connect to CPAN(Comprehensive Perl Archive Network), it comes with the Perl installation so no need to install it separately and CPAN helps us in installing Perl modules.

perl -MCPAN -e shellCode language: Bash (bash)

After connecting to CPAN, we’ll install the MySQL driver for Perl, to do that use the below command:

install DBD:MySQLCode language: Bash (bash)

Steps to create MySQL table using Perl

There are four steps to creating a MySQL table with Perl language:

1. Connect to MySQL Database
2. Write Create Statement to create “demo” table
3. Execute Create Statement
4. Disconnect from MySQL

The below code is for Perl MySQL connectivity from which we’ll create a table in MySQL. Firstly we’ll connect to the database where we’ll be needing the name of the database which is ‘DB’, the user name that is ‘sam’, and the password is ‘root’.

After that, we’ll write the create a statement as shown in the code below as well as will mention the engine name ‘InnoDB’ as MySQL uses InnoDB.

In the end, we’ll Execute the statement and close the connection.

use strict;
use warnings;
use v5.10;

use DBI;

say "Perl Tutorial: Create Table in MySQL Demo";

#1. Connect to MySQL Database 
# Database name =db ,  user name = sam ,  password = root
my $dbh = DBI->connect("DBI:mysql:db",'sam','root');

#2. Write create statement
my @ddl = (
    "CREATE TABLE demo(
        demo_number int,
        demo_name varchar(10)
        ) ENGINE=InnoDB;"
);

#3. execute statements
for my $sql(@ddl){
    $dbh->do($sql);
}

say "Table created successfully";

#4. disconnect 
$dbh->disconnect();Code language: Perl (perl)

Now that we have written the code, let’s save it with a .pl extension. After saving our work we’ll execute the code.

For executing the code open Command Prompt, and enter the directory where you’ve saved the Perl file. Write the below code to execute the code:

perl filename.plCode language: Bash (bash)

Successful execution will be giving you an output as shown below example:

Let’s check for the Demo table in the MySQL command line. To do that we’ll log in to MySQL, open the command prompt, and write the below command:

mysql -u username -p Code language: Bash (bash)

Now let’s use the database which we used for creating the table using Perl, use the below command:

USE databasename;Code language: SQL (Structured Query Language) (sql)

Now let’s see all the tables present in this database and see if the table exists that we created using Perl:

SHOW TABLES;Code language: SQL (Structured Query Language) (sql)

We can clearly see the Demo table which we created using Perl, and that’s how we create a table using Perl language in MySQL.

Conclusion

In this tutorial we learned about Perl MySQL creating table, connecting Perl with MySQL, and executing the query all by using Perl programming language.

For more information about Perl MySQL refer to the official documentation using the link provided below:
https://dev.mysql.com/doc/refman/8.0/en/apis-perl.html