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

  • [アーティクル]

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening 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.

Tip

If you are looking to build a Django Application with PostgreSQL then checkout the tutorial, Deploy a Django web app with PostgreSQL tutorial.

Prerequisites

For this quickstart you need:

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

  • 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.azure.com, 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.

Important

Replace the following values:

  • <server-name> and <admin-username> with the values you copied from the Azure portal.
  • <admin-password> with your server password.
  • <database-name> a default database named postgres was automatically created when you created your server. You can rename that database or create a new database by using SQL commands.

Step 1: Connect and insert data

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

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:

c:\>cd postgres

c:\postgres>python postgres.py
Connection established
Finished dropping table (if existed)
Finished creating table
Inserted 3 rows of data

c:\postgres>

Step 2: Read data

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

# 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