Create database

This page discusses issue of creating databases in postgres instance using python scripts.

In the following cell, a postgres instance has been deployed in a docker container. No databases were mentioned during creation. Final command shows databases available in just created postgres instance, there are some database looks like it is required by postgres by default.

%%bash
docker run --rm -d\
    -e POSTGRES_USER=docker_app\
    -e POSTGRES_PASSWORD=docker_app\
    --name test_postgres\
    -p 5431:5432\
    postgres:15.4 &> /dev/null
sleep 5

docker exec test_postgres \
    psql --username docker_app -h localhost -p 5432 -l;

                                                    List of databases
    Name    |   Owner    | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |     Access privileges     
------------+------------+----------+------------+------------+------------+-----------------+---------------------------
 docker_app | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 postgres   | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/docker_app            +
            |            |          |            |            |            |                 | docker_app=CTc/docker_app
 template1  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/docker_app            +
            |            |          |            |            |            |                 | docker_app=CTc/docker_app
(4 rows)

Now the central code for this page - creating new databases with psycopg2. Actually it looks like any other query through psycopg with a few features:

• You don’t need to specify a database to connect to;

• You need to set autocommit = True for the postgres database;

• You can use the standard sql command CREATE DATABASE to create a database with the required name.

So in the following cell I just add 10 new databases to the postgres instance.

import psycopg2

conn = psycopg2.connect(
    port = "5431", # same as when creating a postgres container
    user = "docker_app",
    password = "docker_app",
    host= "localhost"
)

conn.autocommit = True
cur = conn.cursor()
for i in range(10): cur.execute(f'CREATE DATABASE test_database{i+1}')

# close connection
cur.close()
conn.close()

Let’s check the database list now - we have 10 more databases.

%%bash
docker exec test_postgres \
    psql --username docker_app -h localhost -p 5432 -c '\l';

                                                      List of databases
      Name       |   Owner    | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |     Access privileges     
-----------------+------------+----------+------------+------------+------------+-----------------+---------------------------
 docker_app      | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 postgres        | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0       | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/docker_app            +
                 |            |          |            |            |            |                 | docker_app=CTc/docker_app
 template1       | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/docker_app            +
                 |            |          |            |            |            |                 | docker_app=CTc/docker_app
 test_database1  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database10 | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database2  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database3  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database4  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database5  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database6  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database7  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database8  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 test_database9  | docker_app | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
(14 rows)

%%bash
docker stop test_postgres

test_postgres