The problem is well known and the keyword you may be searching for is pooling.
The idea is that opening a connection is a complicated task: is usually requires reaching the server, doing authentication, checking the rights, auditing, etc.
On the other hand, preserving the same connection through the application by either storing it in a static variable or passing it to every method which may require access to the database is usually ugly and difficult to implement. Not counting the fact that in this case, the connection may remain opened for a long time while being unused, eventually reaching the maximum number of connections allowed by the server.
Connection pooling consists of preserving the pool of connections under the hood in order to make it possible for the programmer to open the connection and close it as soon as it is not needed any longer. The actual connections are managed transparently, meaning that you don't have the overheat of a connection opened again and again and you are less at risk of using all available connections.
Now, not reusing the connection doesn't mean you should duplicate database logic in every of your tests. You may put connection and disposal logic in a class with special
__exit__ methods, and then use it as:
with DatabaseConnection() as db:
cursor = db.connection.cursor()
Note that since you mention tests in your code sample:
You may be interested in using a testing framework. After all, you said that you "just play with Python, learning by doing", so why not to learn a Python's testing framework as well?
As others noted, unit tests shouldn't access a database. But access to databases is perfectly fine in integration or system tests.