Database Reference
In-Depth Information
We're using a
for
statement here to loop through the results ofa
fetchall()
call for
the
cur
cursor object. It takes the values from each row fetched and stores it in an array
we named
row
. Within the statement block of the
for
statement, we extract each array
element and store the values temporarily in string variables, in
user_name
and
host_address
. Then we assemble them with some text for nicer formatting and store
them in a variable we named
user_account
. Its contents will look like
lena_stankoska@localhost
.
We end this program by displaying the
user_account
values to the administrator, and
then closing the cursor object and the connection to MySQL.
Sample Python Program
It's easier to discuss aprogram by breaking it into its components as we've just done, but
it can be confusing to understand how it all comes together. The following listing com-
bines the preceding snippets, but with some additions that make it a bit more elaborate:
#!/usr/bin/python
import
re
import
mysql.connector
# connect to mysql
config
= {
'user'
:
'admin_granter'
,
'password'
:
'avocet_123'
,
'host'
:
'localhost'
,
'database'
:
'rookery'
}
cnx
=
mysql
.
connector
.
connect
(**
config
)
cur
=
cnx
.
cursor
(
buffered
=
True
)
# query mysql database for list of user accounts
sql_stmnt
=
"SELECT DISTINCT User, Host FROM mysql.db "
sql_stmnt
+=
"WHERE Db IN('rookery','birdwatchers') "
sql_stmnt
+=
"ORDER BY User, Host"
cur
.
execute
(
sql_stmnt
)
# loop through list of user accounts
for
user_accounts
in
cur
.
fetchall
() :
user_name
=
user_accounts
[
0
]
host_address
=
user_accounts
[
1
]
user_account
=
"'"
+
user_name
+
"'@'"
+
host_address
+
"'"
