psycopg2

PostgreSQL database adapter for the Python programming language

3,308

502

3,308

View on GitHub

Top Related Projects

aiopg

1,391

aiopg is a library for accessing a PostgreSQL database from the asyncio

peewee

11,053

a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb

Quick Overview

Psycopg2 is a popular PostgreSQL adapter for Python. It's a robust and high-performance library that allows Python applications to communicate with PostgreSQL databases efficiently. Psycopg2 is designed to be thread-safe and compliant with Python DB API 2.0 specifications.

Pros

High performance and efficient database operations
Full support for Python DB API 2.0 specifications
Thread-safe implementation
Extensive PostgreSQL-specific features and data types support

Cons

Requires compilation of C extensions, which can be challenging on some systems
Not compatible with asyncio-based applications (use psycopg3 for async support)
Learning curve for advanced features and optimizations
Occasional installation issues on Windows systems

Code Examples

Connecting to a database and executing a simple query:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres password=secret")
cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE active = true")
rows = cur.fetchall()
for row in rows:
    print(row)
cur.close()
conn.close()

Using parameterized queries to prevent SQL injection:

cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("John Doe", "john@example.com"))
conn.commit()

Working with transactions:

try:
    with conn:
        with conn.cursor() as cur:
            cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
            cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
except psycopg2.Error as e:
    print(f"An error occurred: {e}")

Using server-side cursors for large result sets:

with conn.cursor(name="large_result") as cur:
    cur.itersize = 10000
    cur.execute("SELECT * FROM large_table")
    for row in cur:
        process_row(row)

Getting Started

To get started with psycopg2, follow these steps:

Install psycopg2:
```
pip install psycopg2
```
Import psycopg2 in your Python script:
```
import psycopg2
```

Establish a connection to your PostgreSQL database:

conn = psycopg2.connect(
    dbname="your_database",
    user="your_username",
    password="your_password",
    host="your_host",
    port="your_port"
)

Create a cursor and execute SQL queries:

cur = conn.cursor()
cur.execute("SELECT * FROM your_table")
rows = cur.fetchall()

Don't forget to close the cursor and connection when done:
```
cur.close()
conn.close()
```

Competitor Comparisons

sqlalchemy

9,387

The Database Toolkit for Python

Pros of SQLAlchemy

Provides a high-level ORM abstraction for database operations
Supports multiple database backends, not limited to PostgreSQL
Offers powerful query construction and optimization features

Cons of SQLAlchemy

Steeper learning curve due to its extensive feature set
Can be slower for simple queries compared to direct SQL execution
Larger overhead and increased memory usage

Code Comparison

SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
cur.execute("CREATE TABLE users (id serial PRIMARY KEY, name varchar);")
conn.commit()

SQLAlchemy provides a more abstract and Pythonic approach to database operations, while Psycopg2 offers direct SQL execution for PostgreSQL databases. SQLAlchemy's ORM capabilities make it easier to work with complex data models, but Psycopg2's simplicity can be advantageous for straightforward PostgreSQL interactions.

PyMySQL

7,638

MySQL client library for Python

Pros of PyMySQL

Pure Python implementation, making it easier to install and use across different platforms
Supports both Python 2 and Python 3
Lightweight and has fewer dependencies

Cons of PyMySQL

Generally slower performance compared to psycopg2
Limited support for advanced PostgreSQL-specific features
Smaller community and fewer resources available

Code Comparison

PyMySQL:

import pymysql

conn = pymysql.connect(host='localhost', user='user', password='password', database='db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
results = cursor.fetchall()

psycopg2:

import psycopg2

conn = psycopg2.connect(host='localhost', user='user', password='password', dbname='db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
results = cursor.fetchall()

The basic usage of both libraries is quite similar, with minor differences in connection parameters and import statements. However, psycopg2 offers more advanced features and optimizations for PostgreSQL, while PyMySQL is specifically designed for MySQL databases.

aiopg

1,391

aiopg is a library for accessing a PostgreSQL database from the asyncio

Pros of aiopg

Designed for asynchronous programming with Python's asyncio
Supports connection pooling out of the box
Allows for non-blocking database operations, improving scalability

Cons of aiopg

Limited to PostgreSQL databases only
Requires understanding of asynchronous programming concepts
May have a steeper learning curve for developers new to asyncio

Code Comparison

psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
cur.execute("SELECT * FROM users")
results = cur.fetchall()

aiopg:

import asyncio
import aiopg

async def fetch_users():
    async with aiopg.create_pool("dbname=test user=postgres") as pool:
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT * FROM users")
                return await cur.fetchall()

results = asyncio.run(fetch_users())

The main difference is that aiopg uses async/await syntax and works with asyncio, while psycopg2 uses traditional synchronous code. aiopg also demonstrates built-in connection pooling in this example.

records

7,142

SQL for Humans™

Pros of Records

Simplified API for database operations
Automatic result set to dictionary conversion
Built-in support for multiple database types

Cons of Records

Less fine-grained control over database operations
Limited support for advanced PostgreSQL features
Smaller community and fewer updates compared to psycopg2

Code Comparison

Records:

import records

db = records.Database('postgresql://user:pass@localhost/dbname')
rows = db.query('SELECT * FROM users')
for row in rows:
    print(row.name, row.email)

psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=dbname user=user password=pass")
cur = conn.cursor()
cur.execute("SELECT * FROM users")
for row in cur.fetchall():
    print(row[0], row[1])

Records provides a more straightforward API for common database operations, while psycopg2 offers more control and PostgreSQL-specific features. Records is better suited for quick prototyping and simple database interactions, whereas psycopg2 is ideal for complex PostgreSQL applications requiring fine-tuned performance and advanced features.

peewee

11,053

a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb

Pros of Peewee

Higher-level ORM abstraction, simplifying database operations
Supports multiple database backends (SQLite, MySQL, PostgreSQL)
Lightweight and easy to learn, with a Pythonic API

Cons of Peewee

Less flexible for complex SQL queries compared to raw SQL
May have performance overhead for large-scale applications
Smaller community and ecosystem compared to Psycopg2

Code Comparison

Peewee:

from peewee import *

db = PostgresqlDatabase('mydb', user='user', password='password')

class User(Model):
    name = CharField()
    email = CharField(unique=True)

    class Meta:
        database = db

User.create(name='John', email='john@example.com')

Psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=mydb user=user password=password")
cur = conn.cursor()

cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
            ("John", "john@example.com"))
conn.commit()

Peewee provides a more abstract and Pythonic approach to database operations, while Psycopg2 offers lower-level control and potentially better performance for complex queries. The choice between them depends on the specific requirements of your project.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

psycopg2 - Python-PostgreSQL Database Adapter

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety (several threads can share the same connection). It was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a large number of concurrent "INSERT"s or "UPDATE"s.

Psycopg 2 is mostly implemented in C as a libpq wrapper, resulting in being both efficient and secure. It features client-side and server-side cursors, asynchronous communication and notifications, "COPY TO/COPY FROM" support. Many Python types are supported out-of-the-box and adapted to matching PostgreSQL data types; adaptation can be extended and customized thanks to a flexible objects adaptation system.

Psycopg 2 is both Unicode and Python 3 friendly.

.. Note::

The psycopg2 package is still widely used and actively maintained, but it
is not expected to receive new features.

`Psycopg 3`__ is the evolution of psycopg2 and is where `new features are
being developed`__: if you are starting a new project you should probably
start from 3!

.. __: https://pypi.org/project/psycopg/
.. __: https://www.psycopg.org/psycopg3/docs/index.html

Documentation

Documentation is included in the doc directory and is available online__.

.. __: https://www.psycopg.org/docs/

For any other resource (source code repository, bug tracker, mailing list) please check the project homepage__.

.. __: https://psycopg.org/

Installation

Building Psycopg requires a few prerequisites (a C compiler, some development packages): please check the install_ and the faq_ documents in the doc dir or online for the details.

If prerequisites are met, you can install psycopg like any other Python package, using pip to download it from PyPI_::

$ pip install psycopg2

or using setup.py if you have downloaded the source package locally::

$ python setup.py build
$ sudo python setup.py install

You can also obtain a stand-alone package, not requiring a compiler or external libraries, by installing the psycopg2-binary_ package from PyPI::

$ pip install psycopg2-binary

The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources.

.. _PyPI: https://pypi.org/project/psycopg2/ .. _psycopg2-binary: https://pypi.org/project/psycopg2-binary/ .. _install: https://www.psycopg.org/docs/install.html#install-from-source .. _faq: https://www.psycopg.org/docs/faq.html#faq-compile

:Linux/OSX: |gh-actions| :Windows: |appveyor|

.. |gh-actions| image:: https://github.com/psycopg/psycopg2/actions/workflows/tests.yml/badge.svg :target: https://github.com/psycopg/psycopg2/actions/workflows/tests.yml :alt: Linux and OSX build status

.. |appveyor| image:: https://ci.appveyor.com/api/projects/status/github/psycopg/psycopg2?branch=master&svg=true :target: https://ci.appveyor.com/project/psycopg/psycopg2/branch/master :alt: Windows build status

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot