How can I initialize a database table with Flask / SQLalchemy / Alembic?

Question

I have a database table of languages with relatively static content:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Language(db.Model):
    __tablename__ = 'languages'

    # Fields
    code = db.Column(db.String(2), primary_key=True)
    name = db.Column(db.String(40))
    native = db.Column(db.String(60))
    rtl = db.Column(db.Boolean())  # right to left

The data is in a CSV. I want to insert it into the database. Can I do this with alembic?

I initialize all tables (the structure, not the data) with

$ flask db init
$ flask db migrate
$ flask db update

(Side-question: Should this be in the database in the first place, or maybe rather as a CSV in the code? I use it in a CMS to allow users to specify which languages the speak / which language the page is they've created.)

My Try

Providing an endpoint which adds the stuff when called:

@app.route('/secret_init_languages')
def db_init_languages():
    from mpu.string import str2bool
    import csv
    path = resource_filename('my_package', 'static/languages.csv')
    nb_languages = 0
    with open(path, 'rt', newline='') as csvfile:
        csvreader = csv.reader(csvfile, delimiter=',')
        next(csvreader, None)  # skip the headers
        for row in csvreader:
            lang = Language(code=row[0],
                            name=row[1],
                            native=row[2],
                            rtl=str2bool(row[3]))
            db.session.add(lang)
            nb_languages += 1
        db.session.commit()
    return 'Added {} languages'.format(nb_languages)

Drawbacks of this solution:

• I have an endpoint which should only be called once
• I could forget to run it

Answer 1

Not sure if you have resolved this yet, I've had a similar problem initializing a database table with a csv using Flask and SQLAlchemy. The following solution worked for me (not using alembic):

from main.app import app
from main.db import db
from models.table import TableModel
from pandas import read_csv

db.init_app(app)

@app.before_first_request
def create_tables():
    db.create_all()
    # Check if the existing table contain data, if not then initialize with csv insert
    s = db.session()
    if len(s.query(TableModel).all()) == 0:
        print('No data in the table detected.')
        print('Initialising the table in database.')
        engine = s.get_bind()
        df = read_csv('./table.csv')
        df.to_sql('table',
                  con=engine,
                  if_exists='append',
                  chunksize=1000,
                  index=False)

app.run(port=5000, debug=True)

Note the code above is contained in a run.py script, which calls the flask app object and the SQLAlchemy object (from flask-sqlalchemy) from the main.app and main.db modules respectively. TableModel is the class mapping to the desired table in the database. I used the @app.before_first_request decorator together with pandas.DataFrame.to_sql method so that the initialization of the database (including insertion of data from csv) will only be performed once when the app fires up initially. I'm not sure if alembic is a requirement for you though, but pandas did work for me and I hope this helps.