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