PostgreSQL is a powerful open source relational database frequently used to create, read, update and delete Python web application data. Psycopg2 is a PostgreSQL database driver that serves as a Python client for access to the PostgreSQL server. This post explains how to install PostgreSQL on Ubuntu 16.04 and run a few basic SQL queries within a Python program.
We won't cover object-relational mappers (ORMs) in this tutorial but these steps can be used as a prerequisite to working with an ORM such as SQLAlchemy or Peewee.
Our walkthrough should work with either Python 2 or 3 although all the steps were tested specifically with Python 3.5. Besides the Python interpreter, here are the other components we'll use:
If you aren't sure how how to install pip and virtualenv, review the first few steps of the how to set up Python 3, Bottle and Green Unicorn on Ubuntu 16.04 LTS guide.
We'll install PostgreSQL via the
apt package manager. There are a few
packages we need since we want to both run PostgreSQL and use the psycopg2
driver with our Python programs. PostgreSQL will also be installed as a
system service so we can start, stop and reload its configuration when
necessary with the
service command. Open the terminal and run:
sudo apt-get install postgresql libpq-dev postgresql-client postgresql-client-common
sudo password when prompted and enter 'yes' when
if you want to install the new packages.
After a few moments
apt will finish downloading, installing and
We now have PostgreSQL installed and the PostgreSQL service is running
in the background. However, we need to create a user and a database instance
to really start using it. Use the
sudo command to switch to the new
sudo -i -u postgres
Within the "postgres" account, create a user from the command line with the
createuser command. PostgreSQL will prompt you with several questions.
Answer "n" to superuser and "y" to the other questions.
createuser matt -P --interactive
Awesome, now we have a PostgreSQL user that matches our Ubuntu login account. Exit out of the postgres account by pressing the "Ctrl" key along with "d" into the shell. We're back in our own user account.
Create a new database we can use for testing. You can name it "testpython" or whatever you want for your application.
Now we can interact with "testpython" via the PostgreSQL command line tool.
psql command line client is useful for connecting directly to our
PostgreSQL server without any Python code. Try out
psql by using this
command at the prompt:
The PostgreSQL client will connect to the localhost server. The client is now ready for input:
Try out PostgreSQL's command prompt a try with commands such as
\dd. We can also run SQL queries such as "SELECT * from testpython",
although that won't give us back any data yet because we have not inserted
any into the database. A full list of PostgreSQL commands can be found in the
Now that PostgreSQL is installed and we have a non-superuser account, we
can install the psycopg2 package. Let's
figure out where our
python3 executable is located, create a virtualenv
python3, activate the virtualenv and then install the psycopg2 package
pip. Find your
python3 executable using the
We will see output like what is in this screenshot.
Create a new virtualenv in either your home directory or wherever you
store your Python virtualenvs. Specify the full path to your
# specify the system python3 installation virtualenv --python=/usr/bin/python3 venvs/postgrestest
Activate the virtualenv.
Next we can install the psycopg2 Python package from
PyPI using the
pip install psycopg2
Sweet, we've got our PostgreSQL driver installed in our virtualenv! We can now test out the installation by writing a few lines of Python code.
Launch the Python REPL with the
python3 command. You can also
write the following code in a Python file such as "testpostgres.py" then
execute it with
python testpostgres.py. Make sure to replace the "user"
and "password" values with your own.
import psycopg2 try: connect_str = "dbname='testpython' user='matt' host='localhost' " + \ "password='myOwnPassword'" # use our connection values to establish a connection conn = psycopg2.connect(connect_str) # create a psycopg2 cursor that can execute queries cursor = conn.cursor() # create a new table with a single column called "name" cursor.execute("""CREATE TABLE tutorials (name char(40));""") # run a SELECT statement - no data in there, but we can try it cursor.execute("""SELECT * from tutorials""") rows = cursor.fetchall() print(rows) except Exception as e: print("Uh oh, can't connect. Invalid dbname, user or password?") print(e)
When we run the above code we won't get anything fancy, just an empty list printed out. However, in those few lines of code we've ensured our connection to our new database works and we can create new tables in it as well as query them.
That's just enough of a hook to get started writing more complicated SQL queries using psycopg2 and PostgreSQL. Make sure to check out the PostgreSQL, relational databases and object-relational mappers (ORMs) pages for more tutorials.