How to execute simple command using mysql.connector (Python)

Question

I installed the MySQL connector from Oracle, and entered the following commands in Python (currently running Python 2.7.6)

import mysql.connector
cnx=mysql.connector.connect(user='genome',host='genome-mysql.cse.ucsc.edu',database='hg19')
cursor=cnx.cursor()
query=('show tables')
cursor.execute(query)

Nothing happened! I expected to see a list of tables. Why? Incidentally, I tried this as well, with the same result:

query=('SELECT * FROM wgRna')
cursor.execute(query)

I know I have MySQL properly installed on my computer, because when I enter the same commands into the terminal everything is fine. Can someone explain to me what I'm doing wrong in Python?

• dannymilsom: nothing is printed as stdout when you execute a query - you need to fetch the results from the cursor using fetchone() fetchall() etc

Answer 1

You never did anything with the selected data; print the rows by iterating over the cursor after executing a query:

query = 'show tables'
cursor.execute(query)
for row in cursor:
 print row

• monkeybiz7: Ok, I get it now. Thanks for helping out a newbie. For future reference, this site should be a good resource, yes? dev.mysql.com/doc/connector-python/en/…
• Martijn Pieters: It's the official reference documentation for that adapter. Like the Python DBAPI2 specification I can imagine the text to be a little dry.
• monkeybiz7: No kidding. This is why I'm so appreciative of you guys on Stack Overflow. Also, why does it seem that each cursor object can only be used once? And why is it that making a new cursor object: cursor=cnx.cursor() Throws the following error: InternalError: Unread result found. Thanks so much!
• Martijn Pieters: Cursor objects can be reused just fine; but they have state. Execute a query, and the cursor is attached to the result set for that query. Execute another query on the cursor and they are tied to the new results set.
• monkeybiz7: How would I reset the state of the cursor object? Specifically, executing another query results in an error. import mysql.connector cnx=mysql.connector.connect(user='genome',host='genome- mysql.cse.ucsc.edu',database='hg19') cursor=cnx.cursor() ### first attempt at a query query=('show tables') cursor.execute(query) #### second attempt query=('SELECT * FROM wgRna') cursor.execute(query)