sqlalchemy连接数据库

1.安装sqlalchemy模块

2.引入需要的模块

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text

3.创建数据库连接

 class DB():
    MYSQL_PROD = "mysql+pymysql://root:root@127.0.0.1:3306/db_py?charset=utf8"

4.设置参数

# 配置文件中读取连接串
DB_URI =DB.MYSQL_PROD
# pool_size连接池数量
# pool_recycle连接池中空闲时间超过设定时间后，进行释放
# echo输出日志
# create_engine 初始化数据库连接
engine = create_engine(DB_URI, echo=False, pool_size=50, pool_recycle=1800)

.插入，修改，删除操作

def query(sql): #sql为需要的sql语言。例:sql='insert into -----'
    # 创建DBSession类型:
    DB_Session = sessionmaker(bind=engine)
    # 创建session对象:
    DB = DB_Session()
    try:
        # 执行sql语句
        DB.execute(text(sql))
        DB.commit()
        return True
    except:
       print("链接有误")
       DB.rollback()
       return False
   finally:
       DB.close()

代码总结

# coding=utf-8
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text

#创建数据库链接
class DB():
    MYSQL_PROD = "mysql+pymysql://root:root@127.0.0.1:3306/db_py?charset=utf8"
    # 配置文件中读取连接串
    DB_URI =DB.MYSQL_PROD
    # pool_size连接池数量
    # pool_recycle连接池中空闲时间超过设定时间后，进行释放
    # echo输出日志
    # create_engine 初始化数据库连接
    engine = create_engine(DB_URI, echo=False, pool_size=50, pool_recycle=1800)


# 插入，修改，删除操作
def query(sql):
# 创建DBSession类型:
DB_Session = sessionmaker(bind=engine)
# 创建session对象:
DB = DB_Session()
try:
    # 执行sql语句
    DB.execute(text(sql))
    DB.commit()
    return True
except:
    print("链接有误")
    DB.rollback()
    return False
finally:
    DB.close()