def extract_models():
blocksize = 10000
session = Session()
count = session.query(func.count(Product.id)).filter_by(level=MODEL_BEST).scalar()
models = set([])
for i in range(count/blocksize):
if i<30:
continue
#if i>=30:
# break
print i
ms = set(( p.model.upper() for p in session.query(Product).filter_by(level=MODEL_BEST).offset(i*blocksize).limit(blocksize) ))
models |= ms
print 'got exitings'
exist_models = set((m.model for m in session.query(Model)))
print 'set minus'
models -= exist_models
print 'inserting new', len(models)
engine.execute("insert into model (model, updated_at) values (%s, %s)", [(m, datetime.utcnow()) for m in models])
def clean_db():
clean_file = open('/Users/ivanalchuk/Desktop/ap_lab/sql/clean_db.sql')
clean_sql = sqlalchemy.text(clean_file.read())
engine.execute(clean_sql)
clean_file.close()
def tearDownClass(cls):
engine.execute('delete from saledetails; delete from sale; delete from\
products_price_history; delete from product; delete from products_masterlist ;'
)
engine.execute("delete from services; delete from drugstore;")
def setupClass(self):
response = self.open_with_auth('/api/v1.1/daily_report/', 'GET')
assert response.status_code == 500
engine.execute("delete from operation_logs;")
engine.execute(
"insert into drugstore(id, name) values (1, 'default');")
engine.execute(
"insert into drugstore(id, name) values (2, 'Store 2');")
engine.execute(
"insert into sale(date) values((CURRENT_DATE - 28) + interval '1 hour');"
)
engine.execute(
"insert into saledetails(idsale, idproduct, productprice, units, storeid)\
select MAX(sale.id), '0001', 50.50, 2, 1 from sale;")
engine.execute(
"update product set units=units-2 where storeid = 1 and barcode='0001'"
)
engine.execute(
"insert into sale(date) values((CURRENT_DATE - 15) + interval '1 hour');"
)
engine.execute(
"insert into saledetails(idsale, idproduct, productprice, units, storeid)\
select MAX(sale.id), '0003', 1, 2, 2 from sale;")
engine.execute(
"update product set units=units-2 where storeid = 1 and barcode='0003'"
)
self.add_product_1_1(self.json_prod_1)
self.add_product_1_1(self.json_prod_2)
self.add_product_1_1(self.json_prod_3)
self.add_service_1_1(self.json_serv_1)
self.add_service_1_1(self.json_serv_2)
self.add_service_1_1(self.json_serv_3)
self.setUpSales()
unittest.TestCase.setUp(self)
def create_task(self):
self.task_id = self.site + "-" + time.strftime("%Y%m%d%H%M%S")
stmt = Task.insert().values({
"task_id": self.task_id
}).return_defaults()
engine.execute(stmt)
def add_editor():
insert_file = open(
'E:\\Лапітєх\\aplied_programming\\flaskProject\\add_editor.sql')
insert = sqlalchemy.text(insert_file.read())
engine.execute(insert)
insert_file.close()
def cleanBase():
file = open("E:\\Лапітєх\\aplied_programming\\flaskProject\\cleanDB.sql")
insert = sqlalchemy.text(file.read())
engine.execute(insert)
file.close()
from app import db from flask import request from models import mysession, User, Admin_Setting, Old_Password, Common_Password, engine from sqlalchemy import update from datetime import datetime from similarityAlgorithm import similar from compareOldToNewPassword import newPasswordToOldPasswordComparison from hashPassword import hashPassword from verify import verifyPassword ''' 1) GET : list_object = mysession.query(TABLE_NAME).filter_by(expressions with commas).all() use a for loop to iterate through the list and handle attributes 2) PATCH (update) : patch_object = update(TABLE_NAME).where(expressions with commas).values(attribute_name = "value") engine.execute(patch_object) 3) POST : row_object = TABLE_NAME(list of attribute_name = "value") mysession.add(row_object) --------- EXAMPLES --------- # GET list = mysession.query(User).filter_by(company_name="Bad Company", ID=3).all() for x in range(0, len(list)): print (list[x].name + " " + list[x].email) # PATCH s = update(User).where(User.ID==1).values(name="brett")
def create_task(self):
self.task_id = "SuiChuan-mgmt-console-" + time.strftime("%Y%m%d%H%M%S")
stmt = Task.insert().values({
"task_id": self.task_id
}).return_defaults()
engine.execute(stmt)
def setupClass(self):
engine.execute("delete from operation_logs;")
unittest.TestCase.setUp(self)