using Flask, python and postgresql how can I connect to a pre-existing database?

Question

I want to connect to a pre-existing postgres database which has no models associated with it in my app. Unsurprisingly perhaps, this is proving troublesome added to which this is my first attempt with Python and Flask.

The app/py code is:

import os
from flask import Flask
from flask import render_template
from flask.ext.sqlalchemy import SQLAlchemy  
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://blah:[email protected]:5432/mydb'
db = SQLAlchemy(app)
db.create_all()
db.session.commit()

@app.route("/")
def main():
    return render_template('index.html')


@app.route('/base')
def base():
    myusers = users.all()
    return render_template('base.html')

if __name__ == '__main__':
    app.run(debug=True)

the def main() works so that much I have achieved.

In the view (base.html) we have:

{% for user in myusers %}
    <div><p>{{ user.first_name }} </b></p></div>
    {% endfor %}

When I go to the base.html page the persistent error is NameError: global name 'users' is not defined

What, of many things probably, am I doing wrong here? All help appreciated. Thank you.

pjcunningham · Accepted Answer · 2015-10-24 13:22:26Z

15

You need to reverse engineer your existing database to generate the python models that SQLAlchemy can use.

sqlacodegen can do it for you, install it using pip - pip install sqlacodegen

From the command prompt you can generate your models with the following:

sqlacodegen --outfile c:/temp/models.py postgres://blah:[email protected]:5432/mydb

Copy the generated models.py file into your project folder and don't forget to import the model classes you need, e.g. your User class.

Check the models.py file to see how the tables were converted into classes and especially the casing of the generated class names - e.g "User" or "user".

You need to pass the "myusers" variable to your template, assuming the generated user class in models.py is called "User":

@app.route('/base')
def base():
    myusers = db.session.query(User).all()
    return render_template('base.html', myusers=myusers)

Don't forget to import the user model at the top of the app.py file (you need to check the name of the user class that was generated by sqlacodgen):

from models import User

edited Oct 24, 2015 at 13:22

answered Oct 24, 2015 at 11:11

pjcunningham

8,0861 gold badge42 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1903663 Over a year ago

thanks I will give it a shot and then run python models.py ?

user1903663 Over a year ago

thanks it produced a beautiful file as you suggested. What do I do now? Sorry to be such a dummy. So I import into my app file or do python run models,py? Or something else ...?

user1903663 Over a year ago

many thanks, works a treat. I am much obliged to you. But still got tons to learn here!

Collectives™ on Stack Overflow

using Flask, python and postgresql how can I connect to a pre-existing database?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related