Skip to main content

Apache Doris dialect for SQLAlchemy

Project description

Apache Doris Dialect for SQLAlchemy

This is a fork of sqlalchemy-doris project. Which is in turn - a fork of pydoris

This implementation fixes a bunch of issues with typing. And adds support for sqlalchemy ORM.

Features

  • support SQLAlchemy 2.
  • support pymysql and mysqlclient as driver.
  • support SQLAlchemy table creation
  • support for SQLALchemy ORM
  • convenient DorisBase class for declaring ORM models

Installation

Use

pip install doris-alchemy[pymysql]

for pymysql.

Or

pip install doris-alchemy[mysqldb]

for mysqlclient.

Note doris-alchemy uses pymysql as default connector for compatibility. If both pymysql and mysqlclient are installed, mysqlclient is preferred.

Usage

from sqlalchemy import create_engine

engine = create_engine(f"doris+pymysql://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4")
# or
engine = create_engine(f"doris+mysqldb://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4")

Create Table (Imperative style)

import sqlalchemy as sa
from sqlalchemy import create_engine
from doris_alchemy import datatype
from doris_alchemy import HASH, RANGE

engine = create_engine(f"doris://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4")


metadata_obj = sa.MetaData()
table = Table(
    'dummy_table',
    METADATA,
    Column('id', Integer, primary_key=True),
    Column('name', String(64), nullable=False),
    Column('description', Text),
    Column('date', DateTime),
    
    doris_unique_key=('id'),
    doris_partition_by=RANGE('id'),
    doris_distributed_by=HASH('id'),
    doris_properties={"replication_allocation": "tag.location.default: 1"},
)

table.create(engine)

SQL is

CREATE TABLE dummy_table (
        id INTEGER NOT NULL, 
        name VARCHAR(64) NOT NULL, 
        description TEXT, 
        date DATETIME
)
UNIQUE KEY (`id`)
PARTITION BY RANGE(`id`) ()
DISTRIBUTED BY HASH(`id`) BUCKETS auto
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
)

Create Table (Declarative style / ORM)

from sqlalchemy import create_engine
from doris_alchemy import datatype, DorisBase
from doris_alchemy import HASH, RANGE

engine = create_engine(f"doris://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4")

class Dummy(DorisBase):
    __tablename__ = 'dummy_two'
    
    id:             Mapped[int] = mapped_column(BigInteger, primary_key=True)
    name:           Mapped[str] = mapped_column(String(127))
    description:    Mapped[str]
    date:           Mapped[datetime]
    
    __table_args__ = {
        'doris_properties': {"replication_allocation": "tag.location.default: 1"}
        }
    doris_unique_key = 'id'
    doris_distributed_by = HASH('id')
    doris_partition_by = RANGE('id')


DorisBase.metadata.create_all(engine)

SQL is

CREATE TABLE dummy_two (
        id BIGINT NOT NULL, 
        name VARCHAR(127) NOT NULL, 
        description TEXT NOT NULL, 
        date DATETIME NOT NULL
)
UNIQUE KEY (`id`)
PARTITION BY RANGE(`id`) ()
DISTRIBUTED BY HASH(`id`) BUCKETS auto
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
)

Insertin and selecting

from sqlalchemy.orm import Session
from sqlalchemy import select, insert, create_engine
from datetime import datetime

engine = create_engine(f"doris+mysqldb://{USER}:{PWD}@{HOST}:{PORT}/{DB}")

row = {
        'id': 0,
        'name': 'Airbus',
        'description': 'Construction bureau',
        'date': datetime(2024, 2, 10)
    }
    
with Session(engine) as s:
    q = insert(Dummy).values([row])
    s.execute(q)
    sel = select(Dummy)
    res = s.execute(sel)
    print(list(res))

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

doris_alchemy-0.2.3.tar.gz (19.2 kB view hashes)

Uploaded Source

Built Distribution

doris_alchemy-0.2.3-py3-none-any.whl (18.3 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page