Creating tables with SQLAlchemy
Previously, you used the Table
object to reflect a table from an existing
database, but what if you wanted to create a new table? You'd still use the Table
object; however, you'd need to replace the autoload
and autoload_with
parameters with Column
objects.
The Column
object takes a name, a SQLAlchemy type with an optional
format, and optional keyword arguments for different constraints.
When defining the table, recall how in the video Jason passed in 255
as the maximum length of a String by using Column('name', String(255))
. Checking out the slides from the video may help.
After defining the table, you can create the table in the database by using the
.create_all()
method on metadata and supplying the engine as the only parameter. Go for it!
This is a part of the course
“Introduction to Databases in Python”
Exercise instructions
- Import
Table
,Column
,String
,Integer
,Float
,Boolean
fromsqlalchemy
. - Build a new table called
data
with columns'name'
(String(255)
),'count'
(Integer()
),'amount'
(Float()
), and'valid'
(Boolean()
) columns. The second argument ofTable()
needs to bemetadata
, which has already been initialized. - Create the table in the database by passing
engine
tometadata.create_all()
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Import Table, Column, String, Integer, Float, Boolean from sqlalchemy
from sqlalchemy import ____, ____, ____, ____, ____, ____
# Define a new table with a name, count, amount, and valid column: data
data = Table('data', ____,
Column(____, ____),
Column('count', Integer()),
Column(____, ____),
Column(____, ____)
)
# Use the metadata to create the table
metadata.create_all(____)
# Print table details
print(repr(data))