Python script to populate a PostgreSQL cluster with some semi random data Raw

README.md

Script to populate a PostgreSQL cluster with databases, tables and rows. It requires python >=2.7. Works also with python3. It requires having psycopg2 installed too

pip install psycopg2
python populate-pg-db.py

populate-pg-db.py

import psycopg2
import string
import random

DATABASES_COUNT = 5
DB_NAME_SIZE = 20

TABLES_COUNT = 4
TABLE_NAME_SIZE = 10

ROWS_COUNT = 500
STRING_NAME_SIZE = 8

WORKERS = 0
PG_HOST = "127.0.0.1"
PG_USER = "postgres"

def generate_random_string(chars):
  return ''.join(random.choice(string.ascii_lowercase + string.digits) for _ in range(chars))

def populate_database(database_name):
  conn = psycopg2.connect("host='%s' dbname='%s' user='%s'" % (PG_HOST, database_name, PG_USER))
  conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
  cursor = conn.cursor()
  for i in range(0, TABLES_COUNT):
    table_name = 't' + generate_random_string(TABLE_NAME_SIZE)
    print("CREATING TABLE %s" % table_name)
    cursor.execute("CREATE TABLE %s (id serial, name varchar(%s) not null)" % (table_name, STRING_NAME_SIZE))
    for j in range(0, ROWS_COUNT):
      cursor.execute("INSERT INTO %s (name) VALUES ('%s')" % (table_name, generate_random_string(STRING_NAME_SIZE)))
  conn.close()

def create_and_populate_database(database_name):
  print("Creating database %s" % database_name)
  conn = psycopg2.connect("host='%s' dbname='postgres' user='%s'" % (PG_HOST, PG_USER))
  conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
  cursor = conn.cursor()
  cursor.execute("CREATE DATABASE %s" % (database_name))
  conn.close()
  populate_database(database_name)

for i in range(0, DATABASES_COUNT):
  create_and_populate_database('d' + generate_random_string(DB_NAME_SIZE))