SQLAlchemy generated table schema doesn't take default value into consideration

Here is a sample python script to generate the CREATE TABLE statement for MySQL.

import sqlalchemy
from sqlalchemy import Table, Column, Integer, ForeignKey, Boolean
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import CreateTable
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://user:password@127.0.0.1/test', echo=True)
Base = declarative_base()

association_table = Table('association', Base.metadata,
    Column('parent_id', Integer, ForeignKey('parent.id')),
    Column('child_id', Integer, ForeignKey('child.id'))
)

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

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    read = Column(Boolean, default=False, nullable=False)

print(CreateTable(Child.__table__).compile(engine))
print('sqlalchemy verison: {}'.format(sqlalchemy.__version__))

Running it will get this SQL:

$ python ~/sqlalchemy-create-table-column-default.py 

CREATE TABLE child (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    `read` BOOL NOT NULL, 
    PRIMARY KEY (id), 
    CHECK (`read` IN (0, 1))
)


-- sqlalchemy verison: 1.0.6

mysql> CREATE TABLE child (
    -> id INTEGER NOT NULL AUTO_INCREMENT, 
    -> `read` BOOL NOT NULL, 
    -> PRIMARY KEY (id), 
    -> CHECK (`read` IN (0, 1))
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `read` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

As you can see, it ignores the default value in the CREATE TABLE statement for the column read, which should have a DEFAULT 0 part.

Comments (1)