CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyalembic
Vishal Patil
Vishal Patil

Reputation: 65

How do you create a sequence using Sqlalchemy and add that particular sequence to a table?

rollback.py :

def write_to_db(dataset_id):
    try:
        initialize_datamodels()
        EpdUmpPushRollback = datamodels.relational_tables.EpdUmpPushRollback
        with session_scope() as session:
            epdumppushrollback_obj = EpdUmpPushRollback()
            epdumppushrollback_obj.dataset_id = dataset_id
            epdumppushrollback_obj.record_id = ''
            epdumppushrollback_obj.operator_name = 'vis'
            epdumppushrollback_obj.activation_flag = 'active'
            epdumppushrollback_obj.record_creation_time = now()
            epdumppushrollback_obj.start_time = now()
            session.add(epdumppushrollback_obj)
            session.flush()

    except Exception as e:
        #err = "Error in updating the table epd_ump_push_rollback "
        #_log.exception(err)
        _log.exception("Error in updating the table {}".format(e))

table.py :

"""epd_ump_push_rollback_table

Revision ID: 4e4d99a8e544
Revises: c010f4d4b319
Create Date: 2018-12-19 18:04:30.271380

"""
from alembic import op
from sqlalchemy import Column, String, INTEGER, VARCHAR, NVARCHAR, TIMESTAMP, \
    Enum, ForeignKey, Sequence, MetaData

# revision identifiers, used by Alembic.
revision = '4e4d99a8e544'
down_revision = '2396e1b7de5c'
branch_labels = None
depends_on = None

meta = MetaData()
seq_obj = Sequence('record_id_seq', metadata=meta)


def upgrade():
    activation_flag_state = Enum('active', 'inactive', name="activation_flag_state")

    op.create_table('epd_ump_push_rollback',
                    Column('dataset_id', String, ForeignKey('epd_ip_dataset.dataset_id'),
                           primary_key=True),
                    Column('record_id', INTEGER, seq_obj, server_default=seq_obj.next_value(),
                           primary_key=True),
                    Column('operator_name', String, nullable=False),
                    Column('activation_flag', activation_flag_state, nullable=False),
                    Column('record_creation_time', TIMESTAMP),
                    Column('start_time', TIMESTAMP),
                    Column('end_time', TIMESTAMP))


def downgrade():
    op.drop_table('epd_ump_push_rollback')
    op.execute('DROP type activation_flag_state')

Explanation:

In the rollback.py file I am writing into db. I am setting a session with db(postgresql) using with session_scope() as session:. I am creating a object of table EpdUmpPushRollback and setting those values appropriately. The column record_id should be generated as sequence which I am defining in table.py and I am using alembic to upgrade my schema to new one which will have the table EpdUmpPushRollback.

I have two questions now.

  1. For the column where we have defined sequence, is it mandatory to add using pdumppushrollback_obj.record_id = '' or it gets added automatically?
  2. What should be the name of the sequence, whenever I try to add any entry in the table, it throws this error.

Error: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) "record_id_seq" is not a sequence [SQL: 'INSERT INTO epd_ump_push_rollback (dataset_id, operator_name, activation_flag, record_creation_time, start_time, end_time) VALUES (%(dataset_id)s, %(operator_name)s, %(activation_flag)s, now(), now(), %(end_time)s) RETURNING epd_ump_push_rollback.record_id'] [parameters: {'dataset_id': '20181221_1200_mno', 'operator_name': 'vis', 'activation_flag': 'active', 'end_time': None}]

Upvotes: 2

Views: 11358

Answers (2)

Srđan Popić
Srđan Popić

Reputation: 1271

The line

seq_obj = Sequence('record_id_seq', metadata=meta)

is not enough. You need to add, in upgrade(), above the table creation:

op.execute(schema.CreateSequence(seq_obj))

Also, drop sequence is needed in downgrade() function

Upvotes: 3

Thiago Valentim
Thiago Valentim

Reputation: 520

I already had this problem once and to solve it I created the sequence, so you can try this postgres statement:

CREATE SEQUENCE record_id_seq
   START WITH 1
   INCREMENT BY 1
   NO MINVALUE
   NO MAXVALUE
   CACHE 1;

Upvotes: 1

Related Questions