models.py class CompanyGoodsModel(Base): id=Column(Integer, primary_key=True) company_id = Column(Integer) 。。。 #我们在model中定义一个to_json方法，来获取模型的数据 def to_json(self): dict = self.__dict__ if "_sa_instance_state" in dict: del dict["_sa_instance_state"] return dict def get_info_by_id(company_id, category_id): if company_id: data=CompanyGoodsModel.query.filter(CompanyGoodsModel.company_id == company_id).all() return data elif category_id: data = CompanyGoodsModel.query.filter(CompanyGoodsModel.category_id == category_id).all() return data
然后在视图层中
@api.route('/company_goods', methods=['GET', 'POST']) def company_goods(): if request.method == 'GET': company_id = request.args.get('company_id', '') category_id = request.args.get('category_id', '') data=CompanyGoodsModel.get_info_by_id(company_id, category_id) result=[] for d in data: d.goods_info=eval(d['goods_info']) result.append(d.to_json()) if not result: return json_response('0001', {}, '数据获取失败 ') return json_response('0000', result, '数据获取成功')
Flask更改和加密密码
from werkzeug.security import generate_password_hash, check_password_hash class CompanyUser(Base): if change_db: __bind_key__ = 'normal_db' else: pass __tablename__ = 'partner_user' _password = Column('password', String(300)) @property def password(self): return self._password @password.setter def password(self, raw): self._password = generate_password_hash(raw) def check_password(self, raw): return check_password_hash(self._password, raw)
在更改密码的时候，直接将明文存储在该字段上即可
sqlalchemy模糊查询
queryset = CompanyUser.query.filter(or_(CompanyUser.username.like('%{}%'.format(search)), CompanyUser.phone_number.like('%{}%'.format(search)))).all()
原生Flask分页
假设 page_index=1,page_size=10；所有分页查询不可以再跟first(),all()等 1.用offset()设置索引偏移量,limit()限制取出 #filter语句后面可以跟order_by语句 db.session.query(User.name).filter(User.email.like('%'+email+'%')). limit(page_size).offset((page_index-1)*page_size) 2.用slice(偏移量，取出量)函数 #filter语句后面可以跟order_by语句 db.session.query(User.name).filter(User.email.like('%'+email+'%')).slice((page_index - 1) * page_size, page_index * page_size) 注释：此方法和第一种相同的效果。 因为：由一下内部方法可知，slice()函数第一个属性就是offset()函数值，第二个属性就是limit()函数值 @_generative(_no_statement_condition) def slice(self, start, stop): """apply LIMIT/OFFSET to the ``Query`` based on a " "range and return the newly resulting ``Query``.""" if start is not None and stop is not None: self._offset = (self._offset or 0) + start self._limit = stop - start elif start is None and stop is not None: self._limit = stop elif start is not None and stop is None: self._offset = (self._offset or 0) + start if self._offset == 0: self._offset = None @_generative(_no_statement_condition) def limit(self, limit): """Apply a ``LIMIT`` to the query and return the newly resulting ``Query``. """ self._limit = limit @_generative(_no_statement_condition) def offset(self, offset): """Apply an ``OFFSET`` to the query and return the newly resulting ``Query``. """ self._offset = offset 3.用paginate(偏移量，取出量)函数,用于BaseQuery user_obj=User.query.filter(User.email.like('%'+email+'%')).paginate(int(page_index), int(page_size),False) #遍历时要加上items object_list =user_obj.items 4.filter中使用limit #此处不能再跟order_by语句，否则报错 db.session.query(User.name).filter(User.email.like('%'+email+'%') and limit (page_index - 1) * page_size, page_size)
有关flask的教程博文
https://me.csdn.net/qq_38949193
字符串时间和时间戳之间的互转
import time, datetime tssl = '2019-11-9' def date2timestamp(date): timeArray = time.strptime(date, "%Y-%m-%d") timeStamp = int(time.mktime(timeArray)) return timeStamp def timestamp2date(timestamp): timeStamp = 1381419600 timeArray = time.localtime(timeStamp) otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray) return otherStyleTime print(date2timestamp(tssl)) print(date2timestamp('2019-11-10') - date2timestamp(tssl)) print(timestamp2date(tssl))
sqlalchemy的简单增删查改
增加： def add_data(): p1 = Person(name='zhiliao1',age=19,country='china') p2 = Person(name='zhiliao2',age=20,country='china') session.add_all([p1,p2]) session.commit() # 查 def search_data(): #获取所有的对象，query类型 # all_person = session.query(Person).all() # for p in all_person: # print(p) # print(p.name) # all_person = session.query(Person).filter_by(name='zhiliao').all() # for x in all_person: # print(x) # all_person = session.query(Person).filter(Person.name=='zhiliao').all() # print(all_person) # [<__main__.Person object at 0x0000015BCE1B4630>] # for x in all_person: # print(x) person = session.query(Person).first() print(person) # 改 def update_data(): person = session.query(Person).first() person.name = 'ketang' session.commit() # 删 def delete_data(): person = session.query(Person).first() session.delete(person) session.commit()
python将字符串类型改成日期类型
将字符串类型的'2019-03-14'改成date类型，如下：
import datetime
b = datetime.date(*map(int,'2019-03-14'.split('-')))
print(b,type(b))
将datetime类型转成str
form datetime import datetime
str_date = datetime.now().strftime("%Y-%m-%d")
将str类型转成datetime
form datetime import datetime
start_date = datetime.strptime("2019-04-15", "%Y-%m-%d")
连表查询
data = a.query.join(c, c.id == a.company_id).add_columns( a.id, a.create_date, a.rating, a.is_display, a.comment, a.customer_id, a.company_id).filter( a.rating < 3, a.is_display == display, or_(c.company_name.contains(search), c.phone_number == search)).limit(page_size).offset((page_index - 1) * page_size)
将字符串的日期转化为datetime格式
start_date = datetime.date(*map(int, datas['start_date'].split('-')))
使用pandas生成excel表格
import pandas as pd import os,time file_path = os.path.abspath('.')+'/'+str(time.time()).split('.')[0] + '.xlsx' print(file_path) df=pd.DataFrame({'ID':[1,2,3],'Name':['tom','sim','victor']}) df.to_excel(file_path) print('done!')
用xlsxwriter包写入Excel文件
data数据就是一个答列表，里面有一个个的字典，字典里面的value可能是字典，也可能是空字典，还有可能是字符串
""" import xlsxwriter # 写excel def write_excel(): workbook = xlsxwriter.Workbook('chat.xlsx') # 创建一个excel文件 worksheet = workbook.add_worksheet(u'sheet1') # 在文件中创建一个名为TEST的sheet,不加名字默认为sheet1 worksheet.set_column('A:A', 20) # 设置第一列宽度为20像素 bold = workbook.add_format({'bold': True}) # 设置一个加粗的格式对象 worksheet.write('A1', 'HELLO') # 在A1单元格写上HELLO worksheet.write('A2', 'WORLD', bold) # 在A2上写上WORLD,并且设置为加粗 worksheet.write('B2', U'中文测试', bold) # 在B2上写上中文加粗 worksheet.write(2, 0, 32) # 使用行列的方式写上数字32,35,5 worksheet.write(3, 0, 35.5) # 使用行列的时候第一行起始为0,所以2,0代表着第三行的第一列,等价于A4 worksheet.write(4, 0, '=SUM(A3:A4)') # 写上excel公式 workbook.close() if __name__ == '__main__': # 写入Excel write_excel() print('写入成功') """ import xlsxwriter import time import os import json from test01 import data import math def write_excel(): file_path = os.path.abspath('.') + '\\' + str(time.time()).split('.')[0] + '.xlsx' work_book = xlsxwriter.Workbook(file_path) work_sheet = work_book.add_worksheet(u'sheet1') bold = work_book.add_format({'bold': True}) columns = [] for i in data[0].keys(): columns.append(i) work_sheet.set_column(f'A:{chr(len(columns)+ ord("A"))}', 20) # 空格的长度 work_sheet.write(str(chr(ord('A')+math.ceil(len(columns)/2)))+'1', U'欧阳国勇的表格', bold) # print('A'+str(math.ceil(len(columns)/2)-1)) # print(str(chr(ord('A')+math.ceil(len(columns)/2)))+'1',type(str(chr(ord('A')+math.ceil(len(columns)/2))))) datas = [] for i in data: dd = [] for j in i.values(): dd.append(j) datas.append(dd) datas.insert(0, columns) for i in datas: if i == {}: continue for j in i: middle_value = j if j=={}: middle_value = 'fuck' if isinstance(j, dict): middle_value = str(j) work_sheet.write(datas.index(i)+2, i.index(j), middle_value) work_book.close() if __name__ == '__main__': a = write_excel() print(a)
UUID
UUID是128位的全局唯一标识符，通常由32字节的字符串表示。它可以保证时间和空间的唯一性，也称为GUID，全称为：UUID —— Universally Unique IDentifier，Python 中叫 UUID。
它通过MAC地址、时间戳、命名空间、随机数、伪随机数来保证生成ID的唯一性。
UUID主要有五个算法，也就是五种方法来实现。
uuid1()——基于时间戳。由MAC地址、当前时间戳、随机数生成。可以保证全球范围内的唯一性，但MAC的使用同时带来安全性问题，局域网中可以使用IP来代替MAC。
uuid2()——基于分布式计算环境DCE（Python中没有这个函数）。算法与uuid1相同，不同的是把时间戳的前4位置换为POSIX的UID。实际中很少用到该方法。
uuid3()——基于名字的MD5散列值。通过计算名字和命名空间的MD5散列值得到，保证了同一命名空间中不同名字的唯一性，和不同命名空间的唯一性，但同一命名空间的同一名字生成相同的uuid。
uuid4()——基于随机数。由伪随机数得到，有一定的重复概率，该概率可以计算出来。
uuid5()——基于名字的SHA-1散列值。算法与uuid3相同，不同的是使用 Secure Hash Algorithm 1 算法。
postgresql时间自动生成
ALTER TABLE sys_backend_log OWNER TO postgres;
GRANT ALL ON TABLE sys_backend_log TO zexingj;
CREATE TRIGGER sys_backend_log_create_tri BEFORE INSERT ON sys_backend_log FOR EACH ROW EXECUTE PROCEDURE create_func();
CREATE TRIGGER sys_backend_log_update_tri BEFORE UPDATE ON sys_backend_log FOR EACH ROW EXECUTE PROCEDURE update_func();
python生成excel表格数据
def download_excel(data): # 拼接文件路径 file_path = os.path.abspath('.') + '\\' + 'excel_file' + '\\' + str(time.time()).split('.')[0] + '.xlsx' work_book = xlsxwriter.Workbook(file_path) # 新建工作表 work_sheet = work_book.add_worksheet(u'sheet1') # 创建工作区 bold = work_book.add_format({'bold': True}) # 字体加黑 columns = [ '操作人', '执行事件', '被操作事物名称', '操作缘由', '操作时间', '门店名', '手机号', '标题', '支付方式', '商品说明', '创建时间', '订单号', '支付金额', '审核结果'] work_sheet.set_column(f'A:{chr(len(columns)+ ord("A"))}', 20) # 设置数据覆盖到的第一行每格宽度 # 写第一行的数据 work_sheet.write(str(chr(ord('A') + len(columns) // 2)) + '1', U'审核-商户端-产品订单退款', bold) datas = [] for i in data: try: sys_log_info = SysBackendLogModel.query.filter(SysBackendLogModel.create_id == str(i['key'])).first() except Exception as e: print(e) sys_log_info = '' print(sys_log_info) initiator = sys_log_info.initiator if sys_log_info is not None else '无' operation = sys_log_info.operation if sys_log_info is not None else '无' receiver = sys_log_info.receiver if sys_log_info is not None else '无' reason = sys_log_info.reason if sys_log_info is not None else '无' create_date = sys_log_info.create_date if sys_log_info is not None else '无' datas.append([initiator, operation, receiver, reason, create_date, i['buy_company_info']['company_name'] if i['buy_company_info'] else '无', i['buy_company_info']['phone_number'] if i['buy_company_info'] else '无', i['product_info']['title'] if i['product_info'] else '无', i['product_order_info']['payment_method'] if i['product_order_info'] else '无', i['product_info']['product_desc'] if i['product_info'] else '无', i['product_order_info']['order_date'] if i['product_order_info'] else '无', i['product_order_info']['trade_no'] if i['product_order_info'] else '无', i['product_order_info']['paid_amount'] if i['product_order_info'] else '无', '退款中' if i['status'] == 3 else '已退款']) print(datas) datas.insert(0, columns) for m, i in enumerate(datas): # m就是 datas元素的索引 print(m) if i == {}: continue for n, j in enumerate(i): # n就是列表i元素的索引 work_sheet.write(m + 1, n, str(j)) work_book.close() return file_path
def file_iterator(file_path, chunk_size=512): """ 文件读取迭代器 :param file_path:文件路径 :param chunk_size: 每次读取流大小 :return: """ with open(file_path, 'rb') as target_file: while True: chunk = target_file.read(chunk_size) if chunk: yield chunk else: break
file_path = CompanyProductOrderModel.download_excel(data[0]) data = CompanyProductOrderModel.file_iterator(file_path) filename = os.path.basename(file_path) response = Response(data) response.headers['Content-Type'] = 'application/octet-stream' response.headers["Content-Disposition"] = 'attachment;filename="{}"'.format(filename) # if os.path.exists(file_path):os.remove(file_path) # 删除文件夹 return response
地图相关值坐标反差
def get_lati_and_longi(city, address): url = 'https://restapi.amap.com/v3/geocode/geo?key={}&address={}&city={}' KEY = '需要去高德地图去登陆获取' response = requests.get(url.format(KEY, address, city)) result = response.content.decode("utf-8") result = json.loads(result) return result['geocodes'][0]['location']
