Full Stack Python logo Full Stack Python

全部主题 | Blog | 时讯 | @fullstackpython | Facebook | 源码 # Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 Posted by Matt Makai on 五月 18, 2016. Last updated 八月 10, 2016.

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.

Tools We Need

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.

Install PostgreSQL

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:

  1. sudo apt-get install postgresql libpq-dev postgresql-client postgresql-client-common

Enter your sudo password when prompted and enter 'yes' when apt asks if you want to install the new packages.

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图2

After a few moments apt will finish downloading, installing and processing.

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图3

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 "postgres" account.

  1. 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.

  1. createuser matt -P --interactive

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图4

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.

  1. createdb testpython

Now we can interact with "testpython" via the PostgreSQL command line tool.

Interacting with PostgreSQL

The 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:

  1. psql

The PostgreSQL client will connect to the localhost server. The client is now ready for input:

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图5

Try out PostgreSQL's command prompt a try with commands such as \dt and \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 psql documentation.

Installing psycopg2

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 with python3, activate the virtualenv and then install the psycopg2 package with pip. Find your python3 executable using the which command.

  1. which python3

We will see output like what is in this screenshot.

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图6

Create a new virtualenv in either your home directory or wherever you store your Python virtualenvs. Specify the full path to your python3 installation.

  1. # specify the system python3 installation
  2. virtualenv --python=/usr/bin/python3 venvs/postgrestest

Activate the virtualenv.

  1. source ~/venvs/postgrestest/bin/activate

Next we can install the psycopg2 Python package from PyPI using the pip command.

  1. pip install psycopg2

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图7

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.

Using PostgreSQL from Python

Launch the Python REPL with the python or 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.

  1. import psycopg2
  2.  
  3. try:
  4. connect_str = "dbname='testpython' user='matt' host='localhost' " + \
  5. "password='myOwnPassword'"
  6. # use our connection values to establish a connection
  7. conn = psycopg2.connect(connect_str)
  8. # create a psycopg2 cursor that can execute queries
  9. cursor = conn.cursor()
  10. # create a new table with a single column called "name"
  11. cursor.execute("""CREATE TABLE tutorials (name char(40));""")
  12. # run a SELECT statement - no data in there, but we can try it
  13. cursor.execute("""SELECT * from tutorials""")
  14. rows = cursor.fetchall()
  15. print(rows)
  16. except Exception as e:
  17. print("Uh oh, can't connect. Invalid dbname, user or password?")
  18. 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.

Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04 - 图8

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.

Questions? Tweet @fullstackpython or post a message on the Full Stack Python Facebook page. Something wrong with this post? Fork this page's source on GitHub.


#### 在这里注册以便每月能收到一份邮件资料,内容包含本站的主要更新、教程和 Python 书籍的打折码等。

Learn more about these concepts

PostgreSQL and Ubuntu logos. Copyright their respective owners. Operating Systems Ubuntu Relational Databases PostgreSQL …or view all topics.


This site is based on Matt Makai's project Full Stack Python, thanks for his excellent work!

此网站由 @haiiiiiyun开源爱好者们 共同维护。 若发现错误或想贡献,请访问: Github fullstackpython.cn 项目