Database Reference
In-Depth Information
The example demonstrates the statement handle
fetch()
method, which returns the
next row of the result set or
FALSE
when there are no more.
fetch()
takes an optional
argument that indicates what type of value it should return. As shown, with an argument
of
PDO::FETCH_NUM
,
fetch()
returns an array with elements accessed using numeric
subscripts, beginning with 0. The array size indicates the number of result set columns.
With an argument of
PDO::FETCH_ASSOC
,
fetch()
returns an associative array con‐
taining values accessed by column name (
$row["id"]
,
$row["name"]
,
$row["cats"]
).
With an argument of
PDO::FETCH_OBJ
,
fetch()
returns an object having members ac‐
cessed using the column names (
$row->id
,
$row->name
,
$row->cats
).
fetch()
uses the default fetch mode if you invoke it with no argument. Unless you have
changed the mode, it's
PDO::FETCH_BOTH
, which is like a combination of
PDO::FETCH_NUM
and
PDO::FETCH_ASSOC
. To set the default fetch mode for all statements
executed within a connection, use the
setAttribute
database-handle method:
$dbh
->
setAttribute
(
PDO
::
ATTR_DEFAULT_FETCH_MODE
,
PDO
::
FETCH_ASSOC
);
To set the mode for a given statement, call its
setFetchMode()
method after executing
the statement and before fetching the results:
$sth
->
setFetchMode
(
PDO
::
FETCH_OBJ
);
It's also possible to use a statement handle as an iterator. The handle uses the current
default fetch mode:
$sth
->
setFetchMode
(
PDO
::
FETCH_NUM
);
foreach
(
$sth
as
$row
)
printf
(
"id: %s, name: %s, cats: %s
\n
"
,
$row
[
0
],
$row
[
1
],
$row
[
2
]);
The
fetchAll()
method fetches and returns the entire result set as an array of rows. It
permits an optional fetch-mode argument:
$rows
=
$sth
->
fetchAll
(
PDO
::
FETCH_NUM
);
foreach
(
$rows
as
$row
)
printf
(
"id: %s, name: %s, cats: %s
\n
"
,
$row
[
0
],
$row
[
1
],
$row
[
2
]);
In this case, the row count is the number of elements in
$rows
.
Python
The Python DB API uses the same calls for SQL statements that do not return a result
set and those that do. To process a statement in Python, use your database connection
object to get a cursor object. Then use the cursor's
execute()
method to send the state‐
ment to the server. If the statement fails with an error,
execute()
raises an exception.
Otherwise, if there is no result set, statement execution is complete, and the cursor's
rowcount
attribute indicates how many rows were changed:
cursor
=
conn
.
cursor
()
cursor
.
execute
(
"UPDATE profile SET cats = cats+1 WHERE name = 'Sybil'"
)
