How do I force Flask/SQLAlchemy to create a table?

Question

I'm using Flask with flask.ext.sqlalchemy for the first time. I'm getting an error telling me a table doesn't exist, but I thought the object relational setup would take care of that. Guess not. What am I missing in the following code?

from flask import Flask, request, url_for
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.secret_key = 'This is really unique and secret'

db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://golfape:mypass@mysql.server/golfape$swarm'


class Suggestion(db.Model):
    __tablename__ = 'suggestions'
    id   = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(100))

    def __init__( self, name ):
        self.name = name

@app.route('/')
def get_suggestion():
    return """
        <p>What do you think swarm prediction could be applied to?</p>
        <form method = "POST" action="%s">
             <input name="name" />
             <input type="submit" value="Go!" />
         </form>
         """ % (url_for('append_suggestion'),)


@app.route('/suggestions', methods=['POST'] )
def append_suggestion():
    name = request.form["name"]
    newSuggestion = Suggestion( name = name )
    db.session.add( newSuggestion )
    db.session.commit()
    return """
        <p>Thanks for suggesting %s</p>
        <p><a href="%s">Back</a></p>
        """ % (name, url_for('get_suggestion'))

Here is the error:

File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue ProgrammingError: (ProgrammingError) (1146, "Table 'golfape$swarm.suggestions' doesn't exist") 'INSERT INTO suggestions (name) VALUES (%s)' ('fishing',)

mehdix · Accepted Answer · 2014-08-23T07:05:20.710

4

You have to call db.create_all() once to have sqlalchemy create the schema:

from my_app import Suggestion, db
db.create_all()

Moreover you have to move the database config URL line before initializing db:

app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://golfape:mypass@mysql.server/golfape$swarm'
db = SQLAlchemy(app)

UPDATE The above mentioned problem is about connecting to mysql db and finding proper connector for it. install the following module:

pip install pymysql

and change your connection string like this:

db = SQLAlchemy('mysql+pymysql://user:password@mysql.server/database_name')

edited Aug 23 '14 at 07:05

answered Aug 22 '14 at 07:09

mehdix

4,984
1
28
36

Thank you. However I get this error. ProgrammingError: (ProgrammingError) (1146, "Table 'golfape$swarm.suggestions' doesn't exist") 'INSERT INTO suggestions (name) VALUES (%s)' ('really',) – Golf Monkey Aug 23 '14 at 02:06
Hi @GolfMonkey, check the update on my answer. I tried it on my laptop and it works seamlessly. Here is the link to the original answer that I used: http://stackoverflow.com/a/22252975/157216 – mehdix Aug 23 '14 at 07:06
I think that solution would help if mysql could not be installed. But on www.pythonanywhere.com that's certainly not the case. I can easily connect to the database. It is just that I can't see how to instruct SQLAlchemy what to do. Annoyingly I can't find a decent tutorial on the subject (they all seem to use SQLite). – Golf Monkey Aug 25 '14 at 01:26
@GolfMonkey In my test case SQLAlchemy creates the corresponding tables in mysql db with no errors. Your problem is not clear for me anymore, you might explain more which problems you have now. – mehdix Aug 28 '14 at 14:22

How do I force Flask/SQLAlchemy to create a table?

1 Answers1

Linked