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.

  • Kumar

    I am getting this error : what I am doing wrong.
    I am using this code on cloud foundary.

    ERR Traceback (most recent call last):
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/bin/pip”, line 9, in
    2016-11-01T22:59:14.68-0700 [STG/0] ERR load_entry_point(‘pip==8.1.2’, ‘console_scripts’, ‘pip’)()
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/__init__.py”, line 221, in main
    2016-11-01T22:59:14.68-0700 [STG/0] ERR return command.main(cmd_args)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/basecommand.py”, line 252, in main
    2016-11-01T22:59:14.68-0700 [STG/0] ERR pip_version_check(session)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/utils/outdated.py”, line 102, in pip_version_check
    2016-11-01T22:59:14.68-0700 [STG/0] ERR installed_version = get_installed_version(“pip”)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/utils/__init__.py”, line 848, in get_installed_version
    2016-11-01T22:59:14.68-0700 [STG/0] ERR working_set = pkg_resources.WorkingSet()
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 619, in __init__
    2016-11-01T22:59:14.68-0700 [STG/0] ERR self.add_entry(entry)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 675, in add_entry
    2016-11-01T22:59:14.68-0700 [STG/0] ERR for dist in find_distributions(entry, True):
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1942, in find_eggs_in_zip
    2016-11-01T22:59:14.68-0700 [STG/0] ERR if metadata.has_metadata(‘PKG-INFO’):
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1463, in has_metadata
    2016-11-01T22:59:14.68-0700 [STG/0] ERR return self.egg_info and self._has(self._fn(self.egg_info, name))
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1824, in _has
    2016-11-01T22:59:14.68-0700 [STG/0] ERR return zip_path in self.zipinfo or zip_path in self._index()
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1704, in zipinfo
    2016-11-01T22:59:14.68-0700 [STG/0] ERR return self._zip_manifests.load(self.loader.archive)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1647, in load
    2016-11-01T22:59:14.68-0700 [STG/0] ERR manifest = self.build(path)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1620, in build
    2016-11-01T22:59:14.68-0700 [STG/0] ERR with ContextualZipFile(path) as zfile:
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/site-packages/pip-8.1.2-py2.7.egg/pip/_vendor/pkg_resources/__init__.py”, line 1669, in __new__
    2016-11-01T22:59:14.68-0700 [STG/0] ERR return zipfile.ZipFile(*args, **kwargs)
    2016-11-01T22:59:14.68-0700 [STG/0] ERR File “/app/.heroku/python/lib/python2.7/zipfile.py”, line 756, in __init__
    2016-11-01T22:59:14.68-0700 [STG/0] ERR self.fp = open(file, modeDict[mode])
    2016-11-01T22:59:14.68-0700 [STG/0] ERR IOError: [Errno 21] Is a directory: ‘/app/.heroku/python/lib/python2.7/site-packages/setuptools-23.1.0-py2.7.egg’
    2016-11-01T22:59:14.70-0700 [STG/0] OUT Staging failed: Buildpack compilation step failed
    2016-11-01T22:59:14.85-0700 [API/0] ERR encountered error: App staging failed in the buildpack compile phase
    2016-11-01T23:01:37.04-0700 [API/2] OUT Updated app with guid 8832a4aa-5fd8-4c2c-bbd1-587a545bf2b4 ({“name”=>”s

    • Leo G

      Hey Kumar,

      I am not really sure why you are getting the error, but one thing I notice is that you are using is python 2 and this tutorial is for python 3, can you upgrade you python version and then check?

  • Kumar

    has anyone have working code of this. I am hitting some issues.

    1. rv = self.dispatch_request()
    File “/Users/212462460/Downloads/venv3.5/lib/python3.5/site-packages/flask/app.py”, line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
    File “/Users/212462460/Downloads/testproject/controller.py”, line 18, in post_add
    post_add=post.add(post)
    File “/Users/212462460/Downloads/testproject/model.py”, line 42, in add
    return session_commit()
    NameError: name ‘session_commit’ is not defined
    127.0.0.1 – – [02/Nov/2016 18:57:55] “GET /add?__debugger__=yes&cmd=resource&f=style.css HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 18:57:55] “GET /add?__debugger__=yes&cmd=resource&f=jquery.js HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 18:57:55] “GET /add?__debugger__=yes&cmd=resource&f=debugger.js HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 18:57:55] “GET /add?__debugger__=yes&cmd=resource&f=ubuntu.ttf HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 18:57:55] “GET /add?__debugger__=yes&cmd=resource&f=console.png HTTP/1.1” 200 –
    * Detected change in ‘/Users/212462460/Downloads/testproject/model.py’, reloading
    * Restarting with stat

    2. File “/Users/212462460/Downloads/venv3.5/lib/python3.5/site-packages/sqlalchemy/engine/default.py”, line 462, in do_execute
    cursor.execute(statement, parameters)
    sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation “Post” does not exist
    LINE 2: FROM “Post”
    ^
    [SQL: ‘SELECT “Post”.id AS “Post_id”, “Post”.author AS “Post_author”, “Post”.title AS “Post_title”, “Post”.created_on AS “Post_created_on”, “Post”.content AS “Post_content”, “Post”.published AS “Post_published” nFROM “Post”‘]
    127.0.0.1 – – [02/Nov/2016 19:09:06] “GET /?__debugger__=yes&cmd=resource&f=style.css HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 19:09:06] “GET /?__debugger__=yes&cmd=resource&f=jquery.js HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 19:09:06] “GET /?__debugger__=yes&cmd=resource&f=debugger.js HTTP/1.1” 200 –
    127.0.0.1 – – [02/Nov/2016 19:09:06] “GET /?__debugger__=yes&cmd=resource&f=ubuntu.ttf HTTP/1.1” 200 –

    I have table the table in postgres in schema called public.

    I am using python3.5 env.