Working with Metadata - MySQL - page 347

table: profile
--- Column 1 (birth) ---
PDO type: 2
native type: DATE
len: 10
precision: 0
flags:
table: profile
To get a row count from a statement that returns rows, fetch the rows and count them
yourself. The rowCount() method is not guaranteed to work for result sets.
Python
For statements that produce a result set, Python's DB API makes row and column counts
available, as well as a few information items about individual columns.
To get the row count for a result set, access the cursor's rowcount attribute. This requires
that the cursor be buffered so that it fetches query results immediately; otherwise, you
must count the rows as you fetch them. The column count is not available directly, but
after calling fetchone() or fetchall() , you can determine the count as the length of
any result set row tuple. It's also possible to determine the column count without fetching
any rows by using cursor.description . This is a tuple containing one element per
column in the result set, so its length tells you how many columns are in the set. (If the
statement generates no result set, such as for UPDATE , the value of description is
None .) Each element of the description tuple is another tuple that represents the met‐
adata for the corresponding column of the result. For Connector/Python, only a few
description values are meaningful. The following code shows how to access them:
stmt = "SELECT name, birth FROM profile"
print ( "Statement: %s " % stmt )
# buffer cursor so that rowcount has usable value
cursor = conn . cursor ( buffered = True )
cursor . execute ( stmt )
# metadata information becomes available at this point ...
print ( "Number of rows: %d " % cursor . rowcount )
if cursor . description is None : # no result set
ncols = 0
else :
ncols = len ( cursor . description )
print ( "Number of columns: %d " % ncols )
if ncols == 0 :
print ( "Note: statement has no result set" )
for i , col_info in enumerate ( cursor . description ):
# print name, then other information
name , type , _ , _ , _ , _ , nullable , flags = col_info
print ( "--- Column %d ( %s ) ---" % ( i , name ))
print ( "Type: %d ( %s )" % ( type , FieldType . get_info ( type )))
print ( "Nullable: %d " % ( nullable ))