Unlocking Database Mastery with Psycopg3: A Comprehensive Guide
Written on
Chapter 1: Introduction to Psycopg
Psycopg serves as a widely-used adapter for the PostgreSQL database. While most developers utilize high-level libraries such as SQLAlchemy for simplified database interactions, understanding Psycopg can greatly enhance your programming skills. Familiarity with Psycopg will reveal many parallels with SQLAlchemy, making the latter easier to grasp. On the other hand, if you require fine-tuned control over your database interactions, or if you prefer writing raw queries typical in data science, Psycopg is an excellent choice.
In this guide, we will utilize Supabase, but feel free to use your local PostgreSQL instance or a Docker setup.
Section 1.1: Establishing a Connection and Creating a Table
Utilizing a context manager simplifies connection handling, as it automatically takes care of closing the connection. A pivotal concept in this process is the cursor, which allows interaction with the database and behaves like a generator when fetching data. For instance, executing a query that retrieves ten rows allows for one-by-one fetching.
import psycopg
from utils import connection_string
# Establish a connection to the PostgreSQL database using the provided connection string
with psycopg.connect(connection_string) as connection:
# Open a cursor within the context of the established connection
with connection.cursor() as cursor:
# Execute a SQL statement to create a new table named 'users' with specified columns
cursor.execute("""
CREATE TABLE users (
id SERIAL PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL
);
""")
# Commit the transaction to make the changes permanent in the database
connection.commit()
Section 1.2: Inserting Data into the Table
When it comes to adding data, you can choose between using tuples or dictionaries. I prefer dictionaries as they allow for flexible data organization without relying on the order of values. This flexibility is crucial, especially when retrieving newly created IDs upon insertion.
To accomplish this with a single row, simply add a RETURNING clause to your query, and use fetchone to get the generated ID.
data = ("tomas", "svojanovsky")
cursor.execute("""
INSERT INTO
users (first_name, last_name)VALUES (%s, %s)
RETURNING id
""", data)
print(cursor.fetchone()) # Output: (33,)
Section 1.3: Inserting Multiple Rows
When inserting multiple rows using the executemany method, fetching all newly created IDs poses a challenge. You might only retrieve one ID if not handled correctly. The solution? Utilize the nextset method.
data = [
("Christine", "Brown"),
("John", "Doe"),
]
cursor.executemany("""
INSERT INTO
users (first_name, last_name)VALUES (%s, %s)
RETURNING id
""", data)
results = []
results.append(cursor.fetchone())
while cursor.nextset():
results.append(cursor.fetchone())
print(results) # Output: [(38,), (39,)]
Section 1.4: Inserting with Dictionaries
For those who prefer using dictionaries, the query structure requires minor adjustments. Instead of the %s placeholder, you will specify the key names from the dictionary.
data = [
{"name": "Tomas", "last_name": "Svojanovsky"},
{"name": "John", "last_name": "Doe"},
]
cursor.executemany("""
INSERT INTO
users (first_name, last_name)VALUES (%(name)s, %(last_name)s)
RETURNING id
""", data)
If you found this guide informative and wish to join our expanding community, please hit the follow button. Your insights and comments are always welcome, so don't hesitate to share!
Thank you for being a part of the In Plain English community! Before leaving, consider following us on X, LinkedIn, YouTube, and Discord. Explore more content across our other platforms: Stackademic, CoFeed, Venture, and Cubed. For additional insights, visit PlainEnglish.io.