Writing MySQL-Based Programs - MySQL - page 87

dbh . quote ( nil ),
dbh . quote ( "eggroll" ),
dbh . quote ( 4 )
count = dbh . do ( stmt )
The statement string generated by this code is the same as when you use placeholders.
PHP
To use placeholders with the PDO extension, pass a statement string to prepare() to
get a statement object. The string can contain ? characters as placeholder markers. Use
this object to invoke execute() , passing to it the array of data values to bind to the
placeholders. Use the PHP NULL value to bind an SQL NULL value to a placeholder. The
code to add the profile table row for De'Mont looks like this:
$sth = $dbh -> prepare ( "INSERT INTO profile (name,birth,color,foods,cats)
VALUES(?,?,?,?,?)" );
$sth -> execute ( array ( "De'Mont" , "1973-01-12" , NULL , "eggroll" , 4 ));
The resulting statement includes a properly escaped quote and a properly unquoted
NULL value:
INSERT INTO profile ( name , birth , color , foods , cats )
VALUES ( 'De\' Mont ',' 1973 - 01 - 12 ',NULL,' eggroll ',' 4 ' )
The PDO placeholder mechanism provides quotes around data values when they are
bound to the statement string, so don't put quotes around the ? characters in the string.
(Note that even the numeric value 4 is quoted; PDO relies on MySQL to perform type
conversion as necessary when the statement executes.)
Python
The Connector/Python module implements placeholders using %s format specifiers in
the SQL statement string. (To place a literal % character into the statement, use %% in the
statement string.) To use placeholders, invoke the execute() method with two argu‐
ments: a statement string containing format specifiers and a sequence containing the
values to bind to the statement string. Use None to bind a NULL value to a placeholder.
The code to add the profile table row for De'Mont looks like this:
cursor = conn . cursor ()
cursor . execute ( '''
INSERT INTO profile (name,birth,color,foods,cats)
VALUES( %s , %s , %s , %s , %s )
''' , ( "De'Mont" , "1973-01-12" , None , "eggroll" , 4 ))
cursor . close ()
conn . commit ()
The statement sent to the server by the preceding execute() call looks like this:
INSERT INTO profile ( name , birth , color , foods , cats )
VALUES ( 'De\' Mont ',' 1973 - 01 - 12 ',NULL,' eggroll ' , 4 )