Convert pymysql result to object

Question

I came to Python from PHP, so I confused with some things I used to do in PHP. For example I got class Database_object which has some common database methods like this:

class DatabaseObject {

protected static $table_name;
protected static $db_fields;

public static function find_all() {
    return self::find_by_query("SELECT * FROM ".static::$table_name);
}

...
private static function instantiate($record) {
    $object = new self;
    foreach($record as $attribute=>$value) {
        if($object->has_attribute($attribute)) {
            $object->$attribute = $value;
        }
    }
    return $object;
}
}

And some child classes like this:

class User extends DatabaseObject {


protected static $table_name = "users";
protected static $db_fields = array('id', 'gid', 'username', 'hashed_password', 'first_name', 'last_name', 'e_mail', 'phone_number', 'position', 'last_login', 'comment', 'supplier_id');

I got some question on static realisation, but now the topic is how to make this instantiate method in Python? I have method like this:

   def FindAll(self):
        query = 'SELECT * FROM ' + self._TableName
        try:
            self.cur.execute(query)
        except(ValueError):
            print("Error " + ValueError)
        else:
            return self.cur.fetchall()

But this one returns arrays and I want to make them objects like in instantiate method in PHP. For example if I call FindAll for user class. I want to get an array of users and then use them like:

   for user in users:
       print("Username: {}, Name: {} {}".format(user.username, user.first_name, user.second_name)

and not:

print("Username: {}, Name: {} {}".format(user[1], user[3], user[4]))

like it is now.

score 0 · Answer 1 · answered Jan 06 '20 at 07:39

0

Use pymysql.cursors.DictCursor, which will return rows represented as dictionaries mapping column names to values.

>>> from pymysql.cursors import DictCursor
>>> with pymysql.connect(db='foo', cursorclass=DictCursor) as cursor:
...     print cursor
...

Credit : Mark Amery

answered Jan 06 '20 at 07:39

Raunak Shamnani

81
4

Thank you. I can access rows now the way I want, but they are still not objects. I want each of this row to be an object of the class. May be I don't really need that in Python but I used to work that may – Andrey Petrov Jan 06 '20 at 09:05
As far as I know you can't directly convert them to classes but dict are as close as I think we can have. Please accept if you find the answer helpful :D – Raunak Shamnani Jan 06 '20 at 09:36

score 0 · Answer 2 · answered Jan 06 '20 at 07:45

What I understand with your topic is that you want to get a python dict object and not a list object.

You can browse a dict in a similar way that what you are looking for. Ex :

people = {"John": "Doe", "Eddy": "Malou"}

You can access names from first names like this

people["John"] # will return Doe
people["Eddy"] # will return Malou

With pymysql, by default you will get a list of results when using a select query.

If you want to get a dict, you need to specify that you want to get headers. You can do this as follow :

results = {}
headers = [row[0] for row in cursor.description]
index = 0

"""
Browse a dict to return a dict with the following format:
results = {
    header1: [result_list1],
    header2: [result_list2],
    header3: [result_list3]
}
"""

for header in headers:
    result_list = []

    for element in result:
        result_list.append(element[index])

    results[header] = result_list
    index +=1

return results

Then you can browse your results as follows, replacing 'headerX' by your column/header name:

for result in results:
    print(f"My first header {result['header1']}, my second header {result['header2']} and my last header {result['header3']}.")

Convert pymysql result to object

2 Answers2