Unable to fetch results from SQL to Python using mysql.connector

Question

I am trying to query a table stored in SQL using python. I am using mysql.connector package to perform the task.

 import mysql.connector
 #Creating a connection
 mydb = mysql.connector.connect(
 host="localhost",
 user="root",
 passwd="something",
 database='mydatabase',
 )
 print(mydb)
 ##Creating a table called customers
 mycursor = mydb.cursor()
 mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
 #Inserting records to the table
 sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
 val = [
 ('Peter', 'Lowstreet 4'),
 ('Amy', 'Apple st 652'),
 ('Hannah', 'Mountain 21'),
 ('Michael', 'Valley 345')]
 mycursor.executemany(sql, val)
 mydb.commit()
 print(mycursor.rowcount, "record inserted.")

The problem is when I query the database, there is no output displayed.

 query=("SELECT name, address FROM customers")
 mycursor.execute(query)
 for (name, address) in mycursor:
 print("{} {}".format(name, address))

Here is a link to what I tried and where I got the code from. Link:https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

Here is the confirmation that the query is storing rows in the database. enter image description here

• s3dev: Have you tried querying the table via a tool like Workbench or mysqlsh; to ensure the inserted record(s) exist?
• Nitin Kumar: Yes the records exist in the database, it just not showing in python.
• s3dev: Mate ... I don't know. I've tested this scenario using 1) my own dataset (CREATE, INSERT, SELECT); 2) your exact copy/paste code (except the login creds) in Jupyter Notebook, and 3) your SELECT statement on your table created in #2 in console iPython ... and they all work perfectly. Your code is fine! I've upvoted the question to help bring some attention. I don't know mate ... sorry. Will have a think. Can you please update your question to show the records are in the database?

Answer 1

The code is running fine. The issue seems to be in the line mycursor.executemany(sql, oval) as the variable is defined as val above. Fixing that should give you the expected output.

• s3dev: Agree with above. Just spotted this myself as well. Good call.
• Nitin Kumar: Sorry about that guys, it was a typo. Code still not executing
• koushikmln: are you getting any error on your console? Did you check if the MySQL table has been created with the entries in your database?
• Nitin Kumar: No, I am not getting any error in the console, Yes I have check MySQL and the table has been created and the entries are present.