Generate database schema using SQLAlchemy

Question

I have a very simple One-to-Many database schema (a parent can have many children but a child can only have one parent). My SQLAlchemy models looks like so:

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

from models import Base

class Parent(Base):
    __tablename__ = 'Parent'
    id = Column(Integer, primary_key=True)
    children = relationship('Child', backref='parent')

class Child(Base):
    __tablename__ = 'Child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('Parent.id'))

I'm able to create the tables in the database using the following commands:

engine = create_engine('mysql://localhost:3306/testdb')
Base.metadata.create_all(engine)

I'm curious what the raw SQL looks like to create these tables. I'd imagine it would look something like this, but I would like to be sure:

CREATE TABLE Parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE Child (
    id INT NOT NULL AUTO_INCREMENT,
    parent_id int,
    PRIMARY KEY (id),
    CONSTRAINT FK_Parent FOREIGN KEY (parent_id) REFERENCES Parent(id)
);

Is there anyway to generate the database schema in raw sql using SQLAlchemy? I know I can generate a query in raw sql but I'm wondering how to generate the initial database schema.

On top of that, is there anyway to generate the schema depending on the actual database type (e.g. the raw sql would look slightly different for MySQL and PostgreSQL)?

score 6 · Accepted Answer · answered Dec 08 '18 at 14:14

how-can-i-get-the-create-table-drop-table-output-as-a-string

from sqlalchemy import MetaData, Table, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import CreateTable
import sqlalchemy
print(sqlalchemy.__version__)

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'Parent'
    id = Column(Integer, primary_key=True)
    children = Column(String(255))

user = Table('users', MetaData(bind=None),
            Column('id', Integer(), primary_key=True, nullable=False),
            Column('name', String()),
            Column('fullname', String()),
            Column('password', String()), schema=None)

print(CreateTable(Parent.__table__))
print(CreateTable(user))

Output:

1.3.0b1

CREATE TABLE "Parent" (
    id INTEGER NOT NULL, 
    children VARCHAR(255), 
    PRIMARY KEY (id)
)

CREATE TABLE users (
    id INTEGER NOT NULL, 
    name VARCHAR, 
    fullname VARCHAR, 
    password VARCHAR, 
    PRIMARY KEY (id)
)

Awesome thanks! Follow up question: when I create do `session.add` and `session.add_all`, is there anyway to generate that raw sql? — Johnny Metz, Dec 11 '18 at 05:35
Note you can add `echo=True` as an argument to `create_engine` if you want to see the raw sql for creating the tables and inserting the data. — Johnny Metz, Dec 11 '18 at 05:50

Generate database schema using SQLAlchemy

1 Answers1