def set_all_class_defaults(Base): """ Django model fields can have defaults. Unfortunately, these defaults are only set in Python not on the database itself, therefore, if we want to use SQLAlchemy to create records in the database table, while adhering to these Django field defaults, we have to set them up again on the SQLAlchemy class, this method does that to all the classes defined on the passed in Base mapping. """ for DjangoModel in apps.get_models(): # Iterate through all the Django Models defined try: # Try to get a class that corresponds to this model # This might not exist because we only did a reflection restricted to a few tables, or # because we have a database table not reflected in our Django models. BaseClass = get_class(DjangoModel, Base) for field in DjangoModel._meta.fields: # If we do have valid class, we can iterate through the fields and find all the fields that # have defaults if field.has_default(): column = BaseClass.__table__.columns.get(field.attname) # If there are schema differences between the Django model and the particular table # that we are looking at (even though it has the same table name), then the column # with a default value may not exist if column is not None: # The column does exist, set up a default by creating a SQLALchemy ColumnDefault object default = ColumnDefault(field.default) # Set the default of this column to our new default column.default = default # This is necessary, but I can't find the part of the SQLAlchemy source code that # I found this. default._set_parent_with_dispatch(column) except ClassNotFoundError: pass
def table_add_column(table, name, col_type, session, default=None): """Adds a column to a table .. warning:: Uses raw statements, probably needs to be changed in order to work on other databases besides SQLite :param string table: Table to add column to (can be name or schema) :param string name: Name of new column to add :param col_type: The sqlalchemy column type to add :param Session session: SQLAlchemy Session to do the alteration :param default: Default value for the created column (optional) """ if isinstance(table, basestring): table = table_schema(table, session) if name in table_columns(table, session): # If the column already exists, we don't have to do anything. return # Add the column to the table if not isinstance(col_type, TypeEngine): # If we got a type class instead of an instance of one, instantiate it col_type = col_type() type_string = session.bind.engine.dialect.type_compiler.process(col_type) statement = 'ALTER TABLE %s ADD %s %s' % (table.name, name, type_string) session.execute(statement) # Update the table with the default value if given if default is not None: # Get the new schema with added column table = table_schema(table.name, session) if not isinstance(default, (ColumnDefault, Sequence)): default = ColumnDefault(default) default._set_parent(getattr(table.c, name)) statement = table.update().values({name: default.execute(bind=session.bind)}) session.execute(statement)
async def test_create_post(db_engine, cleanup_db): post_topic = 'post topic' post_description = 'post description' dt_now = datetime.utcnow() async with db_engine.acquire() as conn: section_id = await conn.scalar( insert(section).values({ 'name': 'section name', 'description': 'section description' })) with mock.patch.object(post.c.created_at, 'default', new=ColumnDefault(lambda: dt_now)): new_post = await create_post(conn, section_id, post_topic, post_description) assert new_post.section_id == section_id assert new_post.topic == post_topic assert new_post.description == post_description assert new_post.created_at == dt_now assert await conn.scalar( select([ exists().where( and_(post.c.id == new_post.id, post.c.section_id == section_id, post.c.topic == post_topic, post.c.description == post_description, post.c.created_at == dt_now)) ]))
async def test_get_post(db_engine, cleanup_db): post_topic = 'post topic' post_description = 'post description' dt_now = datetime.utcnow() async with db_engine.acquire() as conn: section_id = await conn.scalar( insert(section).values({ 'name': 'section name', 'description': 'section description' })) with mock.patch.object(post.c.created_at, 'default', new=ColumnDefault(lambda: dt_now)): post_id = await conn.scalar( insert(post).values({ 'section_id': section_id, 'topic': 'post topic', 'description': 'post description' })) _post = await get_post(conn, post_id) assert _post is not None assert _post.section_id == section_id assert _post.topic == post_topic assert _post.description == post_description assert _post.created_at == dt_now
class Payment(Base): __tablename__ = "payment" paymntId = Column(Integer, primary_key=True) paymntDueDate = Column(Date, nullable=False) paymntBank = Column(Unicode(100), nullable=True) paymntSerial = Column(Unicode(50), nullable=True) paymntAmount = Column(Float, ColumnDefault(0), nullable=False) # paymntPayer = Column( Integer, ForeignKey('customers.custId') ) paymntNamePayer = Column(Unicode(50), nullable=True) paymntWrtDate = Column(Date, nullable=True) paymntDesc = Column(Unicode(200), nullable=True) # Transaction id is zero for non-invoice payments. paymntTransId = Column(Integer, ColumnDefault(0)) # Bill id is zero for temporary transactions. paymntBillId = Column(Integer, ColumnDefault(0)) paymntTrckCode = Column(Unicode(50), nullable=True) paymntOrder = Column(Integer, ColumnDefault(0), nullable=False) # paymntChq = Column( Integer, ForeignKey('cheques.chqId') ) def __init__(self, paymntDueDate, paymntBank, paymntSerial, paymntAmount, paymntNamePayer, paymntWrtDate, paymntDesc, paymntTransId, paymntBillId, paymntTrckCode, paymntOrder, paymntId=1): #self.paymntNo = paymntNo self.paymntDueDate = paymntDueDate self.paymntBank = paymntBank self.paymntSerial = paymntSerial self.paymntAmount = paymntAmount self.paymntNamePayer = paymntNamePayer self.paymntWrtDate = paymntWrtDate self.paymntDesc = paymntDesc self.paymntTransId = paymntTransId self.paymntBillId = paymntBillId self.paymntTrckCode = paymntTrckCode self.paymntOrder = paymntOrder
class Notebook(Base): __tablename__ = "notebook" id = Column(Integer, primary_key=True) subject_id = Column(Integer, ForeignKey('subject.id')) bill_id = Column(Integer, ForeignKey('bill.id')) desc = Column(UnicodeText, ColumnDefault("")) value = Column(Float, ColumnDefault(0), nullable=False) factorId = Column(Integer, ColumnDefault(0), nullable=False) chqId = Column(Integer, ForeignKey('Cheque.chqId')) def __init__(self, subject_id, bill_id, value, desc, factorId=0, chqId=0, id=1): self.subject_id = subject_id self.bill_id = bill_id self.value = value self.desc = desc self.factorId = factorId self.chqId = chqId
class ClusterState(BASE): """The state of the Cluster. :param id: int, identity as primary key. :param state: Enum, 'UNINITIALIZED': the cluster is ready to setup. 'INSTALLING': the cluster is not installing. 'READY': the cluster is setup. 'ERROR': the cluster has error. :param progress: float, the installing progress from 0 to 1. :param message: the latest installing message. :param severity: Enum, the installing message severity. ('INFO', 'WARNING', 'ERROR'). :param update_timestamp: the lastest timestamp the entry got updated. :param cluster: refer to Cluster. """ __tablename__ = 'cluster_state' id = Column(Integer, ForeignKey('cluster.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True) state = Column(Enum('UNINITIALIZED', 'INSTALLING', 'READY', 'ERROR'), ColumnDefault('UNINITIALIZED')) progress = Column(Float, ColumnDefault(0.0)) message = Column(Text) severity = Column(Enum('INFO', 'WARNING', 'ERROR'), ColumnDefault('INFO')) update_timestamp = Column(DateTime, default=datetime.now, onupdate=datetime.now) cluster = relationship('Cluster', backref=backref('state', uselist=False)) def __init__(self, **kwargs): super(ClusterState, self).__init__(**kwargs) @hybrid_property def clustername(self): """clustername getter""" return self.cluster.name def __repr__(self): return ( '<ClusterState %r: state=%r, progress=%s, ' 'message=%s, severity=%s>' ) % ( self.clustername, self.state, self.progress, self.message, self.severity )
def get_metadata(version=latest_version, db=db): metadata = MetaData(db) schema_version = Table( '_schema_version', metadata, Column('version', Integer), ) users = Table( 'users', metadata, Column('user_id', Integer, primary_key=True), Column('user_name', String, unique=True), Column('pass_hash', String), Column('pass_salt', String), Column('commit_name', String), Column('commit_email', String), Column('can_create_users', Boolean, ColumnDefault(False)), Column('can_create_repositories', Boolean, ColumnDefault(False)), ) keys = Table( 'keys', metadata, Column('key_id', Integer, primary_key=True), Column('user_id', Integer, ForeignKey('users.user_id')), Column('name', String), Column('public_key', Text), # TODO: public_key should be unique, but TEXT columns can't be (I think) # verify me maybe? ) repos = Table( 'repositories', metadata, Column('repository_id', Integer, primary_key=True), Column('repository_name', String, unique=True), ) repo_acls = Table( 'repository_acls', metadata, Column('user_id', Integer, ForeignKey('users.user_id')), Column('repository_id', Integer, ForeignKey('repositories.repository_id')), Column('is_owner', Boolean, ColumnDefault(False)), Column('can_write', Boolean, ColumnDefault(False)), Column('can_rewind', Boolean, ColumnDefault(False)), Column('can_read', Boolean, ColumnDefault(False)), Column('can_create_tag', Boolean, ColumnDefault(False)), Column('can_modify_tag', Boolean, ColumnDefault(False)), ) return metadata
class HostState(BASE): '''the state of the ClusterHost. id: int, identity as primary key. state: Enum. 'UNINITIALIZED': the host is ready to setup. 'INSTALLING': the host is not installing. 'READY': the host is setup. 'ERROR': the host has error. progress: float, the installing progress from 0 to 1. message: the latest installing message. severity: Enum, the installing message severity. Should be in one of ['INFO', 'WARNING', 'ERROR']. update_timestamp: the lastest timestamp the entry got updated. host: refer to ClusterHost. ''' __tablename__ = "host_state" id = Column(Integer, ForeignKey('cluster_host.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True) state = Column(Enum('UNINITIALIZED', 'INSTALLING', 'READY', 'ERROR'), ColumnDefault('UNINITIALIZED')) progress = Column(Float, ColumnDefault(0.0)) message = Column(String) severity = Column(Enum('INFO', 'WARNING', 'ERROR'), ColumnDefault('INFO')) update_timestamp = Column(DateTime, default=datetime.now, onupdate=datetime.now) host = relationship('ClusterHost', backref=backref('state', uselist=False)) def __init__(self, **kwargs): super(HostState, self).__init__(**kwargs) @property def hostname(self): '''hostname getter''' return self.host.hostname def __repr__(self): return ('<HostState %r: state=%r, progress=%s, ' 'message=%s, severity=%s>') % (self.hostname, self.state, self.progress, self.message, self.severity)
def uuid(cls): parent_tablename = get_parent_tablename_by_bases(cls.__bases__) if has_inherited_table(cls) and parent_tablename != '': return Column(ForeignKey(parent_tablename + '.uuid'), primary_key=True) else: return Column(UUID(as_uuid=True), ColumnDefault(uuid.uuid4), primary_key=True)
class Trades (Base): __tablename__ = "trades" Id = Column(Integer, primary_key=True) Code = Column(Integer, nullable=False) tDate = Column(Date, nullable=False) Bill = Column(Integer, ColumnDefault(0)) # Bill id is zero for temporary Cust = Column(Integer, ForeignKey('customers.custId')) Addition = Column(Float, ColumnDefault(0), nullable=False) Subtraction = Column(Float, ColumnDefault(0), nullable=False) VAT = Column(Float, ColumnDefault(0), nullable=False) Fee = Column(Float, ColumnDefault(0), nullable=False) PayableAmnt = Column(Float, ColumnDefault(0), nullable=False) CashPayment = Column(Float, ColumnDefault(0), nullable=False) ShipDate = Column(Date, nullable=True) Delivery = Column(Unicode(50), nullable=True) #Place of delivery ShipVia = Column(Unicode(100), nullable=True) Permanent = Column(Boolean, ColumnDefault(0)) Desc = Column(Unicode(200), nullable=True) Sell = Column(Boolean, ColumnDefault(0), nullable=False) LastEdit = Column(Date, nullable=True) Acivated = Column(Boolean, ColumnDefault(0), nullable=False) def __init__(self, Code, Date, Bill, Cust, Add, Sub, VAT, Fee, PayableAmnt, Cash, ShpDate, Delivery, ShipVia, Prmnt, Desc, Sell, LastEdit, Acivated): self.Code = Code self.tDate = Date self.Bill = Bill self.Cust = Cust self.Addition = Add self.Subtraction = Sub self.VAT = VAT self.Fee = Fee self.PayableAmnt = PayableAmnt self.CashPayment = Cash self.ShipDate = ShpDate self.Delivery = Delivery self.ShipVia = ShipVia self.Permanent = Prmnt self.Desc = Desc self.Sell = Sell self.LastEdit = LastEdit self.Acivated = Acivated
class ChequeHistory(Base): __tablename__ = "ChequeHistory" Id = Column(Integer, primary_key=True) ChequeId = Column(Integer, ForeignKey('Cheque.chqId')) Amount = Column(Float, ColumnDefault(0), nullable=False) WrtDate = Column(Date, nullable=False) DueDate = Column(Date, nullable=False) Serial = Column(Unicode(50), nullable=False) Status = Column(Integer, ColumnDefault(0), nullable=False) Cust = Column(Integer, ForeignKey('customers.custId')) Account = Column(Integer, ForeignKey('bankAccounts.accId'), nullable=True) TransId = Column( Integer, ColumnDefault(0)) #Transaction id is zero for non-invoice cheques. Desc = Column(Unicode(200), nullable=True) Date = Column(Date, nullable=False) # Delete = Column(Boolean) def __init__(self, ChequeId, Amount, WrtDate, DueDate, Serial, Status, Cust, Account, TransId, Desc, Date, Delete=False, Id=1): self.ChequeId = ChequeId self.Amount = Amount self.WrtDate = WrtDate self.DueDate = DueDate self.Serial = Serial self.Status = Status self.Cust = Cust self.Account = Account self.TransId = TransId self.Desc = Desc self.Date = Date
class Products(Base): __tablename__ = "products" id = Column(Integer, primary_key=True) code = Column(Unicode(20), nullable=False) name = Column(Unicode(60), nullable=False) accGroup = Column(Integer, ForeignKey('productGroups.id')) location = Column(Unicode(50), nullable=True) quantity = Column(Float, ColumnDefault(0), nullable=False) qntyWarning = Column(Float, ColumnDefault(0), nullable=True) oversell = Column(Boolean, ColumnDefault(0)) purchacePrice = Column(Float, ColumnDefault(0), nullable=False) sellingPrice = Column(Float, ColumnDefault(0), nullable=False) discountFormula = Column(Unicode(100), nullable=True) productDesc = Column(Unicode(200), nullable=True) uMeasurement = Column(Unicode(30), nullable=True) def __init__(self, code, name, qntyWarning, oversell, location, quantity, purchacePrice, sellingPrice, accGroup, productDesc, discountFormula, uMeasurement, id=1): self.code = code self.name = name self.oversell = oversell self.location = location self.quantity = quantity self.accGroup = accGroup self.productDesc = productDesc self.qntyWarning = qntyWarning self.sellingPrice = sellingPrice self.purchacePrice = purchacePrice self.discountFormula = discountFormula self.uMeasurement = uMeasurement
class LogProgressingHistory(BASE): """host installing log history for each file. :param id: int, identity as primary key. :param pathname: str, the full path of the installing log file. unique. :param position: int, the position of the log file it has processed. :param partial_line: str, partial line of the log. :param progressing: float, indicate the installing progress between 0 to 1. :param message: str, str, the installing message. :param severity: Enum, the installing message severity. ('ERROR', 'WARNING', 'INFO') :param line_matcher_name: str, the line matcher name of the log processor. :param update_timestamp: datetime, the latest timestamp the entry updated. """ __tablename__ = 'log_progressing_history' id = Column(Integer, primary_key=True) pathname = Column(String(80), unique=True) position = Column(Integer, ColumnDefault(0)) partial_line = Column(Text) progress = Column(Float, ColumnDefault(0.0)) message = Column(Text) severity = Column(Enum('ERROR', 'WARNING', 'INFO'), ColumnDefault('INFO')) line_matcher_name = Column(String(80), ColumnDefault('start')) update_timestamp = Column(DateTime, default=datetime.now, onupdate=datetime.now) def __init__(self, **kwargs): super(LogProgressingHistory, self).__init__(**kwargs) def __repr__(self): return ( 'LogProgressingHistory[%r: position %r,' 'partial_line %r,progress %r,message %r,' 'severity %r]' ) % ( self.pathname, self.position, self.partial_line, self.progress, self.message, self.severity )
class Exchanges(Base): __tablename__ = "exchanges" exchngId = Column(Integer, primary_key=True) exchngNo = Column(Integer, nullable=False) exchngProduct = Column(Integer, ForeignKey('products.id')) exchngQnty = Column(Float, ColumnDefault(0), nullable=False) exchngUntPrc = Column(Float, ColumnDefault(0), nullable=False) exchngUntDisc = Column(Unicode(30), ColumnDefault("0"), nullable=False) exchngTransId = Column(Integer, ForeignKey('transactions.transId')) exchngDesc = Column(Unicode(200), nullable=True) def __init__(self, exchngNo, exchngProduct, exchngQnty, exchngUntPrc, exchngUntDisc, exchngTransId, exchngDesc): self.exchngNo = exchngNo self.exchngProduct = exchngProduct self.exchngQnty = exchngQnty self.exchngUntPrc = exchngUntPrc self.exchngUntDisc = exchngUntDisc self.exchngTransId = exchngTransId self.exchngDesc = exchngDesc
class Transactions(Base): __tablename__ = "transactions" transId = Column( Integer, primary_key = True ) transCode = Column( Integer, nullable = False ) transDate = Column( Date, nullable = False ) transBill = Column( Integer, ColumnDefault(0) ) #Bill id is zero for temporary transactions transCust = Column( Integer, ForeignKey('customers.custId') ) transAddition = Column( Float, ColumnDefault(0), nullable = False ) transSubtraction= Column( Float, ColumnDefault(0), nullable = False ) transTax = Column( Float, ColumnDefault(0), nullable = False ) transPayableAmnt= Column( Float, ColumnDefault(0), nullable = False ) transCashPayment= Column( Float, ColumnDefault(0), nullable = False ) transShipDate = Column( Date, nullable = True ) transFOB = Column( Unicode(50), nullable = True ) transShipVia = Column( Unicode(100), nullable = True ) transPermanent = Column( Boolean, ColumnDefault(0) ) transDesc = Column( Unicode(200), nullable = True ) transSell = Column( Boolean, ColumnDefault(0), nullable = False ) transLastEdit = Column( Date, nullable = True ) transAcivated = Column( Boolean, ColumnDefault(0), nullable = False ) def __init__( self, transCode, transDate, transBill, transCust, transAdd, transSub, transTax,transPayableAmnt, transCash, transShpDate, transFOB, transShipVia, transPrmnt, transDesc, transSell,transLastEdit,transAcivated ): self.transCode = transCode self.transDate = transDate self.transBill = transBill self.transCust = transCust self.transAddition = transAdd self.transSubtraction = transSub self.transTax = transTax self.transPayableAmnt = transPayableAmnt self.transCashPayment = transCash self.transShipDate = transShpDate self.transFOB = transFOB self.transShipVia = transShipVia self.transPermanent = transPrmnt self.transDesc = transDesc self.transSell = transSell self.transLastEdit = transLastEdit self.transAcivated = transAcivated
class Bill(Base): __tablename__ = "bill" id = Column(Integer, primary_key=True) number = Column(Integer, nullable=False) creation_date = Column(Date, nullable=False) lastedit_date = Column(Date, nullable=False) date = Column(Date, nullable=False) #date of transactions in the bill permanent = Column(Boolean, ColumnDefault(False), nullable=False) def __init__(self, number, creation_date, lastedit_date, date, permanent): self.number = number self.creation_date = creation_date self.lastedit_date = lastedit_date self.date = date self.permanent = permanent
def table_add_column(table, name, col_type, session, default=None): """Adds a column to a table .. warning:: Uses raw statements, probably needs to be changed in order to work on other databases besides SQLite :param string table: Table to add column to (can be name or schema) :param string name: Name of new column to add :param col_type: The sqlalchemy column type to add :param Session session: SQLAlchemy Session to do the alteration :param default: Default value for the created column (optional) """ if isinstance(table, str): table = table_schema(table, session) if name in table_columns(table, session): # If the column already exists, we don't have to do anything. return # Add the column to the table if not isinstance(col_type, TypeEngine): # If we got a type class instead of an instance of one, instantiate it col_type = col_type() type_string = session.bind.engine.dialect.type_compiler.process(col_type) statement = 'ALTER TABLE %s ADD %s %s' % (table.name, name, type_string) session.execute(statement) session.commit() # Update the table with the default value if given if default is not None: # Get the new schema with added column table = table_schema(table.name, session) if not isinstance(default, (ColumnDefault, Sequence)): default = ColumnDefault(default) default._set_parent(getattr(table.c, name)) statement = table.update().values( {name: default.execute(bind=session.bind)}) session.execute(statement) session.commit()
class Volunteer(Base): __tablename__ = 'volunteers' id = Column(Integer, primary_key=True) volunteer_id = Column(String(5), nullable=True, unique=True) # karma = Column(Integer, server_default="50") interests = relationship('Tag', secondary=volunteer_tag, back_populates='volunteers') # additional from presentation email = Column(String, nullable=True, unique=True) # phone_number = Column(String(length=20), nullable=True) # # phone_number_constraint = CheckConstraint(f"phone_number ~* {phone_number_regex}") work = Column(String, nullable=True) # speciality = Column(String, nullable=True) # food_preferences = Column(Enum(FoodPreferences), nullable=True) ### volunteering_experience = Column(Text, nullable=True) # interested_in_projects = Column(Text, nullable=True) ## * children_work_experience = Column(Text, nullable=True) ## * additional_skills = Column(String, nullable=True) ## * reasons_to_work = Column(String, nullable=True) ## * expectations = Column(Text, nullable=True) ## medical_contradictions = Column(Text, nullable=True) ### cloth_size = Column(Enum(ClothSize), nullable=True) ### * accept_news = Column(Boolean, ColumnDefault(True), server_default='t') ## ### save photo login_id = Column(Integer, ForeignKey("volunteer_logins.id")) login = relationship("VolunteerLogin", back_populates="volunteer") languages = relationship("VolunteerLanguageAssociation", back_populates="volunteer") # known_by_id = Column(Integer, ForeignKey("information_sources.id"), nullable=True) known_by = relationship("InformationSource", back_populates='volunteers') ## * events = relationship("EventVolunteer", back_populates="volunteer") visits = relationship("VolunteerVisit", back_populates="volunteer")
class Subject(Base): __tablename__ = "subject" id = Column(Integer, primary_key=True) code = Column(String(20), nullable=False) name = Column(Unicode(60), nullable=False) parent_id = Column(Integer, ColumnDefault(0), ForeignKey('subject.id'), nullable=False) lft = Column(Integer, nullable=False) rgt = Column(Integer, nullable=False) type = Column(Integer) # 0 for Debtor, 1 for Creditor, 2 for both def __init__(self, code, name, parent_id, left, right, type): self.code = code self.name = name self.parent_id = parent_id self.lft = left self.rgt = right self.type = type
async def test_create_child_comment(db_engine, cleanup_db): comment_text = 'comment text' dt_now = datetime.utcnow() async with db_engine.acquire() as conn: section_id = await conn.scalar( insert(section).values({ 'name': 'section name', 'description': 'section description' })) post_id = await conn.scalar( insert(post).values({ 'section_id': section_id, 'topic': 'post topic', 'description': 'post description' })) parent_id = await conn.scalar( insert(comment).values({ 'post_id': post_id, 'text': comment_text })) with mock.patch.object(comment.c.created_at, 'default', new=ColumnDefault(lambda: dt_now)): new_comment = await create_comment(conn, post_id, comment_text, parent_id=parent_id) assert new_comment is not None assert new_comment.post_id == post_id assert new_comment.text == comment_text assert new_comment.parent_id == parent_id assert new_comment.created_at == dt_now assert await conn.scalar( select([ exists().where( and_(comment.c.id == new_comment.id, comment.c.post_id == post_id, comment.c.text == comment_text, comment.c.created_at == dt_now, comment.c.parent_id == parent_id)) ]))
Column('ip', String(length=15), nullable=False, unique=True), Column('last_ping', TIMESTAMP, nullable=True), Column('rating', Integer(), default=0)) hoster_file = Table( 'hoster_files', meta, Column('id', Integer(), primary_key=True, nullable=False), Column('hash', String(length=64), nullable=False, unique=True), Column('owner_key', String(length=128), nullable=False), Column('signature', String(length=128), nullable=True), Column('path', String(length=512), nullable=True), Column('timestamp', TIMESTAMP, nullable=False, default=datetime.utcnow), Column('size', Integer()), Column('client_contract_address', String(length=128), nullable=False), Column('my_monitoring_number', Integer(), default=ColumnDefault(0)), Column('status', String(length=32), nullable=False, default=ColumnDefault('active')), Column('no_deposit_counter', Integer(), default=ColumnDefault(0)), Column('replacing_host_address', String(length=128)), Column('send_data_to_contract_after_uploading_body', Boolean(), default=ColumnDefault(False)), ) hoster_file_m2m = Table( 'hoster_files_m2m', meta, Column('file_id',
from datetime import datetime from sqlalchemy import (Column, ColumnDefault, DateTime, ForeignKey, Integer, MetaData, String, Table) meta = MetaData() # Раздел форума section = Table( 'section', meta, Column('id', Integer, primary_key=True), Column('name', String), Column('description', String), Column('created_at', DateTime, default=ColumnDefault(datetime.utcnow)), Column('updated_at', DateTime, onupdate=ColumnDefault(datetime.utcnow))) # Пост post = Table( 'post', meta, Column('id', Integer, primary_key=True), Column('section_id', ForeignKey('section.id', ondelete='CASCADE')), Column('topic', String), Column('description', String), Column('created_at', DateTime, default=ColumnDefault(datetime.utcnow)), Column('updated_at', DateTime, onupdate=ColumnDefault(datetime.utcnow))) # Комментарий к посту comment = Table( 'comment', meta, Column('id', Integer, primary_key=True), Column('post_id', ForeignKey('post.id', ondelete='CASCADE')), Column('parent_id', ForeignKey('comment.id', ondelete='CASCADE')), Column('text', String), Column('created_at', DateTime, default=ColumnDefault(datetime.utcnow)), Column('updated_at', DateTime, onupdate=ColumnDefault(datetime.utcnow)))
class Customers(Base): __tablename__ = "customers" custId = Column(Integer, primary_key=True) custCode = Column(Unicode(15), unique=True, nullable=False) custName = Column(Unicode(100), nullable=False) custSubj = Column(Integer, ForeignKey('subject.id')) custPhone = Column(Unicode(15), nullable=True) custCell = Column(Unicode(15), nullable=True) custFax = Column(Unicode(15), nullable=True) custAddress = Column(Unicode(100), nullable=True) custPostalCode = Column(Unicode(15), nullable=True) custEmail = Column(Unicode(15), nullable=True) custEcnmcsCode = Column(Unicode(20), nullable=True) custPersonalCode = Column(Unicode(15), nullable=True) custWebPage = Column(Unicode(50), nullable=True) custResposible = Column(Unicode(50), nullable=True) custConnector = Column(Unicode(50), nullable=True) custGroup = Column(Integer, ForeignKey('custGroups.custGrpId')) custDesc = Column(Unicode(200), nullable=True) custBalance = Column(Float, ColumnDefault(0), nullable=False) custCredit = Column(Float, ColumnDefault(0), nullable=False) custRepViaEmail = Column(Boolean, ColumnDefault(False), nullable=False) custAccName1 = Column(Unicode(50), nullable=True) custAccNo1 = Column(Unicode(30), nullable=True) custAccBank1 = Column(Unicode(50), nullable=True) custAccName2 = Column(Unicode(50), nullable=True) custAccNo2 = Column(Unicode(30), nullable=True) custAccBank2 = Column(Unicode(50), nullable=True) custTypeBuyer = Column(Boolean, ColumnDefault(True), nullable=False) custTypeSeller = Column(Boolean, ColumnDefault(True), nullable=False) custTypeMate = Column(Boolean, ColumnDefault(False), nullable=False) custTypeAgent = Column(Boolean, ColumnDefault(False), nullable=False) custIntroducer = Column(Integer, ForeignKey('customers.custId')) custCommission = Column(Unicode(15), nullable=True) custMarked = Column(Boolean, ColumnDefault(False), nullable=False) custReason = Column(Unicode(200), nullable=True) custDiscRate = Column(Unicode(15), nullable=True) def __init__(self, custCode, custName, custSubj, custPhone, custCell, custFax, custAddress, custEmail, custEcnmcsCode, custWebPage, custResposible, custConnector, custGroup, custPostalCode="", custPersonalCode="", custDesc="", custRepViaEmail=False, custAccName1="", custAccNo1="", custAccBank1="", custAccName2="", custAccNo2="", custAccBank2="", custTypeBuyer=True, custTypeSeller=True, custTypeMate=False, custTypeAgent=False, custIntroducer="", custCommission="", custMarked=False, custReason="", custDiscRate="", custBalance=float(0), custCredit=float(0), custId=1): self.custCode = custCode self.custName = custName self.custSubj = custSubj self.custPhone = custPhone self.custCell = custCell self.custFax = custFax self.custAddress = custAddress self.custPostalCode = custPostalCode self.custEmail = custEmail self.custEcnmcsCode = custEcnmcsCode self.custPersonalCode = custPersonalCode self.custWebPage = custWebPage self.custResposible = custResposible self.custConnector = custConnector self.custGroup = custGroup self.custDesc = custDesc self.custBalance = custBalance self.custCredit = custCredit self.custRepViaEmail = custRepViaEmail self.custAccName1 = custAccName1 self.custAccNo1 = custAccNo1 self.custAccBank1 = custAccBank1 self.custAccName2 = custAccName2 self.custAccNo2 = custAccNo2 self.custAccBank2 = custAccBank2 self.custTypeBuyer = custTypeBuyer self.custTypeSeller = custTypeSeller self.custTypeMate = custTypeMate self.custTypeAgent = custTypeAgent self.custIntroducer = custIntroducer self.custCommission = custCommission self.custMarked = custMarked self.custReason = custReason self.custDiscRate = custDiscRate
def type(cls): return Column(VARCHAR, ColumnDefault(cls.__tablename__), nullable=False)
class Event(UUIDMixin, TypeMixin, ElementBase): timestamp = Column(TIMESTAMP, ColumnDefault(datetime.datetime.now), nullable=False) event_type = Column(VARCHAR, nullable=False)
class Cheque(Base): __tablename__ = "Cheque" chqId = Column(Integer, primary_key=True) chqAmount = Column(Float, ColumnDefault(0), nullable=False) chqWrtDate = Column(Date, nullable=False) chqDueDate = Column(Date, nullable=False) chqSerial = Column(Unicode(50), nullable=False) # an integer describes cheque status # # 1 Pardakhti, Vosol nashode # 2 Pardakhti, Vosol shode # 3 Daryafti, Vosol shode # 4 Daryafti, Vosol nashode # 5 Kharj shode # 6 Odat az moshtari shode # 7 Odat be moshtari shode # 8 Pardakhti, Bargasht shode # 9 Daryafti, Bargasht shode # 10 Dar jaryan e vosul chqStatus = Column(Integer, ColumnDefault(0), nullable=False) #chqOwnerName = Column(Unicode(200), nullable=True) chqCust = Column(Integer, ForeignKey('customers.custId'), nullable=True) chqAccount = Column(Integer, ForeignKey('bankAccounts.accId'), nullable=True) # Transaction id is zero for non-invoice cheques. chqTransId = Column(Integer, ColumnDefault(0), ForeignKey('factors.Id')) chqNoteBookId = Column(Integer, ColumnDefault(0)) chqDesc = Column(Unicode(200), nullable=True) chqHistoryId = Column(Integer) # chqBillId = Column(Integer, ColumnDefault(0)) #Bill id is zero for temporary transactions. #chqOrder = Column(Integer , nullable=False) chqDelete = Column(Boolean) def __init__(self, chqAmount, chqWrtDate, chqDueDate, chqSerial, chqStatus, chqCust, chqAccount, chqTransId, chqNoteBookId, chqDesc, chqHistoryId=0, chqOrder=0, chqDelete=False, chqId=0): if chqId != 0: self.chqId = chqId self.chqAmount = chqAmount self.chqWrtDate = chqWrtDate self.chqDueDate = chqDueDate self.chqSerial = chqSerial self.chqStatus = chqStatus #self.chqOwnerName= chqOwnerName self.chqCust = chqCust self.chqAccount = chqAccount self.chqTransId = chqTransId self.chqNoteBookId = chqNoteBookId self.chqDesc = chqDesc self.chqHistoryId = 0 #self.chqBillId = chqBillId #self.chqOrder = chqOrder self.chqDelete = False
def map_scandb(metadata): """set up mapping of SQL metadata and classes returns two dictionaries, tables and classes each with entries tables: {tablename: table instance} classes: {tablename: table class} """ tables = metadata.tables classes = {} map_props = {} keyattrs = {} for cls in (Info, Messages, Config, Status, PV, PVType, MonitorValues, Macros, ExtraPVs, Commands, ScanData, ScanPositioners, ScanCounters, ScanDetectors, ScanDetectorConfig, ScanDefs, SlewScanPositioners, SlewScanStatus, Position, Position_PV, Instrument, Instrument_PV, Instrument_Precommands, Instrument_Postcommands): name = cls.__name__.lower() props = {} if name == 'commands': props = {'status': relationship(Status)} elif name == 'scandata': props = {'commands': relationship(Commands)} elif name == 'monitorvalues': props = {'pv': relationship(PV)} elif name == 'pvtype': props = {'pv': relationship(PV, backref='pvtype')} elif name == 'instrument': props = { 'pv': relationship(PV, backref='instrument', secondary=tables['instrument_pv']) } elif name == 'position': props = { 'instrument': relationship(Instrument, backref='position'), 'pv': relationship(Position_PV) } elif name == 'instrument_pv': props = { 'pv': relationship(PV), 'instrument': relationship(Instrument) } elif name == 'position_pv': props = {'pv': relationship(PV)} elif name == 'instrument_precommands': props = { 'instrument': relationship(Instrument, backref='precommands'), 'command': relationship(Commands) } elif name == 'instrument_postcommands': props = { 'instrument': relationship(Instrument, backref='postcommands'), 'command': relationship(Commands) } mapper(cls, tables[name], properties=props) classes[name] = cls map_props[name] = props keyattrs[name] = 'name' keyattrs['info'] = 'key' keyattrs['commands'] = 'command' keyattrs['position_pv'] = 'id' keyattrs['instrument_pv'] = 'id' keyattrs['monitovalues'] = 'id' keyattrs['messages'] = 'id' keyattrs['slewscanstatus'] = 'id' # set onupdate and default constraints for several datetime columns # note use of ColumnDefault to wrap onpudate/default func fnow = ColumnDefault(datetime.now) for tname in ('info', 'messages', 'commands', 'position', 'scandefs', 'scandata', 'slewscanstatus', 'scandetectorconfig', 'monitorvalues', 'commands'): tables[tname].columns['modify_time'].onupdate = fnow tables[tname].columns['modify_time'].default = fnow for tname, cname in (('info', 'create_time'), ('commands', 'request_time'), ('scandefs', 'last_used_time')): tables[tname].columns[cname].default = fnow return tables, classes, map_props, keyattrs
class DayDescriptor(UUIDMixin, TypeMixin, ElementBase): date = Column(DATE, ColumnDefault(datetime.datetime.now), nullable=False) day_descriptor_type = Column(VARCHAR, nullable=False)