SQLAlchemy is database toolkit and Object Relational Mapper for python.Flask-SQLAlchemy is extention of SQLAlchemy to flask framework.

Supported database List:

The main goal of SQLAlchemy is to change the way you think about databases and SQL!.

Here i am not explaining the full feature of SQLAlchemy. We going to see how to use Flask-SQLAlchemy only in this post.

To install SQLAlchemy extension use the below pip command


pip install Flask-SQLAlchemy
Connection URI Format:

Postgres:


postgresql://scott:tiger@localhost/mydatabase

MySQL:


mysql://scott:tiger@localhost/mydatabase

Oracle:


oracle://scott:tiger@127.0.0.1:1521/sidname

SQLite (note the four leading slashes):


sqlite:////absolute/path/to/foo.db

 

After installing the flask extension, create flask application object and configure the database using the credentials.

Below is an example for Mysql connection. First import the necessary objects.


from sqlalchemy import create_engine, MetaData,Table

Then create connection using the URI format , which is mentioned above.


engine = create_engine('mysql+pymysql://root:raja@localhost/test1', convert_unicode=True)
metadata = MetaData(bind=engine)

Connect to particular table using.


users = Table('users', metadata, autoload=True)
###here users is table in mysql database.

Next we going to retrieve some data from database using the above users object.


@app.route("/getdata")
def getdata():
    r = users.select(users.c.id == 1).execute().first()
    return r['name']

Thats all. Now we connected to Mysql database and retrieved some data from table named users.

Actually the table users must had some data or else it will display empty . We can use our own query and execute them using the execute() method.


engine.execute('select * from users where id = :1', [1]).first()

To insert data you can use the insert method. We need to get a connection first so that we can use a transaction.


con = engine.connect() 
###create connection to database
con.execute(users.insert(), name='raja', email='raja@mail.com')

SQLAlchemy will automatically commit. There is no need to issue commit explicitly.

 

Full source code:


from flask import Flask, render_template,request,json
from sqlalchemy import create_engine, MetaData,Table
app = Flask(__name__)

engine = create_engine('mysql+pymysql:////username:password@localhost/db_name', convert_unicode=True)
metadata = MetaData(bind=engine)
users = Table('users', metadata, autoload=True)

@app.route("/getdata")
def getdata():
    r = users.select(users.c.id == 1).execute().first()
    return r['name']

@app.route("/")
def index():
    return "welcome"

if __name__ == "__main__":
    app.run()

Note: Here i used mysql+pymysql in URI. Because there is no native support for mysql. So pymysql is dialect.

You must install any of the below DBAPI , if you dont have access to mysql database, or it throws any error like ImportError: No module named 'MySQLdb'

For PyMySQL example

http://ampersandacademy.com/tutorials/flask-framework-mysql-connection-using-pymysql/