Python PostgreSQL example

Python PostgreSQL example

In this post I will describe how you can connect to a  PostgreSQL database  with Psycop2 , SQLAlchemy and Flask.

Update: I have written a  python script which lets you create a CRUD app along with validations in Python and PostgreSQL by simply specifying the  database fields, You can download it from https://github.com/Leo-G/Flask-Scaffold.

Software versions
Python 3.4
PostgreSQL 9.3
Flask 0.10.1
Flask-SQLAlchemy 2.0
psycopg2 2.6
SQLAlchemy 0.9.9
Ubuntu Linux 14.4

Install PostgreSQL 9 on Ubuntu Linux

leo@python-postgresql $ sudo apt-get install postgresql-9.3postgresql-client-9.3postgeresql-contrib-9.3
leo@python-postgresql $ sudo su - postgres
postgres@python-postgresql $ psql
postgres => create database yourdbname;
postgres => create user yourusername;
postgres =>\password yourusername
postgres =>GRANT ALL PRIVILEGES ON DATABASE yourdbname TO yourusername;
postgres =>\q
postgres@python-postgresql $ exit

leo@python-postgresql $ sudo vim /etc/postgresql/9.3/main/pg_hba.conf

#Modify peer to md5
local md5

local md5

# IPv4 local connections:
host all all
127.0.0.1/32 md5
# IPv6 local connections:
host all all
::1/128 md5

leo@python-postgresql $ sudo service postgresql restart

Psycop2

Psycop2 is  a PostgreSQL adapter for python. You don’t need Flask or SQLAlchemy to connect to PostgreSQL you can connect straight away with Psycop2.

Install Psycopg2

leo@python-postgresql $  virtualenv -p /usr/bin/python3.4 venv-3.4
leo@python-postgresql $ source venv-3.4/bin/activate
(venv-3.4)leo@python-postgresql $ pip install psycopg2

Psycop2 sample code


(venv-3.4)leo@python-postgresql $ gedit psycop2_example.py

import psycopg2

import sys

db_con = None

try:

#Create a database session

db_con = psycopg2.connect(database='yourdbname', user='yourusername', password='yourpassword')

#Create a client cursor to execute commands

cursor = db_con.cursor()

cursor.execute("CREATE TABLE customers (id SERIAL PRIMARY KEY, name VARCHAR age INTEGER);")

#The variables placeholder must always be a %s, psycop2 will automatically convert the values to SQL literal

cursor.execute("INSERT INTO customers ( name, AGE) VALUES (%s, %s)",("leo", 26))

db_con.commit()

cursor.execute("SELECT * FROM customers)

print(cursor.fetchone())

except psycopg2.DatabaseError as e: 

print ('Error %s' % e    )

sys.exit(1)

finally:

if db_con:

db_con.close()

Running the script will give you

(venv-3.4)leo@python-postgresql $ python psycop2_example.py

(1,  'leo', 26)

However, if you use only Psycop2 you will need to write a lot of SQL statements,  Scaling your application will be a nightmare with all that SQL and python code.

Introducing SQLAlchemy

SQLAlchemy  is a SQL toolkit which gives you access to ORM’s (Object Relational Mappers). Instead of writing lengthy code with SQL queries you can map your classes to database tables and operate on data with class methods like add, query etc.

Install SQLAlchemy on Ubuntu

(venv-3.4)leo@python-postgresql $ pip install SQLAlchemy

SQLAlchemy PostgreSQL sample code

(venv-3.4)leo@python-postgresql $ gedit sqlalchemy_example.py

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemey.orm import sessionmaker

#Create a DBAPI connection

engine = create_engine('postgresql://leo:qwedsa@localhost:5432/yourdbname')

#Declare an instance of the Base class for mapping tables

Base = declarative_base()

#Map a table to a class by inheriting base class

class Customer(Base):

 __tablename__ = 'customer'

 id = Column(Integer, primary_key=True)
 name = Column(String(1000), nullable=False)
 email = Column(String, unique=True) 

 def __init__(self, name, email):
   self.name = name
   self.email = email

#Create the table using the metadata attribute of the base class
Base.metadata.create_all(engine)

‘’’SQLAlchemy SESSIONS

Sessions give you access to Transactions, whereby on success you can commit the transaction or rollback one incase you encounter an error’’’

Session = sessionmaker(bind=engine)
session = Session()

#Insert multiple data in this session, similarly you can delete
customer = Customer(name='Linux', email='linux@example.com')
customer2=Customer(name='Python', email='python@example.com')

session.add(customer)
session.add(customer2)

try:
 session.commit()
#You can catch exceptions with  SQLAlchemyError base class
except SQLAlchemyError as e:
 session.rollback()
 print (str(e)) 

#Get data
for customer in session.query(Customer).all():
  print ("name of the customer is" ,customer.name)
  print ("email id of the customer is" ,customer.email)

#Close the connection
engine.dispose()

Running this Script will give you

(venv-3.4)leo@python-postgresql $ python sqlalchemy_example.py

name of the customer is Linux

email id of the customer is linux@example.com

name of the customer is Python

email id of the customer is python@example.com

These examples and methods are all good if you are writing command line applications but you will need to use a web framework like  Flask and segregate your code into MVC (Models, Views and Controllers) for better scaling.

If you have never used Flask, I recommend you read this tutorial before moving on.

Install Flask, Flask-SQLAlchemy,Flask-Migrate,Flask-Script

pip install Flask-SQLAlchemyFlask-Migrate Flask-Script

Model

Models contain all the database code.

(venv-3.4)leo@python-postgresql $ gedit model.py
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.sql.expression import text
from sqlalchemy.exc import SQLAlchemyError
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI']= 'postgresql://yourusername:yourpassword@localhost:5432/yourdbname'
app.secret_key = 'some_secret'
db = SQLAlchemy(app)

#Create Database migrations
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)

#Create the Post Class
class Post(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    author = db.Column(db.String(255))
    title = db.Column(db.String(255),nullable=False)
    created_on=db.Column(db.TIMESTAMP,server_default=db.func.current_timestamp())
    content = db.Column(db.Text)
    published = db.Column(db.Boolean, server_default='True', nullable=False)    

    def __init__(self, author,title, content,published):
        self.author = author
        self.title = title
        self.content=content
        self.published=published

    def add(self,post):
        db.session.add(post)
        return session_commit()

    def update(self):
        return session_commit()

    def delete(self,post):
        db.session.delete(post)
        return session_commit()

def  session_commit():
    try:
    db.session.commit()
    except SQLAlchemyError as e:
    reason=str(e)

if __name__ == '__main__':
manager.run()

Controller
Controller will contain all the python code.

(venv-3.4)leo@python-postgresql $ gedit controller.py
from flask import render_template, request,flash, redirect, url_for
from model import Post, app

#READ
@app.route('/' )
def post_index():
    post = Post.query.all()
    return render_template('index.html', post=post)

#CREATE
@app.route('/add' , methods=['POST', 'GET'])
def post_add():
    if request.method == 'POST':
        post=Post(request.form['author'],request.form['title'],request.form['content'], request.form['published'])
        post_add=post.add(post)
        if not post_add:
            flash("Add was successful")
            return redirect(url_for('post_index'))
        else:
            error=post_add
            flash(error)
    return render_template('add.html')

#UPDATE
@app.route('/update/<id>' , methods=['POST', 'GET'])
def post_update (id):
    #Check if the post exists:
    post = Post.query.get(id)
    if post == None:
        flash("This entry does not exist in the database")
        return redirect(url_for('post_index'))
    if request.method == "POST":
        post.author=request.form['author']
        post.title = request.form['title']
        post.content =  request.form['content']
        post.published=request.form['published']
        post_update=post.update()
        #If post.update does not return an error
        if not post_update:
            flash("Update was successful")
            return redirect(url_for('post_index'))
        else:
            error=post_update
            flash(error)
    return render_template('update.html', post=post)

#DELETE
@app.route('/delete/<id>' , methods=['POST', 'GET'])
def post_delete (id):
    post = Post.query.get(id)
    #Check if the post exists:
    if post == None:
        flash("This entry does not exist in the database")
        return redirect(url_for(post_index))
    post_delete=post.delete(post)
    if not post_delete:
        flash("Post was deleted successfully")
    else:
        error=post_delete
        flash(error)
    return redirect(url_for('post_index'))

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

Views
All your HTML/CSS and jinja code goes here.

(venv-3.4)leo@python-postgresql $ gedit templates/index.html
<--! Start flash message -->
{% with messages = get_flashed_messages() %}
  {% if messages %}
  <ul class=flashes>
    {% for message in messages %}
        <li>{{ message }}</li>
    {% endfor %}
   </ul>
  {% endif %}
{% endwith %}

<--! End flash message -->

<div class="center row">
 <div class="small-8 columns"><p><h4 style="display:inline">Posts  </h4><a href="{{ url_for('post_add')}}">Add new</a></p></div>
</div>

<hr />
<div class="center row">
 <div class="small-12 columns">
  <table>
    <thead><tr><th>Title</th><th>Author</th><th>Content</th><th>Published</th><th>Actions</th></tr></thead>
    <tbody>
    <tr>
    {% for post in post %}
        <td>{{ post.title }}</td>
        <td>{{ post.author }}</td>
        <td>{{ post.content|safe }}</td>
        <td>{{ post.published }}</td>
        <td><a href="{{ url_for('post_update', id=post.id) }}">Edit</a></td>
        <td><a href="{{ url_for('post_delete', id=post.id) }}">Delete</a></td>
       </tr>
    {% endfor %}
    </tbody>
    </table>
   </div>
</div>

(venv-3.4)leo@python-postgresql $ gedit templates/add.html
<--! Start flash message -->
{% with messages = get_flashed_messages() %}
  {% if messages %}
  <ul class=flashes>
    {% for message in messages %}
        <li>{{ message }}</li>
    {% endfor %}
   </ul>
  {% endif %}
{% endwith %}

<--! End flash message -->
<!--Tiny MCE -->
<script src="//tinymce.cachefly.net/4.1/tinymce.min.js"></script>
<script>tinymce.init({paste_data_images: true, paste_as_text: true,selector: '#content', plugins: ["image","code"] });</script>
<!--Tiny MCE end -->

<div class="center row">
  <div class="large-12 columns">

   <fieldset>

        <form action="" method="POST">

            <label>Title  <input type="text" name="title" required/></label><br>
            <label>Author  <input type="text" name="author"  required/></label><br>
            <label>Body<textarea type="text" id="content" name="content" rows="20" ></textarea></label>
            <label>Published
            <select name="published">
            <option value="True"selected>Yes</option>
            <option value="False">No</option>
            </select></label>
            <button class="button "  type="submit">Add</button>
        </form>
      </fieldset>
    </div>
    </div>

(venv-3.4)leo@python-postgresql $ gedit templates/update.html

<--! Start flash message -->
{% with messages = get_flashed_messages() %}
  {% if messages %}
  <ul class=flashes>
    {% for message in messages %}
        <li>{{ message }}</li>
    {% endfor %}
   </ul>
  {% endif %}
{% endwith %}

<!--Tiny MCE -->
<script src="//tinymce.cachefly.net/4.1/tinymce.min.js"></script>
<script>tinymce.init({paste_data_images: true, paste_as_text: true,selector: '#content', plugins: ["image","code"] });</script>
<!--Tiny MCE end -->

<div class="center row">
  <div class="large-12 columns">
    <fieldset>

        <form action="" method="POST">
        <input type="text" name="author" value="{{post.author}}" required/>
        <label>Title<input type="text" name="title" value="{{post.title}}" required/></label>
        <label>Body<textarea type="text" id="content" name="content" rows="20">{{post.content}}</textarea></label>
        <label>Published
        <select name="published">
        {% if post.published == "True" %}
        <option value="True"selected>Yes</option>
        <option value="False">No</option>
        {% else %}
        <option value="True">Yes</option>
        <option value="False"selected>No</option>
        {% endif %}
        </select>
        </label>
        <button class="button "  type="submit">Update</button>
        </form>
      </fieldset>
    </div>
</div>

Run the application

python controller.py

* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

* Restarting with stat

When you resolve http://127.0.0.1:5000/, you should be able to create, read, update and delete posts as shown in the video below.

You should also read my other articles about Database Relationships

One to Many Relationships with SQLAlchemy

Many to Many Relationships with SQLAlchemy

And in case you need to build a Database Driven API with Authentication then you should read:

Building Database Driven API’s in Python with Flask-Restful and SQLAlchemy

Token Based Authentication in Python with Flask

 

 

Follow me

Leo G

Is a Linux Hobbyist and Enthusiast. He Strongly believes in OpenSource Software and would like you to view and download his software at https://github.com/Leo-g
Follow me

  • Michael Lockhart

    to install psycopg2, you also need the postgres development tool pg_config, and the python development library.

    On openSUSE/RPM systems, the packages are postgresql-devel and python3-devel

    On ubuntu/DEB systems, they are called -dev instead

    • Jeffrey Jeanpierre

      I’m on Ubuntu, and I found this necessary as well.

  • Pingback: How to build CRUD app with Python, Flask, SQLAlchemy and MySQL -()

  • Jeffrey Jeanpierre

    Maybe I’m missing something, but running the psycopg2_example.py gives me an error

    NameError: name “psygopg2.DatabaseError” is not defined

    • jeffrey

      It’s pyscopg2, not psygopg2

      • Leo G

        Where do you see psygopg2 ??

  • jeffrey

    what is sessionmaker()? You haven’t imported this anywhere.

    • jeffrey

      Found it, need this line:
      from sqlalchemy.orm import sessionmaker

      • jeffrey

        And you need this line if you want the catch to work:
        from sqlalchemy.exc import SQLAlchemyError

        Did you even test this tutorial?

      • Leo G

        Made the change

  • Pingback: Creating your first Linux App with Python 3 and Flask -()

  • T Gihan

    Hi leo G,
    i want to display logged username/email in home.html. Is there any way to get current user details.