Python coding: Why query results of mysql.connector are in brackets and how to print real values

Written by Claudio Kuenzler - 0 comments

Published on March 10th 2022 - Listed in Coding Python MySQL Databases

---

When working with Python and MySQL databases (using mysql.connector) you are probably baffled by the format of the query result, containing brackets and commas - instead of just the data.

But let's start at the beginning.

MySQL cli (shell)

Using the mysql command line, the contents of a table from a database (here a PowerDNS database in MySQL) can be viewed very quickly:

$ mysql -u root -p -e "select name from powerdns.domains LIMIT 0,5"
Enter password: ***********
+-------------------------+
| name                    |
+-------------------------+
| example1.com            |
| example2.com            |
| example3.com            |
| example4.com            |
| example5.com            |
+-------------------------+

By using additional parameters -B (batch) and -s (silent), only the data from the "name" column are showing up:

$ mysql -u root -p -Bse "select name from powerdns.domains LIMIT 0,5"
Enter password: ***********
example.1.com
example.2.com
example.3.com
example.4.com
example.5.com

This is great for quick parsing, running through a loop, etc.

MySQL in Python using mysql.connector

In Python, the connection to the database first needs to be established and saved in a variable (here cnx). For the query itself, a cursor needs to be created, which basically executes the query and fetches the data.

The data can then be printed using a for loop:

$ cat pdns_domains.py
#!/usr/bin/env python
import mysql.connector

# Establish a connection to MySQL
cnx = mysql.connector.connect(user=root, password=secret, host=localhost, database=powerdns)
cursor = cnx.cursor()

# Execute a SQL Query, save the data in variable "result"
cursor.execute("SELECT name FROM powerdns.domains LIMIT 0,5")
result = cursor.fetchall()

# Print our domains
for domain in result:
    print("{} is a domain".format(domain))

# Close connections
cursor.close()
cnx.close()

The result of running this Python script looks kind of weird:

$ python3 pdns_domains.py
('example1.com',) is a domain
('example2.com',) is a domain
('example3.com',) is a domain
('example4.com',) is a domain
('example5.com',) is a domain

Each domain is showing up inside a bracket and string, followed by a comma.

The result is a Tuple, not a string

The reason for this is that the data is saved as a tuple object, not as a string (what I expected). Being a tuple object basically means:

Tuples are used to store multiple items in a single variable.
Tuple is one of 4 built-in data types in Python used to store collections of data, the other 3 are List, Set, and Dictionary, all with different qualities and usage.
Tuples are written with round brackets.
Tuple items are indexed, the first item has index [0], the second item has index [1] etc.

After reading the description of a Tuple data object, it now makes sense to be used for MySQL queries. The data from multiple columns can be read this way, accessing one column after another using the index. However in my case, only a single column (name) from the table (domains) needs to be read.

To only return the name, we need to retrieve the first (and only) item of this tuple object (domain[0]:

for domain in result:
    domain = domain[0]
    print("{} is a domain".format(domain))

Executing the Python script with this minor change now show the domains as expected:

$ python3 pdns_domains.py
example.1.com is a domain
example.2.com is a domain
example.3.com is a domain
example.4.com is a domain
example.5.com is a domain

Full script

The final and finished script will be used as a monitoring script to detect stale DNS records on PowerDNS secondary (slave) servers, but for this article here's a fully working Python script establishing a connection to a MySQL database, handling connection errors, and showing the results (first column) from the query:

$ cat pdns_domains.py
#!/usr/bin/env python

import mysql.connector
from mysql.connector import errorcode
import sys

# Establish a connection to MySQL and handle errors
try:
  cnx = mysql.connector.connect(user=dbuser, password=dbpass, host=dbhost, database=powerdns)
except mysql.connector.Error as err:
  print("Error: %s" % err)
  sys.exit(2)

cursor = cnx.cursor()

# Execute a SQL Query, save the data in variable "result"
cursor.execute("SELECT name FROM powerdns.domains")
result = cursor.fetchall()

# Print our domains
for domain in result:
    print("{} is a domain".format(domain))

# Close connections
cursor.close()
cnx.close()

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.