def test_string_types( self, metadata, connection, ): specs = [ (String(125), VARCHAR(125)), (String(42).with_variant(VARCHAR2(42), "oracle"), VARCHAR(42)), (Unicode(125), VARCHAR(125)), (Unicode(42).with_variant(NVARCHAR2(42), "oracle"), NVARCHAR(42)), (CHAR(125), CHAR(125)), (NCHAR(42), NCHAR(42)), ] self._run_test(metadata, connection, specs, ["length"])
class Misc(Base): """This model contains all possible MySQL types""" __tablename__ = "misc" id = Column(Integer, primary_key=True) big_integer_field = Column(BigInteger, default=0) large_binary_field = Column(LargeBinary, nullable=True) boolean_field = Column(Boolean, default=False) char_field = Column(CHAR(255), nullable=True) date_field = Column(Date, nullable=True) date_time_field = Column(DateTime, nullable=True) decimal_field = Column(DECIMAL(10, 2), nullable=True) float_field = Column(Float(12, 4), default=0) integer_field = Column(Integer, default=0) json_field = Column(JSON, nullable=True) nchar_field = Column(NCHAR(255), nullable=True) numeric_field = Column(Numeric(12, 4), default=0) unicode_field = Column(Unicode(255), nullable=True) real_field = Column(REAL(12, 4), default=0) small_integer_field = Column(SmallInteger, default=0) string_field = Column(String(255), nullable=True) text_field = Column(Text, nullable=True) time_field = Column(Time, nullable=True) varbinary_field = Column(VARBINARY(255), nullable=True) varchar_field = Column(VARCHAR(255), nullable=True) timestamp_field = Column(TIMESTAMP, default=current_timestamp())
class Misc(Base): """This model contains all possible MySQL types""" __tablename__ = "misc" id = Column(Integer, primary_key=True) big_integer_field = Column(BigInteger, default=0) big_integer_unsigned_field = Column(BIGINT(unsigned=True), default=0) large_binary_field = Column(LargeBinary, nullable=True) boolean_field = Column(Boolean, default=False) char_field = Column(CHAR(255), nullable=True) date_field = Column(Date, nullable=True) date_time_field = Column(DateTime, nullable=True) decimal_field = Column(DECIMAL(10, 2), nullable=True) float_field = Column(Float(12, 4), default=0) integer_field = Column(Integer, default=0) integer_unsigned_field = Column(INTEGER(unsigned=True), default=0) tinyint_field = Column(TINYINT, default=0) tinyint_unsigned_field = Column(TINYINT(unsigned=True), default=0) mediumint_field = Column(MEDIUMINT, default=0) mediumint_unsigned_field = Column(MEDIUMINT(unsigned=True), default=0) if environ.get("LEGACY_DB", "0") == "0": json_field = Column(JSON, nullable=True) nchar_field = Column(NCHAR(255), nullable=True) numeric_field = Column(Numeric(12, 4), default=0) unicode_field = Column(Unicode(255), nullable=True) real_field = Column(REAL(12, 4), default=0) small_integer_field = Column(SmallInteger, default=0) small_integer_unsigned_field = Column(SMALLINT(unsigned=True), default=0) string_field = Column(String(255), nullable=True) text_field = Column(Text, nullable=True) time_field = Column(Time, nullable=True) varbinary_field = Column(VARBINARY(255), nullable=True) varchar_field = Column(VARCHAR(255), nullable=True) timestamp_field = Column(TIMESTAMP, default=current_timestamp()) dupe = Column(Boolean, index=True, default=False)
class PaperInfluence(Base): __tablename__ = 'Paper_Influence' id = Column(Integer, primary_key=True) _class = Column('class', String(100, 'Chinese_PRC_CI_AS')) year = Column(String(50, 'Chinese_PRC_CI_AS')) Influence = Column(Float(53)) subsys = Column(NCHAR(10))
class DenysClients(Base): __tablename__ = 'DenysClients' id = Column(String(40), primary_key=True, unique=True, nullable=False) name = Column( NCHAR(length=100), nullable=False, )
class Distrito(Model): __tablename__ = 'Distrito' Cod_Distrito = Column(Integer, primary_key=True) Nombre = Column(String(100, 'Modern_Spanish_CI_AS')) Codigo_Postal = Column(NCHAR(10)) Cod_Canton = Column(ForeignKey('Canton.Cod_Canton')) Canton = relationship('Canton')
class FeeCategory(Base): """ A high-level category of fee. """ __tablename__ = 'fee_category' fee_cat_id = Column(String(20), primary_key=True, comment="Unique identifier for the category.") description = Column(NCHAR(10), nullable=False, comment="Description of the category.")
def set_sql_table(self): from sqlalchemy.schema import MetaData from sqlalchemy import Table, Column, DateTime, CHAR, NCHAR meta = MetaData(self.sql_engine, schema=None) sql_table = Table(self.table_name, meta, Column('Station_ID', CHAR(length=6), primary_key=True, nullable=False), Column('Station_Area', NCHAR(length=32), nullable=False), Column('Reporttime', DateTime, nullable=False), Column('Daily_Start_Period', CHAR(length=10)), Column('Daily_End_Period', CHAR(length=10)), Column('Hourly_Start_Period', CHAR(length=10)), Column('Hourly_End_Period', CHAR(length=10))) return sql_table
def test_string_types(self): specs = [ (String(1), mysql.MSString(1)), (String(3), mysql.MSString(3)), (Text(), mysql.MSText()), (Unicode(1), mysql.MSString(1)), (Unicode(3), mysql.MSString(3)), (UnicodeText(), mysql.MSText()), (mysql.MSChar(1), mysql.MSChar(1)), (mysql.MSChar(3), mysql.MSChar(3)), (NCHAR(2), mysql.MSChar(2)), (mysql.MSNChar(2), mysql.MSChar(2)), (mysql.MSNVarChar(22), mysql.MSString(22)), ] self._run_test(specs, ["length"])
def test_type_adapt_nchar(self): dialect = cx_oracle.dialect(use_nchar_for_unicode=True) for start, test in [ (String(), String), (VARCHAR(), cx_oracle._OracleString), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleUnicodeStringNCHAR), (Text(), cx_oracle._OracleText), (UnicodeText(), cx_oracle._OracleUnicodeTextNCLOB), (NCHAR(), cx_oracle._OracleUnicodeStringNCHAR), (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR), ]: assert isinstance( start.dialect_impl(dialect), test), "wanted %r got %r" % (test, start.dialect_impl(dialect))
def test_char_length(self, metadata, connection): t1 = Table( "t1", metadata, Column("c1", VARCHAR(50)), Column("c2", NVARCHAR(250)), Column("c3", CHAR(200)), Column("c4", NCHAR(180)), ) t1.create(connection) m2 = MetaData() t2 = Table("t1", m2, autoload_with=connection) eq_(t2.c.c1.type.length, 50) eq_(t2.c.c2.type.length, 250) eq_(t2.c.c3.type.length, 200) eq_(t2.c.c4.type.length, 180)
class Department(Base): """ A school or department. Usually a parent object for areas of study, or cost centre. """ __tablename__ = 'department' department_id = Column(NCHAR(3), primary_key=True, comment="Unique identifier for the department.") description = Column(String(50), nullable=False, comment="Short description of the department.") long_description = Column(String(50), nullable=False, comment="Formal title of the Department.")
def test_char_length(self): metadata = self.metadata t1 = Table( "t1", metadata, Column("c1", VARCHAR(50)), Column("c2", NVARCHAR(250)), Column("c3", CHAR(200)), Column("c4", NCHAR(180)), ) t1.create() m2 = MetaData(testing.db) t2 = Table("t1", m2, autoload=True) eq_(t2.c.c1.type.length, 50) eq_(t2.c.c2.type.length, 250) eq_(t2.c.c3.type.length, 200) eq_(t2.c.c4.type.length, 180)
def test_type_adapt(self): dialect = cx_oracle.dialect() for start, test in [ (Date(), cx_oracle._OracleDate), (oracle.OracleRaw(), cx_oracle._OracleRaw), (String(), String), (VARCHAR(), cx_oracle._OracleString), (DATE(), cx_oracle._OracleDate), (oracle.DATE(), oracle.DATE), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleNVarChar), (Text(), cx_oracle._OracleText), (UnicodeText(), cx_oracle._OracleUnicodeText), (NCHAR(), cx_oracle._OracleNVarChar), (oracle.RAW(50), cx_oracle._OracleRaw), ]: assert isinstance(start.dialect_impl(dialect), test), \ "wanted %r got %r" % (test, start.dialect_impl(dialect))
class aos_code(Base): """ An area of study. Used Matches the code used in the student record system. """ __tablename__ = 'aos_code' aos_code = Column(CHAR(6), primary_key=True, comment="6 character unique identifier.") description = Column(String(50), comment="Name of the area of study.") fee_cat_id = Column(String(20), ForeignKey("fee_category.fee_cat_id")) department_id = Column(NCHAR(3), ForeignKey("department.department_id")) pathway = Column(String(50), comment="Alternative to description field.") valid_for_projection = Column( BoolField, comment="Flag for inclusion in future projection exercises.") require_foundation = Column( BoolField, comment="Flag for whether the area of study includes a foundation year." )
class Change(Base): """ A request to change the system. """ __tablename__ = 'tt_change' tt_change_id = Column(Integer, primary_key=True, comment="Unique identifier.") date_created = Column(Date, nullable=False, comment="Date the change was requested.") category = Column(String(50), nullable=False, comment="Rough categorisation of the change.") screen = Column( String(50), comment="Screen of the app which would primarily be affected.") requested_by = Column(String(100), comment="User that requested the change.") description = Column( String(8000), nullable=False, comment="Verbose description of what should be changed.") progress = Column( String(8000), comment="Feedback and explanation of any progress made (or not made).") closed = Column(BoolField, nullable=False, server_default=text("((0))"), comment="If the request is either complete or cancelled") closed_date = Column(Date, comment="Date the request was closed.") closed_version = Column( NCHAR(10), comment="The version of the app in which the change was closed.") suspend = Column(BoolField, server_default=text("((0))"), comment="If the field has been 'parked'.")
# coding: utf-8 from sqlalchemy import CHAR, Column, DateTime, ForeignKey, Integer, NCHAR, String, Table, Unicode, text from sqlalchemy.dialects.mssql.base import BIT from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata t_CustomerCaseTable = Table( 'CustomerCaseTable', metadata, Column('Title', Unicode(800)), Column('SR', Unicode(14), nullable=False), Column('Severity', NCHAR(10)), Column('Engineer', Unicode(50)), Column('Company', Unicode(300)), Column('Product', Unicode(50)), Column('IssueCode', Unicode(300)), Column('InquireType', Unicode(50)), Column('Description', Unicode), Column('EngageMethod', Unicode(50)), Column('SLA', Unicode(50)), Column('SLATime', DateTime), Column('Status', Unicode(50)), Column('CloseReason', Unicode(300)), Column('Creator', Unicode(50)), Column('ContactPerson', Unicode(300)), Column('TenantID', NCHAR(40)), Column('closedclassification', Unicode(50)), Column('CustomerType', Unicode(50)), Column('LookUpSource', Unicode(50)), Column('PoDCategory', Unicode(300)), Column('PoDProblemType', Unicode(300)), Column('PoDProduct', Unicode(300)), Column('ReportMonth', Unicode(10)), Column('ItemID', CHAR(12, 'SQL_Latin1_General_CP1_CI_AS'), nullable=False), Column('PartnerName', Unicode(300)), Column('PartnerType', Unicode(300)), Column('CaseType', Unicode(300)), Column('Seat', Integer), Column('OrgID', Unicode(300)), Column('SubscriptionID', Unicode(100)), Column('Modifier', Unicode(50)), Column('CreationTime', DateTime), Column('ModificationTime', DateTime), Column('AzulPartner', Unicode(300)))
class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = oracle.OracleDialect() def test_no_clobs_for_string_params(self): """test that simple string params get a DBAPI type of VARCHAR, not CLOB. This is to prevent setinputsizes from setting up cx_oracle.CLOBs on string-based bind params [ticket:793].""" class FakeDBAPI(object): def __getattr__(self, attr): return attr dialect = oracle.OracleDialect() dbapi = FakeDBAPI() b = bindparam("foo", "hello world!") eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING") b = bindparam("foo", "hello world!") eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING") def test_long(self): self.assert_compile(oracle.LONG(), "LONG") @testing.combinations( (Date(), cx_oracle._OracleDate), (oracle.OracleRaw(), cx_oracle._OracleRaw), (String(), String), (VARCHAR(), cx_oracle._OracleString), (DATE(), cx_oracle._OracleDate), (oracle.DATE(), oracle.DATE), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleUnicodeStringCHAR), (Text(), cx_oracle._OracleText), (UnicodeText(), cx_oracle._OracleUnicodeTextCLOB), (CHAR(), cx_oracle._OracleChar), (NCHAR(), cx_oracle._OracleNChar), (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR), (oracle.RAW(50), cx_oracle._OracleRaw), ) def test_type_adapt(self, start, test): dialect = cx_oracle.dialect() assert isinstance( start.dialect_impl(dialect), test ), "wanted %r got %r" % (test, start.dialect_impl(dialect)) @testing.combinations( (String(), String), (VARCHAR(), cx_oracle._OracleString), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleUnicodeStringNCHAR), (Text(), cx_oracle._OracleText), (UnicodeText(), cx_oracle._OracleUnicodeTextNCLOB), (NCHAR(), cx_oracle._OracleNChar), (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR), ) def test_type_adapt_nchar(self, start, test): dialect = cx_oracle.dialect(use_nchar_for_unicode=True) assert isinstance( start.dialect_impl(dialect), test ), "wanted %r got %r" % (test, start.dialect_impl(dialect)) def test_raw_compile(self): self.assert_compile(oracle.RAW(), "RAW") self.assert_compile(oracle.RAW(35), "RAW(35)") def test_char_length(self): self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)") oracle8dialect = oracle.dialect() oracle8dialect.server_version_info = (8, 0) self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect) self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)") self.assert_compile(CHAR(50), "CHAR(50)") @testing.combinations( (String(50), "VARCHAR2(50 CHAR)"), (Unicode(50), "VARCHAR2(50 CHAR)"), (NVARCHAR(50), "NVARCHAR2(50)"), (VARCHAR(50), "VARCHAR(50 CHAR)"), (oracle.NVARCHAR2(50), "NVARCHAR2(50)"), (oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"), (String(), "VARCHAR2"), (Unicode(), "VARCHAR2"), (NVARCHAR(), "NVARCHAR2"), (VARCHAR(), "VARCHAR"), (oracle.NVARCHAR2(), "NVARCHAR2"), (oracle.VARCHAR2(), "VARCHAR2"), ) def test_varchar_types(self, typ, exp): dialect = oracle.dialect() self.assert_compile(typ, exp, dialect=dialect) @testing.combinations( (String(50), "VARCHAR2(50 CHAR)"), (Unicode(50), "NVARCHAR2(50)"), (NVARCHAR(50), "NVARCHAR2(50)"), (VARCHAR(50), "VARCHAR(50 CHAR)"), (oracle.NVARCHAR2(50), "NVARCHAR2(50)"), (oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"), (String(), "VARCHAR2"), (Unicode(), "NVARCHAR2"), (NVARCHAR(), "NVARCHAR2"), (VARCHAR(), "VARCHAR"), (oracle.NVARCHAR2(), "NVARCHAR2"), (oracle.VARCHAR2(), "VARCHAR2"), ) def test_varchar_use_nchar_types(self, typ, exp): dialect = oracle.dialect(use_nchar_for_unicode=True) self.assert_compile(typ, exp, dialect=dialect) @testing.combinations( (oracle.INTERVAL(), "INTERVAL DAY TO SECOND"), (oracle.INTERVAL(day_precision=3), "INTERVAL DAY(3) TO SECOND"), (oracle.INTERVAL(second_precision=5), "INTERVAL DAY TO SECOND(5)"), ( oracle.INTERVAL(day_precision=2, second_precision=5), "INTERVAL DAY(2) TO SECOND(5)", ), ) def test_interval(self, type_, expected): self.assert_compile(type_, expected)
class SetInputSizesTest(fixtures.TestBase): __only_on__ = "oracle+cx_oracle" __backend__ = True @testing.combinations( (SmallInteger, 25, int, False), (Integer, 25, int, False), (Numeric(10, 8), decimal.Decimal("25.34534"), None, False), (Float(15), 25.34534, None, False), (oracle.BINARY_DOUBLE, 25.34534, "NATIVE_FLOAT", False), (oracle.BINARY_FLOAT, 25.34534, "NATIVE_FLOAT", False), (oracle.DOUBLE_PRECISION, 25.34534, None, False), (Unicode(30), u("test"), "NCHAR", True), (UnicodeText(), u("test"), "NCLOB", True), (Unicode(30), u("test"), None, False), (UnicodeText(), u("test"), "CLOB", False), (String(30), "test", None, False), (CHAR(30), "test", "FIXED_CHAR", False), (NCHAR(30), u("test"), "FIXED_NCHAR", False), (oracle.LONG(), "test", None, False), ) @testing.provide_metadata def test_setinputsizes( self, datatype, value, sis_value_text, set_nchar_flag ): if isinstance(sis_value_text, str): sis_value = getattr(testing.db.dialect.dbapi, sis_value_text) else: sis_value = sis_value_text class TestTypeDec(TypeDecorator): impl = NullType() def load_dialect_impl(self, dialect): if dialect.name == "oracle": return dialect.type_descriptor(datatype) else: return self.impl m = self.metadata # Oracle can have only one column of type LONG so we make three # tables rather than one table w/ three columns t1 = Table("t1", m, Column("foo", datatype)) t2 = Table( "t2", m, Column("foo", NullType().with_variant(datatype, "oracle")) ) t3 = Table("t3", m, Column("foo", TestTypeDec())) m.create_all() class CursorWrapper(object): # cx_oracle cursor can't be modified so we have to # invent a whole wrapping scheme def __init__(self, connection_fairy): self.cursor = connection_fairy.connection.cursor() self.mock = mock.Mock() connection_fairy.info["mock"] = self.mock def setinputsizes(self, *arg, **kw): self.mock.setinputsizes(*arg, **kw) self.cursor.setinputsizes(*arg, **kw) def __getattr__(self, key): return getattr(self.cursor, key) if set_nchar_flag: engine = testing_engine(options={"use_nchar_for_unicode": True}) else: engine = testing.db with engine.connect() as conn: connection_fairy = conn.connection for tab in [t1, t2, t3]: with mock.patch.object( connection_fairy, "cursor", lambda: CursorWrapper(connection_fairy), ): conn.execute(tab.insert(), {"foo": value}) if sis_value: eq_( conn.info["mock"].mock_calls, [mock.call.setinputsizes(foo=sis_value)], ) else: eq_( conn.info["mock"].mock_calls, [mock.call.setinputsizes()], ) def test_event_no_native_float(self): def _remove_type(inputsizes, cursor, statement, parameters, context): for param, dbapitype in list(inputsizes.items()): if dbapitype is testing.db.dialect.dbapi.NATIVE_FLOAT: del inputsizes[param] event.listen(testing.db, "do_setinputsizes", _remove_type) try: self.test_setinputsizes(oracle.BINARY_FLOAT, 25.34534, None, False) finally: event.remove(testing.db, "do_setinputsizes", _remove_type)
def test_nchar(self): self._test_setinputsizes(NCHAR(30), u("test"), testing.db.dialect.dbapi.NCHAR)