2

I have a Python webapp that is connected to SQL Server database using SQLalchemy migration.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager
import urllib

SQLALCHEMY_TRACK_MODIFICATIONS = False

app = Flask(__name__)
app.config['SECRET_KEY'] = '*******************'

params = urllib.parse.quote_plus('DRIVER={SQL Server};SERVER=SOMESERVER;DATABASE=PD;Trusted_Connection=yes;')
app.config['SQLALCHEMY_DATABASE_URI'] = "mssql+pyodbc:///?odbc_connect=%s" % params
db = SQLAlchemy(app)

now the database has a few tables with some values and I have a registration page and a login page that should use that. but I only know that I can use a model.py file that models the table so that I can use them like this :


class User(db.Model, UserMixin):
    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}')"

When I ran the files, the webapp launched normally, but when I entered a form (login or register) I got this error which, I presume, indicates that the table "User" was not found in the database.

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/14/rvf5)

So should I just change the class name from User to the table name I have in the database, and then name all the fields using the names I have in DB as well? Or is there some other way to just call the table I have in DB and let it fill the "User" class (metaphorically) so that when User is imported, its my DB table that is used?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Daniel
  • 63
  • 7
  • The problem is the database uri. Unfortunately I don't have access to MSSQL so I can't say exactly what is wrong with it. – snakecharmerb Sep 22 '22 at 07:21

1 Answers1

-1
class User(db.Model, UserMixin):
     __table__ = Table('table_name_here')
     # rest of your definition

Should sort you.

EDIT: the URL should be something like:

app.config['SQLALCHEMY_DATABASE_URI'] = "mssql+pyodbc://daneil@server/database?driver=ODBC+Driver+17+for+SQL+Serve"
hd1
  • 33,938
  • 5
  • 80
  • 91
  • do I need to install Table using pip? And when I want to use it in another .py files, does it act like the "User" class i have above? – Daniel Sep 23 '22 at 05:28
  • No, it comes with sqalchemy -- from sqlalchemy import Table – hd1 Sep 23 '22 at 22:37