def populte(): print('drop database') db.drop_all() print('drop done') print('create database') db.create_all() print('create database done') print('creating......') for user in users: hash_password = bcrypt.generate_password_hash( user.get('password')).decode('utf-8') create_user = User(username=user.get('username'), email=user.get('email'), password=hash_password) account = Account() create_user.account = account db.session.add(create_user) db.session.commit() print('create user success') print('create users done') admin = User.query.filter_by(username='******').first() for post in posts: create_post = Post(title=post, content=post * 3, author=admin) db.session.add(create_post) db.session.commit() print('create post success') print('done')
def app(): print("\nCalling app fixture...\n") app = create_app(testing=True) with app.app_context(): db.drop_all() db.create_all() db.session.add_all((User(username='******', password='******'), User(username='******', password='******'), Post(title='test title', body='test\nbody', author_id=1, created=datetime.date(2021, 2, 28)))) db.session.commit() yield app
def run(self): db.drop_all()
def make_migration(): with app.app_context(): db.drop_all() tables_name = ['user','post','account'] for name in tables_name: conn = psycopg2.connect(database='flaskdb',user='******',password='******',host='localhost') cursor = conn.cursor() try: cursor.execute(f'select * from public.{name}') conn.close() except Exception as e: # 需要将连接写入循环,因查询第一次后会触发连接断开 if f'错误: 关系 "public.{name}" 不存在' in str(e): print(f'确认删除表{name}') conn.close() # print(dir(cursor)) else: print(e) print('清空数据库成功') print('准备重新初始化数据库') db.create_all() print('初始化数据库成功') # 获取上次excel文件 os.getcwd() file_dir = os.getcwd()+'/migrations/' ls = os.listdir(file_dir) next_number = 0 # 初始编号 if ls: combine_tuple = tuple(zip(range(len(ls)),ls)) print(combine_tuple) next_number = combine_tuple[-1][0] ex_file_name = f'migrations/mg{next_number}_blog.xlsx' wb = load_workbook(filename = ex_file_name) with psycopg2.connect(database='flaskdb',user='******',password='******',host='localhost') as conn: cursor = conn.cursor() for name in tables_name: sheet = wb[f'{name} data'] rows = sheet.rows # print(dir(rows)) print('-'*12) model_name = name.title() # model名 same_fields = None # 字段相同但是位置不一样时这个变量用来代替real_fields tag_delete = None # 字段减少标志重置 tag_add = None # 字段增加标志重置 diff_fields = None # 子循环完毕重置 diff_fields_index = [] # 索引位列表,用于删除或插入line的数据 for row in rows: cursor.execute(f'select * from public.{name}') fields = cursor.description # print(fields) fields_name = [ field[0] for field in fields] line = [col.value for col in row] real_fields = fields_name orig_fields = line # print(real_fields) # print(orig_fields) if fields_name == orig_fields: # 字段没有改变 print(f'The {name} table fields no change') continue elif len(fields_name) < len(orig_fields) and tag_delete is not True: # 字段减少 diff_fields = [ field for field in orig_fields if field not in fields_name] print(f'The {name} table fields have been changed ,{diff_fields} are removed') tag_delete = True # 字段减少导致的数据错位,需要删除对应字段的数据 if diff_fields: for field in diff_fields: index = orig_fields.index(field) diff_fields_index.append(index) # print(diff_fields_index) continue elif len(fields_name) > len(orig_fields) and tag_add is not True: # 字段增加 diff_fields = [ field for field in real_fields if field not in orig_fields] print(f'The {name} table fields have been changed ,{diff_fields} are added') tag_add = True # 字段增加导致的数据错位,需要插入对应字段的数据,默认插入None,字段要在数据库中允许为空 if diff_fields: for field in diff_fields: index = real_fields.index(field) diff_fields_index.append(index) continue elif fields_name != orig_fields and orig_fields[0] == 'id': # print(fields_name == line) diff_fields = [ field for field in orig_fields if field not in fields_name] # print(diff_fields) if not diff_fields and len(fields_name) == len(orig_fields): # 字段相同但是位置不一样 print(f'The {name} table fields are same before , but locations are difference') same_fields = orig_fields # print(same_fields) if diff_fields: raise AttributeError(f'{diff_fields} fields not found') continue if same_fields: import_db(name,same_fields,line) if diff_fields_index: # print(diff_fields_index) diff_index = 0 for index in diff_fields_index: if tag_delete: _data = line.pop(index-diff_index) print(f'因{diff_fields[diff_index]}移除,{_data}将不被导入数据库') if tag_add: line.insert(index,None) print(f'字段{diff_fields[diff_index]}增加,开始导入默认数据') diff_index += 1 dict_data = dict(zip(real_fields,line)) if tag_add: list_data = list(zip(real_fields,line)) rm_index = 0 for index in diff_fields_index: rm_data = list_data.pop(index-rm_index) rm_index += 1 print(f'该行导入默认数据完成') dict_data = dict(list_data) import_db(model_name,dict_data) print('-'*8) if (not tag_add and not tag_delete) and not same_fields: dict_data = dict(zip(real_fields,line)) import_db(model_name,dict_data)
def drop_db(): db.drop_all()
def tearDown(self): db.session.remove() db.drop_all() self.app_context.pop()
def init_db(): db.drop_all() db.create_all()