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)
