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)
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 test_sysdate(self):
d = testing.db.scalar(func.sysdate())
assert isinstance(d, datetime.datetime)
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')
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()
