python flask 操作 sql server数据库

1 app.py 操作页面。

from flask import Flask, render_template

from config.env_config import BaseConfig, ProConfig, DevConfig
from controller.test.TestController import test_bp
# 实例化 Flask的类，并且绑定module
from config.db_mg import DatabaseManagement
from controller.test.Person import Person
from sqlalchemy import and_


class MyTest():
    def __init__(self):
        self.db_obj = DatabaseManagement()
 
    def process(self):
        person_obj = Person(5,"james",18)
        person_obj = self.db_obj.add_obj(person_obj)
        query_filter=and_(Person.Name=="james",Person.Age==18)
        person_list = self.db_obj.query_all(Person, query_filter)
        for i in person_list:
            print(i.Name)

# 注入 有点像springBoot 注入
app.register_blueprint(test_bp)
if __name__ == '__main__':
    # app.run(host='127.0.0.1', port=5001, debug=True)
    # 创建表
    myTest = MyTest()
    myTest.process()

2 db数据库交互的py

#db_mg.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import pyodbc
 
class DatabaseManagement():
    def __init__(self):
        DATABASE = 'EAWP_Workspace'
        USERNAME = 'MMIP_User_DEV'
        PASSWORD = 'mindray2017!'
        SERVER = '10.1.9.118'
        conn_str = f'mssql+pyodbc://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver=ODBC+Driver+17+for+SQL+Server'
        self.engine = create_engine(conn_str,echo=True)    #初始化数据库连接
        
        DBsession = sessionmaker(bind=self.engine)    #创建DBsession类
        self.session = DBsession()    #创建对象
 
    def add_obj(self,obj):    #添加内容
        self.session.add(obj)
        self.session.commit()    #提交
        return obj
 
    def query_all(self,target_class,query_filter):    #查询内容
        result_list = self.session.query(target_class).filter(query_filter).all()
        return result_list
 
    def update_by_filter(self, obj, update_hash,query_filter):     #更新内容
        self.session.query(obj.__class__).filter(query_filter).update(update_hash)
        self.session.commit()
 
    def delete_by_filter(self, obj, query_filter):     #删除内容
        self.session.query(obj).filter(query_filter).delete()
 
    def close(self):    #关闭session
        self.session.close()
 
    def execute_sql(self, sql_str):    #执行sql语句
        return self.session.execute(sql_str)

3 定义一个数据库类。

#Person.py
from sqlalchemy import Column,String,INT
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()   #创建对象的基类
 
class Person(Base):    #定义一个类，继承Base
    __tablename__='Person'
    ID = Column(INT(),primary_key=True)
    Name = Column(String(50))
    Age = Column(INT())
 
    def __init__(self,id,name,age):
        self.ID=id
        self.Name=name
        self.Age=age

完成后，可以实现数据库访问插入数据

查看数据。