Re: Querying Data from MySQL with Python

[Patrick Starrenburg]

Hi - you are making a number of very basic Python errors due to unfamiliarity with Python which put together with unfamiliarity with the Python Connector while trying to dive directly into your app is a bad way to approach things.

Too many variables and too many unknowns.

You should read and follow and code the examples that come with the Python Connector and also examples from the Python documentation site.

Also a much better resource for asking questions like this (which are really Python, not the DB connector, related) is StackOverflow.com.

Anyway some basic pointers for you (copy/pasting from an app of mine)

Note you execute the query via a cursor object from already opened database connection

# Connection OK - open cursor
cursor = cnx.cursor()

Once you have a cursor you can execute queries. You should usually always put the database connection code into a Python exception block:

================
try:
cursor.execute(query, query_params)
rows = cursor.fetchall()
except mysql.connector.Error as query_err:
print('DB error: {0}'.format(query_err))
print('Problem executing query, terminating program.')
# Close database resources
cursor.close()
cnx.close()
exit()
================

Note in your code you execute the query but you are not fetching the row(s). The Python variable 'rows' is a list of tuples which is OK if you know in advance that the number of rows returned will not be large. If you had a very large DB then usually 'fetchone()' would be used.

You are confused then trying to use a classic Python programming for [each] construct:

"for row in rows"

when you say "for (nome_imagem, estado, type) in cursor:"

If one assumes that you want to return multiple rows from your database, and each row has multiple columns and that you want to reference each column then:


cursor.execute(query, query_params)
rows = cursor.fetchall()

# Assuming you have multiple rows then

# Step through the rows by row
for row in rows:
# Step through the columns
for i, column in enumerate(cursor.column_names):
# Get data, a specific field
some_data = row
print(some_data)

Data rows from the DB is returned in a Python tuple, you can reference tuples by index or name.


My suggestion - read the docs, do the examples, get things working and then once you have the basics build your app.