How to connect Flask with SQL Database
Flask SQLAlchemy
SQLAlchemy is Python SQL Toolkit and lets us fully usage to SQL databases. Flask SQLAlchemy is to connect flask app with SQL database.
Quickstart
main.py
from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['ENV'] = 'development'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///library.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(100), nullable=False, unique=True)
password = db.Column(db.String(100), nullable=False)
db.create_all()
@app.route("/")
def home():
return "<a href='/add'>add data</a><br><a href='/update'>update data</a><br><a href='/delete'>delete data</a>"
@app.route("/add")
def add():
user = User.query.filter_by(username=request.args['username']).first()
if user:
return "user already existed"
else:
new_user = User(
username=request.args['username'],
password=request.args['password']
)
db.session.add(new_user)
db.session.commit()
return "successfully added new user"
@app.route("/update")
def update():
print(request.args)
try:
update_user = User.query.get(request.args['id'])
except:
return "there is no user with id 1"
else:
update_user.password = request.args['password']
db.session.add(update_user)
db.session.commit()
return "successfully updated user"
@app.route("/delete")
def delete():
try:
delete_user = User.query.get(request.args['id'])
except:
return "there is no user with id 1"
else:
db.session.delete(delete_user)
db.session.commit()
return "successfully deleted"
if __name__ == "__main__":
app.run(debug=True)- Add New user
http://127.0.0.1:5000/add?username=amy@gmail.com&password=pa$$word
- Update Password
http://127.0.0.1:5000/update?id=1&password=qwerty
- Delete user
Connect SQL database
- install flask_sqlalchemy
pip install flask_sqlalchemy- import sqlalchemy
from flask_sqlalchemy import SQLAlchemy- connect url
Flask SQLAlchemy support many sql databases. You can connect url with flask app according to type of your database. Connection URI Format. In this case, I use sqlite with url sqlite:///{db_name}. I create library database to store user data.
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///library.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = FalseThere will be a warning, if there is no Track Modifications config key. That warning wants us to set True or False to TRACK_MODIFICATIONS. In our case, we don’t need it so you can set it to False.
- connect database with flask
db = SQLAlchemy(app)Create database
db.create_all()
If you use sqlite db type, there will be a database file in our project folder. You can open with DB Browser for SQLite. You can freely download it and can edit and view table and data with it.
Create table
Now in our library database, create a table name user.
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(100),nullable=False, unique=True)
password = db.Column(db.String(100),nullable=False)
db.create_all()
CRUD
- create data
new_user = User(
username="bb@gmail.com",
password="password"
)
db.session.add(new_user)
db.session.commit()
- read all data
User.query.all()- read match data
User.query.filter_by(username="bb@gmail.com").first()- read with id
User.query.get(1)- update data
update user password with id of 1 (id=1)
update_user = User.query.get(1)update_user.password = "pa$$word"
db.session.add(update_user)
db.session.commit()
- delete data
delete user of id=1
delete_user = User.query.get(1)
db.session.delete(delete_user)
db.session.commit()
Database Relationship
One to Many relationship
In my example book table, there are two columns title and author.
class Book(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
author_name = db.Column(db.String(100), nullable=False)
db.create_all()
In author_name column, there is repeating data in book table. It will become more and more as the data grows. So we have to split into two table. In our situation we will only test that one author can have many books and a book have only one author.
- split Into Author Table and Book Table.
class Author(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
class Book(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)2. connect As One to Many Relationship
class Author(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
books = db.relationship('Book', backref='author')
class Book(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
db.create_all()- An Author can have many books so we create books column and relationship with Book table.
- backref (backreference) make Book table to have author column.
- Book table need a reference to connect with Author Table. So, we will pass primary key (author_id) to Book table. author_id is a reference from Author table is foreign key. db.ForeignKey(‘author.id’) . author.id must be in lower case.
3. create new author
new_author = Author(
name = "Allen B. Downey"
)
db.session.add(new_author)
db.session.commit()
4. create new book
author = Author.query.filter_by(name="Cay S. Horstmann").first()
new_book = Book(
title = "Core Java Volume I",
author = author
)
db.session.add(new_book)
db.session.commit()we don’t pass author id directly to author_id when creating new book. We will pass the whole author object(backref from Author table)and then new book object we created will save the author.id as author_id.
5. show all Cay S. Horstmann’s book in database
author = Author.query.filter_by(name='Cay S. Horstmann').first()
print(author.books)
Many to Many relationship
We have User Table, Author Table and Book Table. Author Table connected with Book Table as one to many relationship.
Now we will connect User Table with Book Table. A User can read many books and many readers can read same book. So we will connect with many to many relationship.
- connecting as many to many relationship
readers = db.Table('readers',
db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
db.Column('book_id', db.Integer, db.ForeignKey('book.id'), primary_key=True)
)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(100), nullable=False, unique=True)
password = db.Column(db.String(100), nullable=False)
books = db.relationship('Book', secondary=readers, backref='reader')
class Author(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
books = db.relationship('Book', backref='author')
class Book(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
db.create_all()Both table is in many relationship, so we can’t connect with reference for both User and Book Table. In this many to many relationship, we need to create helper table to record for all connection between two table.
So we create readers helper table to connect but it is not actually connect. One table will connect with both helper table and relationship table. So User Table connect with both readers and Book Table (db.relationship(‘Book’, secondary=readers, backref=’reader’)
2. create new book
author = Author.query.filter_by(name="Cay S. Horstmann").first()
new_book = Book(
title = "Core Java Volume I",
author = author
)
db.session.add(new_book)
db.session.commit()
3. create new user
new_user = User(
username = "amy@gmail.com",
password = "qwerty",
)
db.session.add(new_user)
db.session.commit()
5. create reader
user = User.query.get(1)
book = Book.query.get(2)user.books.append(book)
db.session.add(user)
db.session.commit()
6. show all users who read book 2
book = Book.query.get(2)print(book.reader)
7. show all books that is read by user 1
user = User.query.get(2)
print(user.books)
