CODEWITHSUNDEEP
pythonmysql-python
Jon Zuilkowski
Jon Zuilkowski

Reputation: 21

python mysql connector query returns none

I am having an issue with mysql connector. I did search for quite a while, but have found nothing.

If I execute the first file that just has the query, it works as expected. But if I try to make a db class and put the query in there, it returns None. I have taken out the location variable and it's the same. No matter what I try to query on, it returns None. I even tried to do a "SHOW TABLES", and it returned None.

Also, I have run this in a debugger and looked at the cursor object to be sure, as well as the mysql general log. The query is correct and everything looks as it should to me.

This is my first try with python and I am hoping the solution is some simple newbie mistake.

The query that works: test.py

import mysql.connector

_config = {
    'user': 'user',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'testdb',
    'raise_on_warnings': True,
    }
cnx = mysql.connector.connect(**_config)

cursor = cnx.cursor()
query = ("SELECT * FROM testtbl WHERE location=%s")
location='HERE'
cursor.execute(query, (location, ))

print("--- " + str(cursor) + " ----")
for (stuff) in cursor:
  print("stuff: '" + stuff[0] + "', more stuff: '" + stuff[1] + "'")

cursor.close()
cnx.close()

The ones that do not work: somedb.py

import mysql.connector

class SomeDB(object):
    def __init__(self):
        _config = {
            'user': 'user',
            'password': 'password',
            'host': '127.0.0.1',
            'database': 'testdb',
            'raise_on_warnings': True,
        }
        self.conn = mysql.connector.connect(**_config)
        self.cur = self.conn.cursor()

    def get_stuff(self):
        query = ("SELECT * FROM testtbl WHERE location=%s")
        location="HERE"
        result = self.cur.execute(query, (location, ))
        return result

    def __del__(self):
        self.conn.close()

Following the advice from Alu, I changed the get_stuff method to this:

def get_nodes(self):
    query = ("SELECT * FROM testtbl WHERE location=%s")
    location="HERE"
    cursor = self.cur.execute(query, (location, ))
    list = []
    for (thing) in cursor:
        list.append(([thing[0],thing[1]]))
    return list

test2.py

import somedb

db = somedb.SomeDB()

cursor = db.get_stuff()
print("--- " + str(cursor) + " ----")
for (stuff) in cursor:
  print("stuff: '" + stuff[0] + "', more stuff: '" + stuff[1] + "'")

UPDATE

Ok, I cannot get this to work. I have gone through this code with a debugger, and class abstraction aside, all else appears to be equal. So let me refine my question: is this possible with any mysql driver? This is what I want to do: https://softwareengineering.stackexchange.com/questions/200522/how-to-deal-with-database-connections-in-a-python-library-module

UPDATE2

I found it!

    result = self.cur.execute(query, (location, ))
    return result

Needs to be, simply:

    self.cur.execute(query, (location, ))
    return self.cur

Upvotes: 2

Views: 5277

Answers (1)

Alu
Alu

Reputation: 737

As far as i remember you have to commit on the connection after every transaction.

You can try to use this as an example. It's my database class. https://github.com/CatCookie/DomainSearch/blob/master/src/additional/database.py

Upvotes: 1

Related Questions