Creating databases and tables

Introduction to Databases in Python

Jason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Creating databases

Varies by the database type
Databases like PostgreSQL and MySQL have command-line tools to initialize the database
With SQLite, the create_engine() statement will create the database and file is they do not already exist

Building a table

from sqlalchemy import (Table, Column, String, 
       Integer, Decimal, Boolean)

employees = Table('employees', metadata,
       Column('id', Integer()), 
       Column('name', String(255)), 
       Column('salary', Decimal()), 
       Column('active', Boolean()))

metadata.create_all(engine)

engine.table_names()

[u'employees']

Creating tables

Still uses the Table object like we did for reflection
Replaces the autoload keyword arguments with Column objects
Creates the tables in the actual database by using the create_all() method on the MetaData instance
You need to use other tools to handle database table updates, such as Alembic or raw SQL

Creating tables - additional column options

unique forces all values for the data in a column to be unique
nullable determines if a column can be empty in a row
default sets a default value if one isn't supplied.

Building a table with additional options

employees = Table('employees', metadata,
       Column('id', Integer()), 
       Column('name', String(255), unique=True, nullable=False), 
       Column('salary', Float(), default=100.00),          
       Column('active', Boolean(), default=True))

employees.constraints

{CheckConstraint(... 
Column('name', String(length=255), table=<employees>, nullable=False), 
Column('salary', Float(), table=<employees>, 
       default=ColumnDefault(100.0)), 
Column('active', Boolean(), table=<employees>, 
       default=ColumnDefault(True)), ...
UniqueConstraint(Column('name', String(length=255), 
                 table=<employees>, nullable=False))}

Let's practice!

Introduction to Databases in Python