Domain 2 of 4 · Chapter 7 of 12

Connect and query Azure Database for PostgreSQL with SDKs

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Developing AI Cloud Solutions on Azure premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • How a Python client reaches flexible server
  • Choosing a driver: psycopg, asyncpg, or SQLAlchemy
  • Building the connection string
  • TLS and what sslmode actually verifies
  • Authenticating with Microsoft Entra ID
  • Passing parameters safely
  • Reading results back
  • How long your state lives: transaction and session scope
  • Streaming large result sets with a server-side cursor

Python PostgreSQL drivers on the axes that change your code

Propertypsycopg2psycopg 3asyncpg
Placeholder syntax%s and %(name)s%s and %(name)s$1, $2 (positional)
Concurrency modelSynchronous onlySynchronous and asyncio in one packageasyncio only
Where parameters are boundMerged into the statement on the clientSent to the server separately from the statementSent to the server separately from the statement
Prepared statementsOnly if you write PREPARE yourselfAutomatic after prepare_threshold executions on a connectionAutomatic through the statement cache
Client-side poolBasic pools in psycopg2.poolpsycopg_pool, a separately distributed packageasyncpg.create_pool(), part of the driver
SQLAlchemy dialect prefixpostgresql+psycopg2://postgresql+psycopg://postgresql+asyncpg://

Decision tree

Choosing the Python data-access pathDoes the codebase alreadyuse SQLAlchemy?yesKeep SQLAlchemy over a driverpostgresql+psycopg:// or postgresql+asyncpg://noIs the code asyncioall the way down?yesasyncpg$1 placeholders, create_pool()noExtending an existingpsycopg2 codebase?yesStay on psycopg2client-side binding, synchronous onlynopsycopg 3%s placeholders, sync or asyncio
Cheat sheet

Cheat sheet

  • Flexible server requires TLS; sslmode decides how much the client verifies
  • psycopg2 and psycopg (v3) are the mainline drivers; v3 adds native async and pooling
  • On flexible server the login is the plain role name, not user@servername
  • Entra authentication passes an access token as the connection password
  • Entra tokens are short-lived, so the app must fetch a fresh token for new connections
  • An Entra admin and mapped database roles must exist before token auth works
  • Pass parameters with %s placeholders so the driver binds values and blocks SQL injection
  • Read rows with fetchone/fetchmany/fetchall and batch writes with executemany
  • A named (server-side) cursor streams a large result set in batches
  • asyncpg is a high-performance async driver that uses numbered $1 placeholders
  • SQLAlchemy selects the driver through the dialect in its connection URL

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. PgBouncer in Azure Database for PostgreSQL flexible server
  2. Connection libraries in Azure Database for PostgreSQL flexible server
  3. Quickstart: Use Python to connect and query data in Azure Database for PostgreSQL flexible server
  4. asyncpg: a fast PostgreSQL client library for Python/asyncio
  5. SQLAlchemy 2.0: PostgreSQL dialect
  6. psycopg 3: differences from psycopg2
  7. PostgreSQL: libpq connection strings and parameter keywords
  8. Quickstart: Connect and query by using Azure CLI in Azure Database for PostgreSQL flexible server
  9. Transport Layer Security (TLS) in Azure Database for PostgreSQL flexible server
  10. asyncpg: API reference
  11. Use Microsoft Entra ID authentication in Azure Database for PostgreSQL flexible server
  12. Connect with managed identity in Azure Database for PostgreSQL flexible server
  13. Microsoft Entra authentication in Azure Database for PostgreSQL flexible server
  14. psycopg 3: passing parameters to SQL queries
  15. asyncpg: usage
  16. psycopg 3: cursor classes
  17. psycopg 3: transactions management
  18. psycopg 3: prepared statements
  19. asyncpg: frequently asked questions FAQ
  20. psycopg 3: cursor types
  21. PostgreSQL: DECLARE