Peewee - Using PostgreSQL

Peewee supports PostgreSQL database as well. It has PostgresqlDatabase class for that purpose. In this chapter, we shall see how we can connect to Postgres database and create a table in it, with the help of Peewee model.

As in case of MySQL, it is not possible to create database on Postgres server with Peewee’s functionality. The database has to be created manually using Postgres shell or PgAdmin tool.

First, we need to install Postgres server. For windows OS, we can download https://get.enterprisedb.com/postgresql/postgresql-13.1-1-windows-x64.exe and install.

Next, install Python driver for Postgres – Psycopg2 package using pip installer.

pip install psycopg2

Then start the server, either from PgAdmin tool or psql shell. We are now in a position to create a database. Run following Python script to create mydatabase on Postgres server.

import psycopg2

conn = psycopg2.connect(host='localhost', user='postgres', password='postgres')
conn.cursor().execute('CREATE DATABASE mydatabase')
conn.close()

Check that the database is created. In psql shell, it can be verified with \l command −

To declare MyUser model and create a table of same name in above database, run following Python code −

from peewee import *

db = PostgresqlDatabase('mydatabase', host='localhost', port=5432, user='postgres', password='postgres')
class MyUser (Model):
   name=TextField()
   city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])
   age=IntegerField()
   class Meta:
      database=db
      db_table='MyUser'

db.connect()
db.create_tables([MyUser])

We can verify that table is created. Inside the shell, connect to mydatabase and get list of tables in it.

To check structure of newly created MyUser database, run following query in the shell.