1

I am connecting to the MS SQL Server database (MS SQL Server 2017) with Flask-SQLAlchemy. The connection string works well when the database was not specified. It could connect to the default database (master) and the tables could be created.

# This works fine
app.config["SQLALCHEMY_DATABASE_URI"]= "mssql+pyodbc://<myuser>:<mypw>@SQL2017"
db = SQLAlchemy(app)

class VideoModel(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(100), nullable = False)
    views = db.Column(db.Integer, nullable = False)
    likes = db.Column(db.Integer, nullable = False)

    def __repr__(self) -> str:
        return f"Video(name = {name}, views = {views}, likes = {likes})"

db.create_all()

However, when the database is specified in the connection string. The errors like below was thrown. The error is also there even when the database has been created manually in the DBMS.

# This does not work
app.config["SQLALCHEMY_DATABASE_URI"]= "mssql+pyodbc://<myuser>:<mypw>@SQL2017/videosDB"
db = SQLAlchemy(app)

class VideoModel(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(100), nullable = False)
    views = db.Column(db.Integer, nullable = False)
    likes = db.Column(db.Integer, nullable = False)

    def __repr__(self) -> str:
        return f"Video(name = {name}, views = {views}, likes = {likes})"

db.create_all()

The error:

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: http://sqlalche.me/e/13/rvf5)

Besides, specifying a database when creating engine is working fine.

# This works fine
cargs = {'database': "quotesDB"}
self.engine = create_engine("mssql+pyodbc://<myuser>:<mypw>@SQL2017", connect_args=cargs)
ShengXue
  • 55
  • 1
  • 7

1 Answers1

0

Your database connection string should be as below:

app.config["SQLALCHEMY_DATABASE_URI"] = "mssql+pyodbc://<myuser>:<mypw>@SQL2017/videosDB?driver=ODBC+Driver+17+for+SQL+Server"

Which is nicely explained below resource:

Connecting to SQL Server 2012 using sqlalchemy and pyodbc

Hope this helps and that you get your db connection working.

begin
  • 46
  • 3