SqlAlchemy “Too many connections”

2019-06-12：真 有用的是这个：

https://blog.csdn.net/weiwangchao_/article/details/80185009

1、在使用 create_engine创建引擎时，如果默认不指定连接池设置的话，一般情况下，SQLAlchemy会使用一个 QueuePool绑定在新创建的引擎上。并附上合适的连接池参数。

2、在这种情况下，当你使用了session后就算显式地调用session.close()，也不能把连接关闭。连接会由QueuePool连接池进行管理并复用。

3、如果想禁用SQLAlchemy提供的数据库连接池，只需要在调用create_engine是指定连接池为NullPool，SQLAlchemy就会在执行session.close()后立刻断开数据库连接。当然，如果session对象被析构但是没有被调用session.close()，则数据库连接不会被断开，直到程序终止。

from sqlalchemy.pool import NullPool

#连接数据库
def getEngine():
    engine = create_engine('mysql://root:123456@127.0.0.1/baa?charset=utf8mb4', poolclass=NullPool, echo=False)
    #print(engine)
    return engine

def getSession():
    engine = getEngine()
    BaseMode.metadata.create_all(engine)## 数据库生成表
    # Session = sessionmaker(bind=engine)
    # session = Session()

    DBSession = sessionmaker(bind=engine)
    session = DBSession()

    return session

 

 File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1040, 'Too many connections') (Background on this error at: http://sqlalche.me/e/e3q8)

Process finished with exit code 1

 

 

1、pandas dataframe to_sql()

#连接数据库
def getEngine():
    engine = create_engine('mysql://root:123456@127.0.0.1/databaseName?charset=utf8mb4',echo=False)
    #print(engine)
    return engine

def getSession():
    engine = getEngine()
    BaseMode.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    return session

 

def get_stock_daily(ts_code, start_date,end_date,startTime):
    # print( ts_code + "  " + str(start_date) + "  " + str(end_date))
    api = ts.pro_api(token)

    try:
        df = ts.pro_bar(pro_api=api, ts_code=ts_code, start_date=start_date, end_date=end_date)  # 【获取数据】
        if df is None:
            print("没有获取到数据")
            return None

        print("data count: " + str(len(df)))
        df['trade_date'] = pd.to_datetime(df['trade_date'])  # 交易日期字符串转换成日期格式

        conn = cheDbUtil.getEngine()
        try:
            df.to_sql('b_stock_daily', con=conn, if_exists='append', index=False)  # replace/append/fail 【save】
        except Exception as e:
            traceback.print_exc()
            print("get_stock_daily() in BaaBusiniessPro.py XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX")
            return None
        finally:
            conn.dispose()
    except Exception as e:
        sleepNeed = 60
        print("Start to sleep  " + str(sleepNeed) + ' 秒')
        print("当前耗时： " + str(round((time.time() - startTime), 2) / 60) + ' 分钟')
        time.sleep(sleepNeed)  # 休眠 ? 秒

2、SqlAlchemy  操作 mysql

#获取日行情最后一条记录的日期
def getMaxTradeDateForStockDaily(ts_code):
    try:
        session = getSession()
        rows = session.query(func.max(StockDaily.trade_date)).filter(StockDaily.ts_code == ts_code).one()
        # print(str(len(rows)))
        # print(type(rows))
        # print(str(rows))
        if rows is not None and len(rows) == 1:
            return rows[0]
        return None
    except Exception as e:
        traceback.print_exc()
        print("getMaxTradeDateForStockDaily() XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX")
        return None
    finally:
        session.close()