Visual Studio 2013进行Python开发（二）---python连接sql server数据库

深碍√TFBOYSˉ_ 2023-08-17 15:56 92阅读 0赞

**1.首先需要安装以下三个包：**

sqlalchemy

flask-sqlalchemy

pydoc

安装方法如下：

选择env右键选择“Install Python Package...”

**![447067-20190911085546995-2108594964.png][]**

弹出如下界面，然后输入包名点击确定即可

![447067-20190911085556525-1699601505.png][]

**2.在项目中添加一个“db\_mg.py”的文件，代码如下：**

#db_mg.py
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from FlaskWebProject3.models import Base
     
    class DatabaseManagement():
        def __init__(self):
            self.engine = create_engine("mssql+pymssql://sa:123456@127.0.0.1/UserRights?charset=utf8",echo=True,encoding='utf-8',convert_unicode=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 queryall(self,target_class):    #查询内容
            result_list = self.session.query(target_class).all()
            return result_list
        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)

其中

create_engine("mssql+pymssql://sa:123456@127.0.0.1/User?charset=utf8",echo=True,encoding='utf-8',convert_unicode=True)中的"sa"为账户登录名，"123456" 为数据库登陆名，"127.0.0.1"为登陆的数据库的服务器名，"User"为数据库名，

如果不添加如下“charset=utf8”，有可能从数据库中查询出来的中文为乱码

** 3.具体的调用如下：**

**views.py**

"""
    Routes and views for the flask application.
    """
    
    from datetime import datetime
    from flask import render_template, session
    #from FlaskWebProject3 import app
    from flask import Flask,request 
    from flask.json import jsonify
    import os
    from flask.helpers import make_response
    
    from flask_bootstrap import Bootstrap
    
    from wtforms import StringField, SubmitField
    from wtforms.validators import Required
    from flask_wtf.form import Form
    from sqlalchemy.sql.schema import Column
    from sqlalchemy.ext.declarative.api import declarative_base
    from click import INT
    from lib2to3.fixer_util import String
    from FlaskWebProject3 import app, models
    from flask_sqlalchemy import SQLAlchemy
    from FlaskWebProject3.models import Person, User, engine
    from FlaskWebProject3.db_mg import DatabaseManagement
    from sqlalchemy.orm.session import sessionmaker
    from sqlalchemy.engine import create_engine
    from _operator import and_
    
    #from FlaskWebProject3.Person import Person
    
    
    
    
    #app.config['SESSION_TYPE'] = 'filesystem'
    #app.config['SECRET_KEY'] = os.urandom(24)
    #app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pymssql://sa:123456@127.0.0.1/UserRights?charset=utf8'
    #app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
    ##app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    #app.config['SQLALCHEMY_NATIVE_UNICODE']=True;
    #app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=True
    #app.config['SQLALCHEMY_ECHO']=True
    app.config['JSON_AS_ASCII']=False
    
    
    @app.route('/111', methods=['GET'])
    def GetUser():
    
     db_session=DatabaseManagement()
     query_filter=and_(Person.Name=="james",Person.Password=="123456");
     person_list = db_session.queryall(Person)
     output = {} 
     for item in person_list:
        output["Name"] = item.Name  
        output["Password"] = item.Password
        
     return jsonify({
        'message': output})   
    
    
    if __name__ == '__main__':
     
    
     app.run(self)

注意：

app.config['JSON_AS_ASCII']=False是设置返回的json对象中中文是否乱码，如果不设置则json中返回的中文为乱码

转载于:https://www.cnblogs.com/zhangabo/p/11504370.html

[447067-20190911085546995-2108594964.png]: /images/20230810/412480c182f244ce966c3362b35d1d36.png
[447067-20190911085556525-1699601505.png]: /images/20230810/7f010cd462154705962176385da07ffd.png