This is the 12th day of my participation in the August Challenge.

Hey, it’s Milo. Company three, please! Beg attention test development pit goods!

review

In the previous section, we wrote the functional interface for database configuration, but the front-end page has not been shown.

If the front-end page is added, deleted, changed and checked, I think we also need several functions:

  • Online test connection

    Because sometimes the database configuration will change, if we do not support the online test function, you may need to manually connect, very difficult to use!

  • Execute SQL online

    This is to enable users to run SQL statements online, similar to the functionality of the small IDE, of course we only do the most core part.

    So today we will improve the front page and complete the online connection test function.

The front part

Js is used to send HTTP requests, Model is used to manage variables, and components invoke specific methods through Dispatch.

The front page is divided into three parts, which can be understood as three divs:

  • Search bar
  • Add a column
  • Table column

This is the general presentation of the page, and today we are going to focus on the database connection.

Think about what to do

We’re still going to use SQLAlchemy for connections, but since we’re already using it and it supports asynchronous sessions, we’re not going to switch until we hit a big hole.

First, let’s consider the following points:

Connection reuse

First of all, we do database connection configuration because we use it when constructing data, so we have to maintain a map of database connections to act as a cache.

When our account password and other connection information remain unchanged, can we take out the session we used last time to continue to use it? The answer is yes!

And when we test the connection, the database configuration is cached, so if we want to use it, we can just grab it from the connection cache. With sqlAlchemy’s own connection pool, basically all we need to do is package a layer of dict.

Cache expansion problem

For example, I configured the database connection of A, tested it once, and you cached it for me. Then I changed A to B. Since the configuration of A and B is different, A piece of B data needs to be inserted into the cache. But because the database configuration is unlikely to change very often, we can also be aware of the previous cache during update configuration.

Multithreaded access cache issues

I’m not familiar with multithreading in Python, so I’m not sure if Fastapi has multiple threads reading and writing to the cache at the same time. If so, it will definitely cause problems. We need to add 🔒, so we can wait and see for now.

Hands on

To modify the previous models/init.py method, we need to add a DatabaseHelper class.

  • The init method

    The init method creates our internal dict cache, where key is the database connection information and value is the corresponding session.

  • get_connection

    Through the database configuration to obtain the corresponding connection information, first splicing key, get the unique key identification, then go to the cache to find whether to initialize the connection, if there is a direct return, not to start the connection.

  • get_jdbc_url

Note: I haven’t found a suitable asynchrony library for PG, so I don’t support it for now. I’ll support it later.

  • A method to delete the cache

  • Methods for testing connections

Accepts a session. If it is empty, an error message is returned. Execute select 1 tests whether the database can be connected. If no, an error message is displayed.

  • Rewrite the update method

  • Write online test methods

Testing a ha

That’s it for today, and in the next section we’ll write an online SQL function.