Python Connect Sql With user and "token"

Question

Python Connect Sql With user and "token"

Navigation

#1 by (1 votes)

1

I have an application in Flask and I'm using SQLAlchemy to get access to 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 only replace the parameters but not! 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')

sql sql-server python-3.x sqlalchemy

asked by anonymous 29.08.2018 / 19:56

1 answer

Passing Date parameters for queries prepare Iframe or embed responsive for mobile

score 1 · Accepted Answer

I make 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's as if it creates a valid URI.

I ran a test with flask-sqlalchemy and the connection occurred without problems, but I can not simulate the token that you are using as a 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 the SQL Server drivers are installed. If they are not

SQL Server Download Page

On Linux, simply install the drivers by adding 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"