def save_comments(self, comments): session = Session() comment_ids = {comment['id'] for comment in comments} comments_in_db = session.query(Comment.id).filter( Comment.id.in_(comment_ids)).all() comment_ids_in_db = {comment.id for comment in comments_in_db} if comment_ids_in_db is None: comment_ids_in_db = [] comment_ids_in_db = set(comment_ids_in_db) # 添加新的评论 need_add_comment_ids = comment_ids - comment_ids_in_db need_add_comments = list( filter(lambda c: c['id'] in need_add_comment_ids, comments)) session.add_all([Comment(**comment) for comment in need_add_comments]) # 更新旧的评论 need_update_comments = list( filter( lambda c: c['id'] in comment_ids_in_db and (c['isDelete'] is True or c['isUpDelete'] is True), comments)) for comment in need_update_comments: db_comment = session.query(Comment).filter( Comment.id == comment.get('id')) db_comment.isDelete = comment.get('isDelete') db_comment.isUpDelete = comment.get('isUpDelete') session.commit() session.commit() session.close()
def save_contents(self, contents): session = Session() content_ids = {content['id'] for content in contents} contents_in_db = session.query(Content.id).filter( Content.id.in_(content_ids)).all() content_ids_in_db = {content.id for content in contents_in_db} need_to_add_content_ids = content_ids - content_ids_in_db need_to_add_contents = list( filter(lambda a: a['id'] in need_to_add_content_ids, contents)) observer_info_logger.debug( '添加数据库content, contentIds={content_ids}'.format( content_ids=need_to_add_content_ids)) session.add_all( [Content(**content) for content in need_to_add_contents]) session.commit() need_to_update_contents = list( filter(lambda a: a['id'] in content_ids_in_db, contents)) for content in need_to_update_contents: observer_info_logger.debug( '更新数据库content, content={content}'.format(content=content)) observer_info_logger.debug( 'content_id, content={content}'.format(content=content)) db_content = session.query(Content).filter( Content.id == content.get('id')).one() db_content.commentNum = content['commentNum'] db_content.viewNum = content['viewNum'] session.commit() # session.query(Content).filter(Content.id == content.get('id')).update({ # 'commentNum': content['commentNum'], # 'viewNum': content['viewNum'] # }) session.close()
def saveComments(comments): session = Session() commentIds = {comment['id'] for comment in comments} commentsInDB = session.query(Comment.id).filter( Comment.id.in_(commentIds)).all() commentIdsInDB = {comment.id for comment in commentsInDB} if commentIdsInDB is None: commentIdsInDB = [] commentIdsInDB = set(commentIdsInDB) # 添加新的评论 needAddCommentIds = commentIds - commentIdsInDB needAddComments = list( filter(lambda c: c['id'] in needAddCommentIds, comments)) session.add_all([Comment(**comment) for comment in needAddComments]) session.commit() #更新旧的评论 needUpdateComments = list( filter( lambda c: c['id'] in commentIdsInDB and (c['isDelete'] is True or c['isUpDelete'] is True), comments)) for comment in needUpdateComments: session.query(Comment).filter(Comment.id == comment.get('id')).update({ 'isDelete': comment.get('isDelete'), 'isUpDelete': comment.get('isUpDelete') }) session.commit() session.close()
class TableMappingsTestCase(unittest.TestCase): def setUp(self): engine = bind_session_engine("sqlite:///:memory:", echo=False) tables.metadata.create_all(engine) self.session = Session() self.room = Room("living-room") self.item = Item("TV", self.room, "80", "plasma screen", True) self.session.add_all([self.room, self.item]) self.session.commit() def test_room_relations(self): r = self.session.query(Room).one() self.assertTrue(isinstance(r, Room)) self.assertTrue(r.name, "living-room") def test_item_relations(self): i = self.session.query(Item).one() self.assertTrue(isinstance(i, Item)) self.assertTrue(i.name, "TV") self.assertTrue(i.room, self.room) self.assertTrue(i.weight, "80") self.assertTrue(i.description, "plasma screen") self.assertTrue(i.is_fragile, True)
class EpisodeListing(object): semaphore = None loop = None def __init__(self): self.loop = asyncio.get_event_loop() if any(map(lambda el: el is None, [self.semaphore])): raise AttributeError("Initialize Class-wide variables!") self.session = Session() async def start(self): await self.get_listing() async def get_listing(self): async with self.semaphore: async with aiohttp.ClientSession() as session: async with session.get( get_url("category=Episodes", limit=1500) ) as resp: data = await resp.json() self.targets = [ Episode(entry["id"], entry["title"], entry["url"]) for entry in data["items"] if "Season_" not in entry["title"] and "Unproduced_episodes" not in entry["title"] ] tasks = [target.start() for target in self.targets] episodes = await asyncio.gather(*tasks) self.session.add_all([episode for episode in episodes if episode]) self.session.commit() return
class CharacterListing(object): semaphore = None loop = None def __init__(self): self.loop = asyncio.get_event_loop() if any(map(lambda el: el is None, [self.semaphore])): raise AttributeError("Initialize Class-wide variables!") self.session = Session() async def start(self): await self.get_listing() async def get_listing(self): async with self.semaphore: async with aiohttp.ClientSession() as session: async with session.get( get_url("category=Recurring_characters", limit=1500) ) as resp: data = await resp.json() self.targets = [ Character(entry["id"], entry["title"], entry["url"]) for entry in data["items"] ] tasks = [target.start() for target in self.targets] chars = await asyncio.gather(*tasks) self.session.add_all([char for char in chars if char]) self.session.commit() return
def cached_jurisdictions(): if not _cached_jurisdictions: s = Session() if len(_query_db_cache(s)) == 0: all = [] for next in import_countries(): all.append(Jurisdiction(name=next["name"], code=next["code"])) all.append(Jurisdiction(name="Unknown", code="XX")) s.add_all(all) s.commit() _load_db_cache(s) if debug: print(_cached_jurisdictions) s.close() return _cached_jurisdictions
def saveContents(contents): session = Session() contentIds = {content['id'] for content in contents} contentsInDB = session.query(Content.id).filter( Content.id.in_(contentIds)).all() contentIdsInDB = {content.id for content in contentsInDB} needToSaveContentIds = contentIds - contentIdsInDB needToSabeContents = list( filter(lambda a: a['id'] in needToSaveContentIds, contents)) session.add_all([Content(**content) for content in needToSabeContents]) session.commit() session.close()
def register_new_user(session: db.Session, user_id, invited_by=None) -> User: new_chat_state = ChatState(id=user_id, node=0, user_id=user_id) new_user = User(id=user_id, permissions=PermissionsLevels.USER, last_activity=datetime.utcnow(), chat_state=new_chat_state) session.add_all([new_chat_state, new_user]) if invited_by is not None: session.commit() new_referral_link = ReferralLink(user_id=user_id, invited_by_id=invited_by) inviter = session.query(User).filter_by(id=invited_by).one_or_none() if inviter is not None: inviter.referral_cash += settings.REFERRAL_BONUS * 100 inviter.cash += settings.REFERRAL_BONUS * 100 session.add(new_referral_link) session.commit() return new_user
class MovieListing(object): semaphore = None loop = None eicontinue = None def __init__(self): self.loop = asyncio.get_event_loop() if any(map(lambda el: el is None, [self.semaphore])): raise AttributeError("Initialize Class-wide variables!") self.session = Session() async def start(self): await self.get_movies() def get_movie_listing_url(self): # https://en.wikipedia.org/w/api.php?action=query&list=embeddedin&einamespace=0&eilimit=max&eititle=Template:Infobox_film&format=json&eicontinue=0|76349 return f"https://en.wikipedia.org/w/api.php?action=query&list=embeddedin&einamespace=0&eilimit=max&eititle=Template:Infobox_film&format=json{f'&eicontinue={self.eicontinue}' if self.eicontinue else ''}" async def get_movies(self, eicontinue=None): self.eicontinue = eicontinue async with self.semaphore: async with aiohttp.ClientSession() as session: async with session.get(self.get_movie_listing_url()) as resp: data = json.loads(await resp.text()) self.data = data self.targets = [ Movie(entry["pageid"], entry["title"]) for entry in self.data["query"]["embeddedin"] ] tasks = [target.start() for target in self.targets] movies = await asyncio.gather(*tasks) self.session.add_all(movies) self.session.commit() try: if self.data["continue"]["eicontinue"]: await self.get_movies(self.data["continue"]["eicontinue"]) except KeyError as err: print(f"Failed to find continue key {err} data: \n {data} ") return
def func(self, character, args, text): direction = args.direction title = args.title if not title: self.exit(message='Room titles cannot be blank.') d = character.location.match_direction(direction) if d is None: self.exit(message=f'Invalid exit: {direction}') direction = d.name if Exit.query(name=direction, location_id=character.location_id).count(): self.exit(message='There is already an exit in that direction.') x, y, z = d.coordinates_from(character.location.coordinates) r = Room.query(x=x, y=y, z=z).first() if r is None: r = Room(name=title, x=x, y=y, z=z, zone_id=character.location.zone_id) s.add(r) s.commit() msg = f'Created room {r}.' else: msg = f'Linking to room {r}.' character.notify(msg) x = Exit(name=direction, location_id=character.location_id, target_id=r.id) y = Exit(name=d.opposite.name, location_id=r.id, target_id=character.location.id) s.add_all([x, y]) s.commit() for thing in [x, y]: character.notify(f'Created exit {thing} from {thing.location} to ' f'{thing.target}.')
class AppTestCase(unittest.TestCase): def setUp(self): app.config["TESTING"] = True self.app = app.test_client() engine = bind_session_engine("sqlite:///:memory:") metadata = tables.metadata metadata.create_all(bind=engine) self.session = Session() # Fixture data self.room = Room("living-room") self.item = Item("TV", self.room, "80", "plasma screen", True) self.session.add_all([self.room, self.item]) self.session.commit() def tearDown(self): Session.remove() def test_routing_error(self): r = self.app.get("/this/api/does/not/exist/yet") self.assertEqual(r.status_code, 404) self.assertEqual( json.loads(r.data), { "error": ("The requested URL was not found on the server. " " If you entered the URL manually please check your " "spelling and try again.") } ) def test_invalid_json_fails(self): r = self.app.post( "/rooms", content_type="application/json", data="THIS IS NOT JSON" ) self.assertEqual(r.status_code, 400) self.assertEqual( json.loads(r.data), { "error": "The browser (or proxy) sent a request that this " "server could not understand." } ) def test_add_room_already_exists(self): r = self.app.post( "/rooms", content_type="application/json", data=json.dumps({"name": "living-room"}) ) self.assertEqual(r.status_code, 409) self.assertEqual(json.loads(r.data), {"error": "Already exists"}) def test_add_room_success(self): r = self.app.post( "/rooms", content_type="application/json", data=json.dumps({"name": "kitchen"}) ) self.assertEqual(r.status_code, 201) self.assertEqual(json.loads(r.data), {"ref": "/rooms/kitchen"}) # Check room has been added Session.remove() new_session = Session() self.assertEqual(len(new_session.query(Room).all()), 2) def test_remove_room_doesnt_exist(self): r = self.app.delete( "/rooms", content_type="application/json", data=json.dumps({"name": "doesnt-exist"}) ) self.assertEqual(r.status_code, 404) self.assertEqual(json.loads(r.data), {"error": "Not found"}) def test_remove_room_success(self): r = self.app.delete( "/rooms", content_type="application/json", data=json.dumps({"name": "living-room"}) ) self.assertEqual(r.status_code, 201) self.assertEqual(json.loads(r.data), {}) # Check room and related items have been deleted Session.remove() new_session = Session() self.assertEqual(len(new_session.query(Room).all()), 0) self.assertEqual(len(new_session.query(Item).all()), 0) def test_get_item(self): r = self.app.get("/rooms/living-room/items/TV") self.assertEqual(r.status_code, 200) self.assertEqual( json.loads(r.data), { "description": "plasma screen", "is_fragile": True, "name": "TV", "weight": 80 } ) def test_add_item_room_doesnt_exist(self): r = self.app.put( "/rooms/non-existant/items/fridge", content_type="application/json", data=json.dumps({"name": "doesnt-exist"}) ) self.assertEqual(r.status_code, 404) self.assertEqual(json.loads(r.data), {"error": "Not found"}) def test_add_item_success(self): r = self.app.put( "/rooms/living-room/items/couch", content_type="application/json", data=json.dumps( { "weight": 65, "description": "Something to sit on", "is_fragile": False } ) ) self.assertEqual(r.status_code, 201) self.assertEqual( json.loads(r.data), {"ref": "/rooms/living-room/items/couch"} ) # Check item has been added Session.remove() new_session = Session() item = new_session.query(Item).filter_by(name="couch").one() self.assertEqual(item.description, "Something to sit on") def test_update_item_success(self): r = self.app.put( "/rooms/living-room/items/TV", content_type="application/json", data=json.dumps( { "weight": 120, "description": "Cathode tv", "is_fragile": False } ) ) self.assertEqual(r.status_code, 201) self.assertEqual( json.loads(r.data), {"ref": "/rooms/living-room/items/TV"} ) # Check item has been updated Session.remove() new_session = Session() items = new_session.query(Item).all() self.assertEqual(len(items), 1) self.assertEqual(items[0].description, "Cathode tv") def test_remove_item_doesnt_exist(self): r = self.app.delete("/rooms/living-room/items/non-existant") self.assertEqual(r.status_code, 404) self.assertEqual(json.loads(r.data), {"error": "Not found"}) def test_remove_item_success(self): r = self.app.delete("/rooms/living-room/items/TV") self.assertEqual(r.status_code, 201) self.assertEqual(json.loads(r.data), {}) # Check room is still there but item has been deleted Session.remove() new_session = Session() self.assertEqual(len(new_session.query(Room).all()), 1) self.assertEqual(len(new_session.query(Item).all()), 0) def test_get_items(self): r = self.app.get("/rooms") self.assertEqual(r.status_code, 200) self.assertEqual( json.loads(r.data), { "living-room": [{ "name": "TV", "description": "plasma screen", "weight": 80, "is_fragile": True }] } ) def test_generate_manifest(self): # Add more fixture data table = Item("table", self.room, 40, "wood", False) couch = Item("couch", self.room, 200, "leather", False) self.session.add_all([table, couch]) kitchen = Room("kitchen") fridge = Item("fridge", kitchen, 100, "Samsung fridge", True) cutlery = Item("cutlery", kitchen, 10, "Ikea", False) plates = Item("plates", kitchen, 15, "Ikea", True) self.session.add_all([kitchen, fridge, cutlery, plates]) self.session.commit() r = self.app.get("/manifest") self.assertEqual(r.status_code, 200) self.assertEqual( json.loads(r.data), { "two_heaviest_items": { "living-room": [ { "name": "couch", "description": "leather", "weight": 200, "is_fragile": False }, { "name": "TV", "description": "plasma screen", "weight": 80, "is_fragile": True } ], "kitchen": [ { "name": "fridge", "description": "Samsung fridge", "weight": 100, "is_fragile": True }, { "name": "plates", "description": "Ikea", "weight": 15, "is_fragile": True } ] }, "fragile_items": { "living-room": [ { "name": "TV", "description": "plasma screen", "weight": 80, "is_fragile": True } ], "kitchen": [ { "name": "fridge", "description": "Samsung fridge", "weight": 100, "is_fragile": True }, { "name": "plates", "description": "Ikea", "weight": 15, "is_fragile": True } ] }, "non_fragile_items": { "living-room": [ { "name": "couch", "description": "leather", "weight": 200, "is_fragile": False }, { "name": "table", "description": "wood", "weight": 40, "is_fragile": False } ], "kitchen": [ { "name": "cutlery", "description": "Ikea", "weight": 10, "is_fragile": False } ] } } )
from db import User, Group, init_db, destroy_db, Session from logger import Logger from logging import getLogger from sqlalchemy.orm import selectinload Logger() logger = getLogger("src." + __name__) try: init_db() user = User(name="ika", email="*****@*****.**") group = Group(group_name="splatoon", user_name="ika") Session.add_all([user, group]) Session.commit() # SELECT # users.id AS users_id, users.name AS users_name, users.email AS users_email # FROM # users # JOIN # groups ON users.name = groups.user_name # WHERE # users.name = groups.user_name logger.info("----test1----") query = Session.query(User).join(Group).filter(User.name == Group.user_name).all() logger.info(query) # [<User 'ika'>] logger.info(type(query)) # <class 'list'> # SELECT
tuvtermin = date(2005, 06, 01), kaufdatum = date(2002, 05, 01), modell_id = 4) a10 = Auto(kennzeichen = "RV BQ 592", kmstand = 66000, tuvtermin = date(2005, 06, 01), kaufdatum = date(2002, 05, 01), modell_id = 4) a11 = Auto(kennzeichen = "RV BQ 593", kmstand = 64500, tuvtermin = date(2005, 06, 01), kaufdatum = date(2002, 05, 01), modell_id = 4) a12 = Auto(kennzeichen = "RV C 45", kmstand = 150000, tuvtermin = date(2005, 04, 01), kaufdatum = date(2002, 05, 01), modell_id = 6) a13 = Auto(kennzeichen = "RV MM 999", kmstand = 16000, tuvtermin = date(2005, 04, 01), kaufdatum = date(2002, 05, 01), modell_id = 5) a14 = Auto(kennzeichen = "RV PF 23", kmstand = 25000, tuvtermin = date(2005, 04, 01), kaufdatum = date(2002, 05, 01), modell_id = 7) print("Inserting sample values...") session = Session() session.add_all([ k1, k2, k3, k4, k5, k6, k7, aa1, aa2, aa3, aa4, aa5, aa6, aa7, am1, am2, am3, am4, am5, am6, am7, au1, au2, au3, au4, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14 ]) session.commit() exit()