Python sqlalchemy.types.DATE Examples

def test_custom_date_text_affinity(self, connection):
        sqlite_date = sqlite.DATE(
            storage_format="%(year)04d%(month)02d%(day)02d",
            regexp=r"(\d{4})(\d{2})(\d{2})",
        )
        t = Table("t", self.metadata, Column("d", sqlite_date))
        self.metadata.create_all(connection)
        connection.execute(t.insert().values(d=datetime.date(2010, 10, 15)))
        exec_sql(testing.db, "insert into t (d) values ('20040521')")
        eq_(
            exec_sql(testing.db, "select * from t order by d").fetchall(),
            [("20040521",), ("20101015",)],
        )
        eq_(
            connection.execute(select([t.c.d]).order_by(t.c.d)).fetchall(),
            [(datetime.date(2004, 5, 21),), (datetime.date(2010, 10, 15),)],
        ) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and \
                                self.server_version_info >= (2, )
                            ) or \
                            ('interbase' in self.server_version_info and \
                                self.server_version_info >= (6, )
                            )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two  and \
                            self.__dict__.get('implicit_returning', True) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def test_custom_date(self, connection):
        sqlite_date = sqlite.DATE(
            # 2004-05-21T00:00:00
            storage_format="%(year)04d|%(month)02d|%(day)02d",
            regexp=r"(\d+)\|(\d+)\|(\d+)",
        )
        t = Table("t", self.metadata, Column("d", sqlite_date))
        self.metadata.create_all(testing.db)
        connection.execute(t.insert().values(d=datetime.date(2010, 10, 15)))
        exec_sql(testing.db, "insert into t (d) values ('2004|05|21')")
        eq_(
            exec_sql(testing.db, "select * from t order by d").fetchall(),
            [("2004|05|21",), ("2010|10|15",)],
        )
        eq_(
            connection.execute(select([t.c.d]).order_by(t.c.d)).fetchall(),
            [(datetime.date(2004, 5, 21),), (datetime.date(2010, 10, 15),)],
        ) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = (
            "firebird" in self.server_version_info
            and self.server_version_info >= (2,)
        ) or (
            "interbase" in self.server_version_info
            and self.server_version_info >= (6,)
        )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names["TIMESTAMP"] = sqltypes.DATE
            self.colspecs = {sqltypes.DateTime: sqltypes.DATE}

        self.implicit_returning = self._version_two and self.__dict__.get(
            "implicit_returning", True
        ) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def initialize(self, connection):
        super(FBDialect, self).initialize(connection)
        self._version_two = ('firebird' in self.server_version_info and
                             self.server_version_info >= (2, )
                             ) or \
                            ('interbase' in self.server_version_info and
                                self.server_version_info >= (6, )
                             )

        if not self._version_two:
            # TODO: whatever other pre < 2.0 stuff goes here
            self.ischema_names = ischema_names.copy()
            self.ischema_names['TIMESTAMP'] = sqltypes.DATE
            self.colspecs = {
                sqltypes.DateTime: sqltypes.DATE
            }

        self.implicit_returning = self._version_two and \
            self.__dict__.get('implicit_returning', True) 

def stdtype_to_sqltype(stdtype):
    import sqlalchemy.types as sqltypes
    if isinstance(stdtype, stdtypes.StringType):
        return sqltypes.VARCHAR(length=stdtype.max_len) if 0 < stdtype.max_len < 65536 else sqltypes.TEXT()
    if isinstance(stdtype, stdtypes.BoolType):
        return sqltypes.BOOLEAN()
    if isinstance(stdtype, stdtypes.DateType):
        return sqltypes.DATE() if stdtype.only_date else sqltypes.TIMESTAMP()
    if isinstance(stdtype, stdtypes.IntegerType):
        return sqltypes.BIGINT() if stdtype.length > 11 else sqltypes.INTEGER()
    if isinstance(stdtype, stdtypes.DecimalType):
        return sqltypes.DECIMAL()
    if isinstance(stdtype, stdtypes.ArrayType):
        return sqltypes.ARRAY(item_type=stdtype.item_type) 

def test_date(self):
        self._test('date', sqltypes.DATE) 

def sqltype_to_stdtype(sqltype):
    import sqlalchemy.types as sqltypes
    if isinstance(sqltype, (sqltypes.VARCHAR, sqltypes.CHAR, sqltypes.TEXT, sqltypes.Enum, sqltypes.String)):
        return _STRING_TYPE
    if isinstance(sqltype, (sqltypes.DATETIME, sqltypes.DATE, sqltypes.TIME, sqltypes.TIMESTAMP)):
        return _DATE_TYPE
    if isinstance(sqltype, (sqltypes.INTEGER, sqltypes.BIGINT, sqltypes.SMALLINT, sqltypes.Integer)):
        return _INTEGER_TYPE
    if isinstance(sqltype, (sqltypes.REAL, sqltypes.DECIMAL, sqltypes.NUMERIC, sqltypes.FLOAT)):
        return _DECIMAL_TYPE
    if isinstance(sqltype, sqltypes.BOOLEAN):
        return _BOOLEAN_TYPE 

def test_default(self):
        dt = datetime.date(2008, 6, 27)
        eq_(str(dt), "2008-06-27")
        sldt = sqlite.DATE()
        bp = sldt.bind_processor(None)
        eq_(bp(dt), "2008-06-27")
        rp = sldt.result_processor(None, None)
        eq_(rp(bp(dt)), dt) 

def test_custom_format(self):
        dt = datetime.date(2008, 6, 27)
        eq_(str(dt), "2008-06-27")
        sldt = sqlite.DATE(
            storage_format="%(month)02d/%(day)02d/%(year)04d",
            regexp=r"(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)",
        )
        bp = sldt.bind_processor(None)
        eq_(bp(dt), "06/27/2008")
        rp = sldt.result_processor(None, None)
        eq_(rp(bp(dt)), dt) 

def test_custom_format(self):
        dt = datetime.date(2008, 6, 27)
        eq_(str(dt), "2008-06-27")
        sldt = sqlite.DATE(
            storage_format="%(year)04d%(month)02d%(day)02d",
            regexp=r"(\d{4})(\d{2})(\d{2})",
        )
        bp = sldt.bind_processor(None)
        eq_(bp(dt), "20080627")
        rp = sldt.result_processor(None, None)
        eq_(rp(bp(dt)), dt) 

def test_reflect_dates(self):
        metadata = self.metadata
        Table(
            "date_types",
            metadata,
            Column("d1", sqltypes.DATE),
            Column("d2", oracle.DATE),
            Column("d3", TIMESTAMP),
            Column("d4", TIMESTAMP(timezone=True)),
            Column("d5", oracle.INTERVAL(second_precision=5)),
        )
        metadata.create_all()
        m = MetaData(testing.db)
        t1 = Table("date_types", m, autoload=True)
        assert isinstance(t1.c.d1.type, oracle.DATE)
        assert isinstance(t1.c.d1.type, DateTime)
        assert isinstance(t1.c.d2.type, oracle.DATE)
        assert isinstance(t1.c.d2.type, DateTime)
        assert isinstance(t1.c.d3.type, TIMESTAMP)
        assert not t1.c.d3.type.timezone
        assert isinstance(t1.c.d4.type, TIMESTAMP)
        assert t1.c.d4.type.timezone
        assert isinstance(t1.c.d5.type, oracle.INTERVAL) 

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 get_columns(self, connection, table_name, schema=None, **kw):
        # Extend types supported by PrestoDialect as defined in PyHive
        type_map = {
            'bigint': sql_types.BigInteger,
            'integer': sql_types.Integer,
            'boolean': sql_types.Boolean,
            'double': sql_types.Float,
            'varchar': sql_types.String,
            'timestamp': sql_types.TIMESTAMP,
            'date': sql_types.DATE,
            'array<bigint>': sql_types.ARRAY(sql_types.Integer),
            'array<varchar>': sql_types.ARRAY(sql_types.String)
        }

        rows = self._get_table_columns(connection, table_name, schema)
        result = []
        for row in rows:
            try:
                coltype = type_map[row.Type]
            except KeyError:
                logger.warn("Did not recognize type '%s' of column '%s'" % (row.Type, row.Column))
                coltype = sql_types.NullType
            result.append({
                'name': row.Column,
                'type': coltype,
                # newer Presto no longer includes this column
                'nullable': getattr(row, 'Null', True),
                'default': None,
            })
        return result 

def _fixed_lookup_fixture(self):
        return [
            (sqltypes.String(), sqltypes.VARCHAR()),
            (sqltypes.String(1), sqltypes.VARCHAR(1)),
            (sqltypes.String(3), sqltypes.VARCHAR(3)),
            (sqltypes.Text(), sqltypes.TEXT()),
            (sqltypes.Unicode(), sqltypes.VARCHAR()),
            (sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
            (sqltypes.UnicodeText(), sqltypes.TEXT()),
            (sqltypes.CHAR(3), sqltypes.CHAR(3)),
            (sqltypes.NUMERIC, sqltypes.NUMERIC()),
            (sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
            (sqltypes.Numeric, sqltypes.NUMERIC()),
            (sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
            (sqltypes.DECIMAL, sqltypes.DECIMAL()),
            (sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
            (sqltypes.INTEGER, sqltypes.INTEGER()),
            (sqltypes.BIGINT, sqltypes.BIGINT()),
            (sqltypes.Float, sqltypes.FLOAT()),
            (sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
            (sqltypes.DATETIME, sqltypes.DATETIME()),
            (sqltypes.DateTime, sqltypes.DATETIME()),
            (sqltypes.DateTime(), sqltypes.DATETIME()),
            (sqltypes.DATE, sqltypes.DATE()),
            (sqltypes.Date, sqltypes.DATE()),
            (sqltypes.TIME, sqltypes.TIME()),
            (sqltypes.Time, sqltypes.TIME()),
            (sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
            (sqltypes.Boolean, sqltypes.BOOLEAN()),
            (
                sqlite.DATE(storage_format="%(year)04d%(month)02d%(day)02d"),
                sqltypes.DATE(),
            ),
            (
                sqlite.TIME(
                    storage_format="%(hour)02d%(minute)02d%(second)02d"
                ),
                sqltypes.TIME(),
            ),
            (
                sqlite.DATETIME(
                    storage_format="%(year)04d%(month)02d%(day)02d"
                    "%(hour)02d%(minute)02d%(second)02d"
                ),
                sqltypes.DATETIME(),
            ),
        ] 

def test_dates(self):
        "Exercise type specification for date types."

        columns = [
            # column type, args, kwargs, expected ddl
            (mssql.MSDateTime, [], {}, "DATETIME", None),
            (types.DATE, [], {}, "DATE", None),
            (types.Date, [], {}, "DATE", None),
            (types.Date, [], {}, "DATETIME", MS_2005_VERSION),
            (mssql.MSDate, [], {}, "DATE", None),
            (mssql.MSDate, [], {}, "DATETIME", MS_2005_VERSION),
            (types.TIME, [], {}, "TIME", None),
            (types.Time, [], {}, "TIME", None),
            (mssql.MSTime, [], {}, "TIME", None),
            (mssql.MSTime, [1], {}, "TIME(1)", None),
            (types.Time, [], {}, "DATETIME", MS_2005_VERSION),
            (mssql.MSTime, [], {}, "TIME", None),
            (mssql.MSSmallDateTime, [], {}, "SMALLDATETIME", None),
            (mssql.MSDateTimeOffset, [], {}, "DATETIMEOFFSET", None),
            (mssql.MSDateTimeOffset, [1], {}, "DATETIMEOFFSET(1)", None),
            (mssql.MSDateTime2, [], {}, "DATETIME2", None),
            (mssql.MSDateTime2, [0], {}, "DATETIME2(0)", None),
            (mssql.MSDateTime2, [1], {}, "DATETIME2(1)", None),
            (mssql.MSTime, [0], {}, "TIME(0)", None),
            (mssql.MSDateTimeOffset, [0], {}, "DATETIMEOFFSET(0)", None),
        ]

        metadata = MetaData()
        table_args = ["test_mssql_dates", metadata]
        for index, spec in enumerate(columns):
            type_, args, kw, res, server_version = spec
            table_args.append(
                Column("c%s" % index, type_(*args, **kw), nullable=None)
            )

        date_table = Table(*table_args)
        dialect = mssql.dialect()
        dialect.server_version_info = MS_2008_VERSION
        ms_2005_dialect = mssql.dialect()
        ms_2005_dialect.server_version_info = MS_2005_VERSION
        gen = dialect.ddl_compiler(dialect, schema.CreateTable(date_table))
        gen2005 = ms_2005_dialect.ddl_compiler(
            ms_2005_dialect, schema.CreateTable(date_table)
        )

        for col in date_table.c:
            index = int(col.name[1:])
            server_version = columns[index][4]
            if not server_version:
                testing.eq_(
                    gen.get_column_specification(col),
                    "%s %s" % (col.name, columns[index][3]),
                )
            else:
                testing.eq_(
                    gen2005.get_column_specification(col),
                    "%s %s" % (col.name, columns[index][3]),
                )

            self.assert_(repr(col)) 

def test_dates(self):
        "Exercise type specification for date types."

        columns = [
            # column type, args, kwargs, expected ddl
            (mssql.MSDateTime, [], {}, "DATETIME", []),
            (types.DATE, [], {}, "DATE", [">=", (10,)]),
            (types.Date, [], {}, "DATE", [">=", (10,)]),
            (types.Date, [], {}, "DATETIME", ["<", (10,)], mssql.MSDateTime),
            (mssql.MSDate, [], {}, "DATE", [">=", (10,)]),
            (mssql.MSDate, [], {}, "DATETIME", ["<", (10,)], mssql.MSDateTime),
            (types.TIME, [], {}, "TIME", [">=", (10,)]),
            (types.Time, [], {}, "TIME", [">=", (10,)]),
            (mssql.MSTime, [], {}, "TIME", [">=", (10,)]),
            (mssql.MSTime, [1], {}, "TIME(1)", [">=", (10,)]),
            (types.Time, [], {}, "DATETIME", ["<", (10,)], mssql.MSDateTime),
            (mssql.MSTime, [], {}, "TIME", [">=", (10,)]),
            (mssql.MSSmallDateTime, [], {}, "SMALLDATETIME", []),
            (mssql.MSDateTimeOffset, [], {}, "DATETIMEOFFSET", [">=", (10,)]),
            (
                mssql.MSDateTimeOffset,
                [1],
                {},
                "DATETIMEOFFSET(1)",
                [">=", (10,)],
            ),
            (mssql.MSDateTime2, [], {}, "DATETIME2", [">=", (10,)]),
            (mssql.MSDateTime2, [0], {}, "DATETIME2(0)", [">=", (10,)]),
            (mssql.MSDateTime2, [1], {}, "DATETIME2(1)", [">=", (10,)]),
        ]

        table_args = ["test_mssql_dates", metadata]
        for index, spec in enumerate(columns):
            type_, args, kw, res, requires = spec[0:5]
            if (
                requires
                and testing._is_excluded("mssql", *requires)
                or not requires
            ):
                c = Column("c%s" % index, type_(*args, **kw), nullable=None)
                testing.db.dialect.type_descriptor(c.type)
                table_args.append(c)
        dates_table = Table(*table_args)
        gen = testing.db.dialect.ddl_compiler(
            testing.db.dialect, schema.CreateTable(dates_table)
        )
        for col in dates_table.c:
            index = int(col.name[1:])
            testing.eq_(
                gen.get_column_specification(col),
                "%s %s" % (col.name, columns[index][3]),
            )
            self.assert_(repr(col))
        dates_table.create(checkfirst=True)
        reflected_dates = Table(
            "test_mssql_dates", MetaData(testing.db), autoload=True
        )
        for col in reflected_dates.c:
            self.assert_types_base(col, dates_table.c[col.key]) 

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)