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

Written by - 0 comments

Published on - 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.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder