Azure Database for PostgreSQL: Use Python to connect and query data

This quickstart demonstrates how to use Python to connect to an Azure Database for PostgreSQL. It also demonstrates how to use SQL statements to query, insert, update, and delete data in the database from macOS, Ubuntu Linux, and Windows platforms. The steps in this article assume that you are familiar with developing using Python and are new to working with Azure Database for PostgreSQL.

Prerequisites

This quickstart uses the resources created in either of these guides as a starting point:

You also need:

  • python installed
  • pip package installed (pip is already installed if you're working with Python 2 >=2.7.9 or Python 3 >=3.4 binaries downloaded from python.org.

Install the Python connection libraries for PostgreSQL

Install the psycopg2 package, which enables you to connect and query the database. psycopg2 is available on PyPI in the form of wheel packages for the most common platforms (Linux, OSX, Windows). Use pip install to get the binary version of the module including all the dependencies.

  1. On your own computer, launch a command-line interface:
    • On Linux, launch the Bash shell.
    • On macOS, launch the Terminal.
    • On Windows, launch the Command Prompt from the Start Menu.
  2. Ensure that you are using the most current version of pip by running a command such as:

    pip install -U pip
    
  3. Run the following command to install the psycopg2 package:

    pip install psycopg2
    

Get connection information

Get the connection information needed to connect to the Azure Database for PostgreSQL. You need the fully qualified server name and login credentials.

  1. Log in to the Azure portal.
  2. From the left-hand menu in Azure portal, click All resources and search for mypgserver-20170401 (the server you created).
  3. Click the server name mypgserver-20170401.
  4. Select the server's Overview page, and then make a note of the Server name and Server admin login name. Azure Database for PostgreSQL - Server Admin Login
  5. If you forget your server login information, navigate to the Overview page to view the Server admin login name and, if necessary, reset the password.

How to run Python code

This article contains a total of four code samples, each of which performs a specific function. The following instructions indicate how to create a text file, insert a code block, and then save the file so that you can run it later. Be sure to create four separate files, one for each code block.

  • Using your favorite text editor, create a new file.
  • Copy and paste one of the code samples in the following sections into the text file. Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.
  • Save the file with the .py extension (for example postgres.py) into your project folder. If you are running on Windows, be sure to select UTF-8 encoding when saving the file.
  • Launch the Command Prompt, Terminal, or Bash shell and then change the directory to your project folder, for example cd postgres.
  • To run the code, type the Python command followed by the file name, for example Python postgres.py.

Note

Starting in Python version 3, you may see the error SyntaxError: Missing parentheses in call to 'print' when running the following code blocks: If that happens, replace each call to the command print "string" with a function call using parenthesis, such as print("string").

Connect, create table, and insert data

Use the following code to connect and load the data using psycopg2.connect function with INSERT SQL statement. The cursor.execute function is used to execute the SQL query against PostgreSQL database. Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

import psycopg2

# Update connection string information obtained from the portal
host = "mypgserver-20170401.postgres.database.azure.com"
user = "mylogin@mypgserver-20170401"
dbname = "mypgsqldb"
password = "<server_admin_password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print "Connection established"

cursor = conn.cursor()

# Drop previous table of same name if one exists
cursor.execute("DROP TABLE IF EXISTS inventory;")
print "Finished dropping table (if existed)"

# Create table
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print "Finished creating table"

# Insert some data into table
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100))
print "Inserted 3 rows of data"

# Cleanup
conn.commit()
cursor.close()
conn.close()

After the code runs successfully, the output appears as follows:

Command-line output

Read data

Use the following code to read the data inserted using cursor.execute function with SELECT SQL statement. This function accepts a query and returns a result set that can be iterated over with the use of cursor.fetchall(). Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

import psycopg2

# Update connection string information obtained from the portal
host = "mypgserver-20170401.postgres.database.azure.com"
user = "mylogin@mypgserver-20170401"
dbname = "mypgsqldb"
password = "<server_admin_password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print "Connection established"

cursor = conn.cursor()

# Fetch all rows from table
cursor.execute("SELECT * FROM inventory;")
rows = cursor.fetchall()

# Print all rows
for row in rows:
    print "Data row = (%s, %s, %s)" %(str(row[0]), str(row[1]), str(row[2]))

# Cleanup
conn.commit()
cursor.close()
conn.close()

Update data

Use the following code to update the inventory row that you previously inserted using cursor.execute function with UPDATE SQL statement. Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

import psycopg2

# Update connection string information obtained from the portal
host = "mypgserver-20170401.postgres.database.azure.com"
user = "mylogin@mypgserver-20170401"
dbname = "mypgsqldb"
password = "<server_admin_password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print "Connection established"

cursor = conn.cursor()

# Update a data row in the table
cursor.execute("UPDATE inventory SET quantity = %s WHERE name = %s;", (200, "banana"))
print "Updated 1 row of data"

# Cleanup
conn.commit()
cursor.close()
conn.close()

Delete data

Use the following code to delete an inventory item that you previously inserted using cursor.execute function with DELETE SQL statement. Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

import psycopg2

# Update connection string information obtained from the portal
host = "mypgserver-20170401.postgres.database.azure.com"
user = "mylogin@mypgserver-20170401"
dbname = "mypgsqldb"
password = "<server_admin_password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print "Connection established"

cursor = conn.cursor()

# Delete data row from table
cursor.execute("DELETE FROM inventory WHERE name = %s;", ("orange",))
print "Deleted 1 row of data"

# Cleanup
conn.commit()
cursor.close()
conn.close()

Next steps