1. Creating databases and tables
With a solid understanding of querying, it's now time to learn how to create databases and tables.
2. Creating databases
Creating databases is different for every database type, and it often requires the use of a command line tool or management application. All these tools are beyond the scope of this class. However with SQLite, if you supply a filename that does not exist to the create_engine method, it will create that file which creates the database.
3. Building a table
In this example, we are creating a table to store data about our employees. We've already created an engine and metadata. We want to store the employee's id, name, salary, and active status.
We start by importing everything we need. This includes the Table and Column objects along with any SQL Data types we want to use in our columns. There are many SQL Data types, and the SQLAlchemy documentation is a great place to see all the ones that are available.
Next, we use the Table object to create a table named employees, in our metadata with a few columns. The first column is the id column and we made it an integer type to store the employees id. Next we have the name column which is a String that can be up to 255 characters long. Next we have the salary column which is a decimal type to hold the employees yearly salary. The last column is the active column which is a Boolean to let us know if they are still actively employed. We store that table object as employees.
Now, we use the create_all method on the metadata objects and pass it the engine to create the table in the database itself.
Finally, we use the table_names method of the engine to verify that the table was created.
4. Creating tables
As you saw in the prior example, creating tables can be done with SQLAlchemy just like queries. We will still use the Table object similar to how we used it for reflection. We'll just remove the autoload keyword arguments and replace them with the Column objects we want to exist in the table. Once we have our table object, we can use the metadata's create_all method and the engine to create the table in the actual database. While it's easy to create tables with SQLAlchemy, if you want to make changes to an existing table structure, such as adding or removing columns, you'll need to build raw SQL ALTER statements or use a tool like Alembic, which is outside the scope of this course.
5. Creating tables - additional column options
In addition to Columns having a type, they can also have constraints and defaults that are additional keyword arguments to the Column object. With constraints we can require that a column be unique or specify that a column can not be empty or null. There other more complex constraints as well that can be used to require any Boolean condition you can imagine. Default sets the initial value of a field if one is not supplied during an insert statement. Let's create a table with some constraints.
6. Building a table with additional options
We're going to create the same employees table; however, we want to make sure the name column is unique and not allowed to be empty. We established a default salary of $100.00, and also set active to be True by default. Finally, we check to see what constraints are on the table using the constraints attribute. We can see that our desired constraints and defaults are in place.
7. Let's practice!
Time for you to create a few tables of your own.