Debug TiDB Cloud Documentation: Connect to TiDB with SQLAlchemy

[qiancai]

This issue is a sub-issue of Debug TiDB Cloud Documentation: Summary Issue · Issue #15480 · pingcap/docs. The purpose of this sub-issue is to verify and debug the Connect to TiDB with SQLAlchemy document.

You can follow the instructions provided in #15480 to verify and debug the instructions in this document.

1. After finishing your verification, please add your verification result to this sub-issue as a comment. The result can be the issues you encounter, the mistakes you find, or any other findings. If everything looks fine, you can also add it as a comment.
2. For any issues you found during the verification, welcome to create a pull request (PR) to fix them directly. In the pull request, please indicate which issue this PR resolves in the PR description (for example, fix #16109). To learn how to create a pull request, see TiDB Documentation Contributing Guide.

Your contribution to testing and verifying the documentation is highly appreciated!

[qiancai]

Hi @minaelee, PTAL here. Thanks.

[minaelee]

In light of @ran-huang's comment in issue #15741 indicating that many of these "Connect to..." guides are being rewritten to use the new connection strings, I didn't give this document too much of a hard time. I will note that everything worked, and I used the connection string given in the Connect dialog in the .env file.

[minaelee]

I hesitate to rewrite the actual document with these steps, because I don't know what approach your team is taking toward updating these documents. Maybe these notes could be helpful to someone anyway!

What I used to test: I took the connection string for SQLAlchemy provided by the Connect dialog that looks like:

mysql+pymysql://4TkAZe59LRrDmGD.root:<PASSWORD>@gateway01.us-west-2.prod.aws.tidbcloud.com:4000/bikeshare?ssl_ca=/etc/ssl/cert.pem&ssl_verify_cert=true&ssl_verify_identity=true

I do have one quibble here. The documentation states that PyMySQL is the preferred driver. However, the default driver code shown by the Connect dialog is for mysqlclient. If PyMySQL is still the preferred driver, then it should be the default code shown in the Connect dialog. This may not be a docs issue however, since it's part of the platform UI.

I replaced <PASSWORD> with my password, then placed it into the .env file as shown below, deleting everything else in there:

DATABASE_URL="mysql+pymysql://4TkAZe59LRrDmGD.root:MYPASSWORDHERE@gateway01.us-west-2.prod.aws.tidbcloud.com:4000/bikeshare?ssl_ca=/etc/ssl/cert.pem&ssl_verify_cert=true&ssl_verify_identity=true"

I specifically used DATABASE_URL for the connection string's env variable name because it's what's used in two of the already updated Connect to... guides, the one for Prisma as well as the one for Ruby on Rails. For consistency.

In config.py, I deleted all the existing self.* definitions and replaced them with:

        self.database_url = os.getenv("DATABASE_URL", "")

Then I modified sqlalchemy_example.py's def get_db_engine() to:

def get_db_engine():
    config = Config()

    return create_engine(
        config.database_url
    )

Then I ran python sqlalchemy-example.py and it worked as expected.