Issue while working with SQLAlchemy MSSQL connection

Question

I am new to python and learning and practising Python flask website development.

I am facing below issue while working on this , request some help on this to make me understand what mistake I am doing , I am reading SQLAlchemy documentation as well and doing this , but somehow I am not able to fix the specific issue. please help.

I am trying the issue using below link: Connecting to SQL Server 2012 using sqlalchemy and pyodbc

But my problem is I am unaware of how to map the below to 'db' variable. for example if I use this ,

engine = create_engine('mssql+pyodbc://sa:abc@99:localhost/Test? 
driver=SQL+Server+Native+Client+17.0', echo=True)

But later in code , how can I assign the engine to db ? Can I do as below : Instead of db = SQLAlchemy(app) in below code can I use below: db = engine.connect()

    from datetime import datetime
from flask import Flask, render_template, url_for, flash, redirect
from flask_sqlalchemy import SQLAlchemy , sqlalchemy
from sqlalchemy.util.deprecations import SQLALCHEMY_WARN_20 
from forms import RegistrationForm, LoginForm
from sqlalchemy import create_engine , Table, Integer, Column, Identity , PrimaryKeyConstraint
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI']='DRIVER={SQL Server Native Client 17.0};SERVER=localhost:1433;DATABASE=Test;UID=sa;PWD=abc99'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False
db = SQLAlchemy(app)

class User(db.Model):
    __tablename_ = 'User'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.image_file}')"


class Post(db.Model):
    __tablename_ = 'Post'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"


posts = [
    {
        'author': 'Corey Schafer',
        'title': 'Blog Post 1',
        'content': 'First post content',
        'date_posted': 'April 20, 2018'
    },
    {
        'author': 'Jane Doe',
        'title': 'Blog Post 2',
        'content': 'Second post content',
        'date_posted': 'April 21, 2018'
    }
]

@app.route("/")
@app.route("/home")
def home():
    return render_template('home.html', posts=posts)


@app.route("/about")
def about():
    return render_template('about.html', title='About')


@app.route("/register", methods=['GET', 'POST'])
def register():
    form = RegistrationForm()
    if form.validate_on_submit():
        flash(f'Account created for {form.username.data}!', 'success')
        return redirect(url_for('home'))
    return render_template('register.html', title='Register', form=form)


@app.route("/login", methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        if form.email.data == 'admin@blog.com' and form.password.data == 'password':
            flash('You have been logged in!', 'success')
            return redirect(url_for('home'))
        else:
            flash('Login Unsuccessful. Please check username and password', 'danger')
    return render_template('login.html', title='Login', form=form)


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

score 0 · Answer 1 · answered Sep 19 '21 at 15:10

Use this to parse it first(python3):

import urllib
uri = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 17.0};SERVER=sqlserver_name;DATABASE=db_name;UID=user_id;PWD=password")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % uri)

and then try to create_all using the db object(basically a SQLalchemy object).

More on this available here on flask sqlalchemy documentation.

Also to get an understanding of the engine please see this: https://docs.sqlalchemy.org/en/14/core/engines.html#microsoft-sql-server

Issue while working with SQLAlchemy MSSQL connection

1 Answers1