What is the correct way of getting all records from a database using Python and OOP?

Question

What would be the correct way of getting all the records from a table in a database?

My idea would be:

import MySQLdb

Class User:

    def __init__(self, id):    
        self.db = MySQLdb.connect(host="localhost", user="root",passwd="password",db="database")
        self.cur = self.db.cursor()

        self.id = id

        self.cur.execute("SELECT * FROM `user` WHERE `id` = %s;", (id))
        res = self.cur.fetchone()

        self.firstName = res[1]
        self.lastName = res[2]
        self.email = res[2]

Class UserManager:
    def __init__(self):
        self.db = MySQLdb.connect(host="localhost", user="root",passwd="password",db="database")
        self.cur = self.db.cursor()

    def get_all_users(self):
        self.cur.execute("SELECT `id` FROM `user`;")
        rows = self.cur.fetchall()

        users = []
        for row in rows:
            users.append(User(row[0]))
        return users

I'm sure there a better way? How would a create users?

Answer 1

Don't try and write this yourself. Use an existing ORM like SqlAlchemy or SqlObject, or Django.