def test_display():
feeds = get_table('Feeds')
display = get_table('Display')
session = get_session()
User = get_model('Users')
Feed = get_model('Feeds')
# Building a many-to-many relationship.
Feed.users = orm.relationship(
User,
secondary = display,
back_populates = 'feeds'
)
User.feeds = orm.relationship(
Feed,
secondary = display,
back_populates = 'users'
)
user_id = session.query(User.UserID).filter(User.Name == "hansolo").scalar()
display_count = session.query(User.feeds).filter(User.UserID == user_id).count
if not display_count():
q = sql.select([
sql.literal_column(str(user_id), type_=Integer).label('UserID'),
feeds.c.FeedID
])
ins = display.insert().from_select(['UserID', 'FeedID'], q)
with get_connection() as conn:
conn.execute(ins.prefix_with("OR IGNORE"))
assert(display_count())
def test_tags():
feeds = get_table('Feeds')
tags = get_table('Tags')
tags2feeds = get_table('Tags2Feeds')
session = get_session()
User = get_model('Users')
Feed = get_model('Feeds')
Tag = get_model('Tags')
# Building a many-to-many relationship.
Feed.tags = orm.relationship(
Tag,
secondary = tags2feeds,
back_populates = 'feeds'
)
Tag.feeds = orm.relationship(
Feed,
secondary = tags2feeds,
back_populates = 'tags'
)
user_id = session.query(User.UserID).filter(User.Name == "hansolo").scalar()
ins = tags.insert().values(UserID=user_id, Name="news")
with get_connection() as conn:
conn.execute(ins.prefix_with("OR IGNORE"))
q = sql.select([
tags.c.TagID,
feeds.c.FeedID
]).where(sql.and_(
feeds.c.Title == "The Atlantic",
tags.c.Name == "news"
))
ins = tags2feeds.insert().from_select([
tags2feeds.c.TagID,
tags2feeds.c.FeedID]
, q)
with get_connection() as conn:
conn.execute(ins.prefix_with("OR IGNORE"))
q = (session.query(Tag.feeds)
.filter(
Tag.Name == "news",
Tag.UserID == user_id,
Tag.feeds.any(Feed.Title.like("The Atlantic%"))
).count())
assert(q)
def execute(self):
# Listar los metadatos en funcion de filtros (caseName==1 and idCase==1)
Comandos.log.info('List metadata')
# Buscar en la base de datos en funcion del filtro
output = list()
try:
model.db.create_table(model.FileCase, safe=True)
if len(self.filter) == 0:
query = model.FileCase.select()
else:
filterStr = util.join_list(' ', self.filter)
query = model.FileCase.raw('select * from filecase where ' +
filterStr)
except:
Comandos.log.error('Database query error')
print('Database query error')
return
# Añadir registros obtenidos a una lista
output = [x for x in query]
if len(output) == 0:
print('No registers found')
else:
# Tabular la informacion en una tabla e imprimirla por pantalla
table = model.get_table(output)
print(table)
Comandos.log.info('List finished')
def test_xml_read():
session = get_session()
User = get_model('Users')
Feed = get_model('Feeds')
Tag = get_model('Tags')
tags2feeds = get_table('Tags2Feeds')
# Building a many-to-many relationship.
Feed.tags = orm.relationship(
Tag,
secondary = tags2feeds,
back_populates = 'feeds'
)
Tag.feeds = orm.relationship(
Feed,
secondary = tags2feeds,
back_populates = 'tags'
)
user_id = session.query(User.UserID).filter(User.Name == "hansolo").scalar()
for file_it in glob.glob(os.path.join("feeds.d", "*.xml")):
tag_name = file_it[len("feeds.d") + 1:-len(".xml")]
with open(file_it, 'r') as f:
read_xml(f, user_id, tag_name)
q = (session.query(Tag.feeds)
.filter(
Tag.UserID == user_id,
Tag.Name == tag_name
).count())
assert(q)
class Role(Base):
__table__ = get_table("Roles")
users = sqla.orm.relationship(
"User",
secondary=t_users2roles,
back_populates="roles",
)
class Tag(Base):
__table__ = get_table("Tags")
feeds = sqla.orm.relationship(
"Feed",
secondary=t_tags2feeds,
back_populates="tags",
)
def reset_magic(
user_id: int = None,
lang: schema_feeds.Language = None,
):
"""
Resets magic.
Args:
user_id: User ID.
lang: Language.
"""
feeds = model.get_table('Feeds')
items = model.get_table('Items')
magic = model.get_table('Magic')
q = magic.delete()
if user_id:
q = q.where(magic.c.UserID == user_id)
if lang:
if user_id:
q_cte = sqla.select(
items.c.ItemID,
).select_from(
items.join(feeds)
.join(magic)
).where(
feeds.c.Language == lang.name,
magic.c.UserID == user_id,
).distinct()
else:
q_cte = sqla.select(
items.c.ItemID,
).select_from(
items.join(feeds)
).where(
feeds.c.Language == lang.name,
).distinct()
q = q.where(magic.c.ItemID.in_(q_cte))
with model.get_connection() as conn:
conn.execute(q)
def get_model(name):
try:
Model = globals()[name]
except KeyError:
Model = type(
name,
(Base, ),
{'__table__': get_table(name)}
)
globals()[name] = Model
return Model
class Feed(Base):
__table__ = get_table("Feeds")
users = sqla.orm.relationship(
"User",
secondary=t_display,
back_populates="feeds",
)
tags = sqla.orm.relationship(
"Tag",
secondary=t_tags2feeds,
back_populates="feeds",
)
class Item(Base):
__table__ = get_table("Items")
feed = sqla.orm.relationship(
"Feed",
back_populates="items",
)
likes = sqla.orm.relationship(
"Like",
back_populates="item",
)
magic = sqla.orm.relationship(
"Magic",
back_populates="item",
)
def upsert_magic(
user: orm_users.User,
items: typing.List[orm_items.Item],
scores: typing.List[float],
):
assert len(items) == len(scores)
magic = model.get_table("Magic")
rows = []
for i in range(len(items)):
rows.append({
"UserID": user.UserID,
"ItemID": items[i].ItemID,
"Score": scores[i]
})
q = magic.insert()
q = q.prefix_with("OR IGNORE", dialect="sqlite")
with model.get_connection() as conn:
conn.execute(q, rows)
#!/usr/bin/env python3
import sqlalchemy as sqla
from model import get_table
from model import Base
t_users2roles = get_table("Users2Roles")
class User(Base):
__table__ = get_table("Users")
roles = sqla.orm.relationship(
"Role",
secondary=t_users2roles,
back_populates="users",
)
class Role(Base):
__table__ = get_table("Roles")
users = sqla.orm.relationship(
"User",
secondary=t_users2roles,
back_populates="roles",
)
from sanic.response import json
from sanic.views import HTTPMethodView
from model import get_table
from data_schema import get_schema
from ..core import restapi
User = get_table("User")
User_Schema = get_schema("user")
@restapi.register('/user/<uid:int>')
class UserAPI(HTTPMethodView):
async def get(self, request, uid):
try:
u = await request.app.db_manager.get(User, id=uid)
except User.DoesNotExist as dn:
return json({
"msg": "未找到用户",
}, status=401, ensure_ascii=False)
except Exception as e:
return json({
"msg": "执行错误",
}, status=500, ensure_ascii=False)
else:
if u:
return json(u.to_dict(), ensure_ascii=False)
else:
return json({
"msg": "未找到用户",
}, status=401, ensure_ascii=False)
#!/usr/bin/env python3
import sqlalchemy as sqla
from model import get_table
from model import Base
from model.orm.users import User
t_display = get_table("Display")
t_tags2feeds = get_table("Tags2Feeds")
class Feed(Base):
__table__ = get_table("Feeds")
users = sqla.orm.relationship(
"User",
secondary=t_display,
back_populates="feeds",
)
tags = sqla.orm.relationship(
"Tag",
secondary=t_tags2feeds,
back_populates="feeds",
)
User.feeds = sqla.orm.relationship(
"Feed",
secondary=t_display,
back_populates="users",
#!/usr/bin/env python3
import sqlalchemy as sqla
from model import get_table
from model import Base
from model.orm.feeds import Feed
from model.orm.users import User
t_like = get_table("Like")
t_magic = get_table("Magic")
class Item(Base):
__table__ = get_table("Items")
feed = sqla.orm.relationship(
"Feed",
back_populates="items",
)
likes = sqla.orm.relationship(
"Like",
back_populates="item",
)
magic = sqla.orm.relationship(
"Magic",
back_populates="item",
)
Feed.items = sqla.orm.relationship(
def hello_world():
l=model.get_table()
return render_template('hello.html',name=l)
