How to connect Flask with SQL Database

database (vecteezy image)

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

http://127.0.0.1:5000/delete?id=1

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'] = False

There 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()
library database in our project folder

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()
relationship (vecteezy image)

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.

  1. 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.

  1. 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’)

four table in total
helper table

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()
helper table

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)

--

--

--

Learning javascript and web-development

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Getting Started With Django

Understanding the term “DevOps”.

The moment is here, what we have been anticipating for, @Shade_Protocol has finally launched, we…

What types of testing can you perform on your e-commerce website?

Boost your flutter development with templates

Integrate Razorpay with Angular

iBetYou Integrating Chainlink Price Feeds and VRF to Support A New Gameplay

Get the Medium app

Cho Zin Thet

Cho Zin Thet

Learning javascript and web-development

More from Medium

Reading CSV file from amazon S3 bucket using csv module in Python

Scraping Reddit posts data using PRAW- a Python Package.

Python for Databases

Creating a basic REST API using Flask