Python Connect Sql With user and "token"

Question

I have an application in Flask and am using Sqlalchemy to access SQL Server.

I would like the connection to the database to be made by a generic user and the password is a "token", I thought it would just replace the parameters but no! And I did not find (or did not know how to search correctly) information about.

This is the current concection string:

engine = create_engine('mssql+pyodbc://Usuario:token@server/db?driver=SQL+Server+Native+Client+11.0') 

Answer 1

I perform the connection between Sqlalchemy and MS SQL Serve with the following string:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""SQLAlchemy + MS SQL Server.

- {SQL Server} - SQL Server 2000.
- {SQL Native Client} - SQL Server 2005 (Também conhecido como versão 9.0).
- {SQL Server Native Client 10.0} - SQL Server 2008.
- {SQL Server Native Client 11.0} - SQL Server 2012.
- {ODBC Driver 11 for SQL Server} - SQL Server 2005 ao 2014.
- {ODBC Driver 13 for SQL Server} - SQL Server 2005 ao 2016.
- {ODBC Driver 13.1 for SQL Server} - SQL Server 2008 ao 2016.
- {ODBC Driver 17 for SQL Server} - SQL Server 2008 ao 2017.
"""

from urllib.parse import quote_plus

import pyodbc
from sqlalchemy import create_engine

# Verificar se os drivers estão instalados.
# print([x for x in pyodbc.drivers() if x.startswith('ODBC')])
# print(pyodbc.drivers())

parametros = (
    # Driver que será utilizado na conexão
    'DRIVER={ODBC Driver 17 for SQL Server};'
    # IP ou nome do servidor.
    'SERVER=192.168.100.178\SQLEXPRESS;'
    # Porta
    'PORT=1433;'
    # Banco que será utilizado.
    'DATABASE=pythonSQL;'
    # Nome de usuário.
    'UID=python;'
    # Senha/Token.
    'PWD=123456')

url_db = quote_plus(parametros)

db = create_engine("mssql+pyodbc:///?odbc_connect=%s" % url_db)

conexao = db.connect()

# resultado = conexao.execute("select * from NomeDaTabela")
#
# for row in resultado:
#     print(row)

quote_plus(parametros) replaces spaces and other special characters with valid HTML characters. It is as if it creates a valid URI.

I ran a test on flask-sqlalchemy and the connection occurred without problems, but I have no way to simulate the "token" you are using as password.

from urllib.parse import quote_plus

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

parametros = (
    # Driver que será utilizado na conexão
    'DRIVER={ODBC Driver 17 for SQL Server};'
    # IP ou nome do servidor.
    'SERVER=192.168.100.178\SQLEXPRESS;'
    # Porta
    'PORT=1433;'
    # Banco que será utilizado.
    'DATABASE=pythonSQL;'
    # Nome de usuário.
    'UID=python;'
    # Senha/Token.
    'PWD=123456')

url_db = quote_plus(parametros)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pyodbc:///?odbc_connect=%s' % url_db
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite3'
# app.config['SECRET_KEY'] = ''

db = SQLAlchemy(app)


class Usuario(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ativo = db.Column(db.Boolean, default=True)
    nome = db.Column(db.String(50))
    idade = db.Column(db.Integer)
    sexo = db.Column(db.String(10))

    def __repr__(self):
        return '<Nome %r>' % self.nome


@app.route('/')
def index():
    print(Usuario.query.all())
    return 'OK'


if __name__ == '__main__':
    db.create_all()
    app.run(debug=True)

Remember to check that SQL Server drivers are installed. If they are not

SQL Server Download Page

On Linux just install the drivers by adding the official repository.

For the test I used the following dependencies (Pipfile):

[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"

[packages]
flask-sqlalchemy = "*"
flask = "*"
pyodbc = "*"

[dev-packages]

[requires]
python_version = "3.6"