Database Reference
In-Depth Information
}
cnx
=
mysql
.
connector
.
connect
(**
config
)
cur
=
cnx
.
cursor
(
buffered
=
True
)
The first line is the required line invoking Python. Next we import
mysql.connector
,
the MySQL Connector/Python. We then create a hash to store the login information we
will need for connecting to MySQL. We're using theadmin_granter@localhostuser ac-
count because it has the privileges to execute the
SHOW GRANTS
statement and to query
the
mysql
database, which contains user account information. We created this user in
The final pair of lines of the previous code snippet establishes the connection to MySQL.
The first usesthe
connect()
call for the MySQL Connector/Python using the values in
the
config
hash, loading its results into the
cnx
variable. The second creates a cursor
object (
cur
) to use for executing queries on the database.
Querying MySQL
Because there is no
SHOW USERS
statement,we'll have to query the
mysql
database to
select a list of user accounts from the
user
table. To do this, we'll first create a variable
to store the
SELECT
statement we want to execute. Then we'll usethe
execute()
call
to execute it. Here's how this part of the program would look:
sql_stmnt
= (
"SELECT DISTINCT User, Host FROM mysql.db "
"WHERE Db IN('rookery','birdwatchers') "
"ORDER BY User, Host"
)
cur
.
execute
(
sql_stmnt
)
So as to fit the
SELECT
statement on the page, we've broken it onto multiple lines. We
pass that variable to the
execute()
function to execute the SQL statement. We're now
ready to fetch the rows, parse the fields from the results, and display them:
for
row
in
cur
.
fetchall
() :
user_name
=
row
[
0
]
host_address
=
row
[
1
]
user_account
=
"'"
+
user_name
+
"'@'"
+
host_address
+
"'"
print
"
%s
@
%s
"
% (
user_name
,
host_address
)
cur
.
close
()
cnx
.
close
()
