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?

score 2 · Accepted Answer · answered Mar 29 '13 at 16:23

2

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

answered Mar 29 '13 at 16:23

Daniel Roseman

588,541
66
880
895

Agreed, your code is fine (I prefer [pyodbc](https://code.google.com/p/pyodbc/)), but using an ORM can help you delegate lower level code that you probably don't actually need to do yourself. – Dave Mar 29 '13 at 16:47
I have been looking in to SqlAlchemy and worked out how to use it. I just have one question. Should I have my business logic in my mapping classes/database classes? – Samuel Taylor Mar 29 '13 at 21:23

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

1 Answers1