def fix_customers(): customers = session.query(Customer).all() customer_cars = session.query(CustomerCar).all() for customer in customers: mongo_customer = dict(id=customer.id, first_name=customer.first_name, last_name=customer.last_name, phone=customer.phone, email=customer.email, company_name=customer.company_name, organisation_number=customer.organisation_number, address_info=dict( address_line_one=customer.address_line_one, address_line_two=customer.address_line_two, zip_code=customer.zip_code, country=customer.country)) mongo_customer['cars'] = [ dict(car_id=car._id, license_number=c.license_number, color=c.color) for car in mm.Car.all() for c in customer_cars if c.CustomerId == customer.id and car.id == c.CarId ] mongo_customer['orders'] = [] mm.Customer(mongo_customer).save()
def fix_associates(): associates = session.query(Associate).all() contact_persson = session.query(ContactPerson).all() product_associate = session.query(ProductAssociate).all() for associate in associates: associate_dict = associate.__dict__ associate_dict['name'] = str(associate_dict['name']) associate_dict['phone'] = str(associate_dict['phone']) associate_dict['email'] = str(associate_dict['email']) associate_dict['phone'] = str(associate_dict['phone']) associate_dict['associates_category'] = str( associate_dict["associates_category"]) associate_dict['address_line_one'] = str( associate_dict["address_line_one"]) associate_dict['address_line_two'] = str( associate_dict["address_line_two"]) associate_dict['zip_code'] = str(associate_dict["zip_code"]) associate_dict['city'] = str(associate_dict["city"]) associate_dict['country'] = str(associate_dict["country"]) for cp in contact_persson: if associate_dict['contact_person_id'] == cp.id: associate_dict['contact_person'] = { 'first_name': cp.first_name, 'last_name': cp.last_name, 'email': cp.email, 'phone': cp.phone } associate_dict['products'] = [] for p_a in product_associate: if p_a.AssociateId == associate_dict['id']: associate_dict['products'].append(p_a.AssociateId) del associate_dict['contact_person_id'] del associate_dict['_sa_instance_state'] mongo_associate = mm.Associate(associate_dict) mongo_associate.save()
def drop_row_by_id(model_obj, t_id, column_name="id"): try: session.query(model_obj).filter( getattr(model_obj, column_name) == t_id).delete() session.commit() except: print("rollback") session.rollback()
def fix_car_models(): car_models = session.query(Car).all() product_compatability = session.query(Compatibility).all() for car in car_models: car_dict = car.__dict__ car_dict['brand_name'] = str(car_dict['brand_name']) car_dict['model_name'] = str(car_dict['model_name']) car_dict['prod_year'] = str(car_dict['prod_year']) car_dict['compatibility'] = [] for pc in product_compatability: if car_dict['id'] == pc.ModelId: car_dict['compatibility'].append(pc.ProductId) del car_dict['_sa_instance_state'] mongo_car_model = mm.Car(car_dict) mongo_car_model.save()
def __init__(self): self.first_name = gen.get_random_from_file("names.txt").title() self.last_name = gen.get_random_from_file("surnames.txt").title() self.job_title = gen.get_random_from_file("job-titles.txt").title() self.email = gen.generate_email(self.first_name, self.last_name) self.reports_to = 1 self.shop_id = session.query(Shop).count()
def populate_database(quantity=50, generations=1): for _ in range(generations): populate_db_random(Customer, [Person, CompanyPerson], quantity) populate_db_random(Car, genCar, quantity) populate_db_random(Shop, genShop, quantity) populate_db_random(Product, genProduct, quantity) populate_db_random(ContactPerson, genCP, quantity) populate_db_random(Employee, genEmployee, quantity) populate_db_random(Order, genOrder, quantity) populate_db_random(Storage, genStorage, quantity) populate_db_random(InternalOrder, genInternalOrder, quantity) populate_db_random(Associate, genAs, quantity) """ ##### relational table linking ##### """ # Customers_has_cars link_two_tables( table_obj_one=dict(model=Customer, id="id"), table_obj_two=dict(model=Car, id="id"), relation_table=dict(model=CustomerCar, attribute_one="CustomerId", attribute_two="CarId") ) # orders_has_products link_two_tables( table_obj_one=dict(model=Order, id="id"), table_obj_two=dict(model=Product, id="id"), relation_table=dict(model=OrderProduct, attribute_one="OrderId", attribute_two="ProductId"), extra_attributes=dict(Amount=random.randint(50, 500)) ) # shops_has_storages link_two_tables( table_obj_one=dict(model=Shop, id="id"), table_obj_two=dict(model=Storage, id="id"), relation_table=dict(model=ShopStorage, attribute_one="ShopId", attribute_two="StorageId"), extra_attributes=dict(ProductId=session.query(Product).count()) ) # products_has_associates link_two_tables( table_obj_one=dict(model=Product, id="id"), table_obj_two=dict(model=Associate, id="id"), relation_table=dict(model=ProductAssociate, attribute_one="ProductId", attribute_two="AssociateId"), ) # Compatibility link_two_tables( table_obj_one=dict(model=Product, id="id"), table_obj_two=dict(model=Car, id="id"), relation_table=dict(model=Compatibility, attribute_one="ProductId", attribute_two="ModelId") ) # products_has_internal_orders link_two_tables( table_obj_one=dict(model=Product, id="id"), table_obj_two=dict(model=InternalOrder, id="id"), relation_table=dict(model=ProductInternalOrder, attribute_one="ProductId", attribute_two="InternalOrderId") )
def get_customers_cars(c_id): customer = session.query(Customer, CustomerCar, Car). \ join(CustomerCar, Customer.id == CustomerCar.CustomerId). \ join(Car, CustomerCar.CarId == Car.id). \ filter(Customer.id == c_id). \ all() return customer
def employees_in_shop(s_id): employees = session.query(Employee.first_name, Employee.last_name). \ join(Shop, Shop.id == Employee.shop_id). \ filter(Employee.shop_id == s_id). \ all() return ((str(employees).strip("[]''").replace(',', '').replace("'", "")))
def product_in_stores_by_id(p_id): query = session.query(Shop.city, Shop.address_line_one, Storage.product_amount). \ join(ShopStorage, Shop.id == ShopStorage.ShopId). \ join(Storage, Storage.id == ShopStorage.ShopId). \ filter(ShopStorage.ProductId == p_id). \ all() return query
def fix_products(): products = session.query(Product).all() order_product = session.query(OrderProduct).all() product_associate = session.query(ProductAssociate).all() storage = session.query(Storage).all() shop_storage = session.query(ShopStorage).all() internal_order = session.query(InternalOrder).all() product_internal_order = session.query(ProductInternalOrder).all() mm_shops = mm.Shop.all() mm_orders = mm.Order.all() mm_associates = mm.Associate.all() for product in products: product_dict = product.__dict__ product_dict['product_name'] = str(product_dict['product_name']) product_dict['description'] = str(product_dict['description']) product_dict['purchase_price'] = float(product_dict['purchase_price']) product_dict['retail_price'] = float(product_dict['retail_price']) product_dict['storage_info'] = [] for ss in shop_storage: if product_dict['id'] == ss.ProductId: for s in storage: if s.id == ss.StorageId: for mms in mm_shops: if ss.ShopId == mms.id: for pio in product_internal_order: if product_dict['id'] == pio.ProductId: for io in internal_order: if io.id == pio.InternalOrderId: product_dict[ 'storage_info'].append({ 'shop_id': mms._id, 'product_amount': int(s.product_amount), 'min_amount': int(s.min_amount), 'reorder_amount': int(s.reorder_amount), 'internal_order': [{ 'lead_time': io.lead_time }] }) product_dict['orders'] = [] for op in order_product: if product_dict['id'] == op.ProductId: # add mongo._id for mmo in mm_orders: if mmo.id == op.ProductId: product_dict['orders'].append(mmo._id) product_dict['associate'] = [] for po in product_associate: if product_dict['id'] == po.ProductId: for mma in mm_associates: if mma.id == po.AssociateId: product_dict['associate'].append(mma._id) del product_dict['_sa_instance_state'] mongo_product = mm.Product(product_dict) mongo_product.save()
def __init__(self): self.name = ge.generate_company_name() self.country = 'Sweden' self.city = ge.get_random_city("worldcities.csv", self.country) self.phone = ge.generate_phone_number(self.country, 9, 'countrycodes.txt') self.email = ge.generate_email(self.name, self.city) self.address_line_one = ge.generate_address() self.address_line_two = self.address_line_one self.zip_code = ge.generate_zip() self.associates_category = random.choice(['Supplier', 'Manufacturer']) self.contact_person_id = session.query(ContactPerson).count()
def connecting_table(model, generated_data, amount): count = session.query(model).count() x = count + 1 y, z = 1, 1 for _ in range(amount): gen = generated_data(x, y, z) populate_db(model, gen.__dict__) x += 1 y = random.randrange(1, amount) z = random.randrange(1, amount) commit_db()
def fix_orders(): orders = session.query(Order).all() customers = session.query(Customer).all() products = session.query(Product).all() order_products = session.query(OrderProduct).all() for order in orders: order_dict = order.__dict__ order_dict['employee_id'] = mm.Employee.find( id=order.employee_id).first_or_none()._id del order_dict['_sa_instance_state'] for customer in customers: if order_dict['customer_id'] == customer.id: order_dict['customer_info'] = ({ 'customer_id': customer.id, 'first_name': customer.first_name, 'last_name': customer.last_name, 'address': customer.address_line_one, 'phone': customer.phone }) order_dict['products'] = [] for op in order_products: if order_dict['id'] == op.OrderId: for product in products: if product.id == op.ProductId: order_dict['products'].append({ 'product_name': product.product_name, 'retail_price': product.retail_price, }) #del order_dict['id'] del order_dict['customer_id'] mongo_orders = mm.Order(order_dict) mongo_orders.save()
def link_two_tables(table_obj_one={}, table_obj_two={}, relation_table={}, extra_attributes={}): # Models relation_model = relation_table.get("model", None) model_one = table_obj_one.get("model", None) model_two = table_obj_two.get("model", None) # table rows model_arr_one = session.query(model_one).all() model_arr_two = session.query(model_two).all() relation_arr = session.query(relation_model).all() # models ids row_one_id = table_obj_one.get("id", None) row_two_id = table_obj_two.get("id", None) link_attrname_one = relation_table.get("attribute_one", None) link_attrname_two = relation_table.get("attribute_two", None) for index, row_one in enumerate(model_arr_one): if index < len(model_arr_two): row_two = model_arr_two[index] row_one_value = getattr(row_one, row_one_id, None) row_two_value = getattr(row_two, row_two_id, None) new_entry = True for link_row in relation_arr: link_value_one = getattr(link_row, link_attrname_one, None) link_value_two = getattr(link_row, link_attrname_two, None) if row_one_value == link_value_one and row_two_value == link_value_two: new_entry = False break if new_entry: insert_dict = { link_attrname_one: row_one_value, link_attrname_two: row_two_value, **extra_attributes } insertion = relation_model(**insert_dict) session.add(insertion) session.commit()
def fix_shop(): shops = session.query(Shop).all() employees = mm.Employee.all() for shop in shops: as_dict = shop.__dict__ as_dict['employees'] = [] as_dict = { key: value for key, value in as_dict.items() if value is not None } del as_dict['_sa_instance_state'] mongo_shop = mm.Shop(as_dict) mongo_shop.save() for employee in employees: if shop.id == employee.shop_id: as_dict['employees'].append(employee._id) mongo_shop.save()
def fix_employees(): employees = session.query(Employee).all() for employee in employees: as_dict = employee.__dict__ del as_dict['_sa_instance_state'] if as_dict['reports_to'] is None or as_dict['reports_to'] == as_dict[ 'id']: del as_dict['reports_to'] mongo_employee = mm.Employee(as_dict) mongo_employee.save() employees = mm.Employee.all() for employee in employees: if hasattr(employee, 'reports_to'): employee.reports_to = mm.Employee.find( id=employee.reports_to).first_or_none()._id employee.save()
def get_all_id(): shop_id = session.query(Shop.id).all() return shop_id
def get_assets_by_columnvalue(model_obj, column_name, value): return session.query(model_obj).filter( getattr(model_obj, column_name).ilike(f'%{value}%')).all()
def get_all_assets(model_obj): return session.query(model_obj).all()
def get_asset_by_id(model_obj, t_id, column_name="id"): return session.query(model_obj).filter( getattr(model_obj, column_name) == t_id).first()