Connecting to PostgreSQL on Raspberry via Python

In this post I will explain how to connect to PostgreSQL on Raspberry PI using python. Python is a good language for scripting, so it could be useful to include our psql statements inside a python script, without the requirement of having psql installed on client machine.

I had already explained on a previous post how to accept incoming connections to a PostgreSQL server inside a Raspberry PI.

From client side, now I will explain how to use fabric to streamline the use of SSH:

1) Inside a python environment, launch

pip install fabric

or, using apt package manager:

sudo apt-get install fabric

2) Make a “fabfile” (eg: myfabfile.py), include fabric.api, then insert into your python module:

from fabric.api import sudo

@task
def my_module():
env.user = ‘pi’ # !set! fabric environment info for the connection to the raspberry PI
env.hosts = ‘XXX.XXX.XX.XXX’
env.password = ‘raspberry’

database = ‘thedatabase’ # user module variables
port = ‘theport’
dbuser = ‘thedatabaseuser’
command = ‘some_statements_to_be_executed’
output = ‘~/file.out’

sudo(‘psql -d {database} -U {user} -p {port} -c “{command}” -o {output}’.format(**vars())

where XXX.XXX.X.XXX is the IP address of Raspberry PI inside the LAN. The -c option of the psql command is used to execute SQL statements, and -o option is used to print results into an output file.

3) As final step, launch your fabric module with the fab inside a python environment in your terminal session:

fab myfabfile.my_module

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>