2

I'm trying to connect my flask application to my local MySQL database for testing. I've made a flask object and a class to represent an example table to be created after a successful connection.

These are my local env vars for my project:

#.env
LOCAL_MYSQL_URL = mysql://Username:somePassword@127.0.0.1:3306/database_name

This is my project_name __init__ file:

#__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

ON_HEROKU = 'ON_HEROKU' in os.environ

if ON_HEROKU:
    DB_URL = os.environ.get('CLEARDB_DATABASE_URL')
else:
    DB_URL = os.environ.get('LOCAL_MYSQL_URL')

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URL
app.config['SECRET_KEY'] = os.environ.get('SECRET_KEY')

db = SQLAlchemy(app)  
db.create_all()
db.session.commit()

However I get the following error after running the app:

  if sa_url.drivername.startswith('mysql'):
AttributeError: 'NoneType' object has no attribute 'drivername'

I am trying to make this work following tutorials and answers contributed here on stackoverlow to similar questions, but none of them helped me out so far.

What am I missing here? What's wrong with my mysql driver name?

MattSom
  • 2,097
  • 5
  • 31
  • 53
  • Does it raise a `KeyError` if you change this line: `DB_URL = os.environ.get('LOCAL_MYSQL_URL')` to `os.environ['LOCAL_MYSQL_URL']`? – SuperShoot Oct 19 '19 at 23:32
  • @SuperShoot Yes. I've tried it out your way, and I get : `raise KeyError(key) from None KeyError: 'MYSQL_URL'` – MattSom Oct 19 '19 at 23:39
  • `'MYSQL_URL'` or `'LOCAL_MYSQL_URL'` in the `KeyError` message? – SuperShoot Oct 19 '19 at 23:59
  • Sorry, I miscopied it. it is `'LOCAL_MYSQL_URL'` of course. – MattSom Oct 20 '19 at 00:13
  • Well if `os.environ[...]` raises a key error, then the env var isn't set meaning `.env` file isn't getting loaded at runtime. For environment variables, if there isn't a default fallback value to use, I think it is better to use the dict index syntax (`[...]`) rather than `.get()` so that it fails early rather than set the variable to `None`. – SuperShoot Oct 20 '19 at 00:22
  • 2
    "What's wrong with my mysql driver name?": `'NoneType' object has no attribute 'drivername'` means that `sa_url` is `None`, there's nothing wrong with the driver name, just that the url you are passing to flask-sqlalchemy is `None` and `None.drivername` doesn't exist. – SuperShoot Oct 20 '19 at 00:25
  • @SuperShoot I'm not sure I understand. I've just removed any db.method() from the example code and printed out the DB_URL. It showed the `mysql://Username:somePassword@127.0.0.1:3306/database_name` string from the `.env` file. How can this be? – MattSom Oct 20 '19 at 00:26
  • 1
    I have had to use pymysql to get this working. `pip install pymysql` – cinch Oct 20 '19 at 00:26
  • @cinch Thanks for the tip, but I it says I already had that lib. – MattSom Oct 20 '19 at 00:27
  • 1
    @MattSom that is in direct conflict with your comment where you said that you get the key error accessing the env var without `.get()`. I'm not sure where to go from here. – SuperShoot Oct 20 '19 at 00:52
  • @SuperShoot Just tried it again. I do get a `KeyError` without the `.get()` and I do get the string from the `.env` file using it. – MattSom Oct 20 '19 at 01:32

2 Answers2

2

Make sure the SQLAlchemy URI resembles this format after pip install pymysql

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://{DB_USERNAME}:{DB_PASSWORD}@localhost:3306/{DB_NAME}'
cinch
  • 182
  • 10
  • Strange. Why do I need the `+pymysql` driver? According to [the docs](https://flask-sqlalchemy.palletsprojects.com/en/2.x/config/#connection-uri-format) I only need the mysql. I've tried it and I get the same error. – MattSom Oct 20 '19 at 00:45
  • 1
    It is the mysql driver for Python 3. I think you're configuring the uri wrong. Try this ```app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'``` – cinch Oct 20 '19 at 00:50
  • Wow, you are right! I've made a typo after your first suggestion, I wrote `+pymsql` instead of `+pymysql`. Tried again, now the database has the two rows! Thank you, kind sir! :) – MattSom Oct 20 '19 at 01:36
  • This is crazy. Tried it again this morning and throws the same error. For the same code. – MattSom Oct 20 '19 at 11:57
  • Okay, I know what was the problem. First I did not include pymysql driver as cicnh has suggested. On the other hand I did not even use the dotenv modul to read from the `.env` file. For some reason `os.environ.get()` did read the `.env` after it was created (I'm using VS Code), but this morning it had not. So use `dotenv` guys, don't let your development tool trick you. – MattSom Oct 20 '19 at 13:05
0

if you ever use "db.Model.metadata.reflect(db.engine)" in your models.py you will still get the same error message. So change the drivername mysqldb to pymysql as suggest by @MattSom and comment or delete this instruction "db.Model.metadata.reflect(db.engine)"

yossep Binyoum
  • 1
  • 2