Help with ENUM insert during migration

[Nikola Radovanovic]

Hello,

I need help; google search and trial-and-error did not do me any good also.

I cant figure out how to populate DB table with ENUM values during migration using bulk_insert. to be more precise, NULL values are inserted instead of proper strings. I can insert using plain SQL from DB UI tools (DBeaver), tried same SQL in alembic script - still not working. 

Maybe this what I currently have is not the best approach, so feel free to point me into right direction. DB is Postgres and here is the relevant code

# model
class ConnectorType(Base):

    __tablename__ = 'ConnectorTypes'

    id = Column(Integer, primary_key=True)

    description = Column(String(255))

    type = Column(ENUM('MODBUS', 'BACNET', 'SNTP', 'SYSLOG', 'IPTV', name='connector_type'))

# alembic

connector_types = op.create_table('ConnectorTypes',

sa.Column('id', sa.Integer(), nullable=False),

sa.Column('description', sa.String(length=255), nullable=True),

sa.Column('type', ENUM(u'MODBUS', u'BACNET', u'SNTP', u'SYSLOG', u'IPTV', name='connector_type'), autoincrement=False, nullable=True),

sa.PrimaryKeyConstraint('id')

)

# insert connector types

op.bulk_insert(connector_types, [

    {'description': '', 'type': 'MODBUS'},

    {'description': '', 'type': 'BACNET'},

    {'description': '', 'type': 'SNTP'},

    {'description': '', 'type': 'SYSLOG'},

    {'description': '', 'type': 'IPTV'},

])

   

Thank you in advance.

[mike bayer]

Hi there -

not able to reproduce.

Using the following migration script:

"""rev1

Revision ID: 71b85e1758dc
Revises:
Create Date: 2017-03-25 22:22:35.731821

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '71b85e1758dc'
down_revision = None
branch_labels = None
depends_on = None

from sqlalchemy.dialects.postgresql import ENUM

def upgrade():

connector_types = op.create_table('ConnectorTypes',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('description', sa.String(length=255), nullable=True),
sa.Column('type', ENUM(u'MODBUS', u'BACNET', u'SNTP', u'SYSLOG',
u'IPTV', name='connector_type'), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id')
)
# insert connector types
op.bulk_insert(connector_types, [
{'description': '', 'type': 'MODBUS'},
{'description': '', 'type': 'BACNET'},
{'description': '', 'type': 'SNTP'},
{'description': '', 'type': 'SYSLOG'},
{'description': '', 'type': 'IPTV'},
])

def downgrade():
pass


run it, log into Postgresql:

test=# select * from "ConnectorTypes";
id | description | type
----+-------------+--------
1 | | MODBUS
2 | | BACNET
3 | | SNTP
4 | | SYSLOG
5 | | IPTV
(5 rows)




I would advise setting the debug level for "sqlalchemy.engine" in your
alembic.ini to INFO or DEBUG and watch the SQL emitted. It should look
like:

INSERT INTO "ConnectorTypes" (description, type) VALUES
(%(description)s, %(type)s)
INFO [sqlalchemy.engine.base.Engine] ({'type': u'MODBUS',
'description': ''}, {'type': u'BACNET', 'description': ''}, {'type':
u'SNTP', 'description': ''}, {'type': u'SYSLOG', 'description': ''},
{'type': u'IPTV', 'description': ''})



Note that since your table name is "ConnectorTypes", containing at least
one upper case character, this a case sensitive name. If you have
another table called "connectortypes", that's a *different* table. Make
sure that's not creating confusion.

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Nikola Radovanovic]

Hi, thank you for quick response - I will try again. Maybe my UI tool was giving me bad output since I did not see any error while inserting and while debugging I saw statement similar to yours. Will report back.

best regards

[Nikola Radovanovic]

Hello,

I tried now and it worked - very strange since I was debugging at Saturday more than 5 hours and kept displaying NULL. Thank you very much for your help!

Best regards

[Nikola Radovanovic]