def create_data(num, model_name):
"""Создает num записей для model модели."""
i = 0
model = Session.query(metadata.tables[model_name])
while i < num:
object_data = {}
for column in model.column_descriptions:
if isinstance(column['type'], Integer) and column['name'] != 'id':
if fk := column['expr'].foreign_keys:
t_model, t_pk = next(iter(fk)).target_fullname.split('.')
t_obj = Session.query(metadata.tables[t_model]).first()
object_data[column['name']] = getattr(t_obj, t_pk)
else:
object_data[column['name']] = random.randint
if isinstance(column['type'], String):
if column['name'] == 'email':
object_data[column['name']] = fake.email()
else:
object_data[column['name']] = fake.name()
if isinstance(column['type'], Date):
object_data[column['name']] = fake.date()
if isinstance(column['type'], DateTime):
object_data[column['name']] = fake.date_time()
insert_query = metadata.tables[model_name].insert().values(
**object_data)
Session.execute(insert_query)
Session.commit()
i += 1
def registerHouse():
get_lang(request)
#generate form data
form = RegisterForm(request.form)
if request.method == 'POST':
#store form data
address = form.address.data
city = form.city.data
state = form.state.data
zip = form.zip.data
#generate lat/long from address
results = Geocoder.geocode(address)
result = results[0].coordinates
lat = result[0]
long = result[1]
#create new db session
sess = Session()
#get user id for database relations
account_id = session['id']
#create new home: A new home is identified by street. city, zip, state, lat/long
new_home = Home(account_id=account_id,
street_address=address,
city=city,
state=state,
zip_code=zip,
latitude=lat,
longitude=long)
#Does the address already exist
match = sess.query(Home.street_address)\
.filter(Home.street_address==address)\
.all()
#If the address is unique the home is added
if not match:
print(f'Added: {new_home}')
#add entry to database for home
sess.add(new_home)
#save changes
sess.commit()
#the address is not unique
else:
#error message
flash("Address already registered")
#return to registration page
return render_template('registerHouse.html', form=form)
#close connection
sess.close()
return redirect(url_for('dashboard'))
return render_template('registerHouse.html', form=form)
def wrapper(bot, update, *args, **kwargs):
s = Session()
if not Chat.q.get(update.message.chat.id):
chat = Chat(id=update.message.chat.id,
is_private=update.message.chat.type == 'private')
s.add(chat)
s.commit()
chat = Chat.q.get(update.message.chat.id) # type: Chat
return fn(bot, update, *args, chat, **kwargs)
def wrapper(*args, **kwargs):
s = Session()
try:
r = fn(*args, s, **kwargs)
s.commit()
return r
except Exception:
s.rollback()
raise
finally:
s.close()
def create_book(**data):
try:
sess = Session()
book = Book(name=data['name'],
author=data['author'],
description=data['description'])
sess.add(book)
sess.commit()
result = True
except Exception:
result = False
return result
def save(headers_and_options, row_header, schedules_data):
session = Session()
for row_index, row in enumerate(schedules_data):
for col_index, col in enumerate(row):
session.merge(
Archive(class_id=row_header[row_index]['id'],
major_id=headers_and_options[col_index]['id'],
info=json.dumps(col, ensure_ascii=False)))
session.commit()
session.close()
def delete_user(user_id):
try:
result = False
sess = Session()
user = sess.query(User).get(user_id)
if user:
sess.delete(user)
sess.commit()
result = True
except Exception:
result = False
return result
def delete_book(book_id):
try:
result = False
sess = Session()
book = sess.query(Book).get(book_id)
if book:
sess.delete(book)
sess.commit()
result = True
except Exception:
result = False
return result
def post_new_class(new_class_name):
session = Session()
new_class_obj = ClassName(class_name=new_class_name)
session.add(new_class_obj)
session.commit()
archives = session.query(Archive).filter_by(class_id=new_class_obj.id)
[session.delete(archive) for archive in archives]
session.commit()
session.close()
response = create_schedules_data()
return response
def save_news(self, news):
session = Session()
q = session.query(News)
for n in news:
published = n['published_parsed']
date = datetime.date(published[0], published[1], published[2])
link = n['link']
title = n['title']
existing_news = q.filter(News.link == link).all()
if not existing_news:
news = News(date=date, title=title, summary=n['summary'],
link=link)
session.add(news)
session.commit()
def ramp_up():
_s = Session()
for t in ['большое спасибо', 'большой рахмет']:
t = ThanksADay(text=t)
_s.add(t)
for id, rate in [(398, 1), (643, 5), (840, 360)]:
e = Exchange(id=id, rate=rate)
_s.add(e)
a = DonateAuthor(name='Неизвестный Человек') # он должен быть #1
_s.add(a)
_s.commit()
_s.close()
def update_book(book_id, **data):
try:
sess = Session()
book = sess.query(Book).get(book_id)
if not book:
result = False
else:
book.name = data['name']
book.author = data['author']
book.description = data['description']
sess.commit()
result = True
except Exception as err:
result = False
return result
async def add_order(request):
"""Метод добавления заказа пользователем."""
user = request.app['user']
request_body = await request.json()
order_data = {'user_id': user.id, 'created_at': datetime.now().isoformat()}
insert_query = metadata.tables['orders'].insert().values(**order_data)
result = Session.execute(insert_query)
Session.commit()
[
item.update({'order_id': result.inserted_primary_key[0]})
for item in request_body
]
Session.execute(metadata.tables['orders_goods'].insert(), request_body)
Session.commit()
return web.Response(status=HTTPStatus.CREATED)
def create_user(**data):
try:
# password = sha512(data['passwd'].encode('utf-8')).hexdigest()
password = data['passwd']
sess = Session()
user = User(name=data['name'],
password=password,
active=True,
role=data['role'])
sess.add(user)
sess.commit()
user_id = user.id
result = True
except Exception:
user_id = None
result = False
return result, user_id
def update_user(user_id, **data):
try:
sess = Session()
print('****', data['role'])
# password = sha512(data['password'].encode('utf-8')).hexdigest()
user = sess.query(User).get(user_id)
if not user:
result = False
else:
user.role = data['role']
# user.passwd = password
# user.active = data['active']
sess.commit()
result = True
except Exception as err:
result = False
return result
def cron_update_db():
session = Session()
items = session.query(Item).all()
for item in items:
url = item.url
response = get_amazon_price(url)
currentPrice = response.get("price", None)
if currentPrice:
if currentPrice <= item.price:
transactions = session.query(Transaction) \
.filter(Transaction.requested_price >= currentPrice) \
.all()
send_emails(transactions)
item.price = currentPrice
session.commit()
def main():
# create a new session before executing inserts
session = Session()
# days grab in 24 hour chunks
for day in range(1, 32):
# get weather for month of march
march = datetime(2018, 3, day).timestamp()
# print(march)
lat = 33.699657
lon = -86.635033
weather = get_weather(march, lat, lon)
weather = filter_weather(weather)
# print(weather)
# select on lat, lon, time
# make a new Forecast
for w in weather:
# print(**w)
new_forecast = Forecast(latitude=lat, longitude=lon, **w)
# match on lat, lon, time
match = session.query(Forecast)\
.filter(Forecast.latitude==lat)\
.filter(Forecast.longitude==lon)\
.filter(Forecast.time==w['time'])\
.all()
# if there were no matching records, insert
if not match:
print(f'Added: {new_forecast}')
session.add(new_forecast)
# save changes
session.commit()
print('DONE')
def clear_data(model_name):
"""Полная очистка БД."""
insert_query = metadata.tables[model_name].delete()
Session.execute(insert_query)
Session.commit()
