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()
