Preparing Your SQLite Database
00:00 Preparing your SQLite Database.
00:05 Although you can reach your API endpoint to shorten the URL, nothing has happened in the back end yet. In this section, you’ll create your database, making it ready to connect to your code.
First store the code that you need for the database connection in a file named
First you import what’s needed from
.config. SQLAlchemy uses
create_engine to define your database
engine. You can think of
engine as the entry point to the database.
The first argument is a database URL, which you receive from
db_url of the settings. You set
False because you’re working with a SQLite database. With this connection argument, SQLite allows more than one request at a time to communicate with the database.
sessionmaker to create the
SessionLocal class. You’ll create a working database session when you instantiate
declarative_base function returns a class that connects the database engine to the SQLAlchemy functionality of the models. You assign
Base will be the class that the database model inherits from your
models.py file. While
database.py contains information about the database connection, the
models.py file will describe the content of the database.
First, the required imports from SQLAlchemy are performed, along with the
Base class you just created in
The code that you add to
models.py looks similar to the code that you wrote in
schemas.py, you define what data your API expected from the client and the server. In
models.py, you declare how your data should be stored in the database.
Here, you create a database model named
URL model is a subclass of
Base, imported from
It’s common to give your model a singlular name and your database tables plural names. That’s why you name your model
URL and provide the special variable
__tablename__. Next you define
id as your database’s primary key. By setting the
primary_key argument to
True, you don’t need to provide a
unique argument, as it defaults to
True for primary keys.
secret_key columns will also contain unique entries. The
key field will contain the random string that will be part of the shortened URL. With
secret_key, you can provide a secret key to the user to manage their shortened URL and see statistics. Here, you define the column
target_url to store the URL strings for which your app provides shortened URLs.
It’s important that this column doesn’t have
unique set to
True. If you only accepted unique values for this database field, then you’d prevent different users from forwarding to the same URL.
03:36 Your app’s expected behavior is that any user can create a shortened URL for any target URL without knowing if such a forward already exists. So while you’ll provide a unique shortened URL, multiple shortened URLs may forward to the same address.
As you’ll see later, the
is_active Boolean column will come in handy when users want to delete a shortened URL. Instead of giving the user the power to delete a database entry directly, you’ll make the entry inactive instead. That way, you’ll have a safety net for critical actions, and you can undo the deletion if it was triggered accidentally. Finally, you define the
clicks column, which starts with zero. Later on in the course, you’ll increment this integer each time someone clicks the shortened link.
04:28 In the next section of the course, you’ll connect the application to the database.
It’s actually the default colour theme for VSCode, but it’s difficult to tell what editor it is as it’s zoomed in without the window chrome!
Become a Member to join the conversation.
mandar-gite on Sept. 19, 2022
Which is the colour theme you are using in PyCharm?