Creating a new table using SQL Alchemy

One of the most common assignments with SQL and SQL Alchemy is to create a table within a schema and database. Let’s create one!

import pandas as pd
from dotenv import load_dotenv
import os as os

load_dotenv('.env')

from sqlalchemy import (create_engine, Column, DateTime, Numeric, MetaData, Table,
                        Integer, Boolean)
from sqlalchemy.dialects.postgresql import TIMESTAMP

user = os.environ['login']
password = os.environ['password']
dbname = os.environ['dbname']
host = os.environ['host']
port = os.environ['port']

engine = create_engine(
    (
        f"postgresql://{user}:"
        f"{password}"
        f"@{host}:{port}/{dbname}"
    )
)

connection = engine.connect()

metadata = MetaData()

# Define new table named new_home_log
new_home_log = Table('new_home_log', metadata,
                    Column('leave_time', DateTime, nullable=True),
                    Column('arrive_time', DateTime, nullable=True),
                    Column('daily_commute', Boolean, nullable=True),
                    Column('distance(mi)', Numeric(10,2), nullable=True),
                    Column('daily_counter', Integer, nullable=True)
)

metadata.create_all(engine)

# Let's see if our table exists:
new_home_log_query = "SELECT * FROM public.new_home_log"
pd.read_sql_query(new_home_log_query, connection)

Here’s what our new_home_log table should look like: