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)