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?

nothing is printed as stdout when you execute a query - you need to fetch the results from the cursor using fetchone() fetchall() etc — dannymilsom
– dannymilsom, Commented Apr 16, 2014 at 17:28

Martijn Pieters · Accepted Answer · 2014-04-16 17:32:34Z

2

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

answered Apr 16, 2014 at 17:32

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

monkeybiz7 Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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)

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related