The first thing we need to do is create a class to represent the data in the table.
A class like this that represents some data is commonly called a model.
Tip
That's why this package is called SQLModel. Because it's mainly used to create SQL Models.
For that, we will import SQLModel (plus other things we will also use) and create a class Hero that inherits from SQLModel and represents the table model for our heroes:
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=None# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=None# More code here later π
This class Herorepresents the table for our heroes. And each instance we create later will represent a row in the table.
We use the config table=True to tell SQLModel that this is a table model, it represents a table.
Info
It's also possible to have models without table=True, those would be only data models, without a table in the database, they would not be table models.
Those data models will be very useful later, but for now, we'll just keep adding the table=True configuration.
The next step is to define the fields or columns of the class by using standard Python type annotations.
The name of each of these variables will be the name of the column in the table.
And the type of each of them will also be the type of table column:
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=None# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=None# More code here later π
Let's start with age, notice that it has a type of int | None (or Optional[int]).
And we import that Optional from the typing standard module.
That is the standard way to declare that something "could be an int or None" in Python.
And we also set the default value of age to None.
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=None# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=None# More code here later π
Now let's review the id field. This is the primary key of the table.
So, we need to mark id as the primary key.
To do that, we use the special Field function from sqlmodel and set the argument primary_key=True:
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=None# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=None# More code here later π
That way, we tell SQLModel that this id field/column is the primary key of the table.
But inside the SQL database, it is always required and can't be NULL. Why should we declare it with Optional?
The id will be required in the database, but it will be generated by the database, not by our code.
So, whenever we create an instance of this class (in the next chapters), we will not set the id. And the value of id will be Noneuntil we save it in the database, and then it will finally have a value.
my_hero=Hero(name="Spider-Boy",secret_name="Pedro Parqueador")do_something(my_hero.id)# Oh no! my_hero.id is None! π±π¨# Imagine this saves it to the databasesomehow_save_in_db(my_hero)do_something(my_hero.id)# Now my_hero.id has a value generated in DB π
So, because in our code (not in the database) the value of idcould beNone, we use Optional. This way the editor will be able to help us, for example, if we try to access the id of an object that we haven't saved in the database yet and would still be None.
Now, because we are taking the place of the default value with our Field() function, we set the actual default value of id to None with the argument default=None in Field():
Field(default=None)
If we didn't set the default value, whenever we use this model later to do data validation (powered by Pydantic) it would accept a value of None apart from an int, but it would still require passing that None value. And it would be confusing for whoever is using this model later (probably us), so better set the default value here.
It is an object that handles the communication with the database.
If you have a server database (for example PostgreSQL or MySQL), the engine will hold the network connections to that database.
Creating the engine is very simple, just call create_engine() with a URL for the database to use:
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)SQLModel.metadata.create_all(engine)# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)SQLModel.metadata.create_all(engine)# More code here later π
Each supported database has its own URL type. For example, for SQLite it is sqlite:/// followed by the file path. For example:
sqlite:///database.db
sqlite:///databases/local/application.db
sqlite:///db.sqlite
SQLite supports a special database that lives all in memory. Hence, it's very fast, but be careful, the database gets deleted after the program terminates. You can specify this in-memory database by using just two slash characters (//) and no file name:
sqlite://
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)SQLModel.metadata.create_all(engine)# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)SQLModel.metadata.create_all(engine)# More code here later π
In this example, we are also using the argument echo=True.
It will make the engine print all the SQL statements it executes, which can help you understand what's happening.
It is particularly useful for learning and debugging:
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)SQLModel.metadata.create_all(engine)# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)SQLModel.metadata.create_all(engine)# More code here later π
SQLModel defines its own create_engine() function. It is the same as SQLAlchemy's create_engine(), but with the difference that it defaults to use future=True (which means that it uses the style of the latest SQLAlchemy, 1.4, and the future 2.0).
And SQLModel's version of create_engine() is type annotated internally, so your editor will be able to help you with autocompletion and inline errors.
This also means that you have to call SQLModel.metadata.create_all()after the code that creates new model classes inheriting from SQLModel.
For example, let's imagine you do this:
Create the models in one Python file models.py.
Create the engine object in a file db.py.
Create your main app and call SQLModel.metadata.create_all() in app.py.
If you only imported SQLModel and tried to call SQLModel.metadata.create_all() in app.py, it would not create your tables:
# This wouldn't work! π¨fromsqlmodelimportSQLModelfrom.dbimportengineSQLModel.metadata.create_all(engine)
It wouldn't work because when you import SQLModel alone, Python doesn't execute all the code creating the classes inheriting from it (in our example, the class Hero), so SQLModel.metadata is still empty.
But if you import the models before calling SQLModel.metadata.create_all(), it will work:
This would work because by importing the models, Python executes all the code creating the classes inheriting from SQLModel and registering them in the SQLModel.metadata.
As an alternative, you could import SQLModel and your models inside of db.py:
The import of SQLModel from db.py would work because SQLModel is also imported in db.py.
And this trick would work correctly and create the tables in the database because by importing SQLModel from db.py, Python executes all the code creating the classes that inherit from SQLModel in that db.py file, for example, the class Hero.
fast βπ¬ We set echo=True, so this will show the SQL codepython app.py π¬ First, some boilerplate SQL that we are not that interested in INFO Engine BEGIN (implicit) INFO Engine PRAGMA main.table_info("hero") INFO Engine [raw sql] () INFO Engine PRAGMA temp.table_info("hero") INFO Engine [raw sql] () INFO Engine
π¬ Finally, the glorious SQL to create the table β¨ CREATE TABLE hero ( id INTEGER, name VARCHAR NOT NULL, secret_name VARCHAR NOT NULL, age INTEGER, PRIMARY KEY (id) )
π¬ More SQL boilerplate INFO Engine [no key 0.00020s] () INFO Engine COMMIT
This is one of the differences. Each database supports some particular data types, like INTEGER and TEXT.
Some databases have some particular types that are special for certain things. For example, PostgreSQL and MySQL support BOOLEAN for values of True and False. SQLite accepts SQL with booleans, even when defining table columns, but what it actually uses internally are INTEGERs, with 1 to represent True and 0 to represent False.
The same way, there are several possible types for storing strings. SQLite uses the TEXT type. But other databases like PostgreSQL and MySQL use the VARCHAR type by default, and VARCHAR is one of the most common data types.
VARCHAR comes from variable length character.
SQLAlchemy generates the SQL statements to create tables using VARCHAR, and then SQLite receives them, and internally converts them to TEXTs.
Additional to the difference between those two data types, some databases like MySQL require setting a maximum length for the VARCHAR types, for example VARCHAR(255) sets the maximum number of characters to 255.
To make it easier to start using SQLModel right away independent of the database you use (even with MySQL), and without any extra configurations, by default, str fields are interpreted as VARCHAR in most databases and VARCHAR(255) in MySQL, this way you know the same class will be compatible with the most popular databases without extra effort.
Tip
You will learn how to change the maximum length of string columns later in the Advanced Tutorial - User Guide.
Now let's restructure the code a bit to make it easier to reuse, share, and test later.
Let's move the code that has the main side effects, that changes data (creates a file with a database and a table) to a function.
In this example it's just the SQLModel.metadata.create_all(engine).
Let's put it in a function create_db_and_tables():
fromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:strsecret_name:strage:int|None=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)# More code here later π
fromtypingimportOptionalfromsqlmodelimportField,SQLModel,create_engineclassHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:strsecret_name:strage:Optional[int]=Nonesqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)# More code here later π
If SQLModel.metadata.create_all(engine) was not in a function and we tried to import something from this module (from this file) in another, it would try to create the database and table every time we executed that other file that imported this module.
We don't want that to happen like that, only when we intend it to happen, that's why we put it in a function, because we can make sure that the tables are created only when we call that function, and not when this module is imported somewhere else.
Now we would be able to, for example, import the Hero class in some other file without having those side effects.
Tip
π Spoiler alert: The function is called create_db_and_tables() because we will have more tables in the future with other classes apart from Hero. π
We prevented the side effects when importing something from your app.py file.
But we still want it to create the database and table when we call it with Python directly as an independent script from the terminal, just as as above.
Tip
Think of the word script and program as interchangeable.
The word script often implies that the code could be run independently and easily. Or in some cases it refers to a relatively simple program.
For that we can use the special variable __name__ in an if block: