Python连接SQL Server之create_engine和pyodbc\pymssql：Flask的SQLAlchemy中query中filter和filter_by的区别

SQLAlchemy中的query，有filter和filter_by两个函数

filter_by表内部精确查询

https://www.cnblogs.com/Jokerguigui/p/11526889.html 

https://blog.csdn.net/weixin_42670402/article/details/84522877

 

>>使用SQLAlchemy：https://www.liaoxuefeng.com/wiki/1016959663602400/1017803857459008

快速入门Flask-SQLAlchemy：http://www.pythondoc.com/flask-sqlalchemy/quickstart.html

SQLAlchemy登录用户模块

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'

win10下使用flask_sqlalchemy连接SQLServer：https://blog.csdn.net/qq_33279781/article/details/78539531

SQLAlchemy连接SQLserver数据库及常用的DB操作：https://blog.csdn.net/young_kp/article/details/82423596

Dealing with disconnect：https://docs.sqlalchemy.org/en/14/core/pooling.html#pool-disconnects

create_engine

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


#Person.py
from sqlalchemy import Column,String,INT
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()   #创建对象的基类


class User(Base):
    __tablename__ = 'users'
    #id = Column(INT())#, primary_key=True
    name = Column(String(80),nullable=True, primary_key=True)
    email = Column(String(120),nullable=True)

    def __init__(self, name, email):
        #self.id = id
        self.name = name
        self.email = email


class DatabaseManagement():
    def __init__(self):
        self.engine = create_engine('mssql+pymssql://sa:密码@localhost:1433/sss',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)

class MyTest():
    def __init__(self):
        self.db_obj = DatabaseManagement()

    def process(self):
        person_obj = User("Wilson4", "111@sina.com")
        self.db_obj.add_obj(person_obj)
        # query_filter = 'users.name == "Wilson", users.email == "111@sina.com"'
        # person_list = self.db_obj.query_all(Person, query_filter)
        # for i in person_list:
        #     print (i.name)
        print ("hello")



if __name__=="__main__":
    myTest = MyTest()
    myTest.process()

第二种方法：不使用create_engine。。。Python连接SQL Server 之pyodbc\pymssql。。。https://blog.csdn.net/qq_41070101/article/details/85081378

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker



app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pymssql://sa:密码@localhost:1433/sss'#sqlite:////tmp/test.db
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)



class User(db.Model):
    __tablename__ = 'users'
    #id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=True, primary_key=True)
    email = db.Column(db.String(120), nullable=True)

    def __init__(self, name, email):
        self.name = name
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.name

if __name__ == '__main__':
  print('zhangsan')
  user = User.query.filter_by(name='Wilson').first()
  print(user)