SHARE
TWEET

Untitled

a guest Jul 22nd, 2019 61 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. """Convert lat/long from float to int
  2.  
  3. Revision ID: b020841d98e4
  4. Revises: 6e741a21efc8
  5. Create Date: 2019-07-10 20:03:38.282042
  6.  
  7.  
  8. Given a source table like
  9. class GPS(Base):
  10.     # $--RMC, hhmmss.sss, x, llll.lll, a, yyyyy.yyy, a, x.x, u.u, xxxxxx,, , v * hh < CR > < LF >
  11.  
  12.     __table_args__ = (UniqueConstraint("date_time", name="uix_dt"),)
  13.  
  14.     video_id = Column(Integer, ForeignKey("Video.id"))
  15.     video = relationship("Video", back_populates="coordinates")
  16.  
  17.     #time = Column(Time)
  18.     date_time = Column(DateTime)
  19.  
  20.     status = Column(String)
  21.     latitude = Column(Float)
  22.     north_south = Column(String)
  23.  
  24.     longitude = Column(Float)
  25.     east_west = Column(String)
  26.     speed = Column(Float)
  27.     course = Column(Float)
  28.    
  29.  
  30. Where I want to convert all of the floating point columns to integer AND convert the data.
  31.  
  32.  
  33. """
  34. from alembic import op
  35. import sqlalchemy as sa
  36. from sqlalchemy import orm
  37. from sqlalchemy.ext.declarative import declarative_base
  38.  
  39. # revision identifiers, used by Alembic.
  40. revision = 'b020841d98e4'
  41. down_revision = '6e741a21efc8'
  42. branch_labels = None
  43. depends_on = None
  44.  
  45.  
  46. Base = declarative_base()
  47.  
  48.  
  49. class GPS(Base):
  50.     __tablename__ = "GPS"
  51.  
  52.     id = sa.Column(sa.Integer, primary_key=True)
  53.     latitude = sa.Column(sa.Float)
  54.     _latitude = sa.Column(sa.Integer)
  55.  
  56.     longitude = sa.Column(sa.Float)
  57.     _longitude = sa.Column(sa.Integer)
  58.  
  59.     speed = sa.Column(sa.Float)
  60.     _speed = sa.Column(sa.Integer)
  61.  
  62.     course = sa.Column(sa.Float)
  63.     _course = sa.Column(sa.Integer)
  64.  
  65.  
  66. def upgrade():
  67.     with op.batch_alter_table("GPS") as batch_op:
  68.         batch_op.add_column(sa.Column("_latitude", sa.Integer))
  69.         batch_op.add_column(sa.Column("_longitude", sa.Integer))
  70.         batch_op.add_column(sa.Column("_speed", sa.Integer))
  71.         batch_op.add_column(sa.Column("_course", sa.Integer))
  72.  
  73.     bind = op.get_bind()
  74.     session = orm.Session(bind=bind)
  75.  
  76.     i = 0
  77.     c_count = session.query(GPS).count()
  78.     seven = 10 ** 7
  79.     for coordinate in session.query(GPS):  # type: GPS
  80.  
  81.         i += 1
  82.         coordinate._latitude = int(coordinate.latitude * seven)
  83.         coordinate._longitude = int(coordinate.longitude * seven)
  84.         coordinate._course = int(coordinate.course * 1000)
  85.         coordinate._speed = int(coordinate.speed * 100)
  86.         session.add(coordinate)
  87.         if i % 3000 == 0:
  88.             print(f"\tProcessed {i}/{c_count}")
  89.             session.commit()
  90.  
  91.     session.commit()
  92.  
  93.     with op.batch_alter_table("GPS") as batch_op:
  94.         batch_op.drop_column("latitude")
  95.         batch_op.drop_column("longitude")
  96.         batch_op.drop_column("status")
  97.         batch_op.drop_column("speed")
  98.         batch_op.drop_column("course")
  99.  
  100.  
  101. # noinspection PyProtectedMember
  102. def downgrade():
  103.  
  104.     with op.batch_alter_table("GPS") as batch_op:
  105.         batch_op.add_column(sa.Column("latitude", sa.Float))
  106.         batch_op.add_column(sa.Column("longitude", sa.Float))
  107.         batch_op.add_column(sa.Column("course", sa.Float))
  108.         batch_op.add_column(sa.Column("speed", sa.Float))
  109.  
  110.     bind = op.get_bind()
  111.     session = orm.Session(bind=bind)
  112.  
  113.     i = 0
  114.     for coordinate in session.query(GPS):  # type: GPS
  115.         i += 1
  116.         coordinate.latitude = coordinate._latitude / 10 ** 7
  117.         coordinate.longitude = coordinate._longitude / 10 ** 7
  118.         coordinate.speed = coordinate._speed / 1000
  119.         coordinate.course = coordinate._course / 100
  120.  
  121.         session.add(coordinate)
  122.         if i % 1000 == 0:
  123.             session.commit()
  124.  
  125.     session.commit()
  126.     with op.batch_alter_table("GPS") as batch_op:
  127.         batch_op.drop_column("_latitude")
  128.         batch_op.drop_column("_longitude")
  129.         batch_op.drop_column("_speed")
  130.         batch_op.drop_column("_course")
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top