MySQL Workbench Manual  /  ...  /  Tutorial: Generate PHP Code to Create a Connection with PDO_MySQL

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 guide shows how to generate code using a different extension, and in this case we use PHP's PDO_MySQL extension. You might choose a different extension or a different language altogether, so adjust the generated code accordingly.

To begin, let us jump straight into the plugin code:

# 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 demonstrate, first install the plugin:

  1. Copy the plugin code into a new file. In our example, we name it php-pdo-connect_grt.py but you can use a different name as long as _grt.py is the suffix.

    Figure C.8 Menu: Install Plugin/Module

    Menu: Install Plugin/Module

  2. Open MySQL Workbench, choose Scripting from the main navigation menu, and then choose Install Plugin/Module. This opens a file menu titled Select Module to Install, so choose the plugin file created from the above code, such as php-pdo-connect_grt.py.

    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.

    Figure C.9 Select Plugin File

    Select Plugin File

  3. Workbench must be restarted to install the plugin. Workbench will generate a compiled bytecode file (.pyc) from your source file. In our case, it generates php-pdo-connect_grt.pyc.

    Figure C.10 Notice to Restart Workbench

    Notice to Restart Workbench

  4. After restarting Workbench, load the MySQL connection that you want the PHP code to generate from. Then, choose Scripting, Utilities, and finally MySQL PDO (Connect to Server), which is the Caption we defined in the plugin's code.

    Figure C.11 Execute the Plugin

    Execute the Plugin

  5. This copied the generated PHP code into your system's clipboard. Our example connection defines "sakila" as the default database, and here is what the generated code will look like:

    $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();
    }

User Comments
Sign Up Login You must be logged in to post a comment.