Alembic op.alter_column deletes all rows in the database table in error

[Richard]

I have an alembic migration which renames a FK column on a table from
'customer_id' to 'customer_pk'.

I used to have more in the migration file but narrowed it down to this code
causing all the rows to be deleted.

def upgrade():
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)


I'm using alembic==1.0.0 and Python 3.6.4.

Is there something wrong with the above code or is this a bug in the
library?

Thanks

[Richard]

Note that if I do the same op.alter_column on another table which has a
customer FK, it works fine and does not delete all the rows.

Post by Richard
I have an alembic migration which renames a FK column on a table from
'customer_id' to 'customer_pk'.
I used to have more in the migration file but narrowed it down to this
code causing all the rows to be deleted.
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)
I'm using alembic==1.0.0 and Python 3.6.4.
Is there something wrong with the above code or is this a bug in the
library?
Thanks

[Mike Bayer]

Hi there -

I have no idea what you are seeing. an actual ALTER COLUMN
operation does not delete rows. Of course, if you are using SQLite
and batch mode, that might affect things, but you have not specified
this. Please specify complete information including log output,
stack traces, database in use, sample schema, etc.

Note that if I do the same op.alter_column on another table which has a customer FK, it works fine and does not delete all the rows.

I have an alembic migration which renames a FK column on a table from 'customer_id' to 'customer_pk'.
I used to have more in the migration file but narrowed it down to this code causing all the rows to be deleted.
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)
I'm using alembic==1.0.0 and Python 3.6.4.
Is there something wrong with the above code or is this a bug in the library?
Thanks

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
For more options, visit https://groups.google.com/d/optout.

[Richard]

I'm using postgres version 10, alembic 1.0.0, sqlalchemy 1.2.12. I'm aware
ALTER COLUMN in SQL doesn't delete rows, but op.alter_column is doing that.

My customer & daily reading models look like:


class Customer(DeclarativeBase):
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
electricity_readings = relationship(
'ElectricityMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
gas_readings = relationship(
'GasMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
daily_smart_meter_readings = relationship(
'DailyMeterReading',
cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)

class DailyMeterReading(DeclarativeBase):
__tablename__ = 'daily_smart_meter_readings'
id = Column(Integer, primary_key=True)
customer_pk = Column(
Integer, ForeignKey('customers.id'), nullable=False, index=True
)
reading = Column(Float, nullable=False)
reading_at = Column(UtcDateTime, nullable=False, index=True)

Post by Mike Bayer
Hi there -
I have no idea what you are seeing. an actual ALTER COLUMN
operation does not delete rows. Of course, if you are using SQLite
and batch mode, that might affect things, but you have not specified
this. Please specify complete information including log output,
stack traces, database in use, sample schema, etc.

Post by Richard
Note that if I do the same op.alter_column on another table which has a

customer FK, it works fine and does not delete all the rows.

Post by Richard

Post by Richard
I have an alembic migration which renames a FK column on a table from

'customer_id' to 'customer_pk'.

Post by Richard

Post by Richard
I used to have more in the migration file but narrowed it down to this

code causing all the rows to be deleted.

Post by Richard

Post by Richard
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)
I'm using alembic==1.0.0 and Python 3.6.4.
Is there something wrong with the above code or is this a bug in the

library?

Post by Richard

Post by Richard
Thanks

--
You received this message because you are subscribed to the Google

Groups "sqlalchemy-alembic" group.

Post by Richard
To unsubscribe from this group and stop receiving emails from it, send
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

can you provide SQLAlchemy statement output please, set "level = INFO"
under [logger_sqlalchemy] in alembic.ini

op.alter_column() does not emit DELETE and nothing in Alembic emits
the DELETE statement anywhere except upon the alembic_version table
itself.

I'm using postgres version 10, alembic 1.0.0, sqlalchemy 1.2.12. I'm aware ALTER COLUMN in SQL doesn't delete rows, but op.alter_column is doing that.
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
electricity_readings = relationship(
'ElectricityMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
gas_readings = relationship(
'GasMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
daily_smart_meter_readings = relationship(
'DailyMeterReading',
cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
__tablename__ = 'daily_smart_meter_readings'
id = Column(Integer, primary_key=True)
customer_pk = Column(
Integer, ForeignKey('customers.id'), nullable=False, index=True
)
reading = Column(Float, nullable=False)
reading_at = Column(UtcDateTime, nullable=False, index=True)

Post by Mike Bayer
Hi there -
I have no idea what you are seeing. an actual ALTER COLUMN
operation does not delete rows. Of course, if you are using SQLite
and batch mode, that might affect things, but you have not specified
this. Please specify complete information including log output,
stack traces, database in use, sample schema, etc.

Note that if I do the same op.alter_column on another table which has a customer FK, it works fine and does not delete all the rows.

I have an alembic migration which renames a FK column on a table from 'customer_id' to 'customer_pk'.
I used to have more in the migration file but narrowed it down to this code causing all the rows to be deleted.
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)
I'm using alembic==1.0.0 and Python 3.6.4.
Is there something wrong with the above code or is this a bug in the library?
Thanks

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
For more options, visit https://groups.google.com/d/optout.

[Richard]

INFO [alembic.runtime.migration] Running upgrade 22923a2e396c ->
d4aceba22da2, daily_smart_meter_readings rename customer fk.
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE
daily_smart_meter_readings RENAME customer_id TO customer_pk
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE INDEX
ix_daily_smart_meter_readings_customer_pk ON daily_smart_meter_readings (
customer_pk)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX one_customer_reading_at_meter_type_meter_point_id_register_uc
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE UNIQUE INDEX
one_customer_reading_at_meter_type_meter_point_id_register_uc ON
daily_smart_meter_readings (customer_pk, reading_at, meter_type,
meter_point_id, register_tier) WHERE register_tier IS NOT NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX one_customer_reading_at_meter_type_meter_point_id_tier_uc
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE UNIQUE INDEX
one_customer_reading_at_meter_type_meter_point_id_tier_uc ON
daily_smart_meter_readings (customer_pk, reading_at, meter_type,
meter_point_id) WHERE register_tier IS NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX ix_daily_smart_meter_readings_customer_id
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE
daily_smart_meter_readings DROP CONSTRAINT
daily_smart_meter_readings_customer_id_fkey
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE
daily_smart_meter_readings ADD FOREIGN KEY(customer_pk) REFERENCES
customers (id)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num
='d4aceba22da2' WHERE alembic_version.version_num = '22923a2e396c'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)

Post by Mike Bayer
can you provide SQLAlchemy statement output please, set "level = INFO"
under [logger_sqlalchemy] in alembic.ini
op.alter_column() does not emit DELETE and nothing in Alembic emits
the DELETE statement anywhere except upon the alembic_version table
itself.

Post by Richard
I'm using postgres version 10, alembic 1.0.0, sqlalchemy 1.2.12. I'm

aware ALTER COLUMN in SQL doesn't delete rows, but op.alter_column is doing
that.

Post by Richard
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
electricity_readings = relationship(
'ElectricityMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
gas_readings = relationship(
'GasMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
daily_smart_meter_readings = relationship(
'DailyMeterReading',
cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
__tablename__ = 'daily_smart_meter_readings'
id = Column(Integer, primary_key=True)
customer_pk = Column(
Integer, ForeignKey('customers.id'), nullable=False, index=True
)
reading = Column(Float, nullable=False)
reading_at = Column(UtcDateTime, nullable=False, index=True)

Post by Mike Bayer
Hi there -
I have no idea what you are seeing. an actual ALTER COLUMN
operation does not delete rows. Of course, if you are using SQLite
and batch mode, that might affect things, but you have not specified
this. Please specify complete information including log output,
stack traces, database in use, sample schema, etc.

Post by Richard
Note that if I do the same op.alter_column on another table which has

a customer FK, it works fine and does not delete all the rows.

Post by Richard

Post by Mike Bayer

Post by Richard

Post by Richard
I have an alembic migration which renames a FK column on a table

from 'customer_id' to 'customer_pk'.

Post by Richard

Post by Mike Bayer

Post by Richard

Post by Richard
I used to have more in the migration file but narrowed it down to

this code causing all the rows to be deleted.

Post by Richard

Post by Mike Bayer

Post by Richard

Post by Richard
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)
I'm using alembic==1.0.0 and Python 3.6.4.
Is there something wrong with the above code or is this a bug in the

library?

Post by Richard

Post by Mike Bayer

Post by Richard

Post by Richard
Thanks

--
You received this message because you are subscribed to the Google

Groups "sqlalchemy-alembic" group.

Post by Richard

Post by Mike Bayer

Post by Richard
To unsubscribe from this group and stop receiving emails from it,

<javascript:>.

Post by Richard

Post by Mike Bayer

Post by Richard
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google

Groups "sqlalchemy-alembic" group.

Post by Richard
To unsubscribe from this group and stop receiving emails from it, send
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

OK, so there is no DELETE statement in there. Where is the DELETE
happening? Are there rows being inserted in a previous migration
that aren't being committed? Can you place some assertions, e.g. :

def upgrade():
conn = op.get_bind()
num_rows = conn.scalar("select count(*) from mytable")
log.info("number of rows before alter: %s", num_rows)
op.alter_column(....)
new_num_rows = conn.scalar("select count(*) from mytable")
log.info("number of rows after alter: %s", new_num_rows)
assert num_rows == new_num_rows

INFO [alembic.runtime.migration] Running upgrade 22923a2e396c -> d4aceba22da2, daily_smart_meter_readings rename customer fk.
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE daily_smart_meter_readings RENAME customer_id TO customer_pk
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE INDEX ix_daily_smart_meter_readings_customer_pk ON daily_smart_meter_readings (customer_pk)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX one_customer_reading_at_meter_type_meter_point_id_register_uc
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE UNIQUE INDEX one_customer_reading_at_meter_type_meter_point_id_register_uc ON daily_smart_meter_readings (customer_pk, reading_at, meter_type, meter_point_id, register_tier) WHERE register_tier IS NOT NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX one_customer_reading_at_meter_type_meter_point_id_tier_uc
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE UNIQUE INDEX one_customer_reading_at_meter_type_meter_point_id_tier_uc ON daily_smart_meter_readings (customer_pk, reading_at, meter_type, meter_point_id) WHERE register_tier IS NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX ix_daily_smart_meter_readings_customer_id
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE daily_smart_meter_readings DROP CONSTRAINT daily_smart_meter_readings_customer_id_fkey
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE daily_smart_meter_readings ADD FOREIGN KEY(customer_pk) REFERENCES customers (id)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='d4aceba22da2' WHERE alembic_version.version_num = '22923a2e396c'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)

Post by Mike Bayer
can you provide SQLAlchemy statement output please, set "level = INFO"
under [logger_sqlalchemy] in alembic.ini
op.alter_column() does not emit DELETE and nothing in Alembic emits
the DELETE statement anywhere except upon the alembic_version table
itself.

I'm using postgres version 10, alembic 1.0.0, sqlalchemy 1.2.12. I'm aware ALTER COLUMN in SQL doesn't delete rows, but op.alter_column is doing that.
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
electricity_readings = relationship(
'ElectricityMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
gas_readings = relationship(
'GasMeterReading', cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
daily_smart_meter_readings = relationship(
'DailyMeterReading',
cascade='all,delete-orphan',
backref=backref('customer', cascade='all')
)
__tablename__ = 'daily_smart_meter_readings'
id = Column(Integer, primary_key=True)
customer_pk = Column(
Integer, ForeignKey('customers.id'), nullable=False, index=True
)
reading = Column(Float, nullable=False)
reading_at = Column(UtcDateTime, nullable=False, index=True)

Post by Mike Bayer
Hi there -
I have no idea what you are seeing. an actual ALTER COLUMN
operation does not delete rows. Of course, if you are using SQLite
and batch mode, that might affect things, but you have not specified
this. Please specify complete information including log output,
stack traces, database in use, sample schema, etc.

Note that if I do the same op.alter_column on another table which has a customer FK, it works fine and does not delete all the rows.

I have an alembic migration which renames a FK column on a table from 'customer_id' to 'customer_pk'.
I used to have more in the migration file but narrowed it down to this code causing all the rows to be deleted.
op.alter_column(
'daily_smart_meter_readings', column_name='customer_id',
new_column_name='customer_pk',
)
I'm using alembic==1.0.0 and Python 3.6.4.
Is there something wrong with the above code or is this a bug in the library?
Thanks

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
For more options, visit https://groups.google.com/d/optout.