Python sqlalchemy.Date() Examples

def upgrade():
    op.create_table('provider_rates',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('valid_from', sa.DateTime(), nullable=False),
    sa.Column('provider', sa.Enum('mmg', 'twilio', 'firetext', 'ses', name='providers'), nullable=False),
    sa.Column('rate', sa.Numeric(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('provider_statistics',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('day', sa.Date(), nullable=False),
    sa.Column('provider', sa.Enum('mmg', 'twilio', 'firetext', 'ses', name='providers'), nullable=False),
    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('unit_count', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_provider_statistics_service_id'), 'provider_statistics', ['service_id'], unique=False) 

def strings_to_dates(model, dictionary):
    """Returns a new dictionary with all the mappings of `dictionary` but
    with date strings and intervals mapped to :class:`datetime.datetime` or
    :class:`datetime.timedelta` objects.

    The keys of `dictionary` are names of fields in the model specified in the
    constructor of this class. The values are values to set on these fields. If
    a field name corresponds to a field in the model which is a
    :class:`sqlalchemy.types.Date`, :class:`sqlalchemy.types.DateTime`, or
    :class:`sqlalchemy.Interval`, then the returned dictionary will have the
    corresponding :class:`datetime.datetime` or :class:`datetime.timedelta`
    Python object as the value of that mapping in place of the string.

    This function outputs a new dictionary; it does not modify the argument.

    """
    result = {}
    for fieldname, value in dictionary.items():
        if is_date_field(model, fieldname) and value is not None:
            if value.strip() == '':
                result[fieldname] = None
            elif value in CURRENT_TIME_MARKERS:
                result[fieldname] = getattr(func, value.lower())()
            else:
                value_as_datetime = parse_datetime(value)
                result[fieldname] = value_as_datetime
                # If the attribute on the model needs to be a Date object as
                # opposed to a DateTime object, just get the date component of
                # the datetime.
                fieldtype = get_field_type(model, fieldname)
                if isinstance(fieldtype, Date):
                    result[fieldname] = value_as_datetime.date()
        elif (is_interval_field(model, fieldname) and value is not None
              and isinstance(value, int)):
            result[fieldname] = datetime.timedelta(seconds=value)
        else:
            result[fieldname] = value
    return result 

def test_interval_coercion(self):
        expr = column("bar", types.Interval) + column("foo", types.Date)
        eq_(expr.type._type_affinity, types.DateTime)

        expr = column("bar", types.Interval) * column("foo", types.Numeric)
        eq_(expr.type._type_affinity, types.Interval) 

def test_conn_execute(self, connection):
        from sqlalchemy.sql.expression import FunctionElement
        from sqlalchemy.ext.compiler import compiles

        class myfunc(FunctionElement):
            type = Date()

        @compiles(myfunc)
        def compile_(elem, compiler, **kw):
            return compiler.process(func.current_date())

        x = connection.execute(func.current_date()).scalar()
        y = connection.execute(func.current_date().select()).scalar()
        z = connection.scalar(func.current_date())
        q = connection.scalar(myfunc())

        assert (x == y == z == q) is True 

def test_extract_expression(self, connection):
        meta = self.metadata
        table = Table("test", meta, Column("dt", DateTime), Column("d", Date))
        meta.create_all(connection)
        connection.execute(
            table.insert(),
            {
                "dt": datetime.datetime(2010, 5, 1, 12, 11, 10),
                "d": datetime.date(2010, 5, 1),
            },
        )
        rs = connection.execute(
            select([extract("year", table.c.dt), extract("month", table.c.d)])
        )
        row = rs.first()
        assert row[0] == 2010
        assert row[1] == 5
        rs.close() 

def define_tables(cls, metadata):
        Table(
            "users",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("name", String(50)),
        )

        Table(
            "stuff",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("date", Date),
            Column("user_id", Integer, ForeignKey("users.id")),
        ) 

def define_tables(cls, metadata):
        Table(
            "items",
            metadata,
            Column(
                "item_policy_num",
                String(10),
                primary_key=True,
                key="policyNum",
            ),
            Column(
                "item_policy_eff_date",
                sa.Date,
                primary_key=True,
                key="policyEffDate",
            ),
            Column("item_type", String(20), primary_key=True, key="type"),
            Column(
                "item_id",
                Integer,
                primary_key=True,
                key="id",
                autoincrement=False,
            ),
        ) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('unavailable_room_report',
                    sa.Column('record_id', sa.Integer(), nullable=False),
                    sa.Column('room_id', sa.String(), nullable=True),
                    sa.Column('date', sa.Date(), nullable=True),
                    sa.Column('time', sa.String(length=4), nullable=True),
                    sa.Column('reporter', sa.String(length=15), nullable=True),
                    sa.Column('reporter_type', sa.String(), nullable=True),
                    sa.PrimaryKeyConstraint('record_id'),
                    sa.UniqueConstraint('room_id', 'date', 'time', 'reporter', 'reporter_type', name='unavailable_room_report_uniq')
                    )
    op.drop_index('idx_token', table_name='calendar_tokens')
    op.alter_column('identity_verify_requests', 'create_time',
                    existing_type=postgresql.TIMESTAMP(timezone=True),
                    nullable=True)
    op.create_index(op.f('ix_simple_passwords_time'), 'simple_passwords', ['time'], unique=False)
    op.drop_index('idx_host_time', table_name='visit_tracks')
    op.create_index('idx_host_time', 'visit_tracks', ['host_id', 'last_visit_time'], unique=False)
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('subscription',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('created_at', sqlalchemy_utils.types.arrow.ArrowType(), nullable=False),
    sa.Column('updated_at', sqlalchemy_utils.types.arrow.ArrowType(), nullable=True),
    sa.Column('cancel_url', sa.String(length=1024), nullable=False),
    sa.Column('update_url', sa.String(length=1024), nullable=False),
    sa.Column('subscription_id', sa.String(length=1024), nullable=False),
    sa.Column('event_time', sqlalchemy_utils.types.arrow.ArrowType(), nullable=False),
    sa.Column('next_bill_date', sa.Date(), nullable=False),
    sa.Column('cancelled', sa.Boolean(), nullable=False),
    sa.Column('plan', sa.Enum('monthly', 'yearly', name='planenum2'), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='cascade'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('subscription_id'),
    sa.UniqueConstraint('user_id')
    )
    op.add_column('users', sa.Column('trial_expiration', sqlalchemy_utils.types.arrow.ArrowType(), nullable=True))
    op.drop_column('users', 'plan_expiration')
    op.drop_column('users', 'plan')
    op.drop_column('users', 'promo_codes')
    # ### end Alembic commands ### 

def downgrade():
	alembic.op.add_column('cards', sqlalchemy.Column("lastprinted", sqlalchemy.Date)) 

def upgrade():
	storm = alembic.op.create_table("storm",
		sqlalchemy.Column("date", sqlalchemy.Date, primary_key=True),
		sqlalchemy.Column("twitch-subscription", sqlalchemy.Integer, nullable=False, server_default='0'),
		sqlalchemy.Column("twitch-resubscription", sqlalchemy.Integer, nullable=False, server_default='0'),
		sqlalchemy.Column("twitch-follow", sqlalchemy.Integer, nullable=False, server_default='0'),
		sqlalchemy.Column("twitch-message", sqlalchemy.Integer, nullable=False, server_default='0'),
		sqlalchemy.Column("patreon-pledge", sqlalchemy.Integer, nullable=False, server_default='0'),
	)

	datafile = alembic.context.config.get_section_option("lrrbot", "datafile", "data.json")
	with open(datafile) as f:
		data = json.load(f)
	try:
		alembic.op.bulk_insert(storm, [
			{'date': datetime.date.fromordinal(data['storm']['date']), 'twitch-subscription': data['storm']['count']}
		])
		del data['storm']
		with open(datafile, 'w') as f:
			json.dump(data, f, indent=2, sort_keys=True)
	except KeyError:
		pass 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('milestone',
    sa.Column('id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('date', sa.Date(), nullable=True),
    sa.Column('name', sa.String(length=40), nullable=False),
    sa.Column('project_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
    sa.Column('task_type_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
    sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
    sa.ForeignKeyConstraint(['task_type_id'], ['task_type.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_milestone_project_id'), 'milestone', ['project_id'], unique=False)
    op.create_index(op.f('ix_milestone_task_type_id'), 'milestone', ['task_type_id'], unique=False)
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('project', sa.Column('end_date', sa.Date(), nullable=True))
    op.add_column('project', sa.Column('start_date', sa.Date(), nullable=True))
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('schedule_item',
    sa.Column('id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('start_date', sa.Date(), nullable=True),
    sa.Column('end_date', sa.Date(), nullable=True),
    sa.Column('project_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
    sa.Column('task_type_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
    sa.Column('entity_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
    sa.ForeignKeyConstraint(['entity_id'], ['task_type.id'], ),
    sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
    sa.ForeignKeyConstraint(['task_type_id'], ['task_type.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_schedule_item_entity_id'), 'schedule_item', ['entity_id'], unique=False)
    op.create_index(op.f('ix_schedule_item_project_id'), 'schedule_item', ['project_id'], unique=False)
    op.create_index(op.f('ix_schedule_item_task_type_id'), 'schedule_item', ['task_type_id'], unique=False)
    # ### end Alembic commands ### 

def test_get_type_of_fields(resources):
    table = sa.Table(
        'Test', sa.MetaData(),
        sa.Column('integer', sa.Integer, primary_key=True),
        sa.Column('text', sa.Text),
        sa.Column('float', sa.Float),
        sa.Column('date', sa.Date),
        sa.Column('boolean', sa.Boolean),
        sa.Column('json', postgresql.JSON),
    )
    fields = ['integer', 'text', 'float', 'date', 'boolean', 'json', ]

    data_type_fields = resources.get_type_of_fields(fields, table)
    expected_type_fields = {
        'integer': rc.TEXT_FIELD.value,
        'text': rc.TEXT_FIELD.value,
        'float': rc.NUMBER_FIELD.value,
        'date': rc.DATE_FIELD.value,
        'boolean': rc.BOOLEAN_FIELD.value,
        'json': rc.JSON_FIELD.value,
    }

    assert data_type_fields == expected_type_fields

    fields = None
    data_type_fields = resources.get_type_of_fields(fields, table)
    expected_type_fields = {
        'integer': rc.TEXT_FIELD.value,
    }

    assert data_type_fields == expected_type_fields


# TODO: added Mongo 

def table():

    meta = sa.MetaData()
    post = sa.Table(
        'post', meta,
        sa.Column('id', sa.Integer, nullable=False),
        sa.Column('title', sa.String(200), nullable=False),
        sa.Column('body', sa.Text, nullable=False),
        sa.Column('views', sa.Integer, nullable=False),
        sa.Column('average_note', sa.Float, nullable=False),
        sa.Column('pictures', postgresql.JSON, server_default='{}'),
        sa.Column('published_at', sa.Date, nullable=False),
        sa.Column('tags', postgresql.ARRAY(sa.Integer), server_default='[]'),

        # Indexes #
        sa.PrimaryKeyConstraint('id', name='post_id_pkey'))
    return post 

def sa_date(_, satype, nullable=True):
    return dt.Date(nullable=nullable) 

def upgrade():
    op.add_column('card', sa.Column('due_date', sa.Date)) 

def downgrade():
    op.add_column('card', sa.Column('description', sa.UnicodeText, default=u''))
    op.drop_table('card_description')

    op.add_column('card', sa.Column('due_date', sa.Date))
    op.drop_table('card_due_date')

    op.add_column('card', sa.Column('weight', sa.Unicode(255)))
    op.drop_table('card_weight') 

def exact_age(self, reference):
        """
        Player's exact age (years + days) relative to a reference date.

        :param reference: Date object of reference date.
        :return: Player's age expressed as a (Year, day) tuple
        """
        delta = reference - self.birth_date
        years = int(delta.days/365.25)
        days = int(delta.days - years*365.25 + 0.5)
        return (years, days) 

def age(self, reference):
        """
        Player's age relative to a reference date.

        :param reference: Date object of reference date.
        :return: Integer value of player's age.
        """
        delta = reference - self.birth_date
        return int(delta.days/365.25) 

def age(cls, reference):
        """
        Person's age relative to a reference date.

        :param reference: Date object of reference date.
        :return: Integer value of person's age.
        """
        return cast((reference - cls.birth_date)/365.25 - 0.5, Integer) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('uix_service_to_day', 'notification_statistics')
    op.alter_column('notification_statistics', 'day', new_column_name='day_string')
    op.add_column('notification_statistics', sa.Column('day', sa.Date(), nullable=True))

    op.get_bind()
    op.execute("UPDATE notification_statistics ns1 SET day = (SELECT to_date(day_string, 'YYYY-MM-DD') FROM notification_statistics ns2 WHERE ns1.id = ns2.id)")

    op.alter_column('notification_statistics', 'day', nullable=False)
    op.create_index(op.f('ix_notification_statistics_day'), 'notification_statistics', ['day'], unique=False)
    op.drop_column('notification_statistics', 'day_string')
    op.create_unique_constraint('uix_service_to_day', 'notification_statistics', columns=['service_id', 'day'])

    ### end Alembic commands ### 

def upgrade():
    op.create_table('returned_letters',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('reported_at', sa.Date(), nullable=False),
        sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('notification_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('notification_id')
    )
    op.create_index(op.f('ix_returned_letters_service_id'), 'returned_letters', ['service_id'], unique=False) 

def upgrade():
    op.create_table('daily_sorted_letter',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('billing_day', sa.Date(), nullable=False),
    sa.Column('unsorted_count', sa.Integer(), nullable=False),
    sa.Column('sorted_count', sa.Integer(), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_daily_sorted_letter_billing_day'), 'daily_sorted_letter', ['billing_day'], unique=True) 

def upgrade():
    op.create_table('ft_notification_status',
    sa.Column('bst_date', sa.Date(), nullable=False),
    sa.Column('template_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('job_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('notification_type', sa.Text(), nullable=False),
    sa.Column('key_type', sa.Text(), nullable=False),
    sa.Column('notification_status', sa.Text(), nullable=False),
    sa.Column('notification_count', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('bst_date', 'template_id', 'service_id', 'job_id', 'notification_type', 'key_type', 'notification_status')
    )
    op.create_index(op.f('ix_ft_notification_status_bst_date'), 'ft_notification_status', ['bst_date'], unique=False)
    op.create_index(op.f('ix_ft_notification_status_job_id'), 'ft_notification_status', ['job_id'], unique=False)
    op.create_index(op.f('ix_ft_notification_status_service_id'), 'ft_notification_status', ['service_id'], unique=False)
    op.create_index(op.f('ix_ft_notification_status_template_id'), 'ft_notification_status', ['template_id'], unique=False) 

def upgrade():
    op.add_column('nct', sa.Column('results_exemption_date', sa.Date)) 

def upgrade():
    op.create_table('icdpcs',

        # Meta

        sa.Column('meta_id', sa.Text, unique=True),
        sa.Column('meta_source', sa.Text),
        sa.Column('meta_created', sa.DateTime(timezone=True)),
        sa.Column('meta_updated', sa.DateTime(timezone=True)),

        # General

        sa.Column('code', sa.Text, primary_key=True),
        sa.Column('is_header', sa.Boolean),
        sa.Column('short_description', sa.Text),
        sa.Column('long_description', sa.Text),
        sa.Column('version', sa.Text),
        sa.Column('last_updated', sa.Date),

    ) 

def upgrade():
    op.create_table('ictrp',

        # Meta

        sa.Column('meta_uuid', sa.Text),
        sa.Column('meta_source', sa.Text),
        sa.Column('meta_created', sa.DateTime(timezone=True)),
        sa.Column('meta_updated', sa.DateTime(timezone=True)),

        # Main

        sa.Column('register', sa.Text, primary_key=True),
        sa.Column('last_refreshed_on', sa.Date),
        sa.Column('main_id', sa.Text, primary_key=True),
        sa.Column('date_of_registration', sa.Text),
        sa.Column('primary_sponsor', sa.Text),
        sa.Column('public_title', sa.Text),
        sa.Column('scientific_title', sa.Text),
        sa.Column('date_of_first_enrollment', sa.Text),
        sa.Column('target_sample_size', sa.Integer),
        sa.Column('recruitment_status', sa.Text),
        sa.Column('url', sa.Text),
        sa.Column('study_type', sa.Text),
        sa.Column('study_design', sa.Text),
        sa.Column('study_phase', sa.Text),

        # Additional

        sa.Column('countries_of_recruitment', ARRAY(sa.Text)),
        sa.Column('contacts', JSONB),
        sa.Column('key_inclusion_exclusion_criteria', sa.Text),
        sa.Column('health_conditions_or_problems_studied', ARRAY(sa.Text)),
        sa.Column('interventions', ARRAY(sa.Text)),
        sa.Column('primary_outcomes', ARRAY(sa.Text)),
        sa.Column('secondary_outcomes', ARRAY(sa.Text)),
        sa.Column('secondary_ids', ARRAY(sa.Text)),
        sa.Column('sources_of_monetary_support', ARRAY(sa.Text)),
        sa.Column('secondary_sponsors', ARRAY(sa.Text)),

    ) 

def upgrade():
    op.create_table('pfizer',

        # Meta

        sa.Column('meta_uuid', sa.Text),
        sa.Column('meta_source', sa.Text),
        sa.Column('meta_created', sa.DateTime(timezone=True)),
        sa.Column('meta_updated', sa.DateTime(timezone=True)),

        # General

        sa.Column('title', sa.Text),

        # Description

        sa.Column('study_type', sa.Text),
        sa.Column('organization_id', sa.Text),
        sa.Column('nct_id', sa.Text),
        sa.Column('status', sa.Text),
        sa.Column('study_start_date', sa.Date),
        sa.Column('study_end_date', sa.Date),

        # Eligibility

        sa.Column('eligibility_criteria', sa.Text),
        sa.Column('gender', sa.Text),
        sa.Column('age_range', sa.Text),
        sa.Column('healthy_volunteers_allowed', sa.Boolean),

    )