def test_fancy_coltypes(metadata):
Table(
'simple_items', metadata,
Column('enum', postgresql.ENUM('A', 'B', name='blah')),
Column('bool', postgresql.BOOLEAN),
Column('number', NUMERIC(10, asdecimal=False)),
)
assert generate_code(metadata) == """\
def test_fancy_coltypes(self):
Table(
'simple_items', self.metadata,
Column('enum', ENUM('A', 'B', name='blah')),
Column('bool', BOOLEAN),
Column('number', NUMERIC(10, asdecimal=False)),
)
assert self.generate_code() == """\
class Criterion(Base):
""" classdocs """
__tablename__ = 'CRITERION'
id = Column(Integer, primary_key=True)
name = Column(String)
description = Column(String)
weight = Column(NUMERIC(precision=3, scale=0))
def __init__(self):
pass
class Evaluation(Base):
""" classdocs """
__tablename__ = 'EVALUATION'
id = Column('id', Integer, primary_key=True)
grade = Column(NUMERIC(precision=2, scale=1))
comments = Column(String)
candidate = Column(Integer, ForeignKey('CANDIDATE.id'))
evaluator = Column(Integer, ForeignKey('EVALUATOR.id'))
criterion = Column(Integer, ForeignKey('CRITERION.id'))
def __init__(self, **kwargs):
""" Constructor """
self.id = kwargs.get('id')
self.grade = kwargs.get('grade')
self.comments = kwargs.get('comments')
self.candidate = kwargs.get('candidate')
self.evaluator = kwargs.get('evaluator')
self.criterion = kwargs.get('criterion')
def test_fancy_coltypes(metadata):
Table(
"simple_items",
metadata,
Column("enum", postgresql.ENUM("A", "B", name="blah")),
Column("bool", postgresql.BOOLEAN),
Column("number", NUMERIC(10, asdecimal=False)),
)
assert (generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, Enum, MetaData, Numeric, Table
metadata = MetaData()
t_simple_items = Table(
'simple_items', metadata,
Column('enum', Enum('A', 'B', name='blah')),
Column('bool', Boolean),
Column('number', Numeric(10, asdecimal=False))
)
""")
def test_numeric_coerced_to_text(self):
"""Numeric columns are coerced to text columns."""
columns = self.coercer._coerce([
Column('column', NUMERIC()),
Column('column', BOOLEAN()),
Column('column', INTEGER()),
Column('column', BIGINT()),
Column('column', SMALLINT()),
Column('column', DATETIME()),
Column('column', TIMESTAMP()),
])
column_types = [type(column.type) for column in columns]
self.assertListEqual(column_types, [
TEXT,
IntegerDecorator,
IntegerDecorator,
IntegerDecorator,
IntegerDecorator,
DatetimeDecorator,
DatetimeDecorator,
])
def get_columns(self, connection, table_name, schema=None, **kw):
# Query to extract the details of all the fields of the given table
tblqry = """
SELECT r.rdb$field_name AS fname,
r.rdb$null_flag AS null_flag,
t.rdb$type_name AS ftype,
f.rdb$field_sub_type AS stype,
f.rdb$field_length/
COALESCE(cs.rdb$bytes_per_character,1) AS flen,
f.rdb$field_precision AS fprec,
f.rdb$field_scale AS fscale,
COALESCE(r.rdb$default_source,
f.rdb$default_source) AS fdefault
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
JOIN rdb$types t
ON t.rdb$type=f.rdb$field_type AND
t.rdb$field_name='RDB$FIELD_TYPE'
LEFT JOIN rdb$character_sets cs ON
f.rdb$character_set_id=cs.rdb$character_set_id
WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=?
ORDER BY r.rdb$field_position
"""
# get the PK, used to determine the eventual associated sequence
pk_constraint = self.get_pk_constraint(connection, table_name)
pkey_cols = pk_constraint["constrained_columns"]
tablename = self.denormalize_name(table_name)
# get all of the fields for this table
c = connection.execute(tblqry, [tablename])
cols = []
while True:
row = c.fetchone()
if row is None:
break
name = self.normalize_name(row["fname"])
orig_colname = row["fname"]
# get the data type
colspec = row["ftype"].rstrip()
coltype = self.ischema_names.get(colspec)
if coltype is None:
util.warn("Did not recognize type '%s' of column '%s'" %
(colspec, name))
coltype = sqltypes.NULLTYPE
elif issubclass(coltype, Integer) and row["fprec"] != 0:
coltype = NUMERIC(precision=row["fprec"],
scale=row["fscale"] * -1)
elif colspec in ("VARYING", "CSTRING"):
coltype = coltype(row["flen"])
elif colspec == "TEXT":
coltype = TEXT(row["flen"])
elif colspec == "BLOB":
if row["stype"] == 1:
coltype = TEXT()
else:
coltype = BLOB()
else:
coltype = coltype()
# does it have a default value?
defvalue = None
if row["fdefault"] is not None:
# the value comes down as "DEFAULT 'value'": there may be
# more than one whitespace around the "DEFAULT" keyword
# and it may also be lower case
# (see also http://tracker.firebirdsql.org/browse/CORE-356)
defexpr = row["fdefault"].lstrip()
assert defexpr[:8].rstrip().upper() == "DEFAULT", (
"Unrecognized default value: %s" % defexpr)
defvalue = defexpr[8:].strip()
if defvalue == "NULL":
# Redundant
defvalue = None
col_d = {
"name": name,
"type": coltype,
"nullable": not bool(row["null_flag"]),
"default": defvalue,
"autoincrement": "auto",
}
if orig_colname.lower() == orig_colname:
col_d["quote"] = True
# if the PK is a single field, try to see if its linked to
# a sequence thru a trigger
if len(pkey_cols) == 1 and name == pkey_cols[0]:
seq_d = self.get_column_sequence(connection, tablename, name)
if seq_d is not None:
col_d["sequence"] = seq_d
cols.append(col_d)
return cols
def _type_descriptor_mock(desc):
if type(desc) == DECIMAL:
return NUMERIC(precision=desc.precision, scale=desc.scale)
def rep_case_get_count(prm_date_start=None, prm_date_end=None):
if prm_date_start is None:
date_start = '2020-03-23'
else:
date_start = prm_date_start
if prm_date_end is None:
date_end = '2020-03-23'
else:
date_end = prm_date_end
query_smtap = session_mis.query(HltSmTapTable.rf_tapid).group_by(HltSmTapTable.rf_tapid)
query_smtap = query_smtap.having(func.count(HltSmTapTable.rf_tapid) > 1)
query = session_mis.query((HltLpuDoctorTable.fam_v+" "+HltLpuDoctorTable.im_v+" "+HltLpuDoctorTable.ot_v).label('fio_v'),
HltDocPrvdTable.name, func.sum(cast(HltTapTable.isclosed, NUMERIC(10, 0))).label('case'))
query = query.join(HltTapTable, HltTapTable.rf_lpudoctorid == HltLpuDoctorTable.lpudoctorid)
query = query.join(HltDocPrvdTable, HltTapTable.rf_docprvdid == HltDocPrvdTable.docprvdid)
query = query.filter(HltTapTable.dateclose >= date_start)
query = query.filter(HltTapTable.dateclose <= date_end)
query = query.filter(HltTapTable.tapid.in_(query_smtap))
query = query.group_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltDocPrvdTable.name)
query = query.order_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltDocPrvdTable.name)
return query.all()
