# psqlpy-sqlalchemy
[](https://img.shields.io/badge/Support-Ukraine-FFD500?style=flat&labelColor=005BBB)
[](https://github.com/h0rn3t/psqlpy-sqlalchemy/actions/workflows/ci.yml)
[](https://codecov.io/gh/h0rn3t/psqlpy-sqlalchemy)[](https://opensource.org/licenses/MIT)
[](https://pypi.org/project/psqlpy-sqlalchemy/)
[](https://pyup.io/repos/github/h0rn3t/psqlpy-sqlalchemy/)
SQLAlchemy dialect for [psqlpy](https://github.com/qaspen-python/psqlpy) - a fast PostgreSQL driver for Python.
## Overview
This package provides a SQLAlchemy dialect that allows you to use psqlpy as the underlying PostgreSQL driver. psqlpy is a high-performance PostgreSQL driver built on top of Rust's tokio-postgres, offering excellent performance characteristics.
## Features
- **High Performance**: Built on psqlpy's Rust-based PostgreSQL driver
- **SQLAlchemy 2.0+ Compatible**: Full support for modern SQLAlchemy features
- **SQLModel Compatible**: Works with SQLModel for Pydantic integration
- **DBAPI 2.0 Compliant**: Standard Python database interface
- **Connection Pooling**: Leverages psqlpy's built-in connection pooling
- **Transaction Support**: Full transaction and savepoint support
- **SSL Support**: Configurable SSL connections
- **Type Support**: Native support for PostgreSQL data types
## Installation
```bash
pip install psqlpy-sqlalchemy
```
This will automatically install the required dependencies:
- `sqlalchemy>=2.0.0`
- `psqlpy>=0.11.0`
## Usage
### Basic Connection
```python
from sqlalchemy import create_engine
# Basic connection
engine = create_engine("postgresql+psqlpy://user:password@localhost/dbname")
# With connection parameters
engine = create_engine(
"postgresql+psqlpy://user:password@localhost:5432/dbname"
"?sslmode=require&application_name=myapp"
)
```
### Connection URL Parameters
The dialect supports standard PostgreSQL connection parameters:
- `host` - Database host
- `port` - Database port (default: 5432)
- `username` - Database username
- `password` - Database password
- `database` - Database name
- `sslmode` - SSL mode (disable, allow, prefer, require, verify-ca, verify-full)
- `application_name` - Application name for connection tracking
- `connect_timeout` - Connection timeout in seconds
### Example Usage
```python
from sqlalchemy import create_engine, text, MetaData, Table, Column, Integer, String
from sqlalchemy.orm import sessionmaker
# Create engine
engine = create_engine("postgresql+psqlpy://user:password@localhost/testdb")
# Test connection
with engine.connect() as conn:
result = conn.execute(text("SELECT version()"))
print(result.fetchone())
# Using ORM
Session = sessionmaker(bind=engine)
session = Session()
# Define a table
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('email', String(100))
)
# Create table
metadata.create_all(engine)
# Insert data
with engine.connect() as conn:
conn.execute(users.insert().values(name='John', email='john@example.com'))
conn.commit()
# Query data
with engine.connect() as conn:
result = conn.execute(users.select())
for row in result:
print(row)
```
### SQLModel Usage
```python
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
# Define a SQLModel model
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None
# Create engine with psqlpy dialect
engine = create_engine("postgresql+psqlpy://user:password@localhost/testdb")
# Create tables
SQLModel.metadata.create_all(engine)
# Insert data
with Session(engine) as session:
hero = Hero(name="Deadpond", secret_name="Dive Wilson", age=30)
session.add(hero)
session.commit()
session.refresh(hero)
print(f"Created hero: {hero.name} with id {hero.id}")
# Query data
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Deadpond")
hero = session.exec(statement).first()
print(f"Found hero: {hero.name}, secret identity: {hero.secret_name}")
```
### Async Usage
While this dialect provides a synchronous interface, psqlpy itself is async-native. For async SQLAlchemy usage, you would typically use SQLAlchemy's async features:
```python
from sqlalchemy.ext.asyncio import create_async_engine
# Note: This would require an async version of the dialect
# The current implementation is synchronous
engine = create_engine("postgresql+psqlpy://user:password@localhost/dbname")
```
## Configuration
### SSL Configuration
```python
# Require SSL
engine = create_engine("postgresql+psqlpy://user:pass@host/db?sslmode=require")
# SSL with custom CA file
engine = create_engine("postgresql+psqlpy://user:pass@host/db?sslmode=verify-ca&ca_file=/path/to/ca.pem")
```
### Connection Timeouts
```python
# Set connection timeout
engine = create_engine("postgresql+psqlpy://user:pass@host/db?connect_timeout=30")
```
## Development
### Setting up Development Environment
```bash
# Clone the repository
git clone https://github.com/your-username/psqlpy-sqlalchemy.git
cd psqlpy-sqlalchemy
# Create virtual environment
python -m venv .venv
source .venv/bin/activate # On Windows: .venv\Scripts\activate
# Install in development mode
pip install -e .
# Install development dependencies
pip install -e ".[dev]"
```
### Running Tests
```bash
pytest tests/
```
### Testing with Real Database
To test with a real PostgreSQL database:
```python
from sqlalchemy import create_engine, text
# Replace with your actual database credentials
engine = create_engine("postgresql+psqlpy://user:password@localhost/testdb")
with engine.connect() as conn:
result = conn.execute(text("SELECT 1"))
print("Connection successful:", result.fetchone())
```
## Architecture
The dialect consists of several key components:
- **`PsqlpyDialect`**: Main dialect class that inherits from SQLAlchemy's `DefaultDialect`
- **`PsqlpyDBAPI`**: DBAPI 2.0 compatible interface wrapper
- **`PsqlpyConnection`**: Connection wrapper that adapts psqlpy connections to DBAPI interface
- **`PsqlpyCursor`**: Cursor implementation for executing queries and fetching results
## Limitations
- **Synchronous Only**: Current implementation provides synchronous interface only
- **Basic Transaction Support**: Advanced transaction features may need additional implementation
- **Limited Error Mapping**: psqlpy exceptions are currently mapped to generic DBAPI exceptions
## Contributing
Contributions are welcome! Please feel free to submit issues, feature requests, or pull requests.
### Development Guidelines
1. Follow PEP 8 style guidelines
2. Add tests for new features
3. Update documentation as needed
4. Ensure compatibility with SQLAlchemy 2.0+
## License
This project is licensed under the MIT License - see the LICENSE file for details.
## Related Projects
- [psqlpy](https://github.com/qaspen-python/psqlpy) - The underlying PostgreSQL driver
- [SQLAlchemy](https://www.sqlalchemy.org/) - The Python SQL toolkit and ORM
- [SQLModel](https://sqlmodel.tiangolo.com/) - SQLAlchemy-based ORM with Pydantic validation
## Changelog
### 0.1.0 (2025-07-21)
- Initial release
- Basic SQLAlchemy dialect implementation
- DBAPI 2.0 compatible interface
- SQLModel compatibility
- Connection string parsing
- Basic SQL compilation support
- Transaction support
- SSL configuration support
Raw data
{
"_id": null,
"home_page": null,
"name": "psqlpy-sqlalchemy",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": "postgresql, psqlpy, sqlalchemy, database, async",
"author": "psqlpy-sqlalchemy contributors",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/ef/37/8aa9c2e6b0689eb2e84eabd611698730e2f32c504a3b08547fc609d37779/psqlpy_sqlalchemy-0.1.0a9.tar.gz",
"platform": null,
"description": "# psqlpy-sqlalchemy\n[](https://img.shields.io/badge/Support-Ukraine-FFD500?style=flat&labelColor=005BBB)\n[](https://github.com/h0rn3t/psqlpy-sqlalchemy/actions/workflows/ci.yml)\n[](https://codecov.io/gh/h0rn3t/psqlpy-sqlalchemy)[](https://opensource.org/licenses/MIT)\n[](https://pypi.org/project/psqlpy-sqlalchemy/)\n[](https://pyup.io/repos/github/h0rn3t/psqlpy-sqlalchemy/)\n\nSQLAlchemy dialect for [psqlpy](https://github.com/qaspen-python/psqlpy) - a fast PostgreSQL driver for Python.\n\n\n\n## Overview\n\nThis package provides a SQLAlchemy dialect that allows you to use psqlpy as the underlying PostgreSQL driver. psqlpy is a high-performance PostgreSQL driver built on top of Rust's tokio-postgres, offering excellent performance characteristics.\n\n## Features\n\n- **High Performance**: Built on psqlpy's Rust-based PostgreSQL driver\n- **SQLAlchemy 2.0+ Compatible**: Full support for modern SQLAlchemy features\n- **SQLModel Compatible**: Works with SQLModel for Pydantic integration\n- **DBAPI 2.0 Compliant**: Standard Python database interface\n- **Connection Pooling**: Leverages psqlpy's built-in connection pooling\n- **Transaction Support**: Full transaction and savepoint support\n- **SSL Support**: Configurable SSL connections\n- **Type Support**: Native support for PostgreSQL data types\n\n## Installation\n\n```bash\npip install psqlpy-sqlalchemy\n```\n\nThis will automatically install the required dependencies:\n- `sqlalchemy>=2.0.0`\n- `psqlpy>=0.11.0`\n\n## Usage\n\n### Basic Connection\n\n```python\nfrom sqlalchemy import create_engine\n\n# Basic connection\nengine = create_engine(\"postgresql+psqlpy://user:password@localhost/dbname\")\n\n# With connection parameters\nengine = create_engine(\n \"postgresql+psqlpy://user:password@localhost:5432/dbname\"\n \"?sslmode=require&application_name=myapp\"\n)\n```\n\n### Connection URL Parameters\n\nThe dialect supports standard PostgreSQL connection parameters:\n\n- `host` - Database host\n- `port` - Database port (default: 5432)\n- `username` - Database username\n- `password` - Database password\n- `database` - Database name\n- `sslmode` - SSL mode (disable, allow, prefer, require, verify-ca, verify-full)\n- `application_name` - Application name for connection tracking\n- `connect_timeout` - Connection timeout in seconds\n\n### Example Usage\n\n```python\nfrom sqlalchemy import create_engine, text, MetaData, Table, Column, Integer, String\nfrom sqlalchemy.orm import sessionmaker\n\n# Create engine\nengine = create_engine(\"postgresql+psqlpy://user:password@localhost/testdb\")\n\n# Test connection\nwith engine.connect() as conn:\n result = conn.execute(text(\"SELECT version()\"))\n print(result.fetchone())\n\n# Using ORM\nSession = sessionmaker(bind=engine)\nsession = Session()\n\n# Define a table\nmetadata = MetaData()\nusers = Table('users', metadata,\n Column('id', Integer, primary_key=True),\n Column('name', String(50)),\n Column('email', String(100))\n)\n\n# Create table\nmetadata.create_all(engine)\n\n# Insert data\nwith engine.connect() as conn:\n conn.execute(users.insert().values(name='John', email='john@example.com'))\n conn.commit()\n\n# Query data\nwith engine.connect() as conn:\n result = conn.execute(users.select())\n for row in result:\n print(row)\n```\n\n### SQLModel Usage\n\n```python\nfrom typing import Optional\nfrom sqlmodel import Field, Session, SQLModel, create_engine, select\n\n# Define a SQLModel model\nclass Hero(SQLModel, table=True):\n id: Optional[int] = Field(default=None, primary_key=True)\n name: str\n secret_name: str\n age: Optional[int] = None\n\n# Create engine with psqlpy dialect\nengine = create_engine(\"postgresql+psqlpy://user:password@localhost/testdb\")\n\n# Create tables\nSQLModel.metadata.create_all(engine)\n\n# Insert data\nwith Session(engine) as session:\n hero = Hero(name=\"Deadpond\", secret_name=\"Dive Wilson\", age=30)\n session.add(hero)\n session.commit()\n session.refresh(hero)\n print(f\"Created hero: {hero.name} with id {hero.id}\")\n\n# Query data\nwith Session(engine) as session:\n statement = select(Hero).where(Hero.name == \"Deadpond\")\n hero = session.exec(statement).first()\n print(f\"Found hero: {hero.name}, secret identity: {hero.secret_name}\")\n```\n\n### Async Usage\n\nWhile this dialect provides a synchronous interface, psqlpy itself is async-native. For async SQLAlchemy usage, you would typically use SQLAlchemy's async features:\n\n```python\nfrom sqlalchemy.ext.asyncio import create_async_engine\n\n# Note: This would require an async version of the dialect\n# The current implementation is synchronous\nengine = create_engine(\"postgresql+psqlpy://user:password@localhost/dbname\")\n```\n\n## Configuration\n\n### SSL Configuration\n\n```python\n# Require SSL\nengine = create_engine(\"postgresql+psqlpy://user:pass@host/db?sslmode=require\")\n\n# SSL with custom CA file\nengine = create_engine(\"postgresql+psqlpy://user:pass@host/db?sslmode=verify-ca&ca_file=/path/to/ca.pem\")\n```\n\n### Connection Timeouts\n\n```python\n# Set connection timeout\nengine = create_engine(\"postgresql+psqlpy://user:pass@host/db?connect_timeout=30\")\n```\n\n## Development\n\n### Setting up Development Environment\n\n```bash\n# Clone the repository\ngit clone https://github.com/your-username/psqlpy-sqlalchemy.git\ncd psqlpy-sqlalchemy\n\n# Create virtual environment\npython -m venv .venv\nsource .venv/bin/activate # On Windows: .venv\\Scripts\\activate\n\n# Install in development mode\npip install -e .\n\n# Install development dependencies\npip install -e \".[dev]\"\n```\n\n### Running Tests\n\n```bash\npytest tests/\n```\n\n### Testing with Real Database\n\nTo test with a real PostgreSQL database:\n\n```python\nfrom sqlalchemy import create_engine, text\n\n# Replace with your actual database credentials\nengine = create_engine(\"postgresql+psqlpy://user:password@localhost/testdb\")\n\nwith engine.connect() as conn:\n result = conn.execute(text(\"SELECT 1\"))\n print(\"Connection successful:\", result.fetchone())\n```\n\n## Architecture\n\nThe dialect consists of several key components:\n\n- **`PsqlpyDialect`**: Main dialect class that inherits from SQLAlchemy's `DefaultDialect`\n- **`PsqlpyDBAPI`**: DBAPI 2.0 compatible interface wrapper\n- **`PsqlpyConnection`**: Connection wrapper that adapts psqlpy connections to DBAPI interface\n- **`PsqlpyCursor`**: Cursor implementation for executing queries and fetching results\n\n## Limitations\n\n- **Synchronous Only**: Current implementation provides synchronous interface only\n- **Basic Transaction Support**: Advanced transaction features may need additional implementation\n- **Limited Error Mapping**: psqlpy exceptions are currently mapped to generic DBAPI exceptions\n\n## Contributing\n\nContributions are welcome! Please feel free to submit issues, feature requests, or pull requests.\n\n### Development Guidelines\n\n1. Follow PEP 8 style guidelines\n2. Add tests for new features\n3. Update documentation as needed\n4. Ensure compatibility with SQLAlchemy 2.0+\n\n## License\n\nThis project is licensed under the MIT License - see the LICENSE file for details.\n\n## Related Projects\n\n- [psqlpy](https://github.com/qaspen-python/psqlpy) - The underlying PostgreSQL driver\n- [SQLAlchemy](https://www.sqlalchemy.org/) - The Python SQL toolkit and ORM\n- [SQLModel](https://sqlmodel.tiangolo.com/) - SQLAlchemy-based ORM with Pydantic validation\n\n## Changelog\n\n### 0.1.0 (2025-07-21)\n\n- Initial release\n- Basic SQLAlchemy dialect implementation\n- DBAPI 2.0 compatible interface\n- SQLModel compatibility\n- Connection string parsing\n- Basic SQL compilation support\n- Transaction support\n- SSL configuration support\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "SQLAlchemy dialect for psqlpy PostgreSQL driver",
"version": "0.1.0a9",
"project_urls": {
"Homepage": "https://github.com/h0rn3t/psqlpy-sqlalchemy",
"Issues": "https://github.com/h0rn3t/psqlpy-sqlalchemy/issues",
"Repository": "https://github.com/h0rn3t/psqlpy-sqlalchemy"
},
"split_keywords": [
"postgresql",
" psqlpy",
" sqlalchemy",
" database",
" async"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "1e6fffd13fe158441796acc0eb26887fe1f3edc5527a518b109f662004420be1",
"md5": "133288f27cbc2cded398309151a29c9a",
"sha256": "2c429131c1f8acd4b89cdb512ac827088cb573b56d7f7150f80ff63574598f73"
},
"downloads": -1,
"filename": "psqlpy_sqlalchemy-0.1.0a9-py3-none-any.whl",
"has_sig": false,
"md5_digest": "133288f27cbc2cded398309151a29c9a",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 14906,
"upload_time": "2025-07-22T21:14:08",
"upload_time_iso_8601": "2025-07-22T21:14:08.160712Z",
"url": "https://files.pythonhosted.org/packages/1e/6f/ffd13fe158441796acc0eb26887fe1f3edc5527a518b109f662004420be1/psqlpy_sqlalchemy-0.1.0a9-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "ef378aa9c2e6b0689eb2e84eabd611698730e2f32c504a3b08547fc609d37779",
"md5": "41fd36b376b67d0f8d28a72b15e94a3e",
"sha256": "338d17098c392ae5fa061cc161233a8f1832af3f83640554fdc4350e0073f51a"
},
"downloads": -1,
"filename": "psqlpy_sqlalchemy-0.1.0a9.tar.gz",
"has_sig": false,
"md5_digest": "41fd36b376b67d0f8d28a72b15e94a3e",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 29952,
"upload_time": "2025-07-22T21:14:09",
"upload_time_iso_8601": "2025-07-22T21:14:09.236168Z",
"url": "https://files.pythonhosted.org/packages/ef/37/8aa9c2e6b0689eb2e84eabd611698730e2f32c504a3b08547fc609d37779/psqlpy_sqlalchemy-0.1.0a9.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-07-22 21:14:09",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "h0rn3t",
"github_project": "psqlpy-sqlalchemy",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "psqlpy-sqlalchemy"
}
JSON
