1

Versions

Mac OSX 10.13.6 (17G65)

AWS RDS MSSQL Instance Select @@VERSION; Microsoft SQL Server 2017

python3 -V Python 3.7.0

pip3 show Flask-SQLAlchemy Name: Flask-SQLAlchemy V: 2.3.2

pip3 show Flask-Migrate Name: Flask-Migrate V: 2.2.1

config.py

class Config(object):
    """Docstring for Config Class."""

    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = (os.environ.get('DATABASE_URL'))

.env

SECRET_KEY=SuperDuperSecretPassKey
DATABASE_URL='mssql+pyodbc://DRIVER={ODBC Driver 17 for SQL Server};SERVER=My-custom-route.a0aaa0000aaaa.us-east-1.rds.amazonaws.com;DATABASE=Databasetoconnectto;UID=JayRizzo;PWD=SuperAwesomePassword!!!'

Problem:

How do I properly set the SQLAlchemy URL in my config?

  1. Encoded or decoded URL?
  2. Can I use a URL String?
  3. Does it have to be a DSN?

Note:

I can connect using a template and a python file directly like Connect to MSSQL Database using Flask-SQLAlchemy and even using pyodbc that I created.

But when I use the env variable, I have gotten errors ranging from:

  1. Values Must be Strings config
  2. Could not parse rfc1738 URL from string '%s'" % name
  3. sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('IM012', '[IM012] [unixODBC][Driver Manager]DRIVER keyword syntax error (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/dbapi)
  4. sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5)

I am Stumped...

JayRizzo
  • 3,234
  • 3
  • 33
  • 49

1 Answers1

4
  • Can I use a URL String?
    • Not that I have been able to get so far.
  • Encoded or decoded URL?
  • Does it have to be a DSN?
    • Yes, So far that is what worked.

USE A DSN

Well, I was able to connect using a DSN which was an interesting treat of research...

  1. Check your odbcinst Drivers and configs
$ odbcinst -j 

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/jkirchoff/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Then View, Customize, Validate your odbcinst.ini & odbc.ini files.

/usr/local/etc/odbcinst.ini

[My_installed_ODBC_driver]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
Driver=/usr/local/Cellar/msodbcsql17/17.2.0.1/lib/libmsodbcsql.17.dylib
UsageCount=1

Important: Please make sure that you check that the paths exist for your drivers!

.

NOTE: Yes, I know that there are two Driver locations, the first is a symlink and the other is the actual location. It still works with either one or both listed. It is just here for the curious.

/usr/local/etc/odbc.ini

[MY_Custom_DSN_NAME]
Driver              = My_installed_ODBC_driver
Description         = My Connection to AWS
Database            = MyDatabaseNameHERE
Server              = My-custom-route.a0aaa0000aaaa.us-east-1.rds.amazonaws.com
Readonly            = No
Port                = 1433
Trace               = No

Make sure to match the naming conventions of your drivers My_installed_ODBC_driver is my example.

Then Set your .env file to contain a connection to the DSN_name as listed above would be

[root of your flask project]/.env

DATABASE_URL='mssql+pyodbc://JayRizzo:SpaceInvaderPassword!@MY_Custom_DSN_NAME'

Note: Thinking about the Linkup, it should be as follows odbcinst.ini supplies driver to > odbc.ini supplies DSN name to > .env[MY_Custom_DSN_NAME]

or

the opposite Env file references odbc and it references the odbcinst for the driver. how ever you want to think about it.

Finally

My_installed_ODBC_driver & MY_Custom_DSN_NAME can be any name you want. just make sure they get matched up correctly, (that was part of my issue, originally with trying the DSN.)

I am unable to use a direct connection url.

I have only been able to connect thus far by using a DSN.

Now I can use SQLAlchemy flask db upgrade & flask db migrate

Any insight would be nice.

Hope you were able to follow my logic.

Links

SQLAlchemy can't connect to an mssql database

JayRizzo
  • 3,234
  • 3
  • 33
  • 49