-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpostgresql-python-windows.html
More file actions
128 lines (127 loc) · 18.9 KB
/
postgresql-python-windows.html
File metadata and controls
128 lines (127 loc) · 18.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Connect Python to PostgreSQL on Windows — psycopg2 Setup and Query Guide</title>
<meta name="description" content="How to connect Python to PostgreSQL on Windows using psycopg2. Install driver, connect, run queries, use environment variables for credentials and SQLAlchemy setup.">
<link rel="canonical" href="https://postgre-sql.github.io/postgresql-python-windows.html">
<meta name="last-modified" content="2026-06-08">
<meta property="og:title" content="Connect Python to PostgreSQL on Windows — psycopg2 Setup and Query Guide">
<meta property="og:description" content="How to connect Python to PostgreSQL on Windows using psycopg2. Install driver, connect, run queries, use environment variables for credentials and SQLAlchemy setup.">
<meta property="og:type" content="article">
<meta property="og:url" content="https://postgre-sql.github.io/postgresql-python-windows.html">
<meta property="og:image" content="https://postgre-sql.github.io/og-image.svg">
<meta name="twitter:card" content="summary_large_image">
<link rel="icon" href="/favicon.svg">
<link rel="stylesheet" href="/style.css">
</head>
<body>
<nav class="site-nav"><div class="wrap"><div class="nav-inner">
<a href="/index.html" class="nav-logo"><div class="nav-logo-mark">Pg</div><span class="nav-logo-name">PostgreSQL</span><span class="nav-logo-sub">for Windows</span></a>
<div class="nav-links">
<a href="/index.html" id="nl-home">Home</a>
<a href="/download-windows.html" id="nl-dl">Download</a>
<a href="/offline-installer.html" id="nl-off">Offline</a>
<a href="/configure-windows-service.html" id="nl-svc">Service</a>
<a href="/fix-path-issues.html" id="nl-path">PATH fix</a>
<a href="/pgadmin-windows.html" id="nl-pgadmin">pgAdmin</a>
<a href="/postgresql-windows-faq.html" id="nl-faq">FAQ</a>
</div>
<a href="/download-windows.html" class="nav-cta"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5" stroke-linecap="round" stroke-linejoin="round"><path d="M21 15v4a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2v-4"/><polyline points="7 10 12 15 17 10"/><line x1="12" y1="15" x2="12" y2="3"/></svg> Download</a>
</div></div></nav>
<div class="page-hero"><div class="wrap">
<nav class="breadcrumb"><a href="/index.html">home</a> <span>/</span> <span>postgresql-python-windows</span></nav>
<span class="page-tag">Python guide</span>
<h1>Connect Python to PostgreSQL on Windows — <span>psycopg2 setup & query guide</span></h1>
<p class="page-lead">Connect Python to PostgreSQL on Windows with psycopg2. Install the driver, connect to the database, run parameterised queries and store credentials safely in environment variables.</p>
</div></div>
<div class="wrap"><div class="content-layout">
<main><div class="callout callout-info"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="1.8" stroke-linecap="round" stroke-linejoin="round"><circle cx="12" cy="12" r="9"/><line x1="12" y1="8" x2="12" y2="12"/><line x1="12" y1="16" x2="12.01" y2="16"/></svg><span>PostgreSQL must be running locally or remotely before connecting from Python. Verify with <code>psql -U postgres -c "SELECT version();"</code></span></div>
<div class="section" aria-labelledby="install-h2"><span class="section-label">Install psycopg2</span><h2 id="install-h2">Install the PostgreSQL Python driver</h2>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">cmd.exe</span></div><div class="term-body"><div><span class="t-cm"># Install psycopg2 (pre-compiled binary, easiest on Windows):</span></div><div><span class="t-p">C:\></span> <span class="t-c">pip install psycopg2-binary</span></div><div><span class="t-ok">Successfully installed psycopg2-binary-2.9.9</span></div><div></div><div><span class="t-cm"># Or psycopg3 (modern async-ready version):</span></div><div><span class="t-p">C:\></span> <span class="t-c">pip install "psycopg[binary]"</span></div></div></div>
<div class="callout callout-ok"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="1.8" stroke-linecap="round" stroke-linejoin="round"><polyline points="20 6 9 17 4 12"/></svg><span>Use <code>psycopg2-binary</code> on Windows to avoid compiling from source. The binary wheel includes all dependencies.</span></div>
</div>
<div class="section" aria-labelledby="connect-h2"><span class="section-label">Connect</span><h2 id="connect-h2">Connect Python to PostgreSQL</h2>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">Python</span></div><div class="term-body"><div><span class="t-v">import</span> <span class="t-c">psycopg2</span></div><div></div><div><span class="t-cm"># Connect to local PostgreSQL:</span></div><div><span class="t-v">conn</span> <span class="t-c">= psycopg2.connect(</span></div><div><span class="t-c"> host=</span><span class="t-ok">"localhost"</span><span class="t-c">,</span></div><div><span class="t-c"> port=</span><span class="t-ok">5432</span><span class="t-c">,</span></div><div><span class="t-c"> database=</span><span class="t-ok">"mydb"</span><span class="t-c">,</span></div><div><span class="t-c"> user=</span><span class="t-ok">"postgres"</span><span class="t-c">,</span></div><div><span class="t-c"> password=</span><span class="t-ok">"your_password"</span></div><div><span class="t-c">)</span></div><div></div><div><span class="t-cm"># Or with a connection string:</span></div><div><span class="t-v">conn</span> <span class="t-c">= psycopg2.connect(</span><span class="t-ok">"postgresql://postgres:password@localhost:5432/mydb"</span><span class="t-c">)</span></div></div></div>
</div>
<div class="section" aria-labelledby="query-h2"><span class="section-label">Run queries</span><h2 id="query-h2">Execute SQL queries from Python</h2>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">Python</span></div><div class="term-body"><div><span class="t-v">import</span> <span class="t-c">psycopg2</span></div><div></div><div><span class="t-v">conn</span> <span class="t-c">= psycopg2.connect(host=</span><span class="t-ok">"localhost"</span><span class="t-c">, database=</span><span class="t-ok">"mydb"</span><span class="t-c">, user=</span><span class="t-ok">"postgres"</span><span class="t-c">, password=</span><span class="t-ok">"pw"</span><span class="t-c">)</span></div><div><span class="t-v">cur</span> <span class="t-c">= conn.cursor()</span></div><div></div><div><span class="t-cm"># Create a table:</span></div><div><span class="t-v">cur</span><span class="t-c">.execute(</span><span class="t-ok">"""</span></div><div><span class="t-ok"> CREATE TABLE IF NOT EXISTS users (</span></div><div><span class="t-ok"> id SERIAL PRIMARY KEY,</span></div><div><span class="t-ok"> name TEXT NOT NULL,</span></div><div><span class="t-ok"> email TEXT UNIQUE</span></div><div><span class="t-ok"> )</span></div><div><span class="t-ok">"""</span><span class="t-c">)</span></div><div></div><div><span class="t-cm"># Insert with parameters (safe from SQL injection):</span></div><div><span class="t-v">cur</span><span class="t-c">.execute(</span><span class="t-ok">"INSERT INTO users (name, email) VALUES (%s, %s)"</span><span class="t-c">, (</span><span class="t-ok">"Alice"</span><span class="t-c">, </span><span class="t-ok">"alice@example.com"</span><span class="t-c">))</span></div><div><span class="t-v">conn</span><span class="t-c">.commit()</span></div><div></div><div><span class="t-cm"># Query rows:</span></div><div><span class="t-v">cur</span><span class="t-c">.execute(</span><span class="t-ok">"SELECT id, name, email FROM users"</span><span class="t-c">)</span></div><div><span class="t-v">rows</span> <span class="t-c">= cur.fetchall()</span></div><div><span class="t-v">for</span> <span class="t-c">row </span><span class="t-v">in</span> <span class="t-c">rows:</span></div><div><span class="t-c"> </span><span class="t-v">print</span><span class="t-c">(row)</span></div><div></div><div><span class="t-v">cur</span><span class="t-c">.close()</span></div><div><span class="t-v">conn</span><span class="t-c">.close()</span></div></div></div>
</div>
<div class="section" aria-labelledby="env-h2"><span class="section-label">Best practice</span><h2 id="env-h2">Store credentials in environment variables</h2>
<p>Never hardcode passwords in your Python code. Use environment variables instead:</p>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">cmd.exe — set env vars</span></div><div class="term-body"><div><span class="t-p">C:\></span> <span class="t-c">setx PGPASSWORD "your_password"</span></div><div><span class="t-p">C:\></span> <span class="t-c">setx PGUSER "postgres"</span></div><div><span class="t-p">C:\></span> <span class="t-c">setx PGDATABASE "mydb"</span></div></div></div>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">Python — read from environment</span></div><div class="term-body"><div><span class="t-v">import</span> <span class="t-c">os, psycopg2</span></div><div></div><div><span class="t-v">conn</span> <span class="t-c">= psycopg2.connect(</span></div><div><span class="t-c"> host=os.environ.get(</span><span class="t-ok">"PGHOST"</span><span class="t-c">, </span><span class="t-ok">"localhost"</span><span class="t-c">),</span></div><div><span class="t-c"> database=os.environ[</span><span class="t-ok">"PGDATABASE"</span><span class="t-c">],</span></div><div><span class="t-c"> user=os.environ[</span><span class="t-ok">"PGUSER"</span><span class="t-c">],</span></div><div><span class="t-c"> password=os.environ[</span><span class="t-ok">"PGPASSWORD"</span><span class="t-c">]</span></div><div><span class="t-c">)</span></div></div></div>
</div>
<div class="section" aria-labelledby="faq-h2"><span class="section-label">FAQ</span><h2 id="faq-h2">Python + PostgreSQL questions</h2><div class="faq">
<div class="faq-item"><details><summary>psycopg2 installation fails on Windows</summary><div class="faq-ans">Use <code>pip install psycopg2-binary</code> instead of <code>pip install psycopg2</code>. The binary version includes precompiled C extensions and does not require Visual Studio or PostgreSQL development headers to install.</div></details></div>
<div class="faq-item"><details><summary>psycopg2 vs psycopg3 — which to use?</summary><div class="faq-ans">psycopg3 (installed as <code>psycopg[binary]</code>) is the modern replacement with native async/await support, better type handling and active development. Use psycopg3 for new projects. Use psycopg2 if you need compatibility with existing code or libraries.</div></details></div>
<div class="faq-item"><details><summary>Connection works in psql but fails in Python</summary><div class="faq-ans">Double-check the host, port, database, user and password parameters. Note that psql defaults to your Windows username if user is not specified, while Python requires explicit credentials. Also verify the pg_hba.conf has a rule allowing connections from localhost.</div></details></div>
<div class="faq-item"><details><summary>Can I use SQLAlchemy with PostgreSQL on Windows?</summary><div class="faq-ans">Yes. Install: <code>pip install sqlalchemy psycopg2-binary</code>. Connection string: <code>postgresql+psycopg2://postgres:password@localhost:5432/mydb</code>. SQLAlchemy works identically on Windows and Linux.</div></details></div>
</div></div>
<div class="cta-banner"><div><h2>Need to connect from another machine?</h2><p>Configure remote access so your Python app can reach the database.</p></div><a href="/postgresql-allow-remote-connections.html" class="btn-white"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5" stroke-linecap="round" stroke-linejoin="round"><path d="M21 15v4a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2v-4"/><polyline points="7 10 12 15 17 10"/><line x1="12" y1="15" x2="12" y2="3"/></svg>Remote connections</a></div>
<div style="margin-bottom:32px"><span class="section-label">Related guides</span><div class="rel-grid"><a href="/postgresql-allow-remote-connections.html" class="rel-card"><div class="rel-title">Remote connections →</div><div class="rel-sub">connect from app server</div></a><a href="/psql-commands-windows.html" class="rel-card"><div class="rel-title">psql commands →</div><div class="rel-sub">test queries</div></a><a href="/pgadmin-windows.html" class="rel-card"><div class="rel-title">pgAdmin 4 →</div><div class="rel-sub">browse data visually</div></a></div></div></main>
<aside class="content-sidebar"><div class="sb-card"><div class="version-badge-label">Latest stable</div><div class="version-badge-val">18.3</div><div class="version-badge-sub">Released May 2026</div></div><div class="sb-card"><div class="sb-card-title">Install & setup</div><ul class="sb-links"><li><a href="/download-windows.html">Download PostgreSQL</a></li><li><a href="/offline-installer.html">Offline installer</a></li><li><a href="/configure-windows-service.html">Windows service</a></li><li><a href="/fix-path-issues.html">Fix PATH / psql</a></li><li><a href="/pgadmin-windows.html">pgAdmin 4</a></li><li><a href="/install-postgresql-windows-server.html">Windows Server</a></li></ul></div><div class="sb-card"><div class="sb-card-title">Configuration</div><ul class="sb-links"><li><a href="/postgresql-config-windows.html">postgresql.conf</a></li><li><a href="/postgresql-port-5432.html">Port 5432</a></li><li><a href="/postgresql-allow-remote-connections.html">Remote connections</a></li><li><a href="/postgresql-password-windows.html">Reset password</a></li></ul></div><div class="sb-card"><div class="sb-card-title">Tools & help</div><ul class="sb-links"><li><a href="/psql-commands-windows.html">psql commands</a></li><li><a href="/pg-dump-restore-windows.html">Backup & restore</a></li><li><a href="/postgresql-python-windows.html" class="cur">Python / psycopg2</a></li><li><a href="/odbc-driver-x64-x86.html">ODBC driver</a></li><li><a href="/postgresql-not-starting-windows.html">Service not starting</a></li><li><a href="/postgresql-uninstall-windows.html">Uninstall</a></li><li><a href="/postgresql-windows-faq.html">FAQ</a></li></ul></div></aside>
</div></div>
<footer class="site-footer"><div class="wrap">
<div class="footer-inner">
<div>
<div class="footer-logo"><div class="footer-logo-mark">Pg</div><span class="footer-logo-name">PostgreSQL for Windows</span></div>
<p class="footer-tagline">Unofficial Windows guide for PostgreSQL — download, install, configure, troubleshoot.</p>
</div>
<div>
<div class="footer-col-title">Install & setup</div>
<ul class="footer-links">
<li><a href="/download-windows.html">Download PostgreSQL</a></li>
<li><a href="/offline-installer.html">Offline installer</a></li>
<li><a href="/configure-windows-service.html">Windows service</a></li>
<li><a href="/fix-path-issues.html">Fix PATH / psql</a></li>
<li><a href="/pgadmin-windows.html">pgAdmin 4</a></li>
</ul>
</div>
<div>
<div class="footer-col-title">Configuration</div>
<ul class="footer-links">
<li><a href="/postgresql-config-windows.html">postgresql.conf</a></li>
<li><a href="/postgresql-port-5432.html">Port 5432</a></li>
<li><a href="/postgresql-allow-remote-connections.html">Remote connections</a></li>
<li><a href="/postgresql-password-windows.html">Reset password</a></li>
<li><a href="/install-postgresql-windows-server.html">Windows Server</a></li>
</ul>
</div>
<div>
<div class="footer-col-title">Tools & help</div>
<ul class="footer-links">
<li><a href="/psql-commands-windows.html">psql commands</a></li>
<li><a href="/pg-dump-restore-windows.html">Backup & restore</a></li>
<li><a href="/postgresql-not-starting-windows.html">Service not starting</a></li>
<li><a href="/postgresql-uninstall-windows.html">Uninstall</a></li>
<li><a href="/postgresql-windows-faq.html">FAQ</a></li>
</ul>
</div>
</div>
<div class="footer-bottom">
<p>Not affiliated with the PostgreSQL Global Development Group. Unofficial community guide.</p>
<p><strong>Affiliate disclosure:</strong> Download links may be partner links.</p>
</div>
</div></footer>
<div class="cookie-bar" id="cookieBar" style="display:none">
<p>We use Google Analytics for anonymous traffic data. <a href="/privacy.html">Privacy policy</a></p>
<div class="cookie-btns"><button class="cookie-accept" id="cookieAccept">Accept</button><button class="cookie-decline" id="cookieDecline">Decline</button></div>
</div>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-MHLW57MR8Q"></script>
<script>
window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments)}
gtag('consent','default',{analytics_storage:'denied',ad_storage:'denied'});
gtag('js',new Date());gtag('config','G-MHLW57MR8Q',{anonymize_ip:true,send_page_view:false});
function grantGA(){gtag('consent','update',{analytics_storage:'granted'});gtag('event','page_view',{page_location:location.href,page_title:document.title})}
document.addEventListener('DOMContentLoaded',function(){
var bar=document.getElementById('cookieBar');
var c=localStorage.getItem('pg_consent');
if(!c){bar.style.display='flex'}else if(c==='1'){grantGA()}
document.getElementById('cookieAccept').addEventListener('click',function(){localStorage.setItem('pg_consent','1');grantGA();bar.style.display='none'});
document.getElementById('cookieDecline').addEventListener('click',function(){localStorage.setItem('pg_consent','0');bar.style.display='none'});
});
</script>
<script type="application/ld+json">{"@context":"https://schema.org","@type":"HowTo","name":"Connect Python to PostgreSQL on Windows","description":"How to install psycopg2 and connect Python to PostgreSQL on Windows.","step":[{"@type":"HowToStep","position":1,"name":"Install driver","text":"pip install psycopg2-binary"},{"@type":"HowToStep","position":2,"name":"Connect","text":"conn = psycopg2.connect(host=localhost, database=mydb, user=postgres, password=pw)"},{"@type":"HowToStep","position":3,"name":"Execute query","text":"cur.execute(SELECT * FROM mytable); rows = cur.fetchall()"}]}</script>
<script>var el=document.getElementById("nl-home");if(el)el.classList.add("active");</script>
</body></html>