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,Booleanfromsqlalchemy. - Build a new table called
datawith 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
enginetometadata.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))
This exercise is part of the course
Introduction to Databases in Python
In this course, you'll learn the basics of relational databases and how to interact with them.
In the previous chapters, you interacted with existing databases and queried them in different ways. Now, you will learn how to build your own databases and keep them updated.
Exercise 1: Creating databases and tablesExercise 2: Creating tables with SQLAlchemyExercise 3: Constraints and data defaultsExercise 4: Inserting data into a tableExercise 5: Inserting a single rowExercise 6: Inserting multiple records at onceExercise 7: Loading a CSV into a tableExercise 8: Updating data in a tableExercise 9: Updating individual recordsExercise 10: Updating multiple recordsExercise 11: Correlated updatesExercise 12: Deleting data from a databaseExercise 13: Deleting all the records from a tableExercise 14: Deleting specific recordsExercise 15: Deleting a table completelyWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.