1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 __author__ = "JentZhang"
 4 from utils.config import AppConfig
 5 from sqlalchemy.ext.declarative import declarative_base
 6 from sqlalchemy.orm import sessionmaker, relationship
 7 from sqlalchemy import create_engine, text, event
 8 
 9 # 创建对象的基类:
10 Base = declarative_base()
11 
12 
13 def get_yun_session():
14     """
15     初始化yun数据库连接
16     :return:
17     """
18     # ‘数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名‘
19     engine = create_engine(
20         "mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8" % (
21             AppConfig.USER, AppConfig.PASSWORD, AppConfig.HOST, AppConfig.PORT, AppConfig.DATABASE_YUN),
22         max_overflow=AppConfig.MAX_OVERFLOW,
23         pool_size=500,  # 连接池大小
24         pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
25         pool_recycle=100  # 多久之后对线程池中的线程进行一次连接的回收（重置）
26     )
27     session_maker = sessionmaker(bind=engine)
28     return session_maker()

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 __author__ = "JentZhang"
 4 
 5 from utils.mysql_utils import get_yun_dw_session, get_yun_session
 6 from model import fac_broker, fac_message
 7 from sqlalchemy import text
 8 
 9 
10 def get_user_list_dal(*args, **kwargs):
11     """
12     获取用户列表
13     :param args:
14     :param kwargs:
15     :return:
16     """
17     session = get_yun_session()
18     sql_str = """
19                 SELECT
20                     a.user_id,
21                     a.city_id,
22                     a.`name`,
23                     a.mobile,
24                     DATE_FORMAT(a.create_time, ‘%Y-%m-%d‘) AS create_date,
25                     IFNULL(
26                         DATE_FORMAT(
27                             a.login_first_time,
28                             ‘%Y-%m-%d‘
29                         ),
30                         ‘‘
31                     ) AS login_first_date,
32                     IFNULL(
33                         DATE_FORMAT(c.last_login, ‘%Y-%m-%d‘),
34                         ‘‘
35                     ) AS last_login_date,
36                     (
37                         CASE
38                         WHEN isnull(d.user_id) THEN
39                             ‘自然下载‘
40                         ELSE
41                             d.channel_name
42                         END
43                     ) AS channel_name
44                 FROM
45                     card_broker a
46                 LEFT JOIN fang_webgetcity b ON a.city_id = b.city_id
47                 LEFT JOIN account_user c ON a.user_id = c.id
48                 LEFT JOIN View_Broker_Channel d ON a.user_id = d.new_user_id
49                 where a.create_time>‘2020-04-01‘
50     """
51     res_rows = session.execute(text(sql_str)).fetchall()
54     session.close()
55     result = [dict(zip(result.keys(), result)) for result in res_rows]
56     print(result)
57 
58 
59 if __name__ == ‘__main__‘:
60     get_user_list_dal()

 1 class AppConfig(object):
 2     # 数据库相关（测试）
 3     HOST = ‘127.0.0.1‘
 4     PORT = 3306
 5     USER = ‘root‘
 6     PASSWORD = ‘admin‘
 7     DATABASE_YUN = ‘yun‘
 8     ECHO = False
 9 
10     MAX_OVERFLOW = 500