SQL statements are executed by, and results returned from, the Connector/Python connection cursor. The cursor provides a number of methods to access the data and metadata of an executed SQL statement. They are:
execute Method
fetchall Method
statement Attribute
column_names Attribute
description Attribute
execute Method
The cursor execute method takes a SQL statement as a parameter and attempts to execute it against its database connection. If successful, the cursor provides access to the resulting data and metadata; if unsuccessful it raises exceptions or errors as appropriate. The syntax for a simple SQL statement (i.e. one with no parameters), is:
cursor_variable.execute(SQL statement)
From a previous example:
try:
# Connect to the DCRIS database with an option file
conn = Connect("dcris.txt")
# Get the connection cursor object
cursor = conn.cursor
# Define a SQL query
sql = "SELECT * FROM broad"
# Execute the query from the connection cursor
cursor.execute(sql)
# Print the column names from the query result
print("Columns:")
print(cursor.column_names)
# Get and print the contents of the query results (raw results)
rows = cursor.fetchall()
print(f"Row count: {cursor.rowcount}")
print("Data:")
for row in rows:
print(row)
# Close the Connect object
conn.close()
except Exception as e:
print(str(e))
Access to the data and metadata is explained below.
For statements with parameters, parameters are bound to the
executed statement with a Python dictionary.
SQL statements must include a %(key)s placeholder
for each parameter. When processed, the placeholder is replaced by the
dictionary value whose key matches the placeholder name. The order in
which the key:value pairs appear in the dictionary is irrelevant.
Example:
# Define a SQL query
sql = "SELECT * FROM broad WHERE broadId = %(broadId)s"
params = {'broadId': 7}
# Execute the query from the cursor with parameter data
cursor.execute(sql, params)
In this example, %(id)s is the placeholder, and is replaced
by the integer 7, which is the value assigned to the dictionary key id.
The s in %(key)s is not significant - i.e. it
does not imply a string value. It is just part of the Python placeholder
syntax for any type and number of values. Never surround it with single
quotes.
Although in these example the key name happen to match the field name
(ex: broadId), it is not necessary, but it does make the
code more readable.
The following example has two parameters to the SQL statement:
sql = "SELECT * FROM pub WHERE pubPubType = %(pubType)s AND pubMemberId = %(memberId)s"
params = {'pubType': 'p', 'memberId': 35}
cursor.execute(sql, params)
In this example the parameters are of mixed data types, string and
integer. No matter what the data types are, the placeholder is always %(key)s.
Do not put single quotes around the placeholder for string
parameters. The connector library provides those when the parameter
values are bound to the statement.
When using one of the SQL wildcard characters (_ or %),
the wildcard character must be part of the parameter data. The %
character in the %(key)s placeholder is not a
wildcard character, it is a placeholder identifier, as in this example:
sql = "SELECT * FROM member WHERE memberSurname LIKE %(surname)s"
params = {'surname': 'G%'}
cursor.execute(sql, params)
This SQL statement looks for all members whose surnames start with the
letter "G". The wildcard is part of the 'G%' value.
Of course, parameter values may be variables:
sql = "SELECT * FROM member WHERE memberSurname LIKE %(surname)s"
for letter in ("ABC"):
params = {'surname': f"{letter}%"}
cursor.execute(sql, params)
...
This example uses the standard Python f-string syntax f"{letter}%"
to generate the values "A%", "B%", and "E%"
in the loop, and pass them to the SQL statement when executed.
Parameter binding is also used for INSERT and UPDATE
statements as in this example:
sql = """INSERT INTO pub
(pubMemberId, pubPubType, pubTitle, pubAuthors, ...)
VALUES
(%(memberId)s, %(typeId)s, %(title)s, %(authors)s, %s, %s, %s, %s, %s)"""
params = {'memberId': 35, 'typeId': 'p', 'title': ... }
cursor.execute(sql, params)
Parameters and placeholders can only be used for values - they cannot be used in place of column or table names. The following example:
sql = "SELECT %(col1)s, %(col2)s FROM member WHERE memberSurname = 'George'"
params = {'col1': 'memberId', 'col2': 'memberForename'}
cursor.execute(sql, params)
executes without errors, but does not produce the results you expect. (Try it.)
Always use parameter binding for values, never use string replacement like this:
var = 'G%'
sql = f"SELECT * FROM member WHERE memberSurname LIKE '{var}'"
cursor.execute(sql)
as this leaves the database open to security breaches such as SQL Injection (discussed elsewhere in the course notes).
And you will lose marks on assignments if you do not use parameter binding.
fetchall Method
The simplest way to extract all of the results from a cursor execute
result set is dump all of the data into a list of tuples using fetchall.
This may cause problems if the result set is significantly large - the
computer may not have enough memory - however this should not be an
issue for this course. (An alternative is to extract the data one row at
a time from the cursor using the fetchone method.) The
following code:
sql = "select * from broad"
cursor.execute(sql)
rows = cursor.fetchall()
print(rows)
print("--------------")
for row in rows:
print(row)
prints:
[(7, 'Arms Control and Non-Proliferation Studies'), (13, 'Civil-Military Relations'), ...]
--------------
(7, 'Arms Control and Non-Proliferation Studies')
(13, 'Civil-Military Relations')
...
(16, 'Weapons Systems')
Note that you cannot execute a second SQL statement on a cursor if the first one still has results left. Attempting to do something like this:
sql = "select * from broad"
cursor.execute(sql)
sql = "select * from member"
cursor.execute(sql)
throws the following error from the second call to execute:
mysql.connector.errors.InternalError: Unread result found
statement Attribute
The cursor statement attribute contains the actual SQL
statement sent to the database by the cursor, i.e. all placeholders are
replaced by the bound parameter values. In this example:
pubType = "p"
memberId = 35
sql = "SELECT * FROM pub WHERE pubPubType = %(pubType)s AND pubMemberId = %(memberId)s"
params = {'pubType': pubType, 'memberId': memberId}
cursor.execute(sql, params)
print(cursor.statement)
the SQL statement printed is:
SELECT * FROM pub WHERE pubPubType = 'p' AND pubMemberId = 35
Note the single quotes for 'p' and the lack of quotes
around the 35. The parameter binding mechanism
automatically understands the data types of the values it is passed.
This is a very useful attribute for debugging as you can see the actual SQL statement sent to the server.
column_names Attribute
The cursor column_names attribute is tuple containing the
column names of the result set returned by the execute
method. In this example:
sql = "SELECT * FROM broad"
cursor.execute(sql)
print(cursor.column_names)
the tuple of column names printed is:
('broadId', 'broadDesc')
If renaming is used, then the alias is provided. In the example:
sql = "SELECT CONCAT(last_name, ', ', first_name) AS name FROM member"
cursor.execute(sql)
print(cursor.column_names)
the tuple of column names printed is:
('name',)
description Attribute
The cursor description attribute provides metadata about
the columns of the result set returned by the execute
method. descriptions is a tuple of column tuples. The
values for each column tuple are:
(column_name, type, None, None, None, None, null_ok, column_flags)
(Obviously only the first two and last two are of interest.)
In order to do something useful with these values, we need to access the
library's FieldType and FieldFlag attributes,
as they give translate the type and column_flats
elements into human readable form.
The following code gives an example of how to work with the descriptions
attribute:
# Required imports
from mysql.connector import FieldFlag, FieldType
sql = "select * from broad"
cursor.execute(sql)
for desc in cursor.description:
print(f"Column: {desc[0]}")
# Convert type code using 'get_info'
print(f" Type: {FieldType.get_info(desc[1])}")
# Convert 0/1 into boolean with 'bool'
print(f" Null OK: {bool(desc[6])}")
# Convert flag code with 'get_bit_info'
print(f" Flags: {FieldFlag.get_bit_info(desc[7])}")
This produces the output:
Column: broadId Type: TINY Null OK: False Flags: ['NOT_NULL', 'PRI_KEY', 'AUTO_INCREMENT', 'NUM', 'PART_KEY', 'GROUP'] Column: broadDesc Type: VAR_STRING Null OK: False Flags: ['NOT_NULL', 'UNIQUE_KEY', 'NUM', 'GROUP']
Thus the field broadId is an auto-incremented tinyint
primary key that cannot be NULL. broadDesc is
a varchar field that must have a unique value within the
table and cannot be NULL. (We will ignore the other fields
for now.)
Unfortunately, descriptions does not contain the field
width. This can either be deduced from the data, or extracted from the information_schema
tables for this schema.
rowcount Attribute
The rowcount attribute contains the number of rows returned
after calling the fetchall method, as in this example:
rows = cursor.fetchall()
print(f"Row count: {cursor.rowcount}")
Note that the same result could be extracted by getting the length of
the rows variable:
rows = cursor.fetchall()
print(f"Row count: {len(rows)}")