PostgreSQL SQLAlchemy: Overview, Usage, and Examples
PostgreSQL is a powerful open-source relational database, and SQLAlchemy is a popular Python SQL toolkit and Object Relational Mapper (ORM). SQLAlchemy simplifies database interactions by allowing developers to manage database objects and queries in Pythonic code, making it easier to work with relational databases like PostgreSQL in Python applications. With SQLAlchemy, you can create tables, define relationships, and perform CRUD operations more intuitively. Below is a guide on how to use SQLAlchemy with PostgreSQL, including syntax and example snippets.
Syntax:
To connect PostgreSQL and SQLAlchemy, you generally start by defining the connection string and initializing the SQLAlchemy engine. Here is the typical syntax to create the connection:
# Import SQLAlchemy library
from sqlalchemy import create_engine
# PostgreSQL connection string format
engine = create_engine('postgresql://username:password@localhost:5432/mydatabase')
Example Code
1. Connect to PostgreSQL Database
Code:
# Import the required module
from sqlalchemy import create_engine
# Create an engine for PostgreSQL connection
# Replace 'username', 'password', 'localhost', '5432', and 'mydatabase' with actual values
engine = create_engine('postgresql://username:password@localhost:5432/mydatabase')
2. Define a Table Model
Using SQLAlchemy’s ORM, define classes to represent database tables.
Code:
# Import necessary classes from SQLAlchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
# Define a base class for declarative models
Base = declarative_base()
# Define a class for 'users' table with columns
class User(Base):
__tablename__ = 'users'
# Define columns
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
3. Create Tables in the Database
Code:
# Create all tables in the database (this is equivalent to 'CREATE TABLE' in SQL)
Base.metadata.create_all(engine)
4. Insert Data into Table
Code:
# Import sessionmaker for managing database sessions
from sqlalchemy.orm import sessionmaker
# Bind the engine to the session
Session = sessionmaker(bind=engine)
session = Session()
# Add a new user
new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit() # Save changes to the database
5. Query Data from Table
Code:
# Query all users from 'users' table
users = session.query(User).all()
for user in users:
print(user.name, user.age)
Explanation of Code:
Additional Notes:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4