I've always written a database class which has methods for each query I need.
Doing this I have ended up with 50+ methods.
I'm thinking I should have written a small class with methods to SELECT, DELETE, UPDATE, INSERT etc. Then just write my query direct into my methods.
Which way is better and why? Is there another way I should be doing it?
Why not use a query builder or a ORM ? https://github.com/kayak/pypika Use query builders for getting granularity of control over queries and ORM for recurring tasks.
This really is applicable to contexts beyond just python and mysql:
What you're asking about is how to design your database adapter, the code you use to communicate with your DB. There are a lot of important concerns to consider:
Having methods for each of the MySQL verbs seems appealing, because then you don't have to add a new method for each query type, but that's about where the benefits end. You'll likely start to copy and paste certain queries into different locations. And once this happens, to update anything in that query (the table name, column names, joins, or even the specifics of how it works--maybe a different sort order or limit) you have to modify the query in every location you copied it to. This quickly spirals out of hand. It also means that your controllers (assuming your using MVC--if not, think of them as the code that calls this DB wrapper you've written) become tightly coupled to the underlying DB implementation. This brittleness makes them harder to test. You'll need to setup a database just to test them.
Your current approach of having methods for each type of query certainly may feel like more code, but what you've approximated is the Repository pattern. The idea here is we abstract all of the behavior specific to how the database works (table names, column names, joins, foreign keys, sorting strategies, caching) into a few specific classes. For blog posts, maybe this is a BlogPostRepository. For comments this could be a CommentRepository. They would behave like this:
class BlogPostRepository(Repository):
def create(self, blog_post):
with self._conn.cursor() as c:
c.execute('INSERT ...', blog_post.name) # etc.
def list(self):
return [BlogPost(r) for r in self._conn.query('SELECT * FROM blog_post')]
The win here is now your controller can just take a repository as an arg to init (and many frameworks can simplify this with dependency injection):
class HomeController(Controller):
def __init__(self, blog_post_repository):
self.blog_posts = blog_post_repository
def show_home_page(self):
posts = self.blog_posts.list()
# Render the posts...
And any other place where you need to list blog posts can use the same code. Then later on if you need to change how listing a blog post works you only have to modify code in BlogPostRepository. Additionally, when testing your controllers you can pass in mocked repositories and assert that the proper methods are called. This way, your controller tests don't need to setup a database (these tests would also need to be changed if you changed your database).
If writing SQL queries becomes unwieldy or you'd like to protect against a scenario where you need to change the underlying database, you can look into an ORM. But if you choose to use one, you should still wrap your use of the ORM in the repository pattern so your controllers are less tightly coupled with database code.
