Database Reference
In-Depth Information
The Connector/Python placeholder mechanism provides quotes around data values as
necessary when they are bound to the statement string, so don't put quotes around the
%s
format specifiers in the string.
If you have only a single value
val
to bind to a placeholder, write it as a sequence using
the syntax
(
val
,
)
:
cursor
=
conn
.
cursor
()
cursor
.
execute
(
"SELECT id, name, cats FROM profile WHERE cats =
%s
"
, (
2
,))
for
(
id
,
name
,
cats
)
in
cursor
:
print
(
"id:
%s
, name:
%s
, cats:
%s
"
%
(
id
,
name
,
cats
))
cursor
.
close
()
Alternatively, write the value as a list using the syntax
[
val
]
.
Java
JDBC provides support for placeholders if you use prepared statements. Recall that the
process for executing nonprepared statements in JDBC is to create a
Statement
object,
and then pass the statement string to the
executeUpdate()
,
executeQuery()
, or
exe
cute()
function. To use a prepared statement instead, create a
PreparedStatement
object by passing a statement string containing
?
placeholder characters to your con‐
nection object's
prepareStatement()
method. Then bind the data values to the state‐
ment using
set
XXX
()
methods. Finally, execute the statement by calling
executeUp
date()
,
executeQuery()
, or
execute()
with an empty argument list.
Here is an example that uses
executeUpdate()
to execute an
INSERT
statement that adds
the
profile
table row for De'Mont:
PreparedStatement
s
;
s
=
conn
.
prepareStatement
(
"INSERT INTO profile (name,birth,color,foods,cats)"
+
" VALUES(?,?,?,?,?)"
);
s
.
setString
(
1
,
"De'Mont"
);
// bind values to placeholders
s
.
setString
(
2
,
"1973-01-12"
);
s
.
setNull
(
3
,
java
.
sql
.
Types
.
CHAR
);
s
.
setString
(
4
,
"eggroll"
);
s
.
setInt
(
5
,
4
);
s
.
close
();
// close statement
The
set
XXX
()
methods that bind data values to statements take two arguments: a place‐
holder position (beginning with 1, not 0) and the value to bind to the placeholder.
Choose each value-binding call to match the data type of the column to which the value
is bound:
setString()
to bind a string to the
name
column,
setInt()
to bind an integer
to the
cats
column, and so forth. (Actually, I cheated a bit by using
setString()
to
treat the date value for
birth
as a string.)
One difference between JDBC and the other APIs is that you don't bind a
NULL
to a
placeholder by specifying some special value (such as
undef
in Perl or
nil
in Ruby).
