Scribd
Upload
2 views3 pages

Postgresql With Python

This document provides a step-by-step guide for connecting to a PostgreSQL database using Python's psycopg2 library. It covers installation, creating and managing tables, inserting, fetching, updating, and deleting data, as well as best practices for using a context manager to handle connections. The emphasis is on using parameterized queries to prevent SQL injection and ensure safe database operations.

Uploaded by

prasanthedagutta31
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views3 pages

Postgresql With Python

This document provides a step-by-step guide for connecting to a PostgreSQL database using Python's psycopg2 library. It covers installation, creating and managing tables, inserting, fetching, updating, and deleting data, as well as best practices for using a context manager to handle connections. The emphasis is on using parameterized queries to prevent SQL injection and ensure safe database operations.

Uploaded by

prasanthedagutta31
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1.

Install Requirements

Install PostgreSQL driver for Python

Most commonly used:

pip install psycopg2-binary

(Production-safe alternative: psycopg2, but needs system dependencies.)

2. Basic Connection to PostgreSQL

import psycopg2

conn = [Link](

host="localhost",

database="mydb",

user="postgres",

password="mypassword",

port=5432

cursor = [Link]()

print("Connected successfully")

3. Create a Table

create_table_query = """

CREATE TABLE IF NOT EXISTS users (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

email VARCHAR(100)

);

"""

[Link](create_table_query)

[Link]()
4. Insert Data (IMPORTANT: use parameterised queries)

insert_query = """

INSERT INTO users (name, email)

VALUES (%s, %s);

"""

[Link](insert_query, ("Twinkle", "twinkle@[Link]"))

[Link]()

✔ Prevents SQL Injection


✔ Safe & recommended

5. Fetch Data

Fetch all rows

[Link]("SELECT * FROM users;")

rows = [Link]()

for row in rows:

print(row)

Fetch one row

[Link]("SELECT * FROM users WHERE id = %s;", (1,))

row = [Link]()

print(row)

6. Update Data

update_query = """

UPDATE users

SET email = %s

WHERE name = %s;

"""
[Link](update_query, ("new@[Link]", "Twinkle"))

[Link]()

7. Delete Data

delete_query = "DELETE FROM users WHERE name = %s;"

[Link](delete_query, ("Twinkle",))

[Link]()

8. Close Connection (Very Important)

[Link]()

[Link]()

9. Best Practice: Use Context Manager

import psycopg2

with [Link](

host="localhost",

database="mydb",

user="postgres",

password="mypassword"

) as conn:

with [Link]() as cursor:

[Link]("SELECT * FROM users;")

print([Link]())

✔ Auto-commit/rollback
✔ Auto-close connection

You might also like