C.6.1 Tutorial: Generate PHP Code to Create a Connection with PDO_MySQL

MySQL Workbench includes a plugin that generates PHP code with the mysqli extension. This tutorial shows how to generate code with the PDO_MySQL extension for PHP. You might choose a different extension or a different language altogether, so adjust the generated code accordingly.

To begin, review the plugin code shown in the example that follows.

# import the wb module
from wb import DefineModule, wbinputs
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms

# define this Python module as a GRT module
ModuleInfo = DefineModule(name= "MySQLPDO", author= "Yours Truly", version="1.0")

@ModuleInfo.plugin("info.yourstruly.wb.mysqlpdo", caption= "MySQL PDO (Connect to Server)", input= [wbinputs.currentSQLEditor()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_Editor)

def mysqlpdo(editor):
    """Copies PHP code to connect to the active MySQL connection using PDO, to the clipboard.
    """
    # Values depend on the active connection type
    if editor.connection:
        conn = editor.connection

        if conn.driver.name == "MysqlNativeSocket":
            params = {
            "host" : "",
            "port" : "",
            "user" : conn.parameterValues["userName"],
            "socket" : conn.parameterValues["socket"],
            "dbname" : editor.defaultSchema,
            "dsn" : "mysql:unix_socket={$socket};dbname={$dbname}"
            }
        else:
            params = {
            "host" : conn.parameterValues["hostName"],
            "port" : conn.parameterValues["port"] if conn.parameterValues["port"] else 3306,
            "user" : conn.parameterValues["userName"],
            "socket" : "",
            "dbname" : editor.defaultSchema,
            "dsn" : "mysql:host={$host};port={$port};dbname={$dbname}"
            }
        text = """$host="%(host)s";
$port=%(port)s;
$socket="%(socket)s";
$user="%(user)s";
$password="";
$dbname="%(dbname)s";

try {
    $dbh = new PDO("%(dsn)s", $user, $password));
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

""" % params
        mforms.Utilities.set_clipboard_text(text)
        mforms.App.get().set_status_text("Copied PHP code to clipboard")
    return 0

This simple plugin generates PHP code to create a MySQL connection using PHP's PDO_MySQL extension. The DSN definition depends on the connection type in MySQL Workbench. The part you might want to modify is within the text definition.

To generate PHP code for a connection, first install the plugin as follows:

1. Copy the plugin code into a new file. The file name used in this example is php-pdo-connect_grt.py, but you can use a different name as long as _grt.py is the suffix.

2. Start MySQL Workbench. Click Scripting and then Install Plugin/Module from the menu to open a file browser. Select the plugin file created by the code in the previous step, php-pdo-connect_grt.py in this case.

   Note

   You could copy the file directly to the plugin folder instead of using the Install Plugin/Module interface. The result would be the same.

3. When prompted, restart MySQL Workbench. This step generates a compiled bytecode file (.pyc) from your source file. In this example, it generates php-pdo-connect_grt.pyc.

4. After restarting MySQL Workbench, load the MySQL connection to use to generate the PHP code. From the menu, click Tools, Utilities, and then MySQL PDO (Connect to Server), which is the Caption defined within the plugin code.

   This action copies the generated PHP code into the clipboard on your system. The following connection example defines "sakila" as the default database in the generated code.

   $host="localhost";
   $port=3306;
   $socket="";
   $user="root";
   $password="";
   $dbname="sakila";
   
   try {
       $dbh = new PDO("mysql:host={$host};port={$port};dbname={$dbname}", $user, $password));
   } catch (PDOException $e) {
       echo 'Connection failed: ' . $e->getMessage();
   }