Sqlalchemy python flask postgresql

Working with PostgreSQL and flask_sqlalchemy

In this article, we’ll be looking at creating and connecting a simple web app built with flask ; a python based microframework, to a PostgreSQL database. Here we’ll be making use of sqlalchemy which is an object-relational mapper for python. Without further ado, let us get right into it. PS: I assume you already have python and flask installed so I won’t be going into details about that. If you don’t, you can visit the python website to download it then run the following command in your terminal to install flask.

What is PostgreSQL?

According to its Wikipedia page, «PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance» -Wikipedia What distinguishes Postgres from other relational databases like MySQL is the fact that it is object-oriented and ships with more use cases and features which is important to run some applications. I am currently working on a Linux operating system (Parrot to be specific). So we’ll be going through these processes the Linux way. Cheers.

Installing Postgres

If like me, you are using the latest version of your Linux distro, then it is very likely that you already have Postgres installed on your machine. Then you can skip to this. If you don’t have it installed, however, you can go through the process below to install it. First, create the file repository configuration

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' 

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 

$ sudo apt-get install postgresql 

Starting The Server

With the installation done, we can then proceed to start the Postgres server by running the following in our terminal

$ sudo service postgresql start [sudo] password for user: $ 

Once this runs without any issue, we know our server is running. With this, let us proceed to create our user and then our database. We are creating a new user here because it is bad practice to use the postgres user (personal opinion though). To do this, we enter the following to get into the postgres cli.

postgres@parrot:~$psql . . postgres=# 

this takes us to an interactive postgres environment where we can run general SQL queries and also postgres specific commands.

Creating a user

postgres=# CREATE ROLE user WITH CREATEDB LOGIN PASSWORD 'password'; 

Here, we created a user with the ability to create databases and also gave the user a login password (password). We can then create a database for this particular user by doing

postgres=# CREATE DATABASE ourdb WITH OWNER user; 

with this done, we have created a database named ourdb for user . Exit the environment by typing \q and exit one after the other. To read more on PostgreSQL, visit the page here

Creating our web app

Next, we’ll be creating our simple web app with flask, a microframework for the python programming language. Read more about it here. We will set up a simple app by doing

from flask import Flask app = Flask(__name__) @app.route('/') def index(): return 'Hello, World!' if __name__ == "__main__": app.run() 

We would be using flask_sqlalchemy to connect to our Postgres database we created earlier. But first,

What is Flask_sqlalchemy?

«Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks.» — PyPi

SQLAlchemy?

«SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.» SQLAlchemy With that, we can proceed to writing our configurations, creating our database tables (also known as models ), and connecting to the database. We make our flask app more workable by adding the following lines

from flask import Flask from flask_sqlalchemy import SQLAlchemy # 1 app = Flask(__name__) app.config['SECRET_KEY'] = "xxxxxxxx" # 2 db = SQLAlchemy(app) # our database uri username = "user" password = "password" dbname = "ourdb" app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://:@localhost:5432/" if __name__ == "__main__": app.run() 

# basic model class User(db.Model): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(64), unique=True, index=True) password = db.Column(db.String(128)) if __name__ == "__main__": app.run() 

With this done, we can then create our tables for the database by proceeding to the terminal. However, before we do that, we have to install a library to help python connect to the PostgreSQL database. The library is known as psycopg2 . We can install this by running the code below in the terminal

$ pip install psycopg2-binary 

With that installed, we can proceed to create our table. The connection to the database takes place here. We open a python shell session in the terminal and run the following commands

$ python . >>>from ourapp import app, db, User >>>app.app_context().push() >>>db.create_all() >>> 

As long as this runs without any errors, we have successfully connected to our database and created the User table. We can then populate the DB either by going to our database and running the SQL commands for that i.e

Then we enter the database ourdb owned by user with the following command. (The password would be required here. For us, the password is password ) psql -h -d -U -p
i.e

postgres@parrot:~$ psql -h localhost -d ourdb -U user -p 5432 

This would ask for our password then take us to an interactive command-line environment where we can run SQL commands to populate our database. We can also do that from our python shell as flask_sqlalchemy allows us to do that.

>>> from ourapp import app, db, User >>>app.app_context().push() >>>db.create_all() >>> >>>user = User(email="example@email.com", password="password_hash") >>>db.session.add(user) >>>db.session.commit() >>> 

Conclusion

In this article, we learned about PostgreSQL, how to install and work with it. We also created a simple flask app and saw how to create tables, connect our flask app to the database and populate the database. I trust with this you are capable of setting up your flask projects for use with the PostgreSQL database. If you have any questions, you can contact me on Twitter @agba_dr3. Cheers!

Источник

Flask PostgreSQL – SQLAlchemy

In this article, we will learn to connect our Flask Application with PostgreSQL Database systems using an ORM – Object Relational Mapper, called Flask SQLAlchemy.

What is PostgreSQL?

Similar to the MySQL Database management system, PostgreSQL is another type of RDBMS used for accessing, storing, and handling the data in the form of database tables.

PostgreSQL also uses SQL- Structured Query Language to access and handle databases and also perform various Tasks in PostgreSQL

The Basic Structure of PostgreSQL

Data is stored inside Postgres DB in the form of Table. A typical Postgres Table looks like this:

2. Installing psycopg2 adapter tool

We also need pyscopg2, which is the PostgreSQL database adapter for Python. Let’s run the pip command:

pip install psycopg2-binary

3. Installing ORM packages for Flask

First we need to install Flask-SQLAlchemy ORM.

To install the package, simply run the code:

pip install flask-sqlalchemy

Also we need to install Flask-Migrate.

Flask-Migrate, uses Alembic which is a light Database migration tool. It helps us to Create/Update Databases and Tables. It also allows us to update an existing Table incase you delete or create new Table Fields.

To install Flask-Migrate, run:

That is we need !! Now let’s get our hands dirty !!

Implementing a PostgreSQL database connection in Flask with SQLAlchemy

In this section, we will create a simple Flask application that stores user information in the Database.

1. Creating a Flask Model

A Model is a Python Class that represents a table in the Database. It contains information regarding the Table structure.

In Flask, it is more systematic to save all the DB information and the models in a separate file called – models.py situated right beside our main application file.

A typical models.py file looks like:

from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() class Model_name(db.Model): __tablename__ = 'table_name' field1_name = db.Column(db.Field1Type, primary_key. ) field2_name = db.Column(db.Field2Type) field3_name = db.Column(db.Field3Type) def __init__(self, Field1_name,Field1_name,Field1_name): self.field1_name = field1_name self.field2_name = field2_name self.field3_name = field3_name def __repr__(self): return f""

This is similar to a classic Python Class. These indicate the Fields of the Table and their representation.

Therefore, let us build a small InfoModel Table to store user information:

from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() class InfoModel(db.Model): __tablename__ = 'info_table' primary_key = True) name = db.Column(db.String()) age = db.Column(db.Integer()) def __init__(self, name,age): self.name = name self.age = age def __repr__(self): return f":"

2. Coding our Main Flask Application

Now we will connect Postgres with our Flask Application. The syntax is :

from flask_sqlalchemy import SQLAlchemy from flask_migrate import Migrate from models import db, InfoModel app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://:@:5432/" app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db.init_app(app) migrate = Migrate(app, db) #general Flask Code @app.route('') # Your code if __name__ == '__main__': app.run(debug=True)

• We create a Flask object – app
• Then configure the PostgreSQL Connection
• I have kept SQL_TRACK_MODIFICATIONS to False just for simplicity.
• Then pass on app object to the SQLAlchemy object db
• Create a Migrate Object for migrations.

Also add the below Views in the app.py file.

from flask import Flask,render_template,request from flask_migrate import Migrate from models import db, InfoModel app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://postgres:[email protected]:5432/flask" app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db.init_app(app) migrate = Migrate(app, db) @app.route('/form') def form(): return render_template('form.html') @app.route('/login', methods = ['POST', 'GET']) def login(): if request.method == 'GET': return "Login via the login Form" if request.method == 'POST': name = request.form['name'] age = request.form['age'] new_user = InfoModel(name=name, age=age) db.session.add(new_user) db.session.commit() return f"Done!!" if __name__ == '__main__': app.run(debug=True)

We can interact with the Table just like a Class Object. We use:

• db.session.add() to add new data
• db.session.comit() to save the changes

3. Implementing the Flask Code

The last thing left is to run the migrations. Hence run the commands:

python db init python db migrate python db upgrade

And lets check the Browser. Go to “/form“

That’s it now in our PostgreSQL shell, Type:

And the data will be right there!!

Conclusion

That’s it, guys!! This was all about setting up Flask PostgreSQL and SQLAlchemy connections. See you the next time !! Till then, Happy Coding!!

Источник