I'm using SqlAlchemy, a python ORM library. And I did previously access database from business layer directly by calling SqlAlchemy API.

However I discovered that will cause a lot of time to operate my test cases and today I believe maybe I ought to produce a DB access layer, in order to use mock objects throughout test rather than access database directly.

I believe you will find 2 options to achieve that :

  1. make use of a single class which consists of a DB connection and several techniques like addUser/delUser/updateUser, addBook/delBook/updateBook. But what this means is this class can be really large.

  2. Another approach is create different manager classes like "UserManager", "BookManager". But which means I must pass a listing of managers to Business layer, which appears just a little Cumbersome.

How would you organize a database layer?

This is a good question!
The issue is not trivial, and could require several methods to tackle it. For example:

  1. Organize the code, to ensure that you can look at the majority of the application logic without being able to access the database. Which means that each class may have techniques for being able to access data, and techniques for processing it, and also the second ones might be examined easily.
  2. When you really need to check database access, you can utilize a proxy (so, like solution #1) you are able to think about it as being an electric train engine for SqlAlchemy or like a drop-in alternative for that SA. In the two cases, you might want to want to a self initializing fake.
  3. When the code doesn't involve saved methods, consider using in-memory databases, like Lennart states (even when within this situation, calling it "unit test" may seem a little strange!).

However, from the experience, everything isn't very difficult on word, after which falls abruptly when you are around the area. For example, how to proceed when the majority of the logic is incorporated in the SQL claims? Let's say being able to access information is strictly interleaved using its processing? Many times you have the ability to refactor, sometimes (particularly with large and legacy programs) not.

Ultimately, It is really a few mindset.
If you feel you must have unit tests, and you must have them running fast, then you definitely design the application in in certain manner, that permit simpler unit testing.
Regrettably, this isn't always true (lots of people see unit tests as something which can run overnight, so time isn't an problem), and also you go that won't be really unit-testable.

I'd setup a database connection throughout testing that connects to some in memory database rather. Like so:

sqlite_memory_db = create_engine('sqlite://')

That'll be virtually as quickly as possible, you're also not hooking up to some real database, but simply a brief one out of memory, so it's not necessary to be worried about the alterations made by your results remaining following the test, etc. And it's not necessary to mock anything.