Python sqlalchemy.DATETIME Examples

def test_non_native_adapt(self):
        interval = Interval(native=False)
        adapted = interval.dialect_impl(testing.db.dialect)
        assert isinstance(adapted, Interval)
        assert adapted.native is False
        eq_(str(adapted), "DATETIME") 

def test_variant_righthand_coercion_returns_self(self):
        my_datetime_normal = DateTime()
        my_datetime_variant = DateTime().with_variant(
            dialects.sqlite.DATETIME(truncate_microseconds=False), "sqlite"
        )

        tab = table(
            "test",
            column("avalue", my_datetime_normal),
            column("bvalue", my_datetime_variant),
        )
        expr = tab.c.avalue == datetime.datetime(2015, 10, 14, 15, 17, 18)

        is_(expr.right.type._type_affinity, DateTime)
        is_(expr.right.type, my_datetime_normal)

        expr = tab.c.bvalue == datetime.datetime(2015, 10, 14, 15, 17, 18)

        is_(expr.right.type, my_datetime_variant) 

def test_round_trip_sqlite_datetime(self):
        variant = DateTime().with_variant(
            dialects.sqlite.DATETIME(truncate_microseconds=True), "sqlite"
        )

        t = Table("t", self.metadata, Column("x", variant))
        with testing.db.connect() as conn:
            t.create(conn)

            conn.execute(
                t.insert(), x=datetime.datetime(2015, 4, 18, 10, 15, 17, 4839)
            )

            eq_(
                conn.scalar(
                    select([t.c.x]).where(
                        t.c.x
                        == datetime.datetime(2015, 4, 18, 10, 15, 17, 1059)
                    )
                ),
                datetime.datetime(2015, 4, 18, 10, 15, 17),
            ) 

def upgrade():
  """ Change tables to use DATETIME column types instead of TIMESTAMP """
  # Change tables to use DATETIME column types instead of TIMESTAMP
  op.alter_column("instance_status_history", "timestamp",
                  type_=sa.DATETIME,
                  server_default=None,
                  existing_nullable=False)

  op.alter_column("metric", "last_timestamp",
                  type_=sa.DATETIME,
                  existing_nullable=True,
                  existing_server_default=sa.text("NULL"))

  op.alter_column("metric_data", "timestamp",
                  type_=sa.DATETIME,
                  existing_nullable=False)
  ### end Alembic commands ### 

def init_database():

    Base = declarative_base()

    class Model(Base):
        __tablename__ = 'models'
        TS_name = Column(String(250), nullable=False,primary_key=True)
        TS_winner_name = Column(String(250), nullable=False)
        TS_model = Column(LargeBinary())
        TS_model_params = Column(String(250))
        TS_metric = Column(Numeric)
        TS_update = Column('TS_update', DATETIME, index=False, nullable=False,primary_key=True,default=datetime.datetime.utcnow)

    class TS(Base):
        __tablename__ = 'timeseries'
        TS_name = Column(String(250), nullable=False,primary_key=True)
        TS_data = Column(Text())
        TS_update = Column('TS_update', DATETIME, index=False, nullable=False,primary_key=True,default=datetime.datetime.utcnow)


    DB_NAME = 'sqlite:///Timecop_modelsv1.db'
    engine = create_engine(DB_NAME)
    #self.__db.echo = True
    Base.metadata.create_all(engine) 

def test_add_datetime_server_default_now(self):
        self._run_alter_col(
            {"type": DATETIME()},
            {"server_default": text("NOW()")},
            compare={"server_default": text("CURRENT_TIMESTAMP")},
        ) 

def downgrade():
    op.add_column('thread',
                  sa.Column('receivedrecentdate', sa.DATETIME(),
                            server_default=sa.sql.null(),
                            nullable=True))
    op.create_index('ix_thread_namespace_id_receivedrecentdate', 'thread',
                    ['namespace_id', 'receivedrecentdate'], unique=False) 

def upgrade():
    from inbox.ignition import main_engine
    engine = main_engine(pool_size=1, max_overflow=0)
    if not engine.has_table('thread'):
        return
    op.add_column('thread',
                  sa.Column('receivedrecentdate', sa.DATETIME(),
                            server_default=sa.sql.null(),
                            nullable=True))
    op.create_index('ix_thread_namespace_id_receivedrecentdate', 'thread',
                    ['namespace_id', 'receivedrecentdate'], unique=False) 

def upgrade():
    """Fix server defaults for DATETIME columns, because
    0 ("0000-00-00 00:00:00") is deprecated as default for those colum types
    as of mysql 5.7.8, and will fail with mysql installed with default config.
    """
    op.alter_column("instance_status_history", "timestamp",
                    server_default=None,
                    existing_type=sa.DATETIME,
                    existing_nullable=False) 

def test_add_datetime_server_default_current_timestamp_bundle_onupdate(
        self,
    ):
        # note SQLAlchemy reflection bundles the ON UPDATE part into the
        # server default reflection see
        # https://github.com/sqlalchemy/sqlalchemy/issues/4652
        self._run_alter_col(
            {"type": DATETIME()},
            {
                "server_default": text(
                    "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                )
            },
        ) 

def search_by_age(cls, *, limit=100, page=1, accounts=None, locations=None, age=720,
                      properties=None, include_disabled=False):
        """Search for resources based on the provided filters

        Args:
            limit (`int`): Number of results to return. Default: 100
            page (`int`): Pagination offset for results. Default: 1
            accounts (`list` of `int`): A list of account id's to limit the returned resources to
            locations (`list` of `str`): A list of locations as strings to limit the search for
            age (`int`): Age of instances older than `age` days to return
            properties (`dict`): A `dict` containing property name and value pairs. Values can be either a str or a list
            of strings, in which case a boolean OR search is performed on the values
            include_disabled (`bool`): Include resources from disabled accounts. Default: False

        Returns:
            `list` of `Resource`
        """
        qry = cls.search(
            limit=limit,
            page=page,
            accounts=accounts,
            locations=locations,
            properties=properties,
            include_disabled=include_disabled,
            return_query=True
        )

        age_alias = aliased(ResourceProperty)
        qry = (
            qry.join(age_alias, Resource.resource_id == age_alias.resource_id)
                .filter(
                age_alias.name == 'launch_date',
                cast(func.JSON_UNQUOTE(age_alias.value), DATETIME) < datetime.now() - timedelta(days=age)
            )
        )

        total = qry.count()
        qry = qry.limit(limit)
        qry = qry.offset((page - 1) * limit if page > 1 else 0)

        return total, [cls(x) for x in qry.all()] 

def test_add_datetime_server_default_current_timestamp(self):
        self._run_alter_col(
            {"type": DATETIME()}, {"server_default": text("CURRENT_TIMESTAMP")}
        ) 

def test_alter_column_modify_datetime_default(self):
        # use CHANGE format when the datatype is DATETIME or TIMESTAMP,
        # as this is needed for a functional default which is what you'd
        # get with a DATETIME/TIMESTAMP.  Will also work in the very unlikely
        # case the default is a fixed timestamp value.
        context = op_fixture("mysql")
        op.alter_column(
            "t",
            "c",
            existing_type=DATETIME(),
            server_default=text("CURRENT_TIMESTAMP"),
        )
        context.assert_(
            "ALTER TABLE t CHANGE c c DATETIME NULL DEFAULT CURRENT_TIMESTAMP"
        ) 

def test_render_table_upgrade(self):
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("name", Unicode(255)),
            Column("address_id", Integer, ForeignKey("address.id")),
            Column("timestamp", DATETIME, server_default="NOW()"),
            Column("amount", Numeric(5, 2)),
            UniqueConstraint("name", name="uq_name"),
            UniqueConstraint("timestamp"),
        )

        op_obj = ops.CreateTableOp.from_table(t)
        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_table('test',"
            "sa.Column('id', sa.Integer(), nullable=False),"
            "sa.Column('name', sa.Unicode(length=255), nullable=True),"
            "sa.Column('address_id', sa.Integer(), nullable=True),"
            "sa.Column('timestamp', sa.DATETIME(), "
            "server_default='NOW()', "
            "nullable=True),"
            "sa.Column('amount', sa.Numeric(precision=5, scale=2), "
            "nullable=True),"
            "sa.ForeignKeyConstraint(['address_id'], ['address.id'], ),"
            "sa.PrimaryKeyConstraint('id'),"
            "sa.UniqueConstraint('name', name='uq_name'),"
            "sa.UniqueConstraint('timestamp')"
            ")",
        ) 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user') as batch_op:
        batch_op.alter_column('updated',
               existing_type=sa.DATETIME(),
               nullable=True)
        batch_op.alter_column('created',
               existing_type=sa.DATETIME(),
               nullable=True)
    ### end Alembic commands ### 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user') as batch_op:
        batch_op.alter_column('created',
               existing_type=sa.DATETIME(),
               nullable=False)
        batch_op.alter_column('updated',
               existing_type=sa.DATETIME(),
               nullable=False)
    ### end Alembic commands ### 

def test_reflect_select(table, table_using_test_dataset):
    for table in [table, table_using_test_dataset]:
        assert len(table.c) == 18

        assert isinstance(table.c.integer, Column)
        assert isinstance(table.c.integer.type, types.Integer)
        assert isinstance(table.c.timestamp.type, types.TIMESTAMP)
        assert isinstance(table.c.string.type, types.String)
        assert isinstance(table.c.float.type, types.Float)
        assert isinstance(table.c.boolean.type, types.Boolean)
        assert isinstance(table.c.date.type, types.DATE)
        assert isinstance(table.c.datetime.type, types.DATETIME)
        assert isinstance(table.c.time.type, types.TIME)
        assert isinstance(table.c.bytes.type, types.BINARY)
        assert isinstance(table.c['record.age'].type, types.Integer)
        assert isinstance(table.c['record.name'].type, types.String)
        assert isinstance(table.c['nested_record.record.age'].type, types.Integer)
        assert isinstance(table.c['nested_record.record.name'].type, types.String)
        assert isinstance(table.c.array.type, types.ARRAY)

        rows = table.select().execute().fetchall()
        assert len(rows) == 1000 

def downgrade():

    # Add old columns with usercache
    op.create_index('ix_usercache_expiration', 'usercache', ['expiration'], unique=False)
    op.add_column('usercache', sa.Column('expiration', sa.DATETIME(), nullable=True))
    op.add_column('usercache', sa.Column('realm', sa.VARCHAR(length=256), nullable=True))

    # Remove Oracle Schema definition
    op.alter_column('tokenrealm', 'id',
               existing_type=sa.INTEGER(),
               nullable=False)
    op.drop_index(op.f('ix_token_user_id'), table_name='token')
    op.drop_index(op.f('ix_token_tokentype'), table_name='token')
    op.drop_index(op.f('ix_token_serial'), table_name='token')
    op.drop_index(op.f('ix_token_resolver'), table_name='token')
    op.alter_column('token', 'active',
               existing_type=sa.BOOLEAN(),
               nullable=True)
    op.drop_index(op.f('ix_subscription_application'), table_name='subscription')
    op.drop_constraint(None, 'smsgateway')
    op.drop_constraint(None, 'resolver')
    op.drop_constraint(None, 'realm')
    op.drop_constraint(None, 'radiusserver')
    op.drop_constraint(None, 'policy')
    op.create_index('ix_pidea_audit_id', 'pidea_audit', ['id'], unique=False)
    op.drop_constraint(None, 'machineresolver')
    op.create_index('ix_clientapplication_id', 'clientapplication', ['id'], unique=False)
    op.drop_constraint(None, 'caconnector') 

def test_create_table(engine):
    meta = MetaData()
    table = Table(
        'test_pybigquery.test_table_create', meta,
        Column('integer_c', sqlalchemy.Integer, doc="column description"),
        Column('float_c', sqlalchemy.Float),
        Column('decimal_c', sqlalchemy.DECIMAL),
        Column('string_c', sqlalchemy.String),
        Column('text_c', sqlalchemy.Text),
        Column('boolean_c', sqlalchemy.Boolean),
        Column('timestamp_c', sqlalchemy.TIMESTAMP),
        Column('datetime_c', sqlalchemy.DATETIME),
        Column('date_c', sqlalchemy.DATE),
        Column('time_c', sqlalchemy.TIME),
        Column('binary_c', sqlalchemy.BINARY),
        bigquery_description="test table description",
        bigquery_friendly_name="test table name"
    )
    meta.create_all(engine)
    meta.drop_all(engine)

    # Test creating tables with declarative_base
    Base = declarative_base()

    class TableTest(Base):
        __tablename__ = 'test_pybigquery.test_table_create2'
        integer_c = Column(sqlalchemy.Integer, primary_key=True)
        float_c = Column(sqlalchemy.Float)

    Base.metadata.create_all(engine)
    Base.metadata.drop_all(engine)