Creating an API with python: Part 12: Database Character Encoding and Connections

In my previous post, Creating an API with python: Part 11: Running as a Service, I set up the FastAPI API to run as a service, controllable via the systemctl command. In this post, I will make some changes to the database code and schema to a). enable utf-8 encoding and to b). prevent database connections from dropping.

Prerequisites

These prerequisites are assumed for this post:

1. Creating an API with python: Part 1: GET Endpoints
2. Creating an API with python: Part 2: MariaDB Database
3. Creating an API with python: Part 3: POST Endpoints
4. Creating an API with python: Part 4: DELETE Endpoints
5. Creating an API with python: Part 5: Authentication
6. Creating an API with python: Part 6: HTTPS and Proxying
7. Creating an API with python: Part 7: CORS
8. Creating an API with python: Part 8: Multiple Account Support
9. Creating an API with python: Part 9: Authentication Scopes
10. Creating an API with python: Part 10: Integration Tests
11. Creating an API with python: Part 11: Running as a Service

As a reminder, the code for the FastAPI API is now on GitHub, with the project name taglink-api. You can checkout the latest copy of the code here: https://github.com/liz-allthecoding/taglink-api.

Step 1: Update database.py

Update the database.py create_engine call with pool_pre_ping and pool_recycle parameters. The pool_pre_ping parameter ensures that the database connection is pinged to check that it is still alive before initiating any queries. If it is found to be inactive, the connection is refreshed. The pool_recyle parameter sets a time after which the database connection should be automatically refreshed. A setting of ‘3600’ means it will be refreshed once an hour.

1. Change directory to the manager directory:

   $ cd manager
   

2. Open the database.py file and replace the line that starts engine = with the following:

   engine = create_engine(SQLALCHEMY_DATABASE_URL, pool_pre_ping=True, pool_recyle=3600)
   

Step 2: Update the database to use utf-8 encoding

The schema changes below are also in the file sql/schema_01_utf8.sql on GitHub.

1. Connect to MariaDB from the command line, using the root password you set up in Part 2:

   $ mysql -u root -p

2. Run the following to Set the encoding for the database to utf-8:

   MariaDB [(none)]> SET collation_connection = 'utf8_general_ci';

   MariaDB [(none)]> ALTER DATABASE apiservice CHARACTER SET utf8 COLLATE utf8_general_ci;

3. We will have to temporarily drop the foreign key constraints on our tables in order to update them:

   MariaDB [(none)]> USE apiservice; 

   MariaDB [(apiservice)]> ALTER TABLE taglink DROP FOREIGN KEY taglink_ibfk_1; -- tag_id 

   MariaDB [(apiservice)]> ALTER TABLE taglink DROP FOREIGN KEY taglink_ibfk_2; -- link_id 

   MariaDB [(apiservice]> ALTER TABLE taglink DROP FOREIGN KEY taglink_ibfk_3; -- account_id 

   MariaDB [(apiservice)]> ALTER TABLE link DROP FOREIGN KEY link_ibfk_1; -- account_id 

   MariaDB [(apiservice)]> ALTER TABLE tag DROP FOREIGN KEY tag_ibfk_1; -- account_id 

4. Now update the tables to use utf-8 encoding:

   MariaDB [(apiservice)]> ALTER TABLE account CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

   MariaDB [(apiservice)]> ALTER TABLE link CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

   MariaDB [(apiservice)]> ALTER TABLE tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

   MariaDB [(apiservice)]> ALTER TABLE taglink CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

   MariaDB [(apiservice)]> ALTER TABLE user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

5. Finally, re-add the foreign key constraints:

   MariaDB [(apiservice)]> ALTER TABLE taglink ADD CONSTRAINT FOREIGN KEY (tag_id) REFERENCES tag (tag_id);

   MariaDB [(apiservice)]> ALTER TABLE taglink ADD CONSTRAINT FOREIGN KEY (link_id) REFERENCES link (link_id);

   MariaDB [(apiservice)]> ALTER TABLE taglink ADD CONSTRAINT FOREIGN KEY (account_id) REFERENCES account (account_id);

   MariaDB [(apiservice)]> ALTER TABLE link ADD CONSTRAINT FOREIGN KEY (account_id) REFERENCES account (account_id);

   MariaDB [(apiservice)]> ALTER TABLE tag ADD CONSTRAINT FOREIGN KEY (account_id) REFERENCES account (account_id);

Step 3: Restart the Service

Restart the service so it picks up the new database and code changes. Make sure that your code change to manager/database.py is copied to your deployed location (/opt/taglink-api) first.

1. Copy database.py:

   $ sudo su
   $ cp manager/database.py /opt/taglink-api/manager/database.py
   $ chown taglink:taglink /opt/taglink-api/manager/database.py
   

2. Restart the service:

   $ sudo systemctl restart taglink-api
   

Step 4: Add New Integration Tests

Since we’ve updated the database to support utf-8 encoded characters, let’s test that it works with some new integration tests:

1. Open the file integration_tests/test_03_link.py. Add/modify the tests below. Be sure to renumber the tests afterwards, and modify the expected tag counts in later tests. Alternatively, get the latest copy of the file from GitHub.

       def test_311_create_link_account_with_tag_with_special_chars(self):
           LOG.info("====TEST create_link_account_with_tag_with_special_chars===")
           account_id = self.accounts[self.account_emails[1]]['account_id']
           account_token = self.get_account_token(account_id=account_id)
           link = self.create_link(link='https://test7.com', token=account_token, tag='両丢',
                                   account_id=account_id)
           self.store_link(link, self.account_emails[1])
   

       def test_321_get_links_by_tag_account(self):
           LOG.info("====TEST get_links_by_tag_account===")
           account_id = self.accounts[self.account_emails[1]]['account_id']
           account_token = self.get_account_token(account_id=account_id)
           links = self.get_links(token=account_token, tag='test1')
           self.assertTrue(len(links) >= 1)
   
           links = self.get_links(token=account_token, tag='test2 tag with spaces')
           self.assertTrue(len(links) >= 1)
   
           links = self.get_links(token=account_token, tag='両丢')
           self.assertTrue(len(links) >= 1)
   

2. Open the file integration_tests/test_04_tag.py. Add/modify the tests below. Be sure to renumber the tests afterwards, and modify the expected tag counts in later tests. Alternatively, get the latest copy of the file from GitHub.

       def test_409_create_tag_account_with_special_chars(self):
           LOG.info("====TEST create_tag_account_with_special_chars===")
           account_id = self.accounts[self.account_emails[1]]['account_id']
           account_token = self.get_account_token(account_id=account_id)
           tag = self.create_tag(tag='両丢', token=account_token)
           self.store_tag(tag, self.account_emails[1])
   

       def test_420_get_tags_by_tag_account(self):
           LOG.info("====TEST get_tags_by_tag_account===")
           account_id = self.accounts[self.account_emails[1]]['account_id']
           account_token = self.get_account_token(account_id=account_id)
           tags = self.get_tags(token=account_token, tag='test1')
           self.assertEqual(len(tags), 1)
   
           tags = self.get_tags(token=account_token, tag='test1 tag with spaces')
           self.assertEqual(len(tags), 1)
   
           tags = self.get_tags(token=account_token, tag='両丢')
           self.assertEqual(len(tags), 1)
   

Step 5: Run the Integration Tests

Run the integration tests to verify that the API is working correctly.

1. Ensure that the service is running:

   $ sudo systemctl start taglink-api
   

2. On your server, change to the code directory (~/vboxshare/fastapi should be replaced with the path to your FastAPI python code):

   $ cd ~/vboxshare/fastapi
   

3. Run the integration tests:

   $ ./integration_test.sh
   

Conclusion

You should now have a FastAPI API running with a database that supports utf-8 characters and with database connection checking and recycling. This makes the API international and resilient to database connections dropping.

If you want to find out about adding pagination to the API, see my follow-on post, Creating an API with python: Part 13: Pagination.

Thanks for reading!