Quickstart: Use Python to connect and query data in Azure Database for PostgreSQL - Single Server

APPLIES TO: Azure Database for PostgreSQL - Single Server

In this quickstart, you will learn how to connect to the database on Azure Database for PostgreSQL Single Server and run SQL statements to query using Python on macOS, Ubuntu Linux, or Windows.

Prerequisites

For this quickstart you need:

• An Azure account with an active subscription. Create an account for trial.

• Create an Azure Database for PostgreSQL single server using Azure portal
  or Azure CLI if you do not have one.

• Based on whether you are using public or private access, complete ONE of the actions below to enable connectivity.

  Action	Connectivity method	How-to guide
  Configure firewall rules	Public	Portal CLI
  Configure Service Endpoint	Public	Portal CLI
  Configure private link	Private	Portal CLI

• Python 2.7 or 3.6+.

• Latest pip package installer.

• Install psycopg2 using pip install psycopg2-binary in a terminal or command prompt window. For more information, see how to install psycopg2.

Get database connection information

Connecting to an Azure Database for PostgreSQL database requires the fully qualified server name and login credentials. You can get this information from the Azure portal.

1. In the Azure portal, search for and select your Azure Database for PostgreSQL server name.

2. On the server's Overview page, copy the fully qualified Server name and the Admin username. The fully qualified Server name is always of the form <my-server-name>.postgres.database.chinacloudapi.cn, and the Admin username is always of the form <my-admin-username>@<my-server-name>.

   You also need your admin password. If you forget it, you can reset it from this page.

   

Step 1: Connect and insert data

The following code example connects to your Azure Database for PostgreSQL database using

• psycopg2.connect function, and loads data with a SQL INSERT statement.
• cursor.execute function executes the SQL query against the database.

import psycopg2

# Update connection string information

host = "<server-name>"
dbname = "<database-name>"
user = "<admin-username>"
password = "<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 a table

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

# Insert some data into the 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")

# Clean up

conn.commit()
cursor.close()
conn.close()

When the code runs successfully, it produces the following output:

Step 2: Read data

The following code example connects to your Azure Database for PostgreSQL database and uses

• cursor.execute with the SQL SELECT statement to read data.
• cursor.fetchall() accepts a query and returns a result set to iterate over by using

# 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])))

Step 3: Update data

The following code example uses cursor.execute with the SQL UPDATE statement to update data.

# 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")

Step 5: Delete data

The following code example runs cursor.execute with the SQL DELETE statement to delete an inventory item that you previously inserted.

# Delete data row from table

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

Clean up resources

To clean up all resources used during this quickstart, delete the resource group using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes

Related content

• Manage Azure Database for MySQL server using Portal
• Manage Azure Database for MySQL server using CLI