Working with PostgreSQL databases from Blender

Remote data storage is becoming more and more useful. Various online libraries of models, materials, textures and assets are becoming more and more popular. Databases are most often used to store data in such libraries. PostgreSQL is one of the most advanced and functional open source databases that we can connect to and work with through the Blender Python API.

.py file and .blend file on Patreon

Preparing Blender to work with a PostgreSQL database

The base distribution of Blender lacks modules for connecting to a PostgreSQL database. However, we can install them using the pip package manager.

Let’s install the module packages we need to work directly into the Blender installation directory. This needs to be done only once, then we can access them in any script, addon or just in the Text Editor in Blender using the standard import command.

The most popular module for interacting with a PostgreSQL database is Psycopg.

To install this module, run Blender as an administrator (“Run as Administrator” on Windows or with the root rights on Linux).

Open the Text Editor area and type the following code:

import subprocess
import sys
import os

python_exe = os.path.join(sys.prefix, 'bin', 'python.exe')
target = os.path.join(sys.prefix, 'lib', 'site-packages')
subprocess.call([python_exe, '-m', 'ensurepip'])
subprocess.call([python_exe, '-m', 'pip', 'install', '--upgrade', 'pip'])
subprocess.call([python_exe, '-m', 'pip', 'install', '--upgrade', 'psycopg2-binary', '-t', target])

print('Psycopg2 installed')

import subprocess

import sys

import os

python_exe = os.path.join(sys.prefix, 'bin', 'python.exe')

target = os.path.join(sys.prefix, 'lib', 'site-packages')

subprocess.call([python_exe, '-m', 'ensurepip'])

subprocess.call([python_exe, '-m', 'pip', 'install', '--upgrade', 'pip'])

subprocess.call([python_exe, '-m', 'pip', 'install', '--upgrade', 'psycopg2-binary', '-t', target])

print('Psycopg2 installed')

Execute it by clicking on the “Run Script” button with the arrow.

Once the installation is complete, we can use the installed module by importing it:

import psycopg2

1	import psycopg2

Working with a PostgreSQL database through Blender Python API

To connect to a database, we first need to create a connection.

To establish a connection with the database, we need the following data:

database host address
database username
password for this user
the name of the database to connect to

They can be obtained from the system administrator who installed the database to the server.

Now, let’s create a connection to the database:

import psycopg2

db_host = '_HOST_'
db_name = '_DATABASE_'
db_user = '_USER_'
db_password = '_PASSWORKD_'

con = psycopg2.connect(
    database=db_name,
    user=db_user,
    password=db_password,
    host=db_host
)

print(con)

# <connection object at 0x000001FA94C34...

import psycopg2

db_host = '_HOST_'

db_name = '_DATABASE_'

db_user = '_USER_'

db_password = '_PASSWORKD_'

con = psycopg2.connect(

database=db_name,

user=db_user,

password=db_password,

host=db_host

)

print(con)

# <connection object at 0x000001FA94C34...

All data transferring with the PostgreSQL database is carried out through a special cursor object.

At first, let’s create a table in the database with the “blender_objects” name and two fields – “name” and “location”, in which we will save the sce eobject names and locations.

with con:
    with con.cursor() as cursor:
        cursor.execute('CREATE TABLE IF NOT EXISTS blender_objects ('
                       'id SERIAL PRIMARY KEY,'
                       'name VARCHAR(128) NOT NULL,'
                       'location VARCHAR(128) NOT NULL'
                       ');'
                       )

with con:

with con.cursor() as cursor:

cursor.execute('CREATE TABLE IF NOT EXISTS blender_objects ('

'id SERIAL PRIMARY KEY,'

'name VARCHAR(128) NOT NULL,'

'location VARCHAR(128) NOT NULL'

');'

)

Note that we have used the “with” construct twice.

With connection:

with con:

with con:

This is necessary for the transaction to be automatically committed. Autocommit in this case is called immediately after the completion of the block of code inside “with”.

The connection is not closed and remains open for further use.

With cursor object:

with con.cursor() as cursor:

1	with con.cursor() as cursor:

This will automatically close the cursor when the code inside the “with” block completes.

Having created a table, we can fill it with data.

Let’s save the name and location of the active scene object into the table (if the scene is empty, first add any mesh to it, for example, a cube: shift + a – Mesh – Cube)

with con:
    with con.cursor() as cursor:
        cursor.execute('INSERT INTO blender_objects (name, location)'
                       'VALUES(%(name)s, %(location)s);',
                       {
                           'name': bpy.context.object.name,
                           'location': bpy.context.object.location[:]
                       }
                       )

with con:

with con.cursor() as cursor:

cursor.execute('INSERT INTO blender_objects (name, location)'

'VALUES(%(name)s, %(location)s);',

{

'name': bpy.context.object.name,

'location': bpy.context.object.location[:]

}

)

In the INSERT statement, we used two named parameters “%(name)s” and “%(location)s)”, whose values were set in the dictionary following the statement itself. The dictionary keys must match the parameter names in the INSERT statement.

Having opened the table, we can make sure that the data has been successfully entered into it.

Now we can select the data from the table back into Blender:

with con:
    with con.cursor() as cursor:
        cursor.execute('SELECT name, location FROM blender_objects '
                        'WHERE name = %(name)s;',
                       {
                           'name': bpy.context.object.name
                       }
                       )
        rows = cursor.fetchall()
        print(rows)
        bpy.context.object.location = eval(rows[0][1])

# [('Cube', '(1.4741322994232178,1.6460041999816895,0.0)')]

with con:

with con.cursor() as cursor:

cursor.execute('SELECT name, location FROM blender_objects '

'WHERE name = %(name)s;',

{

'name': bpy.context.object.name

}

)

rows = cursor.fetchall()

print(rows)

bpy.context.object.location = eval(rows[0][1])

# [('Cube', '(1.4741322994232178,1.6460041999816895,0.0)')]

We received data from the database and entered the result of the query into the “rows” variable.

Now we can set the location for the active object to be equal to the coordinates received from the base.

At the end close the connection:

con.close()

1	con.close()