For speedier testing it's nicer to use a memory-based sqlite, but it's still necessary once in a while to use MySQL for testing that more closely matches production. To avoid a dry discussion/abstract question, the code below inserts a couple of words and confirms they're in the database, for both types of SQL databases just mentioned.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import unittest
db = SQLAlchemy()
TEST_DATABASE_URL_MEMORY = 'sqlite:///:memory:'
TEST_DATABASE_URL_MYSQL = 'mysql+pymysql://root:@127.0.0.1:3306/somewords'
def create_app(db_url):
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = db_url
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)
return app
class Word(db.Model):
__tablename__ = 'words'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
word = db.Column(db.String(32), index=True)
class TestInsertion(unittest.TestCase):
def manual_set_up(self, db_url):
self.app = create_app(db_url)
self.app_context = self.app.app_context()
self.app_context.push()
db.drop_all()
db.create_all()
def insert(self):
words = ['hello', 'world']
for word in words:
w = Word(word=word)
db.session.add(w)
db.session.commit()
for word in words:
assert Word.query.filter_by(word=word).first() is not None
def test_dbs(self):
for db_url in [TEST_DATABASE_URL_MEMORY,
TEST_DATABASE_URL_MYSQL]:
self.manual_set_up(db_url)
self.insert()
The first (sqlite) passes. The second fails:
E sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1049, "Unknown database 'somewords'")
We can, everytime we run the tests, create the database
> mysql -u root -p
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> create database somewords;
Query OK, 1 row affected (0.02 sec)
mysql> quit;
but this precludes automated testing, and so I'm guessing I'm missing something basic.
How can a test such as the one above be run unattended, by automating the creation of the database?
Update
The test examples for 1.0, 1.1, and the tutorial (flask/examples/tutorial/tests/conftest.py) use tempfile.mkstemp(), which seems like a nice way to go. One needs neither to worry about setting the name and creating the database, nor even to care about the (random and discardable) db name. How/where is the database creation part done?
import tempfile
db_fd, db_path = tempfile.mkstemp()
Creating and dropping databases doesn't seem to be directly supported by sqlalchemy, but it is supported by sqlalchemy-utils: https://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/functions/database.html#create_database.
This will require you to already have a mysql server running, so it won't allow you to control the file placement with tempfile.mkstemp().
Another approach would be to run the mysql command as part of the automated testing.
With some modification to manual_set_up and test_dbs I was able to run code.
For mysql database, I remove database name from the db_url. And db.drop_all() also fails because the database does not exist so I put in try/except and pass the exception here. Then before db.create_all() I create a sqlachemy engine bypassing db_url which does not have database name db.create_engine(db_url).
# your imports ...
import sqlalchemy.exc
#...
TEST_DATABASE_URL_MYSQL = 'mysql+pymysql://root:@127.0.0.1:3306/'
def manual_set_up(self, db_url, db_kind, db_name=None):
if db_kind == "mysql":
self.app = create_app(db_url + db_name)
else:
self.app = create_app(db_url)
self.app_context = self.app.app_context()
self.app_context.push()
try:
db.drop_all()
except sqlalchemy.exc.InternalError as e:
if "unknown database" in str(e.args[0]).lower():
pass
try:
db.create_all()
except sqlalchemy.exc.InternalError as e:
if "unknown database" in str(e.args[0]).lower():
db.create_engine(db_url, {}).execute(f"CREATE DATABASE IF NOT EXISTS {db_name};")
db.create_all()
def test_dbs(self):
for args in [(TEST_DATABASE_URL_MEMORY, "sqlite"),
(TEST_DATABASE_URL_MYSQL, "mysql", "somewords")]:
self.manual_set_up(*args)
self.insert()