Build a Python application

The following tutorial creates a simple Python application that connects to a YugabyteDB cluster using the psycopg database adapter, performs a few basic database operations — creating a table, inserting data, and running a SQL query — and prints the results to the screen.

Before you begin

This tutorial assumes that you have satisfied the following prerequisites.

YugabyteDB

YugabyteDB is up and running. If you are new to YugabyteDB, you can have YugabyteDB up and running within five minutes by following the steps in Quick start.

Python

Python 3, or later, is installed.

Psycopg database adapter

Psycopg,the popular PostgreSQL database adapter for Python, is installed. To install a binary version of psycopg2, run the following pip3 command.

  1. $ pip3 install psycopg2-binary

For details about using this database adapter, see the Psycopg documentation.

Create the Python application

Create a file yb-sql-helloworld.py and add the following content to it.

  1. import psycopg2
  3. # Create the database connection.
  5. conn = psycopg2.connect("host=127.0.0.1 port=5433 dbname=yugabyte user=yugabyte password=yugabyte")
  7. # Open a cursor to perform database operations.
  8. # The default mode for psycopg2 is "autocommit=false".
  10. conn.set_session(autocommit=True)
  11. cur = conn.cursor()
  13. # Create the table. (It might preexist.)
  15. cur.execute(
  16.   """
  17.   DROP TABLE IF EXISTS employee
  18.   """)
  20. cur.execute(
  21.   """
  22.   CREATE TABLE employee (id int PRIMARY KEY,
  23.                          name varchar,
  24.                          age int,
  25.                          language varchar)
  26.   """)
  27. print("Created table employee")
  28. cur.close()
  30. # Take advantage of ordinary, transactional behavior for DMLs.
  32. conn.set_session(autocommit=False)
  33. cur = conn.cursor()
  35. # Insert a row.
  37. cur.execute("INSERT INTO employee (id, name, age, language) VALUES (%s, %s, %s, %s)",
  38.             (1, 'John', 35, 'Python'))
  39. print("Inserted (id, name, age, language) = (1, 'John', 35, 'Python')")
  41. # Query the row.
  43. cur.execute("SELECT name, age, language FROM employee WHERE id = 1")
  44. row = cur.fetchone()
  45. print("Query returned: %s, %s, %s" % (row[0], row[1], row[2]))
  47. # Commit and close down.
  49. conn.commit()
  50. cur.close()
  51. conn.close()

Run the application

To run the application, run the following Python script you just created.

  1. $ python yb-sql-helloworld.py

You should see the following output.

  1. Created table employee
  2. Inserted (id, name, age, language) = (1, 'John', 35, 'Python')
  3. Query returned: John, 35, Python