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()