class TestData(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) data = Column(Text) created_at = Column(TIMESTAMP, server_default=func.sysdate()) updated_at = Column(TIMESTAMP, server_default=func.sysdate())
def get_cores_by_site(session): """ Gets the number of cores by site """ # SELECT sysdate, ps.site_name, VO, SUM(corecount) AS num_cores # FROM ATLAS_PANDA.jobsActive4 ja4, atlas_panda.panda_site ps # WHERE jobstatus in ('sent', 'starting', 'running', 'holding') # AND ps.panda_site_name = ja4.computingsite # GROUP BY sysdate, ps.site_name, VO try: for ts, site_name, core_count in session.query(func.sysdate(), PandaSite.site_name, func.sum(Jobsactive4.corecount)).\ filter(Jobsactive4.computingsite==PandaSite.panda_site_name).\ filter(Jobsactive4.jobstatus=='running').\ group_by(func.sysdate(), PandaSite.site_name).all(): try: site_stat = SiteStats(site_name=site_name, ts=ts, key='total_corepower', value=core_count) session.add(site_stat) session.commit() except exc.SQLAlchemyError: session.rollback() _logger.critical('get_cores_by_site excepted when adding new site stats (site_name: {0}, ts: {1}, key: corecount, value: {2}). Exception {3}'.format(site_name, ts, core_count, sys.exc_info())) except exc.SQLAlchemyError: session.rollback() _logger.critical('get_cores_by_site excepted querying the corecount by sites with {0}'.format(sys.exc_info()))
def fejlmeld_koordinat(self, sagsevent: Sagsevent, koordinat: Koordinat): """ Fejlmeld en allerede eksisterende koordinat. Hvis koordinaten er den eneste af sin slags på det tilknyttede punkt fejlmeldes og afregistreres den. Hvis koordinaten indgår i en tidsserie sker en af to ting: 1. Hvis koordinaten forekommer midt i en tidsserie fejlmeldes den uden videre. 2. Hvis koordinaten er den seneste i tidsserien fejlmeldes den, den foregående koordinat fejlmeldes og en ny koordinat indsættes med den foregåendes værdier. Denne fremgangsmåde sikrer at der er en aktuel og gyldig koordinat, samt at den samme koordinat ikke fremtræder to gange i en tidsserie. """ punkt = koordinat.punkt srid = koordinat.srid ny_koordinat = None if len(punkt.koordinater) == 1: self._luk_fikspunkregisterobjekt(koordinat, sagsevent, commit=False) # Er koordinaten den sidste i tidsserien? if koordinat.registreringtil is None: # Find seneste ikke-fejlmeldte koordinat så den # bruges som den seneste gyldige koordinat for forrige_koordinat in reversed(punkt.koordinater[0:-1]): if forrige_koordinat.srid != srid: continue if not forrige_koordinat.fejlmeldt: break if not forrige_koordinat.fejlmeldt: ny_koordinat = Koordinat( punktid=forrige_koordinat.punktid, sridid=forrige_koordinat.sridid, x=forrige_koordinat.x, y=forrige_koordinat.y, z=forrige_koordinat.z, t=forrige_koordinat.t, sx=forrige_koordinat.sx, sy=forrige_koordinat.sy, sz=forrige_koordinat.sz, transformeret=forrige_koordinat.transformeret, artskode=forrige_koordinat.artskode, _registreringfra=func.sysdate(), ) sagsevent.koordinater = [ny_koordinat] self.session.add(sagsevent) koordinat.fejlmeldt = True if ny_koordinat: koordinat._registreringtil = ny_koordinat._registreringfra self.session.add(koordinat) self.session.commit()
class FugleOverBought(Base): __tablename__ = 'fugle_over_bought' symbol = Column(String(16), nullable=False, primary_key=True) date = Column(Date, nullable=False, primary_key=True, server_default=func.sysdate()) quantity = Column(Integer, nullable=False) def __repr__(self): return str([getattr(self, c.name, None) for c in self.__table__.c])
class DayGreeting(Base): __tablename__ = 'day_greetings' id = Column(Integer, primary_key=True) count = Column(Integer) discord_user_id = Column(Integer) guild_id = Column(Integer, default=543793263942041630) type = Column(Text) latest = Column(TIMESTAMP, server_default=func.sysdate(), onupdate=func.now())
def test_no_paren_fns(self): for fn, expected in [ (func.uid(), "uid"), (func.UID(), "UID"), (func.sysdate(), "sysdate"), (func.row_number(), "row_number()"), (func.rank(), "rank()"), (func.now(), "CURRENT_TIMESTAMP"), (func.current_timestamp(), "CURRENT_TIMESTAMP"), (func.user(), "USER"), ]: self.assert_compile(fn, expected)
class ReunionTrend(Base): __tablename__ = 'reunion_trend' symbol = Column(String(16), nullable=False, primary_key=True) date = Column(Date, nullable=False, primary_key=True, server_default=func.sysdate()) popularity = Column(Integer, nullable=False) def __repr__(self): return str([getattr(self, c.name, None) for c in self.__table__.c])
class testimonals(db.Model): id = db.Column('id', db.Integer, primary_key=True) username = db.Column('username', db.String(30), ForeignKey("users.username")) comment = db.Column('comment', db.String(250)) rating = db.Column('rating', db.Integer) c_date = db.Column('c_date', db.DateTime, default=func.sysdate()) def __init__(self, username, comment, rating, c_date): self.username = username self.comment = comment self.rating = rating self.c_date = c_date def as_dict(self): return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class RegisteringFraObjekt(DeclarativeBase): # SQLALchemy knows abstract classes do not map to a table. # If class is not declared abstract then SQLAlchemy whines about missing table declaration. __abstract__ = True objectid = Column(Integer, primary_key=True) _registreringfra = Column( "registreringfra", DateTime(timezone=True), nullable=False, default=func.sysdate(), ) @property def registreringfra(self): return self._registreringfra
class notifications(db.Model): id = db.Column('id', db.Integer, primary_key=True) template_name = db.Column('template_name', db.String(40), ForeignKey("templates.name")) sender = db.Column('sender', db.String(30)) cc = db.Column('cc', db.String(30)) bcc = db.Column('bcc', db.String(30)) subject = db.Column('subject', db.String(30)) created_date = db.Column('created_date', db.DateTime, default=func.sysdate()) status = db.Column('status', db.String(10)) def __init__(self, template_name, sender, cc, bcc, subject, created_date, status): self.template_name = template_name self.sender = sender self.cc = cc self.bcc = bcc self.subject = subject self.status = status
def log(methods=['GET', 'POST']): # session["game_id"] = request.args.get("game_id", "default") # test = request.args.get("test", "no set yo") print(session) data = { "game_id" : session['game_id'], "event_key" : request.args.get("event_key"), "event_value": request.args.get("event_value"), "player" : request.args.get("player"), "created" : func.sysdate() } # print("this far") sql = """ INSERT INTO game_events (`game_id`, `player`, `event_key`, `event_value`, `created`) VALUES('%s', '%s', '%s', '%s', NOW(6)) """ % (data['game_id'], data['player'],data['event_key'],data['event_value']) # print("this far 2") db = get_dbh() # print("this far 3") sql = text(sql) # print("this far 4") result = db.execute(sql) # print("this far 5") print("result from database", result) # conn.merge(orm.Event( # game_id = session['game_id'], # player = request.args.get("player"), # event_key = request.args.get("event_key"), # event_value = request.args.get("event_value"), # created = func.now() # )) # conn.commit() db.close() return str(session['game_id'])
def listener(thissession, flush_context, instances): for obj in thissession.deleted: if isinstance(obj, RegisteringTidObjekt): obj._registreringtil = func.sysdate() thissession.add(obj)
def test_sysdate(self): self.assert_compile(func.sysdate(), "SYSDATE()")
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql', 'maxdb') is_oracle = testing.against('oracle') # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([ func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today') ])) assert isinstance( ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('maxdb'): def2 = sa.text("curdate") elif testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table( 'default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl')) t.create()
class NewMemberJoin(Base): __tablename__ = 'new_member_joins' id = Column(Integer, primary_key=True) date = Column(TIMESTAMP, server_default=func.sysdate()) guild_id = Column(Integer)
def test_sysdate(self): d = testing.db.scalar(func.sysdate()) assert isinstance(d, datetime.datetime)
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql') is_oracle = testing.against('oracle') class MyClass(object): @classmethod def gen_default(cls, ctx): return "hi" # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar(sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today')])) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table('default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl'), # python method w/ context Column('col10', String(20), default=MyClass.gen_default) ) t.create()
def test_sysdate(self, connection): d = connection.execute(func.sysdate()).scalar() assert isinstance(d, datetime.datetime)
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {"x": 50} def mydefault(): default_generator["x"] += 1 return default_generator["x"] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text("13")])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text("12")])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against("postgresql", "mssql", "maxdb") is_oracle = testing.against("oracle") # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label("today")]) ) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length("abcdef")], bind=db).scalar() f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length("abcdef")], bind=db).scalar() f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against("maxdb"): def2 = sa.text("curdate") elif testing.against("mssql"): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len("abcdef") f2 = len("abcdefghijk") def1 = def2 = "3" ts = 3 deftype = Integer t = Table( "default_test1", metadata, # python function Column("col1", Integer, primary_key=True, default=mydefault), # python literal Column("col2", String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column("col3", Integer, default=func.length("abcdef"), onupdate=func.length("abcdefghijk")), # SQL-side default from sql expression Column("col4", deftype, server_default=def1), # SQL-side default from literal expression Column("col5", deftype, server_default=def2), # preexecute + update timestamp Column("col6", sa.Date, default=currenttime, onupdate=currenttime), Column("boolcol1", sa.Boolean, default=True), Column("boolcol2", sa.Boolean, default=False), # python function which uses ExecutionContext Column("col7", Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column("col8", sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column("col9", String(20), default="py", server_default="ddl"), ) t.create()