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

 

 


Comments

Subscribe

Search

Creative Commons License
All Techarena51.com posts Content by Leonard Gonsalves is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Based on a work at http://techarena51.com.
Permissions beyond the scope of this license may be available at http://techarena51.com.